Sidebar: Sybase Overview

Sybase is based on the client-server model. Databases reside on servers responsible for database integrity, user concurrency, database recovery, and database security. Clients request information from servers over a network.

For data integrity, Sybase provides triggers, defaults, rules, and stored procedures. Triggers ensure that updates, deletes, and inserts cascade properly throughout a database, keeping the data consistent. Defaults are defined on attributes where null values must be avoided. A null value represents the absence of a recorded value. Null values can cause inaccurate results from scalar SQL queries. Rules enforce an allowable range of values for a given attribute. Stored procedures take advantage of precompiled SQL. The database engine must parse SQL statements, formulate and optimize the best plan to access the data, compile the optimized plan, and then execute it. Unless a recompile is forced, stored procedures only compile on the first execution. Subsequent calls execute the precompiled plan, usually resulting in performance gains. All of the above are written in SQL and are stored on the server. Storing code on a server can improve performance, increase integrity, and make applications more portable.

Locks provide concurrency. Sybase uses page-level locking. Sybase's data page is 2048 bytes. Accessing page data requires a lock on that page. There are two basic types of locks: shared and exclusive. A shared lock allows multiple read access to the data on a page. An exclusive lock is used for updates, deletes, and inserts, prohibiting access by all other processes until the data on that page has been changed.

Recovery is accomplished by logging database changes to a transaction log. A transaction is a single unit of work (atomic) which may be made up of several SQL statements. If a transaction is successful (committed), it is then written to the transaction log. Sybase also writes to the transaction log at a (tunable) recovery interval by performing a checkpoint. Checkpoint flushes all dirty log and data pages from memory to disk, and creates a checkpoint entry in the transaction log. Because it is possible to fill up the transaction log, there is an option to truncate the log on a checkpoint. This option limits recovery to the last backup of the database. For up-to-the-minute recovery, the transaction log must be periodically dumped and then truncated. Should a database become corrupt, the database administrator must restore the database from the most recent backup. Then, to recover subsequent transactions, the transaction log must also be restored. Transactions committed before the last checkpoint roll forward, and uncommitted transactions are rolled back.

Security is accomplished at several levels. A Sybase server may have several databases. Access to a database requires a login and a password for the server. Then that login must be defined as a user with access for each database. The tables and stored procedures in each database also have permissions that further increase security.

Clients issue requests in Transact SQL (SQL with Sybase extensions), and receive results in Tabular Data Stream (TDS is a Sybase proprietary protocol). Clients may or may not reside on the same host as the server. Database administrators use the client Interactive-SQL, or isql, to perform most daily administrative tasks.

Sybase is a relational database. In a relational model, all data is stored in tables (relations). A table is made up of rows (tuples) and columns (attributes). Rows in Sybase need not be unique but a true relational model does not allow duplicate rows. Duplicates violate set theory, the basis of relational mathematics. One way to preserve uniqueness is to create a unique index on a particular column or combination of columns that make the rows in the table unique. This unique column is also referred to as the primary key. Keys made up of two or more columns are known as composite keys. Indexes are also created to increase performance by decreasing the amount of disk I/O required to access data. Tables and indexes are database objects and all database objects are stored on segments.

A segment is a logical name given to a database device (a raw UNIX partition) or a collection of database devices (two or more raw UNIX partitions). Segments let the database administrator place database objects on a specific device or collection of specific devices. Each database is automatically created with three segments: system, default, and log. The system segment is where Sybase places system tables and indexes. System tables are also known as the system catalog. When a user object is created, the object's creator can specify which segment the object should be placed on. If no segment is specified, Sybase will place the object on the default segment. Since each database has its own transaction log, Sybase automatically defines a segment for log placement. Database administrators can define their own segments. Placing an index on a segment different from the table segment it was built on may increase performance by using disk I/O bandwidth more effectively.

Databases are created on disk devices, which are usually raw partitions. Flat files may also be used but compromise recovery and performance. Because operating systems buffer I/O, the transaction log described above cannot guarantee that committed transactions are in fact on the disk when using flat files. It is important to stress that databases are created on devices, segments are defined on those devices, and database objects are placed on segments.