Automating Database Space Monitoring

Carolyn Conner

System activity monitoring often gets put on the back burner, although it is essential for future planning and problem avoidance. When system administrators are up to their necks in critical problems, they often find it easy to reschedule system activity monitoring to next week, or next month, or "when I get time." Because system monitoring is not considered a critical problem that must be handled immediately, and because gathering the necessary information required can be time consuming and boring, this task is usually assigned to a junior administrator. Unfortunately, by the time that junior sys admin has received enough training to accurately complete system monitoring tasks, he or she is too valuable a resource not to use in dealing with the daily critical problems.

Automating the process of gathering system monitoring information is one way to reduce the burden of this task. Before writing the scripts to accomplish this, however, several criteria should be considered to help make these scripts a viable, long-term solution. The first criterion is to have only one copy of all scripts located on one system in one specific directory, however, you should make these scripts capable of executing on all of the appropriate systems. Keeping these scripts on one system and in one location simplifies maintenance of the scripts. To execute these scripts on other systems, this script directory can be NFS mounted or automounted on the appropriate UNIX systems.

The second script criterion is to use data currently available on the system and to avoid duplication of any data used by the scripts. That is, if this data exists and is maintained in a specific place on the system, it should not be copied or recreated in another location. This practice prevents the problems associated with maintaining duplicate data (i.e., keeping it up-to-date and in sync). Also, if the data already exists and is vital to the functioning of the system, it is certain to be kept current.

The third script criterion is to make the scripts as maintenance free as possible. As the systems change, the scripts should be able to adjust as much as possible without administrator alteration. Variables should be used as often as possible and listed near the beginning of the scripts. Whenever possible, dynamic system data can be queried every time the scripts are executed to generate the most current data.

This article focuses strictly on a Sybase database space monitoring script, but it may be adjusted to perform other functions. The biggest time consumer when monitoring database space manually is logging into each Sybase/UNIX server to access its database information. Once you've logged into the correct server, you must do a series of repetitive commands to obtain the segment information for each database on that Sybase server.

The script monitors the database space for several Sybase servers, each running on its own independent UNIX box. All of these Sybase servers experience a lot of activity in terms of database addition and deletion. Because there is no reliable list of which database resides on which Sybase/UNIX server, queries must be done at script execution to obtain the names of the current databases on each server.

This information is collected daily, so it can provide a historical record for use in anticipating database growth. All of the data is then consolidated in an easy-to-read format for the various Sybase servers and their databases.

Following the first script criterion, this database space script is located on only one UNIX system in a script directory, but is able to access all of the other Sybase/UNIX servers. In this case, mounting the script directory on the other UNIX servers is unnecessary. The database space script issues SQL commands on the other Sybase/UNIX servers through remote procedure calls (RPC). The information that Sybase requires for RPCs (i.e., the name and address of every Sybase server) is contained in a Sybase system file. The name, location, and format of this file depends on the operating system. The most common names are interfac, sql.ini, or, in this case, interfaces.

The recommended way to maintain this file is by using the Sybase utility sybinit. Listing 1 shows an example of a Sybase interfaces file from a SUN Solaris 2.x system.

The comment lines (beginning with ##) in the interfaces file are created by the Sybase sybinit utility and are critical to the database space script. For example, the comment "## SUNSYB01 on dssu001" indicates that the Sybase SQL server is called SUNSYB01 and that it is running on UNIX host dssu001. According to our standards, all Sybase SQL servers are named SUNSYB##. All other server names (i.e., OMNI, SYBBKP##) are disregarded by the database space script. Minor script modifications can be made to include Sybase SQL server names that do not meet the standard.

The second script criterion (to use existing data and avoid duplication of data) is also achieved by using the Sybase interfaces file. Because this file is required by Sybase to communicate with other clients and servers, the Sybase administrator is responsible for keeping it as accurate as possible. The database space script reads this file each time that it is executed, so the only way a Sybase SQL server will not have its database space monitored, is if the Sybase administrator does not add this server to the interfaces file. Because the different Sybase SQL environments are tightly linked, the database space script is not the only, or the most critical, task affected by the omission of a Sybase SQL server from the interfaces file.

The third script criterion is to make the database space script as maintenance free as possible. This is done by using variables to represent the Sybase and UNIX servers. These variables (sybase and unix) are populated by interrogating the interfaces file every time this script is executed. The script creates an execution time list of the current Sybase/UNIX servers and an execution time list of every user database. These two lists are created each time the database space script is executed.

A logon must be set up that can access all of the user databases on all of the Sybase SQL servers. All of the Sybase system databases are excluded from database space monitoring in this script, but they can easily be included. This logon must be defined as a user in each user database on all of the Sybase SQL servers. The easiest logon to use is sa, the Sybase superuser, since sa is automatically given access to every database when it is created. If another logon is used in place of sa, someone must remember to manually give this logon access to each user database when it is created. If the logon is not able to access a user database, the segment information about that database will not be reported by the database space script.

Segments are labels that point to one or more database devices. Equating a segment with a database device is really important if the database was created using user-defined segments or if a Sybase system segment (i.e., default or system) was deleted. When a segment runs out of free space, no more data can be inserted until that segment is expanded onto another database device. This can be catastrophic in a high-update environment.

Using sa as the logon is more reliable than making someone remember to give another logon access to each user database when it is created. Instead of coding the sa password in the database space script, the password is located in the file /home/sybadm01/sajobs. The password is passed to the sa logon in the database space script (Listing 2) using the following commands:

cat /home/sybadm01/sajobs | isql -Usa

By accessing the sa password in this manner, the password does not need to be hard coded into all the scripts that use sa to access the databases. Also, when the sa password is changed, it needs to be changed in only one file instead of in every script that uses it. Because the file is restricted, for security purposes, all scripts using this file must be executed by a user or a cron with read access to it.

# ls -l /home/sybadm01/sajobs
-rw-r-----   1 sybadm01 sybadm      /home/sybadm01/sajobs

The Sybase administrator is responsible for changing the sa password in the Sybase SQL servers and in /home/sybadm01/sajobs. If the password in the Sybase SQL server does not match the one in /home/sybadm01/sajobs, several daily database maintenance jobs will fail, thus this file must be accurately maintained. The database space script assumes that the sa password is the same on all Sybase SQL servers, but because this is not always the case, a few modifications to the script can accommodate different sa passwords on different servers.

The result of the database space script is the report shown in Listing 3. The report contains the information needed for basic user database space monitoring. Once the report has been reviewed to determine whether any of the user databases need more space, it can be printed or copied into an EXCEL worksheet to be used a historical record for predicting database growth.

Few system administrators have the time to devote to system activity monitoring; however, taking the time to write some scripts for collecting the necessary monitoring information is a wise investment. Critical, high-impact areas such as user database space can be targeted as a place to begin monitoring. In this case, even a simple preventative action can stop big problems from happening.

About the Author

Carolyn Conner, previously a mainframe systems programmer, is a two year veteran UNIX administrator for Diamond Shamrock, a refining and marketing company. She can be reached at carolyn_conner@diasham.com