Section 17.2. The Power User s View

17.2. The Power User's View

Power users of your database know enough to call you and ask questions about the terms in this section. They probably won't know anything more than these terms.

17.2.1. Server

A Sybase server generally refers to the Sybase database server, which is known as Sybase Adaptive Server Enterprise, or Sybase ASE. Each Sybase installation also includes a Sybase Backup Server and can include other Sybase server products, such as Sybase Monitor Server and Sybase Replication Server. All these servers use the same communication protocols and are, in fact, built using the same libraries (the API is published so that you can design your own servers if you so wish). The Sybase ASE Database Server uses symmetric multiprocessing. In other words, it can create multiple system processes that communicate through shared memory, and none of the processes has priority. When you start the database server, one or more dataserver processes will start. The number of dataserver processes that start is determined by the max online engines server configuration option. A normally functioning system also has a single backupserver process, which is responsible for the I/O of backups and restores. The backupserver process is a system process dedicated to performing fast backups and restores.

You can have as many servers on your machine as your system's resources can support. Each dataserver process requires a preallocated block of system memory, but the other servers are lightweight. For availability and performance reasons, it might make sense to have multiple Sybase ASE servers running on the same machine, but it is common that each machine supports a single Sybase ASE server. The logic behind this is simple. With two servers, you can isolate a particular application and protect critical users from badly behaved applications and queries. On the other hand, a single 4-CPU ASE running on 16 GB of RAM has significantly greater throughput than two 2-CPU ASEs running with 8 GB RAM each.

17.2.2. Engine

When you start a Sybase ASE server, it executes one or more instances of the dataserver executable. Each running instance of dataserver is known as an engine. If you have a multiprocessor system with N processors, it is common to start either N or N1 engines. The number of engines is set by the server configuration variable max online engines.

17.2.3. Database

A schema is a logically grouped collection of tables, indexes, stored procedures, and other database objects. A database is a collection of one or more schemas. A new server starts with several system databases. The system administrator creates one or more application databases on your server to contain the data and schemas of your business applications.

Because Sybase backups and restores run on a single database at a time, you should design your system so that all related objects are in the same database. In other words, a database usually represents an application, although your application can have several databases if it can be split into several logically separated groups of objects.

Each account, after logging into your server, is placed in its default database (which is set on an account-by-account basis). Each account can be granted or denied access to objects in any of your databases. Sybase objects are normally accessed via the notation database_name.owner.object_name. Usually, the owner of each object is the database owner, and owner can be omitted. The shorthand to refer to objects in your current database is owner.object_name or simply object_name.

All servers contain the following system databases:

master

The master database stores system configuration information such as logins and disk file mappings.

model

This is a template database, and new user databases are copied from model.

tempdb

This is a temporary space for sorts, temporary tables, and joins.

sybsystemprocs

This database stores system stored procedures.

sybsystemdb

This is used in ASE 15 to store transactions in progress and is used during recovery.

Servers also contain one or more user databases that contain your application information and can have pretty much any name.

By convention, database names are in lowercase, often with an underscore in the name. It is also common to end databases with the suffix db. For example, riskdb, logininfo_db, and tracedb are all good database names.

17.2.4. Transaction

Sybase supports standard SQL transactions, guaranteeing that a set of one or more SQL statements succeed or fail together. Programmers create their own transactions by using the standard SQL statements begin TRansaction and commit TRansaction. Additionally, each insert, update, and delete statement is, by nature, its own transaction. A common example of a transaction is moving $100 from my account to your account. First, $100 must be subtracted from my account and added to your account. Both statements must either succeed or fail together. If the machine crashes between the statements, neither of the two statements should run, or else money will be lost.

17.2.5. Table

A table is a collection of related rows that all have the same attributes. Sybase tables can be partitioned, or spread across multiple page chains. When you partition a table, you allow that table to perform inserts faster.

By convention, table names are in lower- or mixed-case, often with an underscore in the name. They also are singular. In other words, a table named order is preferable to a table named orders.

17.2.6. System Table

The system table stores system information such as logins, disk layouts, and system activity. In the Sybase ASE Architecture, all configuration information is stored in these system tables, which are well documented and can be accessed just like any other table. Sybase provides system stored procedures to read and modify this data.

System configuration information is stored in the master database. Database configuration information is stored in each database. All system tables start with the prefix sys. You cannot, by default, use Data Manipulation Language (DML) such as insert/update/delete statements on system tables. System tables can be manipulated only if you set the system configuration option that allows updates to system tables. This is set using the sp_configure system stored procedure. Needless to say, unless you understand exactly what you are doing, you should not edit your system tables by hand.

Sybase system tables start with the prefix sys. Some system table names include syslogins, sysdatabases, and sysdevices.

17.2.7. Index

A database index is analogous to an index in a book: it allows Sybase to find data quickly. Sybase supports two types of indexes: clustered and nonclustered. A clustered index represents the sort order of the actual data pages (except with data-only locked, or DOL, page tables), and a nonclustered index represents a way to do index lookups to fetch the data.

17.2.8. Stored Procedures

A stored procedure is a precompiled set of SQL statements. Stored procedures provide a much faster way to perform common administrative tasks, queries, and other tasks.