Custom Search : Technical Guides Sybase Oracle UNIX Javascript

Technical Guides

Of Interest

Be responsible for your future
Enter the USA legally!

Visa, Green Card, Citizenship, Passport
Consultation Services

Sybase » Administration » DBA » Memory Model

Sybase ASE Architecture Overview


The Sybase Server

A Sybase server consists of:

A) two processes, data server and backup server ;

B) devices which house the databases; one database (master) contains system and configuration data ;

C) a configuration file which contains the server attributes.

Memory Model

The Sybase memory model consists of:

A) the program area, which is where the dataserver executable is stored;

B) the data cache, stores recently fetched pages from the database device

C) the stored procedure cache, which contains optimized sql calls

The Sybase dataserver runs as a single process within the operating system; when multiple users are connected to the database, only one process is managed by the OS. Each Sybase database connection requires 40-60k of memory.

The "total memory" configuration parameter determines the amount of memory allocated to the server. This memory is taken immediately upon startup, and does not increase.

Transaction Processing

Transactions are written to the data cache, where they advance to the transaction log, and database device. When a rollback occurs, pages are discarded from the data cache. The transaction logs are used to restore data in event of a hardware failure. A checkpoint operation flushes all updated (committed) memory pages to their respective tables.

Transaction logging is required for all databases; only image (blob) fields may be exempt.

During an update transaction, the data page(s) containing the row(s) are locked. This will cause contention if the transaction is not efficiently written. Record locking can be turned on in certain cases, but this requires sizing the table structure with respect to the page size.
The locking mechanism can be bypassed by setting the isolation level, to allow data to be read from uncommitted transactions.

Backup Procedures

A "dump database" operation can be performed when the database is on-line or offline. Subsequent "dump transaction" commands need to be issued during the day, to ensure acceptable recovery windows.
A new ASE 12.5 feature allows database dump files to be "mined" for a single table. Thus an entire database loaded is not required when only a single table restore is needed.

Recovery Procedures

A "load database" command loads the designated database with the named dump file. Subsequent "load transaction" commands can then be issued to load multiple transaction dump files.

Security and Account Setup

The initial login shipped with Sybase is "sa" (system administrator). This login has the role "sa_role" which is the super-user, in Sybase terms.

User logins are added at the server level, and then granted access to each database, as needed. Within each database, access to tables can be granted per application requirements. A user can also be aliased as "dbo", which automatically grants them all rights within a database.

Database Creation

Databases are initialized with the "create database" command. It is not unusual for a Sybase server to contain many different databases. Tables are created within each database; users refer to tables by using ownername.tablename nomenclature. "Aliasing" users with the database eliminates the need for the prefix. Typically, a user will be aliased as "dbo" (database owner), which also gives the same result.

A typical Sybase database will consist of six segments spread across various devices (non-SAN environment).

Data Types

Supported data types include integer, decimal, float, money, char, varchar, datetime, image, and text datatypes.

Text and image datatypes are implemented via pointers within the physical record structure ; the field contents are stored in dedicated pages. As a result, each text or image field requires at least 2K of storage (on most platforms).

For string data, the varchar type can be used for lengths up to 4000; the text type can be used for longer field data.

Datetime fields are stored as a number which is accurate to 1/300 of a second.

Within a "create table" statement, a column can be flagged as an "identity" column, which causes it to be incremented automatically when rows are inserted.

Storage Concepts

Tables are stored in segments; a segment is an area within a device, with a name and a size, that is allocated for a database. The transaction log is stored in its own segment, usually on a separate device.


Semantic "smart" partitioning is new in ASE 15.
Tables can be partitioned by range, value, or round-robin. By default, all tables in ASE 15 are partitioned, round-robin.

Failover and High Availability

The ASE High Availability option allows server failover to occur within specified metrics.
ASE 15 supports for server clusters, shared disk storage between servers, and multiple client failover in a cluster setting
will be available in late 2007 / early 2008.


Transact-SQL is a robust programming language in which stored procedures can be written. The procedures are stored in a compiled format, which allows for faster execution of code. Cursors are supported for row by row processing. Temporary tables are supported, which allows customized, private work tables to be created for complex processes. Any number of result sets can be returned to calling applications via SELECT statements.


Sybase ASE supports insert, update, and delete triggers. Triggers are fired after the transaction starts, and have the capability
to roll back if required. Images of the 'before' and 'after' records are visible within the trigger. Triggers by default fire
for all rows in the transaction.

Analytic Functions, OLAP

Sybase ASE does not support analytic functions (median, n-tile, lead/lag, over partitions) at the moment;
Sybase IQ, the data warehouse product, does support most analytic functions.

Flat File Processing

Sybase ASE includes the BCP utility (Bulk-Copy) which can import or export data via flat files.
"Fast" mode imports are achieved by simply removing the indexes (and triggers) from the target table.

Performance and scalability

On a regular basis, Sybase Adaptive Server Enterprise on Sun captures the #1 TPC benchmark for SMP performance, check for the latest results.

