db_update -- Controlling UNIX Enterprise Database Releases

Ed Schaefer

Application software changes generally require database schema updates. Questions about a software release often arise. Is the database schema in sync with the application object code? Was a previously required software version installed? Were there any errors during the update? If so, have these errors endangered the software release and should the update be reversed? Answering these questions requires a concise, consistent method for updating, as well as reversing changes.

This article presents a Korn shell template, db_update, for controlling enterprise database changes (see Listing 1). db_update, with the database vendor's access utility, controls a release by defining the previous and current release and executing Structure Query Language (SQL) statements against the database. I call db_update a template because a base version of db_update exists in my Revision Control System, RCS. I create a separate shell script based on the release name (e.g., 2001.1.0.chg.ss), place my desired database changes in this script, and check this new file into RCS. In this article, I refer to the script as "db_update".

After covering the major database vendors and portability issues, I will discuss the following db_update program design in detail:

• If no command-line option exists, execute the usage function and terminate.
• If the database engine is not functioning, terminate with an error.
• If the database cannot be opened, terminate with an appropriate error.
• Determine whether the command-line option is an update or an undo.
• If other users are using the database, prompt whether to continue.
• If continuing, get the Revision Control System, RCS, number of the script.
• If executing an update and the previous version is not installed, terminate with an appropriate error.
• If executing an update and the current version is already installed, terminate with an appropriate error.
• If executing an undo and the current version is not yet installed, terminate with an appropriate error.
• Execute the update or undo SQL statements.
• Register the completed update or undo.

The three major UNIX database vendors provide their own access utilities: Sybase has isql, Oracle relies on sqlplus, and Informix bundles dbaccess. Typically, these utilities are called from shell scripts.

