Home

	Expand Search

System Administration Guide (Online Only)

Chapter 2 System and Optional Databases

Chapter 2

System and Optional Databases

This chapter describes the system databases that reside on all Adaptive Server systems. It also describes optional Sybase-supplied databases that you can install, and a database that Sybase Technical Support may install for diagnostic purposes.

Overview of system databases

When you install Adaptive Server, it includes these system databases:

• The master database

• The model database

• The system procedure database, sybsystemprocs

• The temporary database, tempdb

Optionally, you can install:

• The auditing database, sybsecurity

• The two-phase commit transaction database, sybsystemdb

• The sample databases, pubs2 and pubs3

• The dbcc database, dbccdb

For information about installing the master, model, sybsystemprocs, and tempdb databases, see the installation documentation for your platform. For information on installing dbccdb, see Chapter 25, "Checking Database Consistency."

The master, model, and temporary databases reside on the device named during installation, which is known as the master device. The master database is contained entirely on the master device and cannot be expanded onto any other device. All other databases and user objects should be created on other devices.

Warning!

Do not store user databases on the master device. Storing user databases on the master device makes it difficult to recover the system databases if they become damaged. Also, you will not be able to recover user databases stored on the master device.

You should install the sybsecurity and sybsystemdb databases on their own devices and segment. For more information, see the installation documentation for your platform.

You can install the sybsystemprocs database on a device of your choice. You may want to modify the installation scripts for pubs2 and pubs3 to share the device you create for sybsystemprocs.

The installpubs2 and the installpubs3 scripts do not specify a device in their create database statement, so they are created on the default device. At installation time, the master device is the default device. To change this, you can either edit the scripts or follow the instructions in Chapter 16, "Initializing Database Devices," for information about adding more database devices and designating default devices.

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-1 describes information the master database tracks.

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

Controlling object creation in master

When you first install Adaptive Server, only a System Administrator can create objects in the master database, because the System Administrator implicitly becomes "dbo" of any database he or she uses. Any objects created on the master database should be used for the administration of the system as a whole. Permissions in master should remain set so that most users cannot create objects there.

Warning!

Never place user objects in master. Storing user objects in master can cause the transaction log to fill quickly. If the transaction log runs out of space completely, you will not be able to use dump transaction commands to free space in master.

Another way to discourage users from creating objects in master is to change the default database for users (the database to which a user is connected when he or she logs in) with sp_modifylogin. See "Adding users to databases" for more information.

If you create your own system procedures, create them in the sybsystemprocs database rather than in master.

Backing up master and keeping copies of system tables

To be prepared for hardware or software failure on Adaptive Server, the two most important housekeeping tasks are:

• Performing frequent backups of the master database and all user databases. See "Keep up-to-date backups of master" for more information. See also Chapter 28, "Restoring the System Databases," for an overview of the process for recovering the master database.

• Keeping a copy (preferably offline) of these system tables: sysusages, sysdatabases, sysdevices, sysloginroles, and syslogins. See "Keep offline copies of system tables" for more information. If you have copies of these scripts, and a hard disk crash or other disaster makes your database unusable, you can use the recovery procedures described in Chapter 28, "Restoring the System Databases." If you do not have current copies of your scripts, it will be much more difficult to recover Adaptive Server when the master database is damaged.

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.

A new database cannot be smaller than the model database.

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 will be reflected in each new database. Some of the changes that System Administrators commonly make to model are:

• Adding user-defined datatypes, rules, or defaults.

• Adding users who should have access to all databases on Adaptive Server.

• Granting default privileges, particularly for "guest" accounts.

• Setting database options such as select into/bulkcopy/pllsort. The settings will be reflected in all new databases. Their original value in model is off. For more information about the database options, see Chapter 22, "Setting Database Options."

Typically, most users do not have permission to modify the model database. There is not much point in granting read permission either, since Adaptive Server copies its entire contents into each new user database.

The size of model cannot be larger than the size of tempdb. By default, the size of the model database is four allocation units. Adaptive Server displays an error message if you try to increase the size of model without making tempdb at least as large.

Keep a backup copy of the model database, and back up model with dump database each time you change it. In case of media failure, restore model as you would a user database.

sybsystemprocs database

Sybase system procedures are stored in the database sybsystemprocs. When a user in any database executes any stored procedure, 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 by that name, Adaptive Server looks for the procedure in master.

If the procedure modifies system tables (for example, sp_adduser modifies the sysusers table), the changes are made in the database from which the procedure was executed.

To change the default permissions on system procedures, you must modify those permissions in sybsystemprocs.

Any time you make changes to sybsystemprocs, you should back up the database.

tempdb database

Adaptive Server has a temporary database, tempdb. It 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.

The default size of tempdb depends on the logical page size for your server, either 2, 4, 8, or 16K. Certain activities may make it necessary to increase the size of tempdb. The most common of these are:

