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 Stored Procedures

Views

Materialized Views

ASE supports views and materialized views. In ASE, a materialized view can only reference base tables. It cannot reference views, other materialized view, or temporary tables. A materialized view must specify the column name.

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.

Logging

Logical Logging Physical Logging

Transactions are used to track database changes. A transaction consists of one or many Transact-SQL statements that all succeed or fail. Each database has a transaction log, which is a write-ahead log, so changes are logged before the change is made to a disk page. If any statement in a transaction fails, all changes made by the transaction are rolled back.

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``` (a variant of nested loop join). Nested loop join works well with relatively small data sets, merge join works well for moderately sized data sets, and hash join performs well for large datasets. Hash joins parallelize and scale better than other joins and maximize throughput well in data warehouses.

Isolation Levels

Read Uncommitted Read Committed Serializable Repeatable Read

ASE provides four isolation levels: * Isolation level 0 - This level is also known as read uncommitted or dirty read. It allows a task to read uncommitted changes. * Isolation level 1 - This is the default level. It is also known as read committed and prevents dirty reads. Queries at this level can read only committed changes. * Isolation level 2 - This level prevents non-repeatable reads. * Isolation level 3 - This level prevents phantoms.

Query Execution

Tuple-at-a-Time Model

ASE uses an iterator model that supports horizontal and vertical parallelism for query execution. Pipelined parallelism (a form of vertical parallelism) is supported also.

Stored Procedures

Supported

ASE supports stored procedures written using SQL. ASE also supports extended stored procedures. Extended stored procedures have the interface of a stored procedure but do not contain SQL statements and instead execute procedural language code that has been compiled into a dynamic link library. The procedural language can be written in any language that can call C functions and manipulate C datatypes.

Indexes

Bw-Tree

ASE uses B tree indexing, and indexes provided are clustered or non-clustered. 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. If a table is partitioned, local and global indexes can be created. ##### Index Types: * Clustered indexes - 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 indexes - Storage order of data is unrelated to index keys. * Local indexes - Local indexes are index subtrees that index only one data partition. * Global indexes - Global indexes span all data partitions in a table.

Storage Architecture

Disk-oriented In-Memory

Traditional ASE databases are disk-oriented. ASE 15.5 supports in-memory databases such that 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.

Foreign Keys

Supported

ASE supports foreign key constraints. When a foreign key is created on a table, an index for the key is automatically created.

Data Model

Relational

Sybase ASE is a relational database and handles normalized data models for OLTP workloads well. It also supports star and snowflake models for OLAP workloads.

System Architecture

Shared-Memory Shared-Disk

ASE is a shared-memory and shared-disk RDBMS. Sybase ASE Cluster Edition is a shared disk cluster implementation of ASE. In the cluster edition, each node points to to the same database storage but runs its own instance with its own memory and CPU.

Query Compilation

Code Generation

ASE uses a cost-based query optimizer. The optimizer relies on information about tables, indexes, partitions, and columns referenced by a query to estimate the cost in terms of I/O and CPU time for possible query plans, and then chooses the lowest estimated cost plan. The optimizer is efficient for both OLTP and OLAP workloads. Optimization strategies can be selected based on need.

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.

Hardware Acceleration

RDMA

ASE uses the Virtual Server Architecture to take advantage of the parallel processing done by symmetric multiprocessing systems. ASE can be run as a single process, a single multi-threaded process, or multiple cooperating processes. The ASE Cluster Edition 16sp01 supports RDMA.

Storage Organization

Heaps

ASE provides logical constructs (devices) for mapping of the underlying operating system data files. One device can only be associated with one data file or raw partition. A database is built on devices and can span multiple devices. When a database is created on devices or a device fragment, three segments (system, default, and log) are generate automatically on each corresponding device or device fragment.

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