Scalability: Sybase 15 scales from handheld devices to enterprise level servers.


Price per seat is average, compared to other vendors; Sybase ASE is the leader in lowest transaction cost, with the
best performance, among the leading enterprise database vendors.

Support is achieved by opening cases with the support team. Response is usually within 24 hours.

Management and Development Tools (for Windows)

ISQL is the interactive query tool used with Sybase ; it is useful for entering queries and stored procedures.

Sybase Central is shipped with Sybase ASE. It offers a good interface for performing basic database tasks. The "best of breed" product in this category is DB-Artisan by Embarcadero Technologies.
Sybase recently released Workspace, an Eclipse based SQL development environment, which features a stored procedure debugger and graphical SQL creator.

For development, Sybase Inc. offers Workspace, Powerbuilder, Powerdesigner, Power J and its "Studio" line products. Powerbuilder remains the most robust, straightforward, and practical choice for windows development, supporting many other RDBMs in addition to Sybase ASE.

Additional Notes, Regarding Sybase ASE

Replication Server

Sybase's enterprise data replication facility is the leader in the industry; more Oracle customers purchase
the Sybase offering than do Oracle's own product.

ASE 15's new patent-pending encryption technology allows seamless encryption of columns with little or
no change to most applications.

Computed Columns
ASE 15 supports both materialized and virtual computed columns; this eliminated the need to perform
CPU-intensive calculations at runtime. Functional indexes can be built on these columns.

Sybase : Related Topics

Sybase : Administration : Post-installation check
Sybase : Administration : Device initialization
Sybase : Administration : Database Creation
Sybase : Administration : A backup routine
Sybase : Administration : Striping Dump Devices
Sybase : Administration : Moving the transaction log to another device
Sybase : Administration : Adding a segment to a database
Sybase : Administration : Configuring the cache
Sybase : Administration : Apply multiple transaction dumps
Sybase : Administration : Database maintenance procedure
Sybase : Administration : Database maintenance script
Sybase : Administration : Server configuration
Sybase : Administration : Dealing with a Corrupted Database
Sybase : Administration : Dealing with a Server Failure
Sybase : Administration : DBCC Notes
Sybase : Administration : Create proxy tables and external logins
Sybase : Administration : Renaming a database
Sybase : Administration : Reorg: rebuilding a table
Sybase : Administration : Sybase ASE 15 Enhancements
Sybase : Administration : Setting the thresholds
Sybase : Administration : Apply a transaction dump
Sybase : Administration : Security Tasks
Sybase : Administration : Setting Process Priorities
Sybase : Administration : Drop an alias, with objects linked to login
Sybase : Administration : Display grants, effective rights to objects
Sybase : Administration : Displaying access information from sysprotects
Sybase : Administration : Database Engines: Status and Settings
Sybase : Administration : Replication Server 15 Configuration
Sybase : Administration : Loading data into Sybase IQ from a Remote Server
Sybase : Administration : Sybase IQ Overview
Sybase : Administration : Sybase IQ Functions
Sybase : Administration : Sybase IQ - Remote Access From ASE
Sybase : Administration : Sybase IQ : Procedure Profiling / Monitoring
Sybase : Administration : Sybase IQ: Restoring a Database from Dump Files
Sybase : Administration : Sybase IQ: Minimize Storage option
Sybase : Administration : Sybase IQ: Index Types
Sybase : Administration : Sybase IQ: HUGEPAGE Support
Sybase : Administration : Sybase IQ: Database Creation (Raw Devices)
Sybase : Administration : Sybase IQ : Repairing Problems / Troubleshooting Corruption
Sybase : Administration : Sybase IQ : Moving / Re-allocating / Re-org
Sybase : Administration : Sybase IQ: Sysmon / System Performance Analysis
Sybase : Administration : Sybase IQ: Monitoring Connections / Processes
Sybase : Administration : Sybase ASE: Installing a new server via srvbuildres utility
Sybase : Administration : Sybase IQ: Copying users/logins between databases
Sybase : Administration : Sybase IQ: Managing User Accounts
Sybase : Administration : Sybase ASE: Database Schema Compare
Sybase : Administration : Sybase IQ: Listing tables, ordered by size allocated
Sybase : Administration : Sybase IQ : Comparing grants / permissions between servers
Sybase : Administration : Sybase ASE: Database page size
Sybase : Administration : Sybase ASE: Managing users and roles
Sybase : Administration : Sybase ASE: Setting resource limits for users
Sybase : Administration : Sybase ASE: Setting up user assigned custom tempdbs
Sybase : Administration : Sybase ASE: Setting up the transaction log cache using logiosize
Sybase : Administration : Sybase IQ: Server Startup Switches
Sybase : Administration : Sybase ASE: Managing Identity Columns
Sybase : Administration : Sybase IQ: Working with options and server settings
Sybase : Administration : Sybase IQ: Monitoring and Troubleshooting via System Procedures

Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site

Get the latest Rocket99 news and tech tips via

Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site

Copyright © 2019 Stoltenbar Inc All Rights Reserved.