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
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 6, 2010

New Mission-Critical Features of Sybase ASE 15.5

By DatabaseJournal.com Staff

By Robert Schneider

Robert Schneider spotlights capabilities present in Sybase Adaptive Server Enterprise (ASE) 15.5, paying particular attention to the new enhancements that help support better performance (via lower latency and very high transactional throughput), augment efficiency, and increased availability.


In this article, we spotlight a series of new capabilities present in Sybase Adaptive Server Enterprise (ASE) 15.5. In earlier editions of ASE 15, Sybase incorporated numerous mission-critical, enterprise-class capabilities, such as:

  • User-defined SQL functions
  • Statistical aggregate functions
  • Row-locked system catalogs
  • Augmented security and encryption
  • SQL statement replication
  • QP Tune tuning utility
  • Shared-disk clustering

Version 15.5 continues this momentum. Throughout this overview, we’ll pay particular attention to the new enhancements Sybase has added that help support better performance (via lower latency and very high transactional throughput), augment efficiency, and increased availability.

Supporting better performance

While data reliability is always a requirement for a modern relational database management system, not every application and operation needs to incur the full overhead costs embodied in rigid enforcement of transactional ACID (Atomicity, Consistency, Isolation, and Durability) principles. For example, industries as diverse as finance, government/defense, and telecommunications all experience very high operational loads. Many of these interactions are short-lived and not intended for persistence. In these scenarios, providing traditional ACID support often unnecessarily taxes the database server, thereby diminishing both throughput and performance.

Since transaction logging is a major component of ACID support, these extra burdens are especially pronounced in terms of disk operations. Unfortunately, given that disk I/O is far more time-intensive than CPU-based operations, this means that the effects of full compliance with the ACID principles are magnified, imposing a particularly expensive performance cost.

To help ameliorate these performance bottlenecks, ASE 15.5 inaugurates two new classes of database: in-memory (IMDB) and relaxed durability (RDDB). Both databases let administrators more effectively balance the need for full ACID compliance with their site-specific usage profiles.

In-memory databases

This class of database runs completely within ASE’s memory space. This approach requires no disk storage whatsoever: neither data nor logs are saved to disk. Although it mandates only minimal transaction logging overhead, rollbacks, triggers, and replication are still supported within the memory cache. It’s fully integrated with ASE, offering the same look-and-feel and full programming language compatibility, and is ideal for scenarios where high data durability isn’t the overarching concern. You’re free to select this type of database for both permanent as well as temporary databases.

IMDB databases achieve their dramatic performance improvements in several ways. Simply eliminating disk storage yields significant throughput gains; there’s no need to wait for I/O. In addition, there are a number of internal optimizations such as faster data access, streamlined buffer and user log cache management and query optimizer adjustments that also contribute to the overall advancement in response times.

When deciding whether to use this option, what’s important to remember is that since both data and transaction logs are written to the memory-based cache and not disk, an unplanned database or server failure will result in the loss of any data that was in the cache at the time of the unexpected shutdown. Thus, it’s best to utilize in-memory databases for situations where long-term persistence isn’t important. For example, you could use this type of database as a transient workspace to capture short-lived data that will then be summarized and written into a standard, disk-based ASE database. By following this approach, you would reduce the overall workload on your server, thereby benefitting all ASE-based applications.

Since your in-memory database must fit in available cache space, it’s also important to only use this type of storage for information that will be able to comply with this requirement. However, note that while in-memory databases must be hosted by a single cache, you’re free to assign multiple memory devices to each cache.

Relaxed durability databases

While in-memory databases can substantially improve performance, their diminished durability means that they aren’t suitable for every application. To help extend the performance benefits of in-memory databases to traditional disk-based databases, ASE now enables administrators to opt for a relaxed durability database. For these types of databases, you’re free to fine-tune ASE’s behavior via two durability levels. For the first level, the durability is identical to an in-memory database: should the server shut down unexpectedly, all data will be lost. For the second level, a standard database shutdown causes all committed data to be written to disk. Note that for both in-memory and relaxed durability databases, ASE neither ensures that log records are ordered correctly nor flushes the transaction log to disk.

The benefit of this approach is that it sports the same tight integration with the full ASE product line, just like the aforementioned in-memory database, yet guarantees transaction durability as long as a normal shutdown has been performed.

Minimally logged Data Manipulation Language (DML)

DML statements (i.e. SQL commands that alter information) normally mandate significant logging overhead. Each data modification is traditionally registered in a disk-based transaction log. This log is then available to assist in restoring the database to an orderly state should a transaction failure or unexpected shutdown occur.

ASE 15.5 lets database administrators control the amount of logging for their in-memory and reduced durability databases. If the full logging option is chosen, ASE will log all transactions. On the other hand, if the minimal logging option is selected, ASE will not log row or page changes unless triggers or replication are involved. These options are available for INSERT, UPDATE, and DELETE statements, at the database, table, or session granularity.

