Sybase Database Administration
System administrators are sometimes asked to wear different
Because UNIX systems are often used as database servers,
administrators are also database administrators.
Because of its high performance, Sybase is one of the
relational databases currently in use. Unfortunately,
Sybase is weak
in database administration tools. Since database administration
part of my job, I have developed several tools that
help me maintain
my company's databases. The first tool generates SQL
can be used with other scripts to recreate your Sybase
tools mix UNIX shell scripts with SQL. (For those of
with Sybase, the sidebar provides a brief overview.)
Sybase automatically creates three databases when installed:
the model, and the tempdb databases. Master is the system
that maintains a definition of the server. Tempdb is
space for the server, recreated every time you reboot
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
of disk space on all Sun and AIX platforms. Pages are
extents of 8 contiguous pages, and extents are grouped
units of 32 contiguous extents. An allocation unit is
thus 512K contiguous
bytes of disk space. The disk init command is used to
disk space into pages. When you create a database, those
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.
physical name is how the device is known to the operating
The database administrator assigns a logical name that
use to reference the device. The database administrator
a device number, which must be unique on that server.
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
the database administrator, Sybase assigns every 2k
page in the server
a unique address. Consider a vdevno of 13. Sybase uses
algorithm to derive the first virtual page address of
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
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
assigns each database a unique ID number. This is how
track of which raw devices are allocated to each database.
that a device may have more than one entry, such as
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.
database objects are stored in segments, each database
has its own
segment definition, or SYSSEGMENTS table. Figure 3 shows
SYSSEGMENTS for database 5. Recall that a segment is
defined on a
device or collection of devices. When a segment is defined,
is created in SYSUSAGES that includes a bitmap field
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
be set in the segmap of SYSUSAGES for dbid 5. This is
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.
pages in extents. Therefore an object will be allocated
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
when you restore your database. Because the disk init
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
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
on your server.
The create.diskinit program is part of a suite of tools.
programs in the suite generate the SQL to recreate databases,
tables, indexes, logins, users, defaults, rules, triggers,
procedures, and data types.
The create.diskinit program has five major sections.
section sets up environment variables. The second major
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
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
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
Environment variables are assigned in section 1 of create.diskinit.
Because create.diskinit is one of a suite of tools,
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
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
in the PROGRAM variable. This script takes input from
file produced by an SQL query in section 3, is executed
4, and is removed in section 5. The query executed in
section 3 will
produce one line of output for each Sybase disk allocation.
fields of each line read from the input file are the
vdevno, and size of each Sybase disk allocation. The
by section 2 of create.diskinit reads each line of section
3's input file and generates the diskinit SQL statements
to re-establish the Sybase disk allocations with the
size, and virtual page addresses. Because this script
on an RS-6000, logical volume information is also included.
create.diskinit on a Sun platform, comment out the code
RS-6000 specific in section 2. (For detailed information
on the logical
volume manager, see Sys Admin July/August 1994, pp.
Section 3 of create.diskinit queries the Sybase server,
the four fields of information required to generate
the diskinit SQL
for each Sybase disk allocation. Because SQL returns
each column is named QQZZ#, allowing the header to be
out with egrep. All the data is retrieved from the system
tables SYSDEVICES and SYSUSAGES, which hold the server's
The keyword "select" performs an SQL query.
The columns to
be retrieved, or attributes to be projected, are listed
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
The join is qualified by the "where" clause.
clause is followed by conditions that restrict the join
to those rows
satisfying the predicate conditions of the "where"
Each row returned from this query will have a vstart
that is between the low and high of an entry from SYSDEVICES.
cntrltype must be for a disk device, not a tape device.
can be for a default or non-default disk device. Default
are defined on default disk devices. Notice there are
one for default disks and one for non-default disks.
outputs are combined by the "union" clause.
by" command sorts the output of the query by vdevno.
is submitted to the server via the client isql. The
requires isql to provide a login and password. This
output is saved in the file assigned to the variable
Section 4 of create.diskinit executes the script created
section 2. This section's output is redirected to the
to the SQL variable. For those of you interested in
source code control
and version control, this section automatically includes
identifiers. If you are not using SCCS, then you can
comment out those
lines that generate the keywords. There are other packages
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
the data retrieved in section 3.
For illustration, I have included the diskinit.sql (Listing
that create.diskinit generated on this system after
was checked into SCCS. Run create.diskinit before you
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
with additional confidence and security in performing
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,
or via email as email@example.com.