Sybase Database Administration: Part 2
Last issue (Sept/Oct '94) I gave a brief overview of
Sybase and a
detailed description of how Sybase catalogs the relationships
logical and physical disk devices. I discussed the disk
command and introduced the create.diskinit program that
the logical-to-physical relationships of Sybase disk
month I will discuss database tables and provide two
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
tables (see Figure 1 for an example table), which are
a part of each
database. The create.table program (Listing 1) retrieves
system tables all the information required to generate
the SQL. The
SQL is used to recreate the table passed as the first
create.table. Figure 2 shows part of the five tables
by create.table. Because every Sybase database has a
table, the entries in Figure 2 are for that table.
How create.table Works
The create.table program can be broken down into six
The first section sets up environment variables. If
this program is
executed alone, then you can set all environment variables
create.table. If you'll execute this program with other
that require setting the same environment variables,
test sets them by using the dot command on a configuration
configuration file eliminates editing each program individually.
Section two checks the usage of create.table, ensuring
a table name is supplied for the first argument.
I will skip section three for a moment. Discussing section
more meaningful now.
Section four is the heart of create.table: it queries
Sybase server to request column information on the table
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,
and as a foreign key in eight other system tables. The
query in section
four executes a subquery, which returns the object ID
for the table named by argument one.
Every column in every table has an entry in the system
The query in section four joins the tables SYSCOLUMNS
returning column names in terms of their relationship
on the ID
column, which was returned from the subquery discussed
Every column must also belong to one specific datatype.
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.
length of each column is also returned.
As shown by the syntax of the CREATE TABLE statement,
column must be defined to either allow or disallow Null
on table creation. A Null represents the absence of
value. This information is stored in the table SYSCOLUMNS
as a bit
value. The query in section four returns a zero if Nulls
not allowed and a one if Nulls are allowed.
Tables are database objects which are placed on segments.
in section four joins the system table SYSSEGMENTS with
table SYSINDEXES, returning the segment name in terms
of their relationship
on the segment column.
The columns returned from the query in section four
by their colid. This guarantees that tables recreated
the SQL generated from the create.table program will
columns in their original order.
Also note that the indid of SYSINDEXES must be less
Tables will always have an indid in SYSINDEXES of zero
one. An indid of one is assigned to clustered indexes.
index is a special type of table.
The output of the query in section four is piped into
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
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
awk's output is saved in the file assigned to the variable
To return to section three, which I skipped earlier:
the syntax of
a CREATE TABLE statement must include a comma after
except for the last column being created. Section three's
is to create a program that adds commas after all columns
last. grep searches for the word Null to extract those
lines that contain column information. The last column
always be the fifth line from the last. A comma is placed
column except for the last. Notice that because the
is assigned a number that is dependent on the result
of a query to
the Sybase server, the shell could not complete variable
if the create.table program were a single executable.
the program created in section three is saved and executed
five, after the query has completed.
Section five executes the program created in section
five's output is filtered to replace question marks
with percent signs
for the SCCS keywords. The idea here is to prevent the
SCCS keywords when create.table is checked into SCCS.
table was created on the default segment, it is also
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
for a single database table, I also wrote a program
(Listing 2). create.table.all builds a list of all user
in a database, executing create.table for each table
in the list. This is a perfect example of when to use
file for variable assignments.
When executed for a given database, create.table.all
all SQL consistently.
About the Author
Bill Genosa is a UNIX Systems/Database Administrator
Consumer Group, located in Woodbury, New York. He works
computers on systems that serve some 150 users across
the US and in
Denmark and Singapore. You can reach Bill at 186 Bryant
Park, NY 11001, or via email as firstname.lastname@example.org.