Utilizing these utilities in shell scripts is not new to Sys Admin magazine. See "Using Oracle SQL in Shell Scripts" by Kyle Gleed and Scott Tarvainen (May 1995, http://www.samag.com/documents/sam9505f/); William Genosa's three-part series on Sybase systems administration (September/October 1994, http://www.samag.com/documents/sam9409a/; November/December 1994, http://www.samag.com/documents/sam9411k/; and January/February 1995, http://www.samag.com/documents/sam9501h/), and my article, "Sybaccess: A Menu Driven Interface to Sybase" (July 1996, http://www.samag.com/documents/sam9607d/). In the above articles, the access utilities are explicitly hard-coded in the scripts. Conversely, db_update, an Informix-based utility, parametrizes the access utility and other pertinent data (e.g., database name, kill command, etc).

Because most industrial-strength UNIX utilities support pipes and here documents, porting db_update to another database environment is simplified by modifying the parameter definitions. Consider the following Informix query example:

DBNAME="testdb"     
INTERFACE_CMD="dbaccess"
DBCOMMAND="eval $INTERFACE_CMD -e $DBNAME"
   $DBCOMMAND << MSG
        SELECT * FROM test_table
MSG

SERVERNAME="TEST_SERVER"
USER="t_user"
PASSWD="t_pass"
DBNAME="testdb"     
DBCOMMAND="eval isql -S $SERVERNAME -U $USER -P $PASSWD"
   $DBCOMMAND << MSG
      use $DBNAME
      go
      SELECT * FROM test_table
      go
MSG

After reviewing the db_revision requirements, the rest of this article delves into the db_update script.

Reviewing the db_revision Table

For db_update to function correctly, each database accessed stores current and prior revision information in a db_revision table:

create table db_revision
  (
    version char(20),   # primary key
    install_date date,
    uninstall_date date,
    installed char(1),
    rcs_version char(10)
  );

Checking Engine and Database Health

After verifying the correct command-line argument count, perform the database engine checks. Shell function database_engine_running uses the robust Informix utility, onstat, to check whether the engine is up. Because describing Informix database specifics is not my intention, I hide the implementation details in ENGINE_UP variable.

While the database engine may be functioning, the database may not exist or access may be denied. The database_check function simply opens the database with a call to the dbaccess utility. If the call fails, the UNIX exit code ($?) is non-zero and the program terminates. The main_driver function then verifies the proper "update" or "undo" command-line argument, sets the proper log name, prompts the user to continue processing, and checks for users still using the database.

Checking for Database Users

Often, users aren't allowed to use the database while major schema changes are in progress. The check_session function checks whether any users are still using the database, and if so, prompts whether to terminate these users.

The Informix onstat utility determines whether user SQL sessions are active and the Informix onmode utility is optionally used to kill a session. Again, implementation details are hidden with shell variables SESSIONS, ONSTAT_SES, ONSTAT_SQL, and KILL.

Retrieving the RCS Version

Because db_update is under RCS change control, the version of this particular script is stored in the rcs_version function:

str="$Revision: 1.0 $"

Checking the Previous Version

The current version of the schema change (defined by shell variable VERSION) cannot be installed if the previous required version (defined by shell variable PREVIOUS) has not been installed. The check_previous function performs this check.

The check_previous function returns immediately if PREVIOUS is not required. If required, determine whether the PREVIOUS version exists in the db_revision table. If it does not exist, PREVIOUS was never installed, and terminate with a required version is not installed error message.

Checking the Current Version

If this is an update, the check_current function determines whether the current VERSION is already installed.

If a row exists in the db_revision table for VERSION and the installed flag is "Y", set the UNIX exit code, $?, to 1 specifying the current version is installed. Otherwise, set the exit code to 0 specifying the current version is not installed.

If this is an update and the VERSION is already installed, terminate with an update previously installed prompt. If this is an undo and the VERSION is not yet installed, terminate with a version has not been installed message.

Explaining check_current Return Value

A number of db_update's functions (rcs_function, database_check, database_engine_running) trap the standard output of commands, such as echo, print, or printf, and use this output as the function return value. All standard output is treated as the return value, not just the echo or print command concluding the function. In check_current, DBCOMMAND writes to standard output, so avoid the standard output side effects by using the UNIX exit code.

Updating/Undoing the Database Schema

After performing all the database and version checks, execute the required database changes. Assuming the update option, place in the change_schema function, the SQL required for the changes, calls to external programs, or calls to other shell functions. The example change_schema function only creates table, wo_table.

To undo a previously installed version, the reverse_schema function contains the SQL to undo the changes made in function change_schema. In this example, simply drop the wo_table.

Registering the Update

Once the Update completes, call the register_update function to update the db_revision table. If this is an initial install, a new record is created registering the new database version. If this is an update after an undo, the VERSION record exists in db_revision, but the installed column is "N". Register the update by setting the installed column to "Y".

The db_revision's rcs_version column updates each time register_update executes. Since db_update may be checked in and out of RCS many times during development, it's often important to know what RCS version of db_update updated a given database.

Registering the Undo

Similar to the update, once an undo option completes, executing function register_undo registers the undo. Previous code checks guarantee a record exists in db_revision for VERSION, so the installed flag is set to "N".

Error Checking

DBCOMMAND_OTF redirects all pertinent schema output to a log file. Once registration completes, I search the log file for string "Error"; any instance of "Error" is an alert that the Informix engine/database had a possible execution problem and should be investigated.

Conclusion

The db_update template is one method of addressing, codifying, and controlling a daunting database administration task. If you are not running Informix, db_update's structure should ease conversion to another environment.

Although it's beyond the scope of this article, the SQL statements in the check_current, check_previous, register_update, and register_undo functions are excellent stored procedure candidates.

Ed Schaefer is a frequent contributor to Sys Admin. He is a DBA for Intel's Factory Integrated Information Systems, FIIS, in Aloha, Oregon. Ed also hosts the UnixReview.com monthly Shell Corner column. He can be reached at olded@ix.netcom.com.