Sybase Database Administration: Part 2

William Genosa

Last issue (Sept/Oct '94) I gave a brief overview of Sybase and a detailed description of how Sybase catalogs the relationships between logical and physical disk devices. I discussed the disk init command and introduced the create.diskinit program that reverse-engineers the logical-to-physical relationships of Sybase disk devices. This month I will discuss database tables and provide two useful tools that generate the SQL required to recreate a single table, or all the tables in a Sybase database.

Sybase catalogs all the information about database objects in system tables (see Figure 1 for an example table), which are a part of each database. The create.table program (Listing 1) retrieves from system tables all the information required to generate the SQL. The SQL is used to recreate the table passed as the first argument to create.table. Figure 2 shows part of the five tables used by create.table. Because every Sybase database has a SYSSEGMENTS table, the entries in Figure 2 are for that table.

How create.table Works

The create.table program can be broken down into six sections. The first section sets up environment variables. If this program is executed alone, then you can set all environment variables within create.table. If you'll execute this program with other tools that require setting the same environment variables, section one's test sets them by using the dot command on a configuration file. The configuration file eliminates editing each program individually.

Section two checks the usage of create.table, ensuring that a table name is supplied for the first argument.

I will skip section three for a moment. Discussing section four is more meaningful now.

Section four is the heart of create.table: it queries the Sybase server to request column information on the table named in the first argument.

Every table within a database has a unique ID number assigned to it. That ID is used as the primary key for the system table, SYSOBJECTS, and as a foreign key in eight other system tables. The query in section four executes a subquery, which returns the object ID from SYSOBJECTS for the table named by argument one.

Every column in every table has an entry in the system table, SYSCOLUMNS. The query in section four joins the tables SYSCOLUMNS and SYSINDEXES, returning column names in terms of their relationship on the ID column, which was returned from the subquery discussed above.

Every column must also belong to one specific datatype. All datatypes are defined in the system table, SYSTYPES. The query in section four joins the tables SYSTYPES with SYSCOLUMNS, returning data type names in terms of their relationship on the usertype column. The length of each column is also returned.

As shown by the syntax of the CREATE TABLE statement, each column must be defined to either allow or disallow Null values on table creation. A Null represents the absence of a recorded value. This information is stored in the table SYSCOLUMNS as a bit value. The query in section four returns a zero if Nulls are not allowed and a one if Nulls are allowed.

Tables are database objects which are placed on segments. The query in section four joins the system table SYSSEGMENTS with the system table SYSINDEXES, returning the segment name in terms of their relationship on the segment column.

The columns returned from the query in section four are ordered by their colid. This guarantees that tables recreated with the SQL generated from the create.table program will restore columns in their original order.

Also note that the indid of SYSINDEXES must be less than two. Tables will always have an indid in SYSINDEXES of zero or one. An indid of one is assigned to clustered indexes. A clustered index is a special type of table.

The output of the query in section four is piped into awk. awk's BEGIN statement creates a header with SCCS keyword identifiers as well as the CREATE TABLE statement itself. awk checks each line for two criteria: first, whether Nulls are allowed for each column, and second, whether the data type is a char (character), because a char datatype must include the length. awk's END statement includes the segment where this table is to be placed, along with the SQL terminator, go. awk's output is saved in the file assigned to the variable QUERYOUT.

To return to section three, which I skipped earlier: the syntax of a CREATE TABLE statement must include a comma after each column except for the last column being created. Section three's function is to create a program that adds commas after all columns except the last. grep searches for the word Null to extract those lines that contain column information. The last column created will always be the fifth line from the last. A comma is placed after each column except for the last. Notice that because the variable LINES is assigned a number that is dependent on the result of a query to the Sybase server, the shell could not complete variable substitution if the create.table program were a single executable. Therefore, the program created in section three is saved and executed in section five, after the query has completed.

Section five executes the program created in section three. Section five's output is filtered to replace question marks with percent signs for the SCCS keywords. The idea here is to prevent the expansion of SCCS keywords when create.table is checked into SCCS. If the table was created on the default segment, it is also filtered out because default is implicit and Sybase does not allow you to explicitly specify default as a segment. Final output is left in the file assigned to the variable SQL.

Section six cleans up by removing files no longer needed.

Recreating All User Tables

The create.table program only generates the SQL to recreate a single table. Because I wanted to generate SQL to recreate all user tables in a database without losing the ability to generate the SQL for a single database table, I also wrote a program called create.table.all (Listing 2). create.table.all builds a list of all user tables in a database, executing create.table for each table named in the list. This is a perfect example of when to use the .config file for variable assignments.

When executed for a given database, create.table.all generates all SQL consistently.

About the Author

Bill Genosa is a UNIX Systems/Database Administrator with Harman Consumer Group, located in Woodbury, New York. He works with RS6000 computers on systems that serve some 150 users across the US and in Denmark and Singapore. You can reach Bill at 186 Bryant Avenue, Floral Park, NY 11001, or via email as wgenosa@attmail.com.