With the recent release of the latest version of its flagship product Adaptive Server Enterprise version 15.5, Sybase has stepped into In-Memory database area.

Unlike Oracle’s TimesTen, Sybase’s In-Memory database feature is fully integrated within the base product Adaptive Server Enterprise. In-Memory database is a part of Adaptive Server Enterprise software and hence does not require any kind of separate in-stallation, it has been tightly integrated with ASE software.

So what is In-Memory database all about? There is a constant need for today’s businesses to process real-time data while being extremely fast when responding to user queries; this is an era of sub-second query response time. With the servers becoming capable of handling more and more memory and at the same time memory being more and more affordable, it is now possible to have a database fully reside inside memory.

Disk i/o in RDBMS has been a big bottleneck in performance. In-Memory database resides inside memory and hence eliminates the need for disk i/o giving superior performance over disk resident databases.

Overview of Sybase ASE 15.5 In-Memory Database

With the introduction of In-Memory database now there are mainly 3 types of databases in ASE:

* Disk Resident Database — Traditional ASE Databases including user created tempdb
* In Memory (IMDB) — In-Memory Database, resides fully in memory , in-cluding user created tempdb
* Disk Resident Relaxed — Gives you an option to have data flushed to disk with ‘polite shutdown’

Let’s take a look at how IMDB works in Sybase ASE. IMDB is not designed for recovery. A traditional ASE database always preserves data after restart. With In-memory database, data is lost after the restart of a server or system failure. The way the Relaxed Durability Database behaves is somewhere in the middle of traditional ASE and In-memory database, i.e. it gives an option to the user to preserve data if the server is shut-down using “shutdown” command (polite shutdown). Such behavior is defined with the parameter “durability” while creating a database.

There are 3 types of durability levels:

* full — Default option, all transactions are written to disk , transactions are not lost after restart
* no_recovery — transactions are lost after restart, database is recreated using model or template database – if defined so
* at_shutdown — Transactions are written to disk with polite shutdown but transactions will not be written to disk in case of abrupt shutdown or system crash

The In-memory database is created using the “in-memory” parameter of “create database command”.

Controlling DML Logging Behavior with ASE 15.5

Until ASE 15.5, there was no control over transaction logging and you always thought you wanted the mechanism so that not to log bunch of transactions, voila!! it has arrived !. In ASE 15.5 it is possible to control DML transaction logging behavior. There are set of rules which govern scenarios in which DML statements are minimally logged. DML logging behavior can be controlled at different levels.

* Database-level logging
* Table-level logging
* Session-level logging

There are 2 types of DML logging:

* full – DML is logged , default behavior
* minimal – DML is minimally logged

DML logging can be specified using “create database” or “alter database” commands for database level, using “create table”, alter table”, “select into” at table level and using “set dml_logging” command at session level

Creating In-Memory Database in Sybase ASE 15.5

Below mentioned steps explains how to create in-memory database in ASE 15.5 step by step.

1. First step is to create memory cache. Allocate enough space to cache such that size of the cache is bigger than the size of your database.
sp_cacheconfig IMDB_tempdb_cache, “60m”, inmemory_storage,”none”, “cache_partition=4”
If you execute sp_cacheconfig you will see that for IMDB_tempdb_cache type is “In-Memory Storage”
2. Once you have created cache, you need to create in-memory device. You can refer to the cache name which you created in step 1 while creating in-memory device.
DISK INIT name = “IMDB_dev”, physname = “IMDB_tempdb_cache”,
size = “60m”, type= ‘inmemory’
If you execute sp_helpdevice IMDB_dev you will see “virtual cache device” in the description
3. Once you have cache and device , you can create in-memory database using that device
CREATE inmemory TEMPORARY DATABASE temp_IMDB
on IMDB_dev = “40m” with durability = no_recovery
If you execute sp_helpdb tempdb_IMDB , you can see “in-memory” displayed in the status column.
4. To change DML logging behavior at database level you can execute below mentioned command
alter database temp_IMDB set dml_logging=minimal
5. There are some new functions introduced in ASE 15.5 to check “durability” and “dml_logging” status. To check the durability and dml_logging settings for the database created above , you can make use of db_attr function.
select db_attr(“temp_IMDB”,”durability”)
go
select db_attr(“temp_IMDB”,”dml_logging”)
go

As we noticed above, with the release of ASE 15.5 version, now it will be possible to have In-Memory database created within ASE and also make use of minimally logged DMLs.