-im server option

Runs the database server in memory, reducing or eliminating writes to disk.

Syntax

{ dbsrv11 | dbeng11 } -im { c | nw } ...

Applies to

All operating systems and database servers.

Separately licensed component required

In-memory mode requires a separate license. See Separately licensed components.

Remarks

This feature is most useful on systems with a large amount of available memory, typically enough to hold all the database files within the cache. There are two in-memory modes available:

Checkpoint only (-im c) When running in checkpoint-only mode, the database server does not use a transaction log, so you cannot recover to the most recent committed transaction. However, because the checkpoint log is enabled, the database can be recovered to the most recent checkpoint. Normally when you run a database without a transaction log, the database server still performs a checkpoint on a commit, which affects performance. However, when you run the database server in checkpoint only mode, the database server does not perform a checkpoint after each commit.
This mode is useful in applications where increased performance is desirable, and the loss of committed transactions after the most recent checkpoint is acceptable.

The following restrictions apply when running in checkpoint-only mode:
1. There is no transaction log.
2. There is no temporary file.
3. Checkpoints are allowed both on demand and at the database server's normal checkpoint frequency.
4. Dirty pages are flushed to disk only on checkpoint.
Never write (-im nw) When running in never write mode, committed transactions are not written to the database file on disk. All changes are lost if the database is shut down or crashes, so database files are always left in their original state. Requests to extend or create new dbspaces are allowed, but the changes are not reflected in the database files. You can create and use new dbspaces, but they are not written to disk. Making a backup in never write mode is not useful because any changes to the system dbspace are not written to the file.
The following restrictions apply when running in never write mode:
1. There is no transaction log.
2. There is no checkpoint log.
3. There is no temporary file.
4. Dirty database pages are never flushed to disk.
5. The original database file is never modified.

Because changes are never written to the original database files, if a persistent copy of current database contents is required, you must use the dbunload utility or the UNLOAD TABLE statement. You can also use SQL queries to retrieve the changes, but you must then manually write these changes to the database file.

The performance benefits gained from in-memory mode depend on the application workload and the speed of the I/O subsystem. The largest performance gains are seen in applications that insert or update large amounts of data, and in applications that commit and checkpoint frequently.

Often, performance of the in-memory modes is as good as, or better than the performance of using transactional global temporary tables. The smallest performance improvement may be seen with applications that predominately query the database. In general, when using in-memory mode, the best performance can be achieved by pre-growing the cache to an amount large enough to hold the full expected contents of the database files. This eliminates much of the overhead involved in growing the cache in increments while the application is running.

Caution

Since pages are not flushed from cache in never write mode, it is possible to exhaust the available cache if the amount of data in the database grows too large. When this happens, SQL Anywhere issues an error and stops processing requests. For this reason, never write mode should be used with caution, and always with a cache large enough to hold the expected complete working set of pages that an application may use. Since checkpoints continue to occur in the "checkpoint only" mode, there is a reduced risk of the server running out of available cache as compared to the "never write" mode.