![]() |
![]() |
|
|
![]() |
![]() |
ALLBASE/SQL Database Administration Guide: HP 9000 Computer Systems > Chapter 7 Maintenance![]() Maintaining the DBEnvironment |
|
DBEnvironment maintenance includes the following tasks:
You can monitor the DBEnvironment using the system catalog. Then, based on the changing needs of users, you can adjust DBECon parameters or increase or decrease space in DBEFileSets and log files. Refer to the "System Catalog" chapter for a complete description of each catalog view and its use in monitoring system performance. The DBECon file contains startup parameters which help the DBA automate DBEnvironment startup and access procedures. Defaults for many of these values are set when you create the DBEnvironment using the START DBE NEW statement. You can change many startup parameters temporarily using the START DBE statement or permanently using the ALLBASE/SQL tool SQLUtil. All DBECon parameters except the DBECreator, Maintenance Word, AutoStart, DDL Enabled, and Authorize Once Per Session parameters can be specified in the START DBE NEW statement when the DBEnvironment is configured. ALLBASE/SQL assigns defaults to DBECreator, Maintenance Word, AutoStart, DDL Enabled Flag, Archive Mode, Control Block pages, Log Buffer Pages, Data Buffer Pages, and Authorize Once Per Session Flag. Table 7-1 “DBECon Parameters” shows all the parameters and how they can be modified. Table 7-1 DBECon Parameters
To change any of the DBECon parameters using SQLUtil, you must be the DBECreator (creator of the DBECon file) or know the maintenance word. To temporarily override any of the DBECon parameters using START DBE, you must have DBA authority. To override using START DBE NEWLOG, you must be the DBECreator. The START DBE and START DBE NEWLOG statements temporarily override the startup parameters in the DBECon file for just the period that the DBEnvironment is open. Note that the DBECon file contains some startup parameters that can be modified only through SQLUtil: Maintenance Word, Autostart, Archive Mode, DDL Enabled, and Authorize Once per Session. Defaults are provided for all of these except the maintenance word. Refer to the chapter "DBEnvironment Configuration and Security," and see the description of ALTDBE command in the "SQLUtil" appendix for details on changing startup parameters. You can use the following SQL statements to change the behavior of rules in a DBEnvironment session:
The effects of these statements are global throughout the DBEnvironment; that is, they affect all connected users. The ENABLE RULES and DISABLE RULES statements turn on and off the operation of rules in a DBEnvironment. Use DISABLE RULES to perform load operations when you do not wish rules to be activated, or for testing the operation of rules and procedures. Use the SET PRINTRULES ON statement to turn on display of the rule's name as it fires. Use SET PRINTRULES OFF to stop the display of rule names. By default, PRINTRULES is set OFF, and rule firing is enabled. The UPDATE STATISTICS statement is used to update the system catalog to reflect the current status of the DBEnvironment. The UPDATE STATISTICS statement operates on one table at a time for the table data, its indexes, the DBEFileSet containing the table, and all DBEFiles in the DBEFileSet. Since ALLBASE/SQL uses data from the system catalog to optimize queries, the DBA should update system catalog statistics after any of the following:
The columns for the following system views are updated for the table specified in the UPDATE STATISTICS statement:
The table owner or a user with DBA authority can update statistics on a table. Users with DBA authority can update statistics on system views. The following statement updates statistics for the PurchDB.Parts table:
Two ALLBASE/SQL special names are supported as owners of the system base tables. STOREDSECT owns the tables used to store compiled sections and views (the section tables); HPRDBSS owns all other system tables. By issuing a query on the SYSTEM.TABLE view, you can see which system tables are owned by HPRDBSS and STOREDSECT. The RTYPE column indicates each table's lock type (granularity). (Refer to the "System Catalog" chapter in this manual for a complete description of the SYSTEM.TABLE view.) If you are a DBA, you can change the lock type of any system base table or user table by means of the ALTER TABLE statement. A sophisticated understanding of locking strategy in general (and for the particular DBEnvironment) is required. For details, please refer to the ALLBASE/SQL Reference Manual chapter, "Concurrency Control through Locks and Isolation Levels." As a DBA, you can use the UPDATE STATISTICS statement to insure that system and user table information is current. Then use SQLMON to detect concurrency problems and, if necessary, alter table types to change lock granularity. The SQLMON help Facility and the ALLBASE/SQL Performance and Monitoring Guidelines provide additional information.
DBEFiles should be added to a DBEFileSet when you need more space in the DBEFileSet for the current tables and indexes or when you are going to create another table or index in the DBEFileSet. Do the following before you add a new DBEFile:
Use SQLMON to monitor the space available in a DBEFileset. The Static DBEFile screen displays the number of pages in use and the maximum number of pages for each DBEFile and DBEFileset. If your table and index data are separated into TABLE and INDEX DBEFiles, you must make sure that each type has enough room for pending inserts and updates. The Static Size screen in SQLMON lists the number of pages occupied by and the type (TABLE, INDEX, or MIXED) of each DBEFile. If you have determined that your DBEFileSet needs another DBEFile, use the following steps to create the additional DBEFile and add it to the DBEFileSet:
DBEFiles are of type TABLE, INDEX, or MIXED. You can change a file from one to the other if necessary. If you find you are not using indexes very often on some tables, you may want to consolidate the tables and indexes into DBEFiles of type MIXED. Mixed DBEFiles use space more efficiently than separate table and index DBEFiles. To change to type MIXED, you do not need to unload and empty the tables. You simply use the ALTER DBEFILE statement to change all DBEFiles to type MIXED. The SQL ALTER DBEFILE statement is a simple maintenance operation:
To change DBEFile type from MIXED to either INDEX or TABLE, you must use a complex operation. For instance, if you want to separate table and index data that is currently stored in MIXED DBEFiles in order to place tables and indexes on different disk drives, use the ALTER DBEFILE statement in conjunction with UNLOAD and LOAD, as follows:
Keep in mind that you should know how much space is required for tables and indexes so that the appropriate number of DBEFiles are altered to type TABLE and to type INDEX. DBEFiles should be dropped when rows have been deleted from tables and space is no longer being used. The most significant implication of empty DBEFiles is wasted disk space. You may also experience slight performance degradation during serial table reads because all DBEFile pages are read during a serial table read. Before a DBEFile can be removed from a DBEFileSet, it must be empty. To empty a DBEFile, you must drop all tables associated with the DBEFileSet that have data in them. Alternatively, you can delete all the rows in the table without dropping the table itself. This preserves the table definition, but has the drawback of requiring enough log space for all the data being deleted. If you want to preserve the data, you must unload the tables before deleting the rows. A DBEFile must be removed from the DBEFileSet with the REMOVE statement, as follows:
A DBEFile is dropped with the DROP DBEFILE statement. The following example drops the WareDataF1 DBEFile:
Once you drop the DBEFile, it cannot be used to store data. However, it still resides on the system as an HP-UX file. |
![]() |
||
![]() |
![]() |
![]() |
|||||||||
|