|
|
|
|
|
|
|
|
ALLBASE/SQL Database Administration Guide: HP 3000 MPE/iX Computer Systems > Chapter 1 DBA Tasks and Tools
Tools for the DBA |
|
In addition to the general set of SQL statements, the DBA uses several utility programs in creating and maintaining DBEnvironments. These are:
The use of each toolset is described in the following paragraphs. Complete command syntax for ISQL, SQLUtil, SQLGEN, SQLMigrate, and SQLAudit is in the appendices. In addition, there is a discussion of SQLINSTL. SQLINSTL is a script used when moving to a new version of ALLBASE/SQL. ISQL lets you issue most SQL commands interactively. In addition, it lets you load and unload tables using the LOAD and UNLOAD statements. ISQL also includes a help facility which explains the syntax of SQL and ISQL commands. ISQL command files offer a shortcut to creating databases. Command files allow you to store a series of ISQL and SQL commands and then, with a single START command, execute all the commands in that file. This is very useful for groups of commands you execute together frequently. In addition, if an entire DBEnvironment is created by using command files, it is easy to recreate the DBEnvironment, as well as examine or modify any part of its definition. The sample DBEnvironment used in all the examples in this manual can actually be created with the series of command files found in the "Sample DBEnvironment" appendix in the ALLBASE/SQL Reference Manual. SQL data definition statements such as CREATE and maintenance commands such as UPDATE STATISTICS obtain locks on the system catalog. To avoid contention, you can use command files to execute these statements on a DBEnvironment during off hours. This will reduce the amount of waiting on locks during peak working hours. You can use SQLGEN to create command files for use through ISQL. See the section "Using SQLGEN" later in this chapter. For complete information on ISQL command files, refer to the ISQL Reference Manual for ALLBASE/SQL and IMAGE/SQL. SQLUtil is an ALLBASE/SQL utility program that lets you manage the startup parameters for each DBEnvironment and perform several other maintenance tasks. With SQLUtil, you can:
There are two ways to run the SQLUtil program: directly from MPE/iX, or within ISQL. For the first method, type the following command:
To execute SQLUtil within ISQL, type:
No matter which method you use, you will see the SQLUtil banner and prompt (>>). The SQLUtil commands are described in the appendix, "SQLUtil." Now you can execute SQLUtil commands until you enter an EXIT or QUIT command. SQLUtil requires the use of the message catalog, SQLCT000.PUB.SYS. If you are using native language data, the name of the catalog is SQLCTxxx.PUB.SYS, where xxx is the number of your current language. See "Native Language Support" for information about specifying a native language as the current language. SQLUtil accepts responses to prompts for a DBEnvironment Name or for a filename in either the current language or NATIVE-3000. Instead of entering commands at the keyboard, you may redirect input from a command file by specifying the STDIN option in the run string, as in the following example:
As with ISQL, you can use a semicolon to terminate an SQLUtil command. Semicolons are not needed, however. All responses to commands must be contained on one line; continuation of responses is not supported. Some SQLUtil commands display a subsidiary prompt ("->"). From this prompt, you can enter // to return to the SQLUtil prompt (">>"). Also, if input from within the ISQL session was coming from a command file or the command buffer when SQLUtil was invoked, that input is suspended during the SQLUtil session until the user enters either EXIT or QUIT. Finally, prompting-mode is the only mode of operation in SQLUtil. You cannot specify a complete command line as you would in ISQL. When running SQLUtil in batch mode, your job can check the following JCWs to ensure that SQLUtil completed successfully. Table 1-1 JCWs Set by SQLUtil
SQLGEN is a utility that generates the commands used to recreate all or part of an existing ALLBASE/SQL DBEnvironment. It also generates LOAD/UNLOAD and UPDATE STATISTICS commands. These commands are placed in one or more data files called schema files which can then be used as ISQL command files to recreate the DBEnvironment. SQLGEN has several uses. It aids in migrating to new releases of ALLBASE/SQL when unloading or reloading are necessary. In addition, SQLGEN helps with other database tasks. DBEnvironments can be designed and tuned on a development system and then the entire schema can be transferred to a production system. SQLGEN can also help move part of a DBEnvironment. For example, if a department transfers from one site to another, that department's portion of a DBEnvironment can be easily moved to a new system. You can also use SQLGEN to create a replicate DBEnvironment schema from the master DBEnvironment. When you do so, you must use the START DBE NEWLOG statement to specify the AUDIT LOG, AUDIT NAME, DEFAULT PARTITION, MAXPARTITIONS, and AUDIT ELEMENTS parameters. You must also use CREATE PARTITIONS to create partitions and ALTER TABLE to assign tables to the partitions. You must have DBA authority to use SQLGEN. Enter commands at the keyboard or redirect the input by specifying a command file name in the run string. To run SQLGEN directly from the operating system, type:
To run SQLGEN from within ISQL, type:
No matter which method you use, the SQLGEN prompt (>>) appears, ready for you to enter commands. SQLGEN requires the use of the message catalog, SQLCT000.PUB.SYS. If you are using native language data, the name of the catalog is SQLCTxxx.PUB.SYS, where xxx is the number of your current language. See "Native Language Support" for information about specifying a native language as the current language. Usually, STARTDBE will be the first command you enter. This command connects to the DBEnvironment and checks to make sure you have DBA authority. It is recommended that the DBEnvironment be shut down prior to issuing the STARTDBE command to ensure that system catalogs are not being altered during SQLGEN execution. When you finish entering commands, use the EXIT command to return either to MPE/iX or to ISQL. When you run SQLGEN directly from MPE/iX, you can specify the editor name as an INFO string. When you run SQLGEN from ISQL, the current editor name is passed to and displayed by SQLGEN. Refer to the SET EDITOR command for more information. SQLGEN commands can be divided into two categories, generate commands and auxiliary commands. Generate commands such as GENERATE ALL build the SQL statements necessary to recreate all or part of a DBEnvironment. Auxiliary commands provide services needed to use SQLGEN. For example, the STARTDBE command connects to the DBEnvironment. The EDITOR command allows you to access an editor from within SQLGEN. With SET commands you redefine the editor or designate a schema file name. HELP, RELEASE, and EXIT are also auxiliary commands. SQLGEN also allows you to enter MPE/iX commands when they are preceded by the character ':'. The SQLGEN commands are described in the appendix, "SQLGEN." SQLGEN places the statements it generates in schema files which are then used as ISQL command files for recreating the DBEnvironment. All GENERATE commands prompt for a schema file name unless you have already designated the name with the SET SCHEMA command. If you enter a carriage return instead of a name at the schema file name prompt, the output is displayed on your terminal screen. In most cases, GENERATE commands automatically include a COMMIT WORK at the end of the schema file. However, if the generate command was not able to build any commands (for example, GENERATE GROUP cannot build commands if no groups exist in the DBEnvironment), then no COMMIT WORK is generated. When a GENERATE command encounters an unexpected ALLBASE/SQL error, SQLGEN automatically returns the schema file in use to the the state it was in before the command began. For example, if an error occurs during a GENERATE ALL command, any commands that have already been placed in the schema file are automatically rolled back. To execute the command, correct the problem and re-enter GENERATE ALL. To use SQLGEN effectively you must be familiar with the conventions it uses. The SQLGEN prompt is >>. Unlike ISQL, SQLGEN does not require a command-terminating semicolon. Once you enter a command, SQLGEN prompts for your responses. At an object name prompt, enter the name of the object for which you wish to generate commands. When you enter an object name, SQLGEN prompts you for another. When you finish entering object names, a carriage return returns you to the SQLGEN prompt. If you wish to select all qualifying names, enter an '@' at the object name prompt. To see a list of all qualifying names, type a '?'. If no objects qualify, SQLGEN displays a message and automatically returns you to the previous prompt. When choosing from two options, (y/n) for example, the first choice listed is always the default (carriage return). To return to the SQLGEN prompt from any command, type '//' or '/'. To access the operating system, enter a colon (:) and a carriage return. Type RESUME to return to SQLGEN. To enter system commands from within SQLGEN, type a colon (:) as the first character of the command. Only commands allowed in break can be executed. Table 1-2 “SQLGEN General Conventions” summarizes SQLGEN's general conventions. Table 1-2 SQLGEN General Conventions
SQLGEN automatically upshifts all object names not entered in quotes. To enter an object name, in most cases all you need to do is type the name. SQL pattern matching symbols (% and _) can be used. For example, at a table name prompt, P% selects all tables starting with P. Single and double quotation marks are used for special cases.
Table 1-3 “SQLGEN Name Entry Conventions” summarizes SQLGEN's naming conventions. Table 1-3 SQLGEN Name Entry Conventions
When running SQLGEN in batch mode, your job can check the following JCWs to ensure that SQLGEN completed successfully. Table 1-4 JCWs Set by SQLGEN
If you are updating from an earlier release of ALLBASE/SQL, you must perform the ALLBASE/SQL migration to migrate your DBEnvironment. The method used depends upon the version of ALLBASE/SQL that you are currently using. Use SQLMigrate to convert the DBEnvironment between major releases such as from F.0 to G.0. Use the SQLINSTL script to migrate between versions of a release (such as from G1.14 to G1.15) or minor releases (such as from G.0 to G.1). The SQLINSTL script is provided in the G.1 and later versions of ALLBASE/SQL to make it easy for the DBA to move to a delta release. Using SQLINSTL ensures that you will have access to the most recent version of the System and Catalog views, and it also uses VALIDATE FORCE statements to revalidate all stored sections to be compatible with the new release. If SQLINSTL is not executed on a DBEnvironment after installing a new version, stored sections may not be properly revalidated causing run-time errors. Revalidating stored sections at run-time during production hours can also cause concurrency problems due to write locks placed on the system catalog. You must use SQLINSTL whenever a new version of ALLBASE/SQL is installed; however, SQLINSTL does not need to be executed separately if SQLMigrate is being executed to migrate between major releases. Refer to the section, "Using SQLINSTL," later in this chapter for more information.
Use the SQLUtil SHOWACCESS command to list DBEFiles. If any are listed as pseudo-mapped, use the SQLUtil MOVEFILE command to convert the pseudo-mapped files to mapped before you upgrade ALLBASE/SQL. Refer to the commands in the appendix, "SQLUtil."
Use SQLMigrate to migrate between major revisions of ALLBASE/SQL. Backup the DBEnvironment prior to running SQLMigrate. You must be the DBECreator or have SM capability to migrate a DBEnvironment. To run SQLMigrate, use the following command:
You will see the SQLMigrate banner and prompt (SQLMIGRATE=>). Command entry follows ISQL rules; that is, commands are terminated with a semicolon. The SQLMigrate commands are described in the appendix "SQLMigrate." Forward and backward migration steps are discussed below. Only one DBEnvironment can be migrated at a time. There are two flavors of forward migration to consider:
For both cases, all tables are placed in the default partition. In the first case, no audit logging is enabled for the DBEnvironment. In the second case, the only audit logging element allowed is DATA because SQLMigrate does not support any other audit elements. If you migrate an audit logging DBEnvironment, it should not be migrated to a non-audit logging DBEnvironment. Therefore, you must specify AUDIT LOG in any START DBE NEWLOG statement that SQLMigrate performs. For backward migration, since previous releases do not support partitions, the only supported option for a DBEnvironment with audit logging in use is to place all the tables in the default partition with the only audit logging element specified being DATA. Then the DBEnvironment can be migrated backward unchanged. If the audit elements include something other than DATA, one of the following may happen:
When running SQLMigrate in batch mode, your job can check the following JCWs to ensure that SQLMigrate completed successfully. Table 1-5 JCWs Set by SQLMigrate
SQLINSTL is a script that you use instead of SQLMigrate when migrating between releases of versions of ALLBASE/SQL (such as G1.14 to G1.15) or minor releases (such as G.0 to G.1). When migrating between major releases (such as F.0 to G.0 or F.0 to G.1), you must instead use SQLMigrate to migrate your DBEnvironment. Refer to the previous section, "Using SQLMigrate," for more information. When using SQLINSTL, execute SQLINSTL on each DBEnvironment on the system to ensure that you have access to the most recent version of the system catalog views. SQLINSTL also ensures that stored sections are properly revalidated to be compatible with the new release. If SQLINSTL is not executed, errors may result when stored sections are executed due to compatibility problems.
The following is an example of using SQLINSTL from ISQL:
Read the SQLINSTL file on your system for more information. If you are using ARCHIVE MODE LOGGING, you must make a backup of the DBEnvironment after using SQLINSTL. This backup must be used if rollforward recovery is to be performed at some time in the future. SQLMON is an online diagnostic tool that monitors the activity of an ALLBASE/SQL DBEnvironment. SQLMON screens provide information on file capacity, locking, I/O, logging, tables, and indexes. They summarize activity for the entire DBEnvironment, or focus on individual sessions, programs, or database components. SQLMON is a read only utility, and cannot modify any aspect of the DBEnvironment. SQLMON is documented fully in the ALLBASE/SQL Performance and Monitoring Guidelines. To run SQLMON, log on as system manager, as the database administrator, or a user that was granted MONITOR authority, and issue the following command:
SQLVer allows you to check the version strings of the ALLBASE/SQL files. The syntax of SQLVer is as follows: SQLVER [file] The file parameter specifies the fully qualified name of the ALLBASE/SQL file whose version you want to check. If the file parameter is omitted, SQLVer verifies all of the ALLBASE/SQL files and summarizes the following:
SQLVer sets the SQLVERERR JCW to the following values: Table 1-6 SQLVERERR JCW
In the example that follows, the version strings of all the ALLBASE/SQL files are checked:
SQLAudit is an ALLBASE/SQL utility program that can be used in conjunction with audit DBEnvironments to view the changes that have been made to the DBEnvironment. They include such groups as DML commands (INSERT, UPDATE, DELETE), and DDL commands (data definition, storage, and authorization commands, for example). The committed transactions are audited by extracting information from the audit log records in the log files. These audit log records are generated when audit logging is enabled on the DBEnvironment through the START DBE NEW or START DBE NEWLOG commands. Besides enabling audit logging, these commands include other audit logging parameters that define the groups of SQL commands you want to audit. These groups are discussed under "Creating Audit DBEnvironments" elsewhere in this manual. They include such groups as DML commands (INSERT, UPDATE, DELETE), and DDL commands (data definition, storage, and authorization commands, for example). SQLAudit is designed with a number of commands for viewing the changes made to the DBEnvironment. The following list of SQLAudit commands provides an overview of these commands:
Refer to the appendix "SQLAudit" for the complete SQLAudit command reference. The following conventions should be noted when using SQLAudit:
Table 1-7 “SQLAudit General Conventions” summarizes these conventions. Table 1-7 SQLAudit General Conventions
SQLAudit requires a beginning and ending point in the log files to determine what portion of the log history to audit. These audit points are defined by using the command GET AUDITPOINT. GET AUDITPOINT determines all the current transaction information for the DBEnvironment and stores it and identification information in a file for later use. The audit point file can then be used by other commands, such as AUDIT or SHOW AUDITPOINT. The beginning audit point provides the AUDIT command with information about the previously committed transactions. The AUDIT command uses this information to audit all transactions that have committed after the beginning audit point was defined by GET AUDITPOINT. The ending audit point is used by the AUDIT command to provide a point where the AUDIT command can stop processing audit log records. In addition, the ending point also provides a list of partitions that have committed transactions, and when compared with the beginning audit point, a list of partitions to be audited. If you do not specify a beginning audit point, the AUDIT command assumes by default that you want to audit every transaction, starting from the first committed transaction for each partition being audited. If you do not specify an ending audit point file, the AUDIT command assumes by default that you want to use the last committed transaction in the log file as the stopping criteria. Whether you specify a beginning audit point or assume the default, SQLAudit must still be able to find the first committed transaction for the partition in the log files. Therefore, if the first committed transaction for an audited partition cannot be found, an error is returned and the partition is dropped from the list of partitions being audited. Suppose, for example, that at the time the beginning audit point is defined, the current list of partitions and their committed transactions exists as follows:
Transaction number 10 was the last transaction that changed data in partition number 1. Similarly, transaction numbers 21 and 16 were the last transactions to change data in partition numbers 2 and 6, respectively. Suppose that the following transaction information is reflected in the ending audit point information:
In comparing these two audit points, SQLAudit can determine several facts:
With this information, the AUDIT command can determine that the only partitions that need to be audited are 1, 4, and 6. The AUDIT command would still include partition 2 in the list of partitions being audited, but no transactions should be found. Note that in this example, two partitions have the same transaction number (partitions 1 and 6). This means that transaction number 45 made changes to data in both partitions 1 and 6. Although this example is an oversimplification of what the transaction information looks like, the concepts still hold true. The following example illustrates how GET AUDITPOINT defines and displays an audit point:
Refer to the SHOW AUDITPOINT command for an explanation of the display of current audit point information. Since audit log records for transactions are written to the same DBEnvironment log files as nonaudit log records, it is possible that transactions that have not been audited can be overwritten. To prevent this, the LOCK AUDITPOINT command can be used to prevent users from overwriting transactions that you have not audited. While it is possible to have many different audit points defined for a DBEnvironment, it is only possible to have one audit point locked for the DBEnvironment. Therefore, only one database administrator should be assigned to determine which audit point should remain locked. This log lock defines the point in the log file which no user can write beyond. If any user transaction attempts to write beyond this point, a LOG FULL message is issued and the transaction is rolled back. The audit point can be unlocked with the UNLOCK AUDITPOINT. Since only one audit point can be locked for the DBEnvironment, locking a new audit point automatically unlocks any previously locked audit point. Audit points can also be locked when they are defined through the GET AUDITPOINT command. Assume that a company (perhaps a bank) wants to determine what changes have been made to their data during the course of a business day. They have previously enabled audit logging for the DBEnvironment and are about to start their business day. The first thing that must be done is to determine the beginning audit point in the DBEnvironment log files. This point will be used as the starting point of the audit process used at the end of the business day. Since the bank does not want to lose any information before they audit it, they also lock the beginning audit point to make sure the transactions are not overwritten. This is done with the SQLAudit command GET AUDITPOINT as follows:
This sequence of commands creates a file called StartPt containing the beginning audit point information and locks the log files to prevent unaudited transactions from being overwritten. Since log files are being locked, the database administrator makes sure that they have enough log space in the DBEnvironment to last the full day. If they find that they are running out of log space during the day, additional log files will be added. At the end of the day the bank wishes to audit the changes that have been made to the data during the course of the business day. Since the AUDIT command needs an ending audit point, the following sequence of commands can be used to define it:
Notice that this audit point is not locked since that would drop the lock on the previous audit point and allow transactions committed between the two audit points to be overwritten. Now the actual auditing of the database changes can be performed as follows:
If the bank wants to do continuous auditing, the ending point of one day can be used as the starting point for the next day. You should lock the new starting audit point so that no transactions get overwritten, as illustrated in the following example:
By using this process every day, you should always be able to audit the last 24 hours without the transactions ever being overwritten. The results of an audit are put into a file in a user readable format. The following example shows what the contents of this file could look like for an audit of partition number 2:
In this example, the first transaction audited shows that a record was inserted into table USER1.TABLE1 and that the third column in the record was then updated. In the next transaction, the record was deleted. The number in parenthesis following the operation type is the partition number that the operation was performed against (partition number two in this case). For performance and log space reasons, some information is not contained in the audit log record. For example, column names of the table being updated are not contained in audit log records. Since log records are being audited rather than an active DBEnvironment, it is not possible to get the column names from the system catalogs. For example, a table may have been dropped and recreated a number of times between the time the audit log record was created and the time you run SQLAudit. Therefore you should keep the following points in mind:
When running ISQL, SQLUtil, SQLGEN, or SQLMigrate in a script, you can check the exit status to ensure that the utility completed successfully. The exit status is set to the number of DBERRs encountered. If a DBERR is not encountered, the exit status is set to 0. The exit status may contain a value in the range 0 - 255. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||
|
|
|
|||||||||
|
|||||||||
