Databases

Default Databases

Master

The Master Database control the operation of ASE server and stores information of all databases and their associated databases

The Master Database stores information about Users,Databases and Devices. You must be in Master Database in order to issue any command like Create Database,Alter Database, diskinit, diskrefit and mirroring commands.

It stores all predefined objects such as Stored Procedures,System Tables,Built in functions

Some system procedures that are stored in Master Database

• Syslogins stores user accounts
• Sysprocess stores the process which are currently running at particular time
• Sysdatabase stores the database information
• Sysdevices stores the disks and tapes that are mounted on system
• Sysusers stores the information about the storage space of each database
• Sysloginroles holds information about server wide roles

Backing up Master Database and keeping copy of System Tables

System Tables like

• Sysusages
• Sysdatabases
• Sysdevices
• Sysloginroles
• Syslogins

By taking a copy of Systables we can easily recover the Master Database when it gets corrupted due to Hard Disk Crash or Power Failure

Model

It contains a set of predefined templates for all the objects each time a user enter the create database command .ASE Server makes a copy of Model database and extends database the size specified by the create database command .

*** A new database cannot be smaller than the Model Database

Sybsystemprocs

Sybsystemprocs (Sybase System Procedures) are stored in the database SybSystemProcs,when a user in any database executes any stored procedures

• Adaptive Server looks First in Current database
• If no procedure with that name exists then Adaptive server look for in sybsystemprocs
• If there is no procedure in sybsystemprocs by that name adaptive server looks for the procedure in MASTER database

TEMP 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.

The default size of tempdb depends on the logical page size for your server, 2, 4, 8, or 16K.

Certain activities may make it necessary for you to increase the size of tempdb:

• Large temporary tables.
• A lot of activity on temporary tables, which fills up the tempdb logs.
• Large or many simultaneous sorts. Subqueries and aggregates with group by also cause some tempdb activity.

Use alter database to increase the size of tempdb. tempdb is initially created on the master device. You can add space to tempdb from the master device or from any other database device.

If you run update index statistics against large tables, the command fails with error number 1105 if tempdb is not large enough.

You can create and manage multiple temporary databases in addition to the system temporary database, tempdb. Multiple temporary databases reduce contention on system catalogs and logs in tempdb.

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

Creation of TEMP tables

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 non sharable, 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 by querying 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.

** Each time you restart Adaptive Server, it copies model to tempdb, which clears the database. You cannot recover temporary tables.

** whenever the server gets restarted tempdb gets cleared and tempdb is created with full space as early before .

sybsystemdb database

The sybsystemdb database stores information about distributed transactions. It is a Two Phase commit Transaction

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.

Optional Databases

Sybsecurity

Audit database is called as sybsecurity the database contains the Audit Systems for adaptive server. It stores information about the Auditing.

System tables are

• Sysaudits_01
• Sysaudits_02
• Sysaudits_03
• Sysaudits_04
• Sysaudits_05
• Sysaudits_06
• Sysaudits_07
• Sysaudits_08
• Audit history
• Audit Data

Pubs2 and Pubs 3

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

dbccdb

dbccdb checks storage records configuration information for the target database

This fixes the page linkage ,page allocation error on a given table on a given database

• Basic unit of measure of adaptive server is pages
• By default 2k page size where K is kilobyte.
• Also the size can be defined while installation

* dbccdb Database Consistency Checker

* Pubs2 and pubs 3 are training databases

* Sybsecurity is an Auditing Database