Incremental data transfer utility

As data volumes grow, even simple tasks such as exporting information can become too time-consuming and unwieldy. In many scenarios, just exporting only altered information is all that’s necessary. This can result in a much smaller, more manageable data set.

To support this more efficient approach, ASE 15.5 introduces a new Transact-SQL statement that only retrieves information that has changed since the last time the command was run. Data integrity is preserved; only information contained in committed transactions is exported. Furthermore, eligible tables are read without the normal locking overhead, and other operations are able to proceed normally.

Exported rows may be written to an output file for designated receivers such as Sybase IQ, ASE, bulk copy, or character-encoded output. The destination may be local, or on a remote server as long as the file is an NFS file visible to ASE.

This new technique is especially relevant in the context of environments using in-memory databases. Transaction-safe, consistent incremental data modifications can be captured and inserted into a source ASE table. The extracted data can then be applied to another ASE table, thus allow for a fast data transfer mechanism from an in-memory database table to an on-disk ASE table.

Augmenting efficiency

Now that we’ve explored new techniques to drive additional performance from ASE 15.5, let’s examine a series of fresh capabilities that help streamline operations.

Enhanced archiving

As mission-critical data volumes have continued expanding, sophisticated storage solutions have kept pace. In response to the demands imposed by information journaling requirements, many organizations have selected the IBM Tivoli Storage Manager (TSM) as their archiving platform.

Beginning with ASE 15.5, Sybase has fully integrated TSM with the ASE Backup Server. This optional add-on is known as the Advanced Backup Services – Tivoli Storage Manager. Administrators are free to continue using the familiar Sybase backup interface, yet are able to archive information onto any TSM-supported media. ASE is responsible for dumping and loading databases to and from TSM; TSM takes care of the storage and retrieval of this information from the physical storage media, delivering significantly more efficient archiving and restoration.

The Adaptive Server and TSM integrated architecture includes Adaptive Server, Backup Server, the TSM client, the TSM server, and the TSM client API. The TSM client API enables the client application to call TSM functions.

In addition to the integration with TSM, ASE 15.5 also offers additional compression options for backing up databases and transaction logs. Two new compression levels (100 and 101) are now available via the dump_database and dump_transaction commands. The first level offers faster compression, while the other delivers more complete compression. The existing compression levels of zero through nine continue to be supported: the new levels have no effect on these levels.

User-defined tempdb groups

Beginning with ASE 12.5.03, Sybase offered support for user-defined temporary databases. These transitory structures are helpful for storing data that doesn’t require the overhead mandated by permanent information storage. However, as administrators and developers have come to rely on temporary databases, it’s become apparent that they require additional control over these provisional structures.

In version 15.5, administrators are now free to define site-specific temporary database groups. Based on user requests, ASE will now assign temporary databases to these administrator-defined groups, as well as continue to utilize the default temporary database group in situations where no preference is expressed.

For additional efficiency, note that in-memory databases may be used as temporary databases and managed accordingly, including being added to user-defined tempdb groups.

Tracking time-sensitive information

Certain classes of database applications and industries generate massive amounts of timestamped information. For example, financial services and telecommunications enterprises are just two instances of organizations that not only create substantial time-focused data, but also must track this data to a very fine granularity.

To store information to the micro-second, ASE 15.5 introduces two new data types: Bigtime and BigDatetime.

Increasing availability

In the past few years, a number of business and technology trends have placed increasing pressure on the IT organization. These strains have included relentless cost control mandates, space and power constraints, and the desire to wring additional usage from the enterprise’s hardware while boosting availability.

To address these needs, Sybase introduced ASE Cluster Edition in version 15. It provides a shared-disk cluster implementation of ASE that helps manage costs by leveraging commodity hardware. Up to 32 concurrent ASE servers are presented as a single system, with built-in workload management capabilities to maximize performance and availability. Significantly, no application logic changes are required to leverage all of this functionality.

ASE Cluster Edition supports today’s demanding Service Level Agreements (SLA) via automated failover and load balancing. Administrators may easily bring standby nodes online, as well as use these servers to eliminate downtime during migration, all through an easily-managed user interface.

Version 15.5 enhances ASE Cluster Edition by providing multi-simultaneous failover for uninterrupted operations on multi-node implementations, elevating continuous availability to new levels.


The new capabilities present in ASE 15.5 demonstrate its continued evolution and attention to meeting the specific needs of mission-critical database-driven applications. Administrators now have supplemental alternatives to help increase performance, enhance operational effectiveness, and maximize the availability of their ASE implementations.

In a follow-on article, we’ll explore the in-memory and reduced durability databases in more detail.

About the author

Robert Schneider is a Silicon Valley-based technology consultant. He has written six books and numerous articles on advanced technical topics such as Cloud Computing, Service Oriented Architecture (SOA), and high-performance relational database design and optimization. He may be reached at Robert.Schneider@think88.com

Sybase Archives

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