Adaptive Server Enterprise

Adaptive Server Enterprise (ASE) (also known as "Sybase DB" or "Sybase ASE") is a relational DBMS developed by the Sybase Corporation in the 1980s.

History

ASE originates in the mid 1980's from Bob Epstein's home in Berkeley, California. It was originally marketed as Sybase SQL Server for unix platforms, and it was the first DBMS to use a client-server architecture, which contrasted from other DBMSs in the market that were monolithic. It was Sybase's innovative client-server architecture and support for OLTP workloads that made their SQL server product popular on Wall Street in the early 1990's. In 1988, Sybase, Microsoft, and Ashton-Tate co-developed a SQL version for PC. In 1993, the co-development licensing agreement terminated, and Sybase and Microsoft continued developing their respective products. In 1996, Sybase changed the name of its product to Adaptive Server Enterprise (ASE) as a means to differentiate its SQL Server product from Microsoft SQL Server. In the late 1990's Sybase started to lose its battle against Oracle and Microsoft SQL server due to its buggy release, overly aggressive sales strategies, pessimistic partners, merger complications and management turnover. The latest ASE version is SAP ASE 16.0

Query Interface

SQL

Concurrency Control

Multi-version Concurrency Control (MVCC) Two-Phase Locking (Deadlock Prevention) Two-Phase Locking (Deadlock Detection)

Sybase ASE uses multi-version concurrency control where all transactions are performed in the in-memory row storage or on disk MVCC, which enables the server to lock rows for writing in one session while granting access to unaltered rows in another session. ASE supports locking at table, page, and row level by providing allpages locking, datapages locking, and datarows locking. ###### Locking Schemes: allpages locking locks data pages and index pages datapages locking locks only data pages datarows locking locks only data rows For each scheme, ASE can acquire a table lock for the entire table or can acquire page and row locks to lock only the affected pages or rows. The types of page and row locks include shared locks for read operations, exclusive locks for inserts, deletes, and updates, and update locks during the initial phases of an update, delete, or fetch. Update locks can be promoted to exclusive locks immediately after no other shared locks exist on the page or row.

Joins

Nested Loop Join Hash Join Sort-Merge Join

Sybase ASE provides four main joins: ```NESTED LOOP JOIN```, ```MERGE JOIN```, ```HASH JOIN```, and ```NARY NESTED LOOP JOIN```. ```NESTED LOOP JOIN``` is a binary operator where the left child and right child form the outer data stream and inner data stream, respectively. ```MERGE JOIN``` is a binary operator where the left and right children are also the outer and inner data streams, respectively. Both streams are sorted on the key of the ```MERGE JOIN```. ```MERGE JOIN``` is usually effective when most of the rows must be processed and when large input streams are already sorted on join keys. ```HASH JOIN``` is a binary operator such that the left child creates the build input stream and the right child creates the probe input stream. From the input stream, each row is read and hashed into a bucket. ```NARY NESTED LOOP JOIN``` is created during code generation and is never evaluated or chosen by the optimizer. ```NARY NESTED LOOP JOIN``` execution has a performance benefit over the execution of a series of ```NESTED LOOP JOIN``` operators, as the next row fetched comes from the scan that produced the failing searchable argument value, reducing unnecessary I/O's.

Stored Procedures

Supported

Indexes

Bw-Tree

ASE uses B tree indexing, and indexes provided are either clustered or non-clustered and can have a root level, leaf level, and/or intermediate level. Only one clustered index can be created on a table, but up to 249 non-clustered indexes can be created per table. Index entries are stored as rows on index pages and contain key and value pointers to lower levels of the index, data pages, or individual data rows. An index on a 15-byte field has around 100 rows per index page. ##### Index Types: ###### Clustered Table data is stored in index key order. For allpages-locked tables, rows are stored in key order on pages that are linked in key order. For data-only-locked tables, indexes are used to handle data storage on rows and pages, but key ordering is not preserved. ###### Non-Clustered Storage order of data is unrelated to index keys. ##### Levels: ###### Root Level The root level is the index's highest level and corresponds to one root page. If an allpages-locked table is small enough such that one page can fit the whole index, then intermediate or leaf levels are not used, and the root page stores pointers to data pages. Data-only-locked tables always have a leaf level between the root and data pages. Otherwise, for larger tables, the root page stores pointers to intermediate or leaf-level pages. ###### Intermediate Level Any level between the root level and leaf level is an intermediate level; many such levels may exist. ###### Leaf Level The leaf level is lowest level of the index. It contains a key value for every table row (which is stored in sorted order by index key). For clustered indexes on allpages-locked tables, the leaf level is the data. For nonclustered indexes and clustered indexes on data-only-locked tables, the leaf level is a level above the data and contains an index row per data row. The leaf level has the index key value per row, a pointer to the page storing the row, and a pointer to the rows on the data page.

Storage Architecture

In-Memory

The database runs entirely in the Adaptive Server memory space (cache), so neither log nor data is ever written to disk, and I/O is not required. Its performance can be better than a disk-oriented database, at the cost of durability. In the event of memory failure, the database cannot be recovered.

Checkpoints

Fuzzy

ASE takes checkpoints automatically based on recovery interval requirement and also supports manual checkpoints.

Data Model

Relational

Sybase ASE is a relational database, and its creation was led by the Ingres, a RDBMS prototype developed at UBC that used a query language called QUEL.

Compression

Naïve (Page-Level) Naïve (Record-Level)

ASE provides row-level and page-level compression. Row-level compression is for fixed-length regular data. Some fixed-length data types, such as tiny integer (which uses 1 byte), cannot be compressed. Page-level compression compresses redundant data on a page. A compressed ASE database can include both compressed and uncompressed tables or partitions.

Adaptive Server Enterprise Logo
Website

https://www.sap.com/products/sybase-ase.html

Tech Docs

http://infocenter.sybase.com/

Former Name

Sybase SQL Server

Developer

SAP

Country of Origin

US

Start Year

1987

Acquired By

SAP

Project Type

Commercial

Written in

C, C++

Supported languages

C, C++, Java, Perl, PHP, Python, Tcl

Compatible With

Microsoft SQL Server

Operating Systems

Linux

Licenses

Proprietary

Wikipedia

https://en.wikipedia.org/wiki/Adaptive_Server_Enterprise