• Large temporary tables.

• A lot of activity on temporary tables, which fills up the tempdb logs.

• Large sorts or many simultaneous sorts. Subqueries and aggregates with group by also cause some activity in tempdb.

You can increase the size of tempdb with alter database. tempdb is initially created on the master device. Space can be added from the master device or from any other database device.

Creating temporary tables

No special permissions are required to use tempdb, that is, to create temporary tables or to execute commands that may require storage space in the temporary database.

Create temporary tables either by preceding the table name in a create table statement with a pound sign (#) or by specifying the name prefix "tempdb..".

Temporary tables created with a pound sign are accessible only by the current Adaptive Server session: users on other sessions cannot access them. These nonsharable, temporary tables are destroyed at the end of each session. The first 13 bytes of the table's name, including the pound sign (#), must be unique. Adaptive Server assigns the names of such tables a 17-byte number suffix. (You can see the suffix when you query tempdb..sysobjects.)

Temporary tables created with the "tempdb.." prefix are stored in tempdb and can be shared among Adaptive Server sessions. Adaptive Server does not change the names of temporary tables created this way. The table exists either until you restart Adaptive Server or until its owner drops it using drop table.

System procedures work on temporary tables, but only if you use them from tempdb.

If a stored procedure creates temporary tables, the tables are dropped when the procedure exits. Temporary tables can also be dropped explicitly before a session ends.

Warning!

Do not create temporary tables with the "tempdb.." prefix from inside a stored procedure unless you intend to share those tables among other users and sessions.

Each time you restart Adaptive Server, it copies model to tempdb, which clears the database. Temporary tables are not recoverable.

sybsecurity database

The sybsecurity database contains the audit system for Adaptive Server. It consists of:

• 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

The audit system is discussed in more detail in Chapter 12, "Auditing."

sybsystemdb database

The sybsystemdb database stores information about distributed transactions. Adaptive Server versions 12.0 and later can provide transaction coordination services for transactions that are propagated to remote servers using remote procedure calls (RPCs) or Component Integration System (CIS). Information about remote servers participating in distributed transactions is stored in the syscoordinations table.

Adaptive Server version 12.0 and later distributed transaction management services are available as a separately-licensed feature. You must purchase and install a valid license for Distributed Transaction Management before it can be used. See Using Adaptive Server Distributed Transaction Management Features and the installation guide for more information.

The sybsystemdb database also stores information about SYB2PC transactions that use the Sybase two-phase commit protocol. The spt_committab table, which stores information about and tracks the completion status of each two-phase commit transaction, is stored in the sybsystemdb database.

Two-phase commit transactions and how to create the sybsystemdb database is discussed in detail in the configuration documentation for your platform.

pubs2 and pubs3 sample databases

Installing the pubs2 and pubs3 sample databases is optional. These databases are provided as a learning tool for Adaptive Server. The pubs2 sample database is used for most of the examples in the Adaptive Server documentation, except for examples, where noted, that use the pubs3 database. For information about installing pubs2 and pubs3, see the installation documentation for your platform. For information about the contents of these sample databases, see the Transact-SQL User's Guide.

Maintaining the sample databases

The sample databases contain a "guest" user that allows access to the database by any authorized Adaptive Server user. The "guest" user has been given a wide range of privileges in pubs2 and pubs3, including permissions to select, insert, update, and delete user tables. For more information about the "guest" user and a list of the guest permissions in pubs2 and pubs3, see Chapter 10, "Managing Adaptive Server Logins and Database Users."

The size of the pubs2 and pubs3 databases are determined by the size of the logical page size for your server, 2, 4, 8, and 16K. If possible, you should give each new user a clean copy of pubs2 and pubs3 so that she or he is not confused by other users' changes. If you want to place pubs2 or pubs3 on a specific database device, edit the installation script before installing the database.

If space is a problem, you can instruct users to issue the begin transaction command before updating a sample database. After the user has finished updating one of the sample databases, he or she can issue the rollback transaction command to undo the changes.

pubs2 image data

Adaptive Server includes a script for installing image data in the pubs2 database (pubs3 does not use the image data). The image data consists of six pictures, two each in PICT, TIF, and Sun raster file formats. Sybase does not provide any tools for displaying image data. You must use the appropriate screen graphics tools to display the images after you extract them from the database.

See the the installation documentation for your platform for information about installing the image data in pubs2.

dbccdb database

dbcc checkstorage records configuration information for the target database, operation activity, and the results of the operation in the dbccdb database. Stored in the database are dbcc stored procedures for creating and maintaining dbccdb and for generating reports on the results of dbcc checkstorage operations. For more information, see Chapter 25, "Checking Database Consistency."

sybdiag database

Sybase Technical Support may create the sybdiag database on your system for debugging purposes. This database holds diagnostic configuration data, and should not be used by customers.