Sybaccess: A Menu-Driven Interface to Sybase
Ed Schaefer Sybase provides an interactive SQL (ISQL) utility for their UNIX database management system for the execution of SQL data management statements. Because ISQL is basically a command-line interpreter, the user help for ISQL is quite austere. To hide the ISQL implementation from software support people, I have developed a shell script, called Sybaccess (Listing 1), that provides a familiar menu interface to ISQL. Sybaccess provides a menu interface from which to choose and display an active database and table. After choosing a database and table, you can display the number of rows in the table and the columns and indexes of the active table. The query option allows entry and execution of valid data management statements. Finally, the administration tools include loading and unloading a table with data and updating statistics on the active table. Why Does Sybaccess Work? The weakness of ISQL as a no-frills utility is also its strength; ISQL is designed as a UNIX pipe, so the crux of Sybaccess is echoing valid SQL or Data Control commands (or concatenating the contents of a disk file containing SQL commands) through a pipe to ISQL and finally, acting or displaying the output. Executing Sybaccess To execute, Sybaccess minimally needs an ISQL userid (the system administrator in our case) and an associated password. If the user or password is undefined or illegal, Sybaccess terminates with an error message. Other optional command-line arguments are the sybase directory location, the current database name, and the Sybase server instance. Not defining the server forces Sybaccess to use the Sybase default instance. Sybase multiple instances are beyond the scope of this article. If the user, password, or current database are not defined, they can be parsed from the optional configuration file. Below is an example configuration file:
DATABASE:v64:what is the user database USER:sa:what is the user name PASSWORD:jetech:what is the user password
The three fields, delimited by colons, are the variable name, the value, and a comment, respectively. See the sidebar "The Sticky Bit and Sybaccess" for a security-related discussion on accessing the configuration file. Main Processing Loop All functions needed by Sybaccess (lines 107-466 in the listing) are declared before processing begins. After opening the current DATABASE using the open_db() function, the main processing loop (lines 468-489) shows the current database with the display_db() function, displays the main menu with the display_main() function, and queries the user for input until termination. An example main menu with the current database being testdb is shown in Figure 1. The current database is always displayed to the user because all input from the Query language and Table information menu options will act on this database. The open_db() function (lines 129-147) attempts to open the database. The Sybase syntax to open a database, use $DATABASE, is echoed and piped to ISQL. Any output from ISQL is interpreted as an error and DATABASE is set to the string, "NO DATABASE." Selecting Another Database The Database select option allows the user to choose another database. The name of each Sybase database is in the master database in the sysdatabases table. The database_select() function (lines 442-465) displays each active database to the screen and prompts the user to enter a new database. The Sybase-defined databases master, model, sybsystemprocs, and tempdb are ignored. The Query Language Option Database administration is contingent upon entering SQL and other Data Control statements. Sybaccess allows the user to edit statements in a file and finally execute the contents of that file. Editing is done using the editor pointed to by the EDITOR variable, set to vi in this case. This menu is shown in Figure 2. All the options are trivial except executing the file contents: Once the file, called the $edit_file in the code, is created using the New, Modify, or Load functions, use the run_edit() function (lines 149-164) to actually execute the commands. The run_edit() function creates a run file, the contents of which are the current database, the contents of the edit file, and a terminating GO. The run file is piped to ISQL for execution. The results are sent to an output file using the tee command and are displayed to standard output with the pager utility, pg. The output file's basename is "output," and the extension is the process id of the current executing program. This output file is not deleted upon program termination. The Table Information Option With Sybaccess table options, you can display the columns and indexes of the current table and obtain the number of records in the current table. The do_table() function (lines 320-359) displays the Table menu shown in Figure 3. You can select a different current table with the select_table function() (lines 411-427). This function displays all the active tables (minus the system tables) in the current database to standard output and prompts the user to enter a valid table. Sybase stores the names of all tables in a database in the sysobjects system table. You can obtain the number of records in the current table using the cnt_table() function (lines 429-440). A simple select count on table is echoed to the ISQL utility returning the number of records in the table. To display the indexes or columns of the current table, call two external shell scripts, create.index or create.table, respectively. These shell scripts are from the Sybase Database Administration series by William Genosa previously published in Sys Admin. The Genosa utilities have been modified to support command-line options similar to Sybaccess. To conserve space, these shell scripts are not printed, but are available from the Sys Admin ftp site (ftp.mfi.com in pub/sysadmin). The Administration Option Sybaccess administration options include loading the current table from an ascii file and unloading the current table to an ascii file. An Update Statistics utility is also provided. The display_admin() function (lines 166-180) displays the administration menu shown in Figure 4. To load from or unload to an ascii file, use the Sybase Bulk Copy UNIX utility, bcp (lines 304 and 309). In this implementation of Sybaccess, the ascii file basename is the current table name with an extension of unl. The ascii file field delimiter is defined as the pipe symbol for both load and unload. You should perform update statistics on the database routinely to enhance database query performance. Sybase performs update statistics on the individual table only, so the data control statement is:
update statistics <table name>
The do_statistics() function (lines 360-371) automates the drugery of updating each table indivdually by selecting each user table from the current database sysobjects table and building a script that updates statistics for each table entry. This script is echoed to the ISQL utility for execution. Conclusion Improvements to Sybaccess are limited only by imagination or need. For example, you could add your favorite Sybase script to the Administration menu. Sybaccess as a shell script is a typical UNIX prototype. You can enhance performance and tighten security by rewriting Sybaccess in a higher level language such as C. References Genosa, W. 1994. Sybase Database Administration: Part 2. Sys Admin 3(6):107-116. Genosa, W. 1995. Sybase Database Administration: Part 3. Sys Admin 4(1):78-90. Garbus, J., D. Solomon, and B. Tretter. 1995. Sybase DBA Survival Guide. Indianapolis, IN: SAMS Publishing.
About the Author
Ed Schaefer is a frequent contributer to Sys Admin. He is an Informix Database Administrator and UNIX administrator for jeTECH Data Systems of Moorpark, CA, where he develops Time and Attendance Software. He can be reached at olded@ix.netcom.com.
|