Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 2, 2010

In-Memory Database option for Sybase Adaptive Server Enterprise - Page 2

By Staff

Setting up IMDB

Setting up an IMDB is remarkably similar to setting up any other database, with the additional step of predefining the cache.

1)  Define the cache in which the database will exist

2)  Create the devices in which the database will reside

3)  Create the database

Defining the database cache

The sp_cacheconfig stored procedure has been modified to accept an additional parameter, in order to define the cache as an IMDB cache (note there’s no buffer replacement strategy here, as there is no FIFO queue in this database).


sp_cacheconfig ‘Cache_Name’, ‘Cache Size’, ‘inmemory_storage’


sp_cacheconfig ‘Lookup DB Cache’, ‘1G’, ‘inmemory_storage’

Creating the device

The disk init syntax has an additional type, to specify that it’s an in-memory device.


disk init name= ‘LookUpDBDevice’, physname = ‘Lookup DB Cache’, 
	size = ‘1G’, type = ‘inmemory’

It is just like creating any other device; you’re just pointing the server towards a different resource from which to allocate pages.

Creating the database

Database creation is similar, but there are a few additional options. Since the IMDB is recreated (in memory) every time the server starts, it has to come from a template. The default template is the model database (just like every other database). You can change this default to another template, as we’re going to do below. Also as with model, any database options follow the database. Note further that, as with other ASE objects, once a database becomes dependant on the template, the template can’t be dropped until the dependant database is dropped.


create inmemory database LookUpDB use ReferenceDB as template on LookUpDBDevice = ‘1G’, 
log on LookUpLogDevice = ‘100M’ with durability = no_recovery

“inmemory” specifies that this database is not disk resident

“Durability = no_recovery” specifies that the database will be recreated at startup.

“use ReferenceDB as template” specifies that the contents of the database will be loaded from ReferenceDB instead of from model.

This is a great way to preload a database into cache. We’re creating an in-memory database called LookUpDB. At startup, it’s going to create a 1G database (plus the log) in the cache specified by the LookupDBDevice, and preload it with the contents of ReferenceDB, which may contain tables, stored procedures, data, and anything else that makes sense. It will be released at server shutdown.

Setting up RDDB

There are two types of RDDB; ephemeral and persistent. The ephemeral database will be dropped and recreated at shutdown/startup regardless of the content. The persistent database, with its minimal logging, will only persist if the server is shut down cleanly (i.e. a “shutdown with nowait” will cause it to be marked as suspect at startup).


Create database MyRddb on rddbdev = ‘2G’ with durability = no_recovery


Create database MyRddb2 on rddbdev2 = ‘2G’ with durability = at_shutdown

Minimally logged DML operations -- an important step

With ephemeral databases, it is important to ensure that the server knows that logging is not a priority. Please note these commands ONLY work in an IMDB and RDDB database, they will be ignored in an DRDB database.

Database-level DML logging

You can (and usually will) enable minimal-logging at the database level. This dramatically reduces log IO. There is no need to change anything at the application level.

You can turn this "on" at create database time, with the “dml_logging = minimal” option, or later with the alter table option or off with the “dml_logging = full” option.

Table-level DML logging

This can also be turned off at the table-level with the create table or alter table statement:

Create table min_log (a int) with dml_logging = minimal

Session-level DML logging

Session-level set options can be used if you want this to take effect only for your process:


set dml_logging {minimal | default }

Maintaining IMDB / RDDB

Full syntax is available in the reference manuals; these databases may be altered as needed to extend their size dynamically.

Dump / Load

IMDB and RDDB databases can be dumped and loaded just like any other database, which offers additional possibilities for data population.


Tempdb databases can be an IMDB, RDDB, or DRDB. Moving forward, the type will most likely depend more on money than on anything else; this will make tempdb fly.

Summary: IMDB vs. RDDB vs. DRDB

IMDB databases are ephemeral; they need to be loaded at server startup, and they disappear at server shutdown. They are amazingly fast, because 100% of all activity is in memory.

DRDB databases (Sybase ASE’s behavior for the past 20+ years) guarantees persistence of any data inserted into the database.

If the primary concern is performance, without regard to data persistence, use an IMDB. If data loss will not be tolerated due to server shutdown, use DRDB.

In between, there may be a situation where;

1)  The IMDB database will not fit into cache, or

2)  You have a persistent database with an IO bottleneck, and you are willing to potentially sacrifice the tail end of the database

IMDB has no durability. RDDB has optional durability, which is configurable at database definition time. DRDB is fully durable.

IMDB was just released, so we can’t make formal recommendations based upon experience, but based on preliminary testing, I’ve got lots of plans:

1)  Tempdb, where memory is available, is going into memory. I’m not even going to think hard about this one. In a high-performance environment, this will eliminate tempdb as a bottleneck (it always did for SSD, and this is a lot less expensive).

2)  Lookup tables and other information that would typically go into its own cache are going into databases, with underlying databases as templates; this will “prime” cache, which we haven’t been able to conveniently do before.

3)  All data migration / load efforts are going to be performed in an IMDB if it fits in memory and an RDDB if it doesn’t.

4)  I’m going to examine opportunities for IMDB, and look at high-availability options (Replication Server, etc.) for continually pushing IMDB into a persistent database with acceptable latencies.

The use cases outlined above, and many more that spring to my mind (and yours), are getting due consideration for this easy-to-use answer to a lot of performance questions.

A 20-year veteran of Sybase ASE database administration, design, performance, and scaling, Jeff Garbus has written over a dozen books, many dozens of magazine articles, and has spoken at dozens of user’s groups on the subject over the years. He is CEO of Soaring Eagle Consulting, and can be reached at

Sybase Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Sybase Forum
Topic By Replies Updated
DB Error Help Prepared Statements sjulian 2 July 5th, 04:58 AM
DB Error Help Prepared Statements sjulian 0 May 31st, 07:19 AM
Test, just a test XRumerTest 0 May 5th, 04:57 AM
Execute Procedure in SQL statement vcs1161 0 August 24th, 07:38 AM