Overview of Sybase ASE In-Memory Database Feature

Overview of Sybase ASE In-Memory Database Feature

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.

Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes