Sybase Database Administration

William Genosa

System administrators are sometimes asked to wear different hats. Because UNIX systems are often used as database servers, some system administrators are also database administrators.

Because of its high performance, Sybase is one of the most popular relational databases currently in use. Unfortunately, Sybase is weak in database administration tools. Since database administration is part of my job, I have developed several tools that help me maintain my company's databases. The first tool generates SQL statements that can be used with other scripts to recreate your Sybase server. My tools mix UNIX shell scripts with SQL. (For those of you unfamiliar with Sybase, the sidebar provides a brief overview.)

Sybase Structures

Sybase automatically creates three databases when installed: the master, the model, and the tempdb databases. Master is the system catalog that maintains a definition of the server. Tempdb is the work space for the server, recreated every time you reboot your server. When a database is created, model is used as the template. If model becomes corrupt and tempdb cannot be created, you may not be able to reboot your server.

Sybase stores information on pages. A page is 2048 contiguous bytes of disk space on all Sun and AIX platforms. Pages are grouped into extents of 8 contiguous pages, and extents are grouped into allocation units of 32 contiguous extents. An allocation unit is thus 512K contiguous bytes of disk space. The disk init command is used to initialize disk space into pages. When you create a database, those pages are grouped into extents, and the extents into allocation units. The syntax of the disk init command is as follows:

disk init name = "sybase_name",
physname = "unix_name",
vdevno = unique_number,
size = number_of_pages

Each field of the disk init command is described below. The physical name is how the device is known to the operating system. The database administrator assigns a logical name that Sybase will use to reference the device. The database administrator also assigns a device number, which must be unique on that server. This unique device number is used to calculate the virtual page address of each page within that device. The size is the number of pages in that device.

Because the device number, or vdevno, is a unique number chosen by the database administrator, Sybase assigns every 2k page in the server a unique address. Consider a vdevno of 13. Sybase uses the following algorithm to derive the first virtual page address of this device:

device number = 13 base 10
13 base 10 = 0xD base 16
0xD * 0x1000000 = 0xD000000 base 16
0xD000000 base 16 = 218103808 base 10
218103808 = starting virtual page address

Figure 1 and Figure 2 show part of the tables SYSDEVICES and SYSUSAGES from the system tables. Each vstart in SYSUSAGES lies between a low and a high attribute of one of the device entries in SYSDEVICES.

Notice that the SYSUSAGES table has an attribute called dbid. Sybase assigns each database a unique ID number. This is how Sybase keeps track of which raw devices are allocated to each database. Also note that a device may have more than one entry, such as device data04, which has entries for database 4 and database 5.

Because SYSUSAGES and SYSDEVICES define disk allocation for all databases on the server, they only exist in the master database. Because all database objects are stored in segments, each database has its own segment definition, or SYSSEGMENTS table. Figure 3 shows the table SYSSEGMENTS for database 5. Recall that a segment is defined on a device or collection of devices. When a segment is defined, an entry is created in SYSUSAGES that includes a bitmap field called segmap. If you raise two by the power of the segment number you will get the bit used in the bitmap for that segment. I have added a field to SYSSEGMENTS in Figure 3 for illustration. Figure 4 illustrates which bits would be set in the segmap of SYSUSAGES for dbid 5. This is how Sybase keeps track of which segments are defined on which devices.

Databases are created on database devices, segments are defined on database devices, and objects are placed on segments. Sybase allocates pages in extents. Therefore an object will be allocated all eight pages in an extent, one-thirty-second of an allocation unit on a segment. All pages in the server have unique page addresses. Since a backup is an image of your database, the page addresses must be identical when you restore your database. Because the disk init command establishes virtual page addresses, it is important to save this script. However, sometimes you inherit a system and must reverse-engineer what was done.

The create.diskinit Script

The create.diskinit script (Listing 1) was written on an RS-6000 but can also run on a Sun platform. You can comment out the RS-6000 specific section of code. This script generates the SQL that recreates the same database devices and page addresses that currently exist on your server.

The create.diskinit program is part of a suite of tools. Other programs in the suite generate the SQL to recreate databases, segments, tables, indexes, logins, users, defaults, rules, triggers, stored procedures, and data types.

The create.diskinit program has five major sections. The first section sets up environment variables. The second major section creates a temporary shell script that will be executed in section 4 to parse the information retrieved from the database in section 3. The reason for creating a temporary executable in section 2 has to do with the way the shell performs variable substitution. The shell scans a shell script, replacing variables with their assigned values before executing the main body of the script. Because scanning is done before the main body execution, and a query must be done within the main body of this script before assigning all the variables, it is impossible to accomplish all five sections of create.diskinit with only one executable shell script. Section 4 runs the temporary executable created in section two, and section 5 removes all the unneeded temporary files.

Environment variables are assigned in section 1 of create.diskinit. Because create.diskinit is one of a suite of tools, rather than edit each script separately, I have all scripts in the suite check for the existence of the same configuration file. If this configuration file exists, executing the configuration file with the dot command will set the environment variables. This eliminates the need to edit each script in the suite individually.

Section 2 of create.diskinit creates a shell script named in the PROGRAM variable. This script takes input from the file produced by an SQL query in section 3, is executed in section 4, and is removed in section 5. The query executed in section 3 will produce one line of output for each Sybase disk allocation. The four fields of each line read from the input file are the name, physname, vdevno, and size of each Sybase disk allocation. The script created by section 2 of create.diskinit reads each line of section 3's input file and generates the diskinit SQL statements required to re-establish the Sybase disk allocations with the proper name, size, and virtual page addresses. Because this script was written on an RS-6000, logical volume information is also included. To run create.diskinit on a Sun platform, comment out the code marked RS-6000 specific in section 2. (For detailed information on the logical volume manager, see Sys Admin July/August 1994, pp. 95-101.

Section 3 of create.diskinit queries the Sybase server, retrieving the four fields of information required to generate the diskinit SQL for each Sybase disk allocation. Because SQL returns header information, each column is named QQZZ#, allowing the header to be filtered out with egrep. All the data is retrieved from the system tables SYSDEVICES and SYSUSAGES, which hold the server's definition.

The keyword "select" performs an SQL query. The columns to be retrieved, or attributes to be projected, are listed after the keyword "select". The keyword "from" lists the tables for the query to search. Because more than one table is listed after the keyword "from," this query involves a table "join". The join is qualified by the "where" clause. The "where" clause is followed by conditions that restrict the join to those rows satisfying the predicate conditions of the "where" clause. Each row returned from this query will have a vstart in SYSUSAGES that is between the low and high of an entry from SYSDEVICES. The cntrltype must be for a disk device, not a tape device. The status can be for a default or non-default disk device. Default segments are defined on default disk devices. Notice there are two queries, one for default disks and one for non-default disks. Both queries' outputs are combined by the "union" clause. An "order by" command sorts the output of the query by vdevno. The query is submitted to the server via the client isql. The server requires isql to provide a login and password. This query's output is saved in the file assigned to the variable QUERYOUT.

Section 4 of create.diskinit executes the script created by section 2. This section's output is redirected to the file assigned to the SQL variable. For those of you interested in source code control and version control, this section automatically includes SCCS keyword identifiers. If you are not using SCCS, then you can comment out those lines that generate the keywords. There are other packages providing source code control and product version control. Use the source code control utility of your choice.

Section 5 removes the two temporary files used by create.diskinit, the executable script created in section 2, and the file containing the data retrieved in section 3.

For illustration, I have included the diskinit.sql (Listing 2) that create.diskinit generated on this system after it was checked into SCCS. Run create.diskinit before you experience media failure. I hope you never need to use it. Whether or not you do need to use diskinit.sql, I expect it will provide you with additional confidence and security in performing your database administration job.

About the Author

Bill Genosa is a systems administrator for American Express, where he has responsibility for RS6000 workstations and servers. He can be reached at 186 Bryant Avenue, Floral Park, NY 11001, or via email as wgenosa@attmail.com.