A database administrator (DBA) has the daunting task of managing a large enterprise's databases. Such an organization usually has a number of databases, often in various stages of development. The physical implementation details of the databases may differ on the various platforms where they are deployed. For each of these databases, the DBA must manage the inter-related database objects -- namely, the tables, indexes, constraints, views, events, rules, triggers, procedures, and security controls. The DBA must also monitor and tune the performance of all of the databases and ensure that they are adequately backed up to recover from any kind of disaster that may occur. In an environment with multiple DBMSs (for example, Sybase SQL Anywhere [formerly Watcom SQL] databases on the desktop, CA-OpenIngres and Informix databases on the operational servers, and the information warehouse stored in an Oracle database), the process is even more complex, because each DBMS's implementations differ.
As a result, a DBA has quite an extensive set of requirements for the tools offered by the DBMS. The DBA in control of the DBMS servers must install, configure, monitor, and tune the tools to ensure optimal utilization of the system resources. The development DBA's main requirement is an interactive tool, preferably with a GUI Explorer-like interface, for managing all of the databases and database objects during development. This tool is used to create, alter, and drop all of the databases, tables, indexes, constraints, views, events, rules, triggers, procedures, and security grants. Ideally, this tool should be wizard-driven, and it should hide the detail syntax of the underlying SQL commands used to manage all of the objects (but of course, it should be able to display the SQL on request to interested "techies" who want to verify that it is, indeed, performing the script correctly). The Explorer-like component should also be able to display all dependencies among the various database objects.
In an ideal world, the database object management tool I just described should also be able to generate SQL scripts to manage all of the database objects, which can, for example, be packaged in a release or with an application shipped to a remote machine or customer. It should be possible to generate these SQL scripts for a collection of objects -- for example, for an entire database or for a specific schema. It should also be possible to generate cross-referencing and detailed reports on the various database objects.
The production DBA requires performance monitoring and tuning facilities. (I covered these facilities were covered in detail in my September and October columns.) In a production environment, however, you also require either a batch facility or a scheduling mechanism for regular performance tuning tasks --for example, to regularly balance volatile tables and indexes. With a batch facility, you want to generate scripts, which can be submitted to the operating system's scheduling mechanisms, such as cron on Unix or the batch queue on VMS. Ideally, however, you want to specify graphically when these tasks should be performed, and the DBMS's facilities should take care of running the performance-tuning tasks. With either facility, you want to be able to monitor its progress and receive acknowledgment that each task has completed successfully. Finally, the DBAs in charge of the production and development databases need a variety of backup and restore facilities and utilities to specify, enforce, and monitor security controls.
In this month's column I investigate the DBA tools offered by the popular DBMSs, listed in alphabetical order; I will focus on the tools used to manage the various servers, databases, and database objects. I will address recovery and security in the next two columns.
Configuration by Forms (CBF) is a character-based utility running on the database server, which you use to configure the CA-OpenIngres servers. From CBF, you can configure the name server, the communication server(s), the DBMS server(s), the Star distributed database server(s), the recovery server, the archive process, and the details of the transaction log files, as well as the parameters of the logging and locking systems.
With CA-Visual/DBA, you can browse through an explorer to create, alter, and drop databases, locations, users, groups, roles, tables, indexes, constraints, views, rules, procedures, events, and their respective security grants. When you create views, stored procedures, and rules (the CA-OpenIngres equivalent of triggers), CA-Visual/DBA has a wizard to help you write the SQL-like code. When you create the database objects, push-buttons, pick-lists, and pull-down menus help you select the correct details for each object. You can use CA-Visual/DBA on any CA-OpenIngres database accessible through the network. It gives you the full enterprise view of all of your CA-OpenIngres databases. You can copy database objects between databases by dragging the icons of the objects from the source database and dropping them onto the target database's icon. For tables, CA-Visual/DBA also prompts you to copy the table contents. You can construct, test, and run SQL queries against your databases, and you can calculate space requirements from CA-Visual/DBA. You can manage all of your replication configurations, and you can generate, inspect, and load statistics for the query optimizer.
CA-OpenIngres includes a utility called copydb, which can be run on the server or from Visual DBA and can be used to generate SQL scripts to copy database objects from one database to another. For tables, you can copy only the table definition or the table's contents as well. When you generate a script for an entire database, the script includes the definitions of all of the tables, constraints, indexes, views, rules, procedures, events, and their respective security grants. However, copydb transforms CREATE TABLE constraints to named constraints, which are listed separately from the table using ALTER TABLE ADD CONSTRAINT statements. For unnamed constraints, copydb assigns long, unpronounceable generated names. You can selectively generate scripts for named tables, indexes, and views, but then it only includes the definitions of the objects and their security grants. For individual tables, copydb generates their index definitions as well, but it doesn't generate their rules and procedures. The scripts generated by copydb are executed by the CA-OpenIngres SQL terminal monitor. The terminal monitor can execute SQL database definition and database manipulation statements; however, it can also execute statements coded in QUEL, Ingres's proprietary relational algebra language. Old Ingres gurus often augment their SQL scripts with powerful QUEL statements to include online error handling, branching, and looping constructs in the SQL scripts.
For ISAM and hash tables and indexes, you must sometimes re-modify the table and re-create its indexes. This process reclaims deleted space, removes overflow chains, and re-creates open spaces if you specified low fill factors. You should also regularly update the optimizer's statistics on the value distributions in the tables. Use copydb to generate some of these performance-tuning scripts, which can be submitted to the operating system's batch utilities for regular performance-tuning tasks.
SQLConsole contains a utility to unload (and reload) the entire database or only selected tables as SQL statements or as delimited data in an ASCII or a DIF file. The unload and reload processes create log files, which you can inspect to check the success or failure of the process. You can use SQL script files to create all of the other database objects through SQLTalk. However, you cannot generate SQL files from an existing database; you must store your SQL script files if you want to deploy them against other databases.
SQLConsole contains a scheduler, through which you can schedule backup operations, database maintenance tasks, and other shell programs. The database maintenance tasks include checking the database's consistency and updating the optimizer statistics. To reorganize the physical table structures, you can activate a reorganize command through a script. The scheduler also contains retry options for failed operations.
Included in the Toolkit is the Database Director, which provides a graphical interface for configuration, backup and recovery, and directory management. Using the Database Director, you can select one or more database objects to perform tasks provided by the DBA Utility, the Performance Monitor, or the Visual Explain tools. The Database Director displays the database objects, such as table spaces and tables, and shows how they are related. An object can be expanded by simply clicking on the expansion icon. Using the Database Director, you can create more databases, table spaces, and tables; all other database objects are created using the DB2 Command Line Processor.
The DBA Utility is used to display and configure the database parameter values and settings -- for example, to change the size of the log file. The management of directories needed for accessing local and remote databases is simplified; you can create a new database locally or remotely, say, or list the local database directory. You can manage table spaces by creating, dropping, or changing them, by adding containers, or even by managing the local Unix file system. All of the database objects (such as the tables, indexes, views, procedures, and triggers) are created, altered, and dropped by capturing the SQL statements in a text file and then processing the file through the DB2 Command Line Processor.
DB2's Performance Monitor provides a comprehensive collection of performance data, with viewing, reporting, analysis, and alerting capabilities of over 200 performance attributes and user-defined statistics. The collected data is shown for database managers, databases, tablespaces, tables, and so on. The Performance Monitor also has very robust alerting. The Visual Explain tool graphically shows you the access plan, chosen by the database manager's optimizer, for a given SQL statement, thereby allowing you to model the effect of the changes before committing it. From an access plan graph you can view the details for tables, indexes, operators (such as sorts and joins), and table spaces and functions.
You can use the Command Center itself to get and change the current status of the database server, list the active user sessions (including the sessions that are being locked by other users), list and manage pending alert conditions that the server has signaled, and generate diagnostic or system reports. The Space Explorer lets you display, create, or delete database or BLOB storage spaces. A database space is a logical storage unit to which you can assign databases and tables, or to which you can mirror another storage space. You can access the data in the mirrored storage space if the primary space becomes unavailable. Data inserts, deletes, and changes are written to both the primary and mirrored storage spaces; the mirror location is cleared when mirroring is disabled. The Setup tool lets you change the password for the Informix account and the shared SQLHOSTS computer, where the OnLine Workgroup Server will look for the SQLHOSTS registry information. This registry information describes the network locations of the database servers and the communications protocol required to access them.
The Informix Relational Object Manager (ROM) is a collection of tools that lets you develop and manage databases in a graphical environment. ROM consists of three tools: Database Explorer, Table Editor, and SQL Editor. The Database Explorer lets you view, create, and edit relational objects such as databases, tables, synonyms, stored procedures, views, and triggers. The Table Editor is used to edit and create tables, table columns, and indexes. You can use the SQL Editor to enter SQL statements to create relational objects that cannot be created directly in the Database Explorer, such as views, stored procedures, and triggers. Objects created using the SQL Editor are displayed in the Database Explorer once the screen is refreshed. By using Informix's dbschema utility, you can create an identical table in a target database as in an identified source database. The dbschema utility creates an ASCII script file, which it runs against the database and table that you want to duplicate. This script file can then be executed against another database. All of this processing is done from the Informix Command Line Processor. You can copy the table data using the insert command in the SQL Editor, or by using the dbload and dbunload commands from the Informix Command Line Processor.
From the Enterprise Manager you can invoke the Database Maintenance Wizard, which helps you create default maintenance batch jobs for any given database. The jobs can be changed afterwards, or the wizard can be rerun to change some of the options. By offering you all of the available options, the wizard ensures that you cannot forget some DBA tasks by accident. Some DBAs may not even have been aware of some options that the Wizard now gives to them on a platter.
The context-sensitive operations for databases include typical DBA operations such as Backup, Recover, Transfer, and Delete. The transfer option lets you easily transfer the entire database or selected objects (and optionally the data and other dependencies, such as permissions and constraints) to another database on any registered SQL Server. The Backup and Recover options are very flexible, presenting the user with several options regarding the required type of backup, the destination device, and whether it should be executed immediately or scheduled for a later time.
The Enterprise Manager also lets you manage all of the scheduled batch tasks, performance maintenance tasks, distributed transactions, SQL Mail, replication configuration, current activity monitoring, and other information useful for DBA tasks. The Database Maintenance Wizard can help you set up the daily and weekly maintenance runs, which can include all of the performance-tuning tasks.
The Enterprise Manager functions can also be performed by calling SQL Server stored procedures. DBAs who do not (yet) like GUI interfaces can perform the required functions using SQL scripts. You can also access the same management information via the Data Management Objects (DMO), an OLE interface. You can use the DMO to write your own Enterprise Manager-like application.
The Oracle instances are configured, started up, and shut down using the Oracle Instance Manager. You can configure the memory model, the buffer sizes, and various advanced logging, locking, and caching parameters.
You use the Oracle Schema Manager to manage the clusters, database links, indexes, packages, snapshots, synonyms, tables, triggers, and views in a connected database, each with its respective security rights. For example, for tables, you define the table's columns (and their details), the storage details, and extensive constraints. The Schema Manager also provides you with a useful CREATE LIKE option, for creating new objects based on existing object definitions. The Schema Manager is an impressive improvement over the Object Manager in Oracle7 release 7.2. The Object Manager had a forms-based interface: You filled in forms and used pull-down lookups to create or change the listed database objects. The Schema Manager, on the other hand, has the Explorer look-and-feel, where you get a view of all of your database objects and their related objects. You use the Security Manager to manage the different users, their roles, and various profiles. The Oracle Data Manager provides export and import utilities, which you can use to port tables, optionally with their grants, constraints, indexes, and actual contents between databases.
The Oracle Enterprise Manager includes an extensive job-scheduling module, through which you can schedule import, export, backup, startup, shutdown, and many other tasks, including DBA and SQL scripts. The jobs can be scheduled to run immediately, once at a specific time, repeatedly at specified intervals, or repeatedly at specific times.
You use the Server Manager to create, alter, drop, and manage all of your databases and database objects, such as the users, groups, tables, views, indexes, triggers, procedures, rules, defaults, user-defined data types, and storage segments. The server manager has push-buttons, pick-lists, and pull-down menus to help you choose the correct details for each object; unfortunately, however, it doesn't have a wizard or assistant to help you write the SQL statements for views and stored procedures. For each indicated object, the Server Manager shows all of the dependent objects, the referenced objects, the other objects referencing the indicated object, and all of its permissions. The Server Manager includes a utility to generate SQL scripts. If you edit the scripts to change the database names, you can use the scripts to port the definitions of the database object between different databases.
Sybase also offers the Sybase Enterprise SQL Server Manager as a separate product. It is used to manage Sybase SQL Server database systems on an enterprisewide scale. Unlike most DBA tools, which function on a single database at a time, you can manage and configure all of your SQL Server database servers, databases, database objects, and backup and recovery tasks from a single GUI-based workstation. For example, you can select a collection of databases and apply a CREATE TABLE operation to all of them. Alternatively, you can create the table on one database, test it, and then deploy it on the other databases by dragging and dropping the table's icon onto the icons of the other databases.
Sybase tables and indexes are self-maintaining; the only time you have to re-create an index is when you want to maintain the fill factor on a hashed index. You should run the update statistics operation regularly on volatile tables, though, to give the optimizer the latest value distribution information. You must schedule both the index re-creation and the UPDATE STATISTICS operations using the operating system's facilities.
Products such as CA-OpenIngres, Informix, Oracle, Microsoft SQL Server, and Sybase have Explorer look-and-feel DBA tools, with which you can navigate your databases and the objects contained in these databases. Some of these tools, such as CA-Visual/DBA, Microsoft SQL Server Enterprise Manager, and the Sybase Enterprise SQL Server Manager, even let you copy database objects between databases using either drag-and-drop or side-by-side double list boxes.
Very few of the products I reviewed actually contain good schedulers for routine performance-tuning tasks. Centura's SQLBase has a scheduler, but it is aimed more at backup and recovery tasks than at performance-tuning tasks. Microsoft SQL Server has a reasonable scheduler, and the Oracle Enterprise Manager has an extensive scheduler. For the other servers without good schedulers, this lack is a serious shortcoming, because most of these DBMSs require regular maintenance to ensure good performance. The notable exception is Borland's InterBase, with its totally self-balancing indexes and tables, which need no performance-related maintenance. However, you still want to schedule recovery tasks -- but that is a following month's topic.