Sybase System Databases

System databases:

A default installation of Adaptive Server includes these system databases:

• master database

• model database

• sybsystemprocs

• sybsystemdb

• tempdb

Optionally, you can install:

• sybsecurity

• The sample databases - pubs2 and pubs3

• The dbcc database - dbccdb

• The job scheduler database - sybmgmtdb

Master database:

The master database controls the operation of Adaptive Server and stores information about all user databases and their associated database devices. Table 2-1describes the information that the master database stores.

Table 2-1: Information the master database stores
Information	System table
User accounts	syslogins
Remote user accounts	sysremotelogins
Remote servers that this server can interact with	sysservers
Ongoing processes	sysprocesses
Configurable environment variables	sysconfigures
System error messages	sysmessages
Databases on Adaptive Server	sysdatabases
Storage space allocated to each database	sysusages
Tapes and disks mounted on the system	sysdevices
Active locks	syslocks
Character sets	syscharsets
Languages	syslanguages
Users who hold server-wide roles	sysloginroles
Server roles	syssrvroles
Adaptive Server engines that are online	sysengines

Because the master database stores information about user databases and devices, you must be in the master database to issue the create database, alter database, disk init, disk refit, disk reinit, and disk mirroring commands.

Model database:

• Adaptive Server includes the model database, which provides a template, or prototype, for new user databases. 

• Each time a user enters the create database command, Adaptive Server makes a copy of the model database and extends the new database to the size specified by the create database command.

• The model database contains the required system tables for each user database. 

• You can modify model to customize the structure of newly created databases everything you do to model is reflected in each new database.

Tempdb database:

• Adaptive Server has a temporary database, tempdb that provides a storage area for temporary tables and other temporary working storage needs. The space in tempdb is shared among all users of all databases on the server.

Sybsystemprocs database:

• Sybase system procedures are stored in the database sybsystemprocs. 

• When a user in any database executes a system stored procedure (that is, a procedure whose name begins with sp_), Adaptive Server first looks for that procedure in the user’s current database.

• If there is no procedure there with that name, Adaptive Server looks for it in sybsystemprocs. If there is no procedure in sybsystemprocs, Adaptive Server looks for the procedure in master.

Sybsystemdb database:

• The sybsystemdb database stores information about distributed transactions. 

• Information about remote servers participating in distributed transactions is stored in the syscoordinations table.

• The sybsystemdb database also stores information about SYB2PC transactions that use the Sybase two-phase commit protocol.

Sybsecurity database:

The sybsecurity database, which contains the auditing system for Adaptive Server, includes:

• The system tables, sysaudits_01, sysaudits_02, sysaudits_08, which contain the audit trail 

• The sysauditoptions table, which contains rows describing the global audit options

• All other default system tables that are derived from model

Sybmgmtdb database:

• The sybmgmtdb database stores jobs, schedules, scheduled jobs information, and data the internal Job Scheduler task needs for processing.

• sybmgmtdb also maintains the output and results from these executed tasks.