Maintaining the DBEnvironment

DBEnvironment maintenance includes the following tasks:

Adjusting startup values
Setting parameters for rule operation
Updating system catalog statistics
Managing DBEFiles and DBEFileSets
Managing log files (discussed in the "Backup and Recovery" chapter)

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.

Adjusting Startup Values

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, Memory Resident Data Buffer Flag, 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, Memory Resident Data Buffer Flag, 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

Parameter	Default	Modify with	Description
Maintenance Word	None	SQLUtil SETDBEMAINT	is a password for SQLUtil. Defining a Maintenance Word protects the DBECon file from being modified by unauthorized users. The DBECreator can set and change the Maintenance Word only with SQLUtil.
DBEnvironment Language	NATIVE-3000	Cannot be changed	is specified in the LANG= option of the START DBE NEW statement. Once the DBEnvironment is configured, you cannot change its language.
DBECreator ID	DBEUserid of the user who configures a DBEnvironment by using START DBE NEW.	Cannot be changed.	Ensures that there is a DBEUserID that has irrevocable DBA authority for each DBEnvironment. In addition, the DBECreator always has access to the SQLUtil commands for DBEnvironment maintenance and security.
AutoStart	ON	SQLUtil ALTDBE	automates DBEnvironment startup. You can set this parameter only with SQLUtil.
User Mode	SINGLE	SQLUtil ALTDBE to change; START DBE or START DBE NEWLOG to override	is SINGLE to allow only one user, and MULTI to allow multiple users to access the DBEnvironment simultaneously. A DBA may want to configure the DBEnvironment in single-user mode until all databases have been created and all authorization has been granted. Once the DBEnvironment is ready for production use, user mode can be changed to MULTI. User mode can be changed using SQLUtil or temporarily overridden with the START DBE statement.
Log File Name(s)	DBELOG1, DBELOG2	START DBE NEWLOG to change	are specified in the LOG DBEFILE clause of the START DBE NEW statement. If DUAL LOG is specified, you must specify two log file names. The log file names and size can be changed with the START DBE NEWLOG statement discussed in the "Backup and Recovery" chapter of this guide.
Archive Mode	Disabled	SQLUtil STOREONLINE; ARCHIVE parameter in START DBE NEWLOG statement	STOREONLINE enables archive logging for rollforward recovery. The ARCHIVE parameter in the START DBE NEWLOG statement configures a new log for a DBEnvironment that is already using archive logging.
DDL Enabled	YES	SQLUtil ALTDBE	enables the use of data definition language (DDL). You can set this parameter with the SQLUtil ALTDBE command. Disabling DDL can improve performance significantly, but it should only be done if your applications do not do any data definition.
Number of Runtime Control Block Pages	37	SQLUtil ALTDBE to change; or SQL START DBE or START DBE NEWLOG to override	The number of blocks of memory allocated for DBCore services such as locking and session management. The maximum is 2000 pages.
Number of Data Buffer Pages	100	SQLUtil ALTDBE to change; START DBE or START DBE NEWLOG to override	are specified in the BUFFER option of the START DBE NEW statement. You should make sure that the buffers are large enough to accommodate the number of concurrent transactions in the DBEnvironment. Refer to "Estimating Shared Memory Requirements" in the "Physical Design" chapter for details on determining the number of buffer pages.
Data Buffer Pages Memory Resident	NO	SQLUtil ALTDBE	When set to YES, this ensures that data buffer pages will remain memory resident and not be swapped out by the operating system. The default is NO.
Number of Log Buffer Pages	24	SQLUtil ALTDBE to change; START DBE or START DBE NEWLOG to override	are specified in the BUFFER option of the START DBE NEW statement. You should make sure that the buffers are large enough to accommodate the number of concurrent transactions in the DBEnvironment. Log buffers can be from 24 (the default) to 1024 pages. Refer to "Estimating Shared Memory Requirements" in the "Physical Design" chapter for details on determining the number of buffer pages.
Maximum Transactions	50	SQLUtil ALTDBE to change; START DBE or START DBE NEWLOG to override	is specified in the TRANSACTIONS option of the START DBE NEW statement. The number of concurrent transactions depends on the number of users that will be concurrently accessing the DBEnvironment. The default number of transactions is 50; the maximum is 240.
Maximum Timeout	NONE	SQLUtil ALTDBE to change; START DBE or START DBE NEWLOG to override	is specified in the MAXIMUM TIMEOUT clause of the START DBE NEW statement. This value is the maximum permitted timeout that can be established by a user in the DBEnvironment.
Default Timeout	MAXIMUM	SQLUtil ALTDBE to change; START DBE or START DBE NEWLOG to override	is specified in the DEFAULT TIMEOUT clause of the START DBE NEW statement. This value is the default user timeout value in the DBEnvironment.
Authorize Once Per Session	OFF	SQLUtil ALTDBE	When ON, authorization to run modules or execute procedures is only done once per session, on the first invocation.

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, Memory Resident Data Buffer Flag, 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.

Determining Behavior of Rules in a DBEnvironment Session

You can use the following SQL statements to change the behavior of rules in a DBEnvironment session:

ENABLE RULES and DISABLE RULES
SET PRINTRULES ON or OFF

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.

Updating System Catalog Statistics

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:

Numerous inserts, updates, or deletes
Creating or dropping database objects
Restructuring databases

The columns for the following system views are updated for the table specified in the UPDATE STATISTICS statement:

The DBEFUPAGES column in the SYSTEM.DBEFILE view is updated for every DBEFile in the DBEFileSet where the table resides. It indicates the number of used pages in each DBEFile.
The DBEFSUPAGES column in the SYSTEM.DBEFILESET view is updated for the DBEFileSet where the table resides. It indicates the number of used pages in the DBEFileSet.
The AVGLEN column in the SYSTEM.COLUMN view is updated for all columns in the table. It indicates for each column the average length of the values in that column.
The NPAGES and CCOUNT columns in the SYSTEM.INDEX and SYSTEM.CONSTRAINTINDEX views are updated for each index (including constraint indexes) created on the table. They indicate how many pages each index occupies, and how well the data is clustered for each index, respectively.
The NPPAGES, AVGLEN, MAXLEN, NFULL, and NOVERFLOW columns in the SYSTEM.HASH view are updated if the table is a hash table. NPPAGES indicates how many primary pages are in use in the table; AVGLEN and MAXLEN indicate the average and maximum chain length, that is, the number of overflow pages it is necessary to traverse before finding a row. NFULL is the number of primary pages that are more than half full, and NOVERFLOW is the total number of overflow pages.
The NPAGES, NROWS, AVGLEN, and USTIME columns in the SYSTEM.TABLE view are updated for that table. NPAGES indicates how many pages the data in the table occupies. NROWS indicates the number of rows in the table. AVGLEN specifies the average row length in the table. USTIME indicates the last time an UPDATE STATISTICS was performed on the table.

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:

   isql=> UPDATE STATISTICS FOR TABLE PurchDB.Parts;




	NOTE: Updating statistics locks system catalog pages, and it also can invalidate sections stored for preprocessed statements. Since there can be an impact on performance while the statement is executing, you should use the UPDATE STATISTICS statement during off hours, or when accessing the DBEnvironment in single-user mode.

Changing System Table Lock Types

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.




	NOTE: Locking is a complex subject. It has far reaching effects on a DBEnvironment and possibly on system performance. For example, one possible effect of setting table lock types to PUBLICROW (row level locking) is that memory requirements may increase.

Managing DBEFiles and DBEFileSets

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:

determine the available space in the DBEFileSet
calculate the number of DBEFile pages needed
determine the DBEFile type needed

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.

Adding a New 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:

Determine the DBEFile type. Use a TABLE DBEFile if you are adding rows to a table. Use both TABLE and INDEX DBEFiles if you are adding rows to a table with an index. Use MIXED DBEFiles if you are not separating table and index data.
Use the formulas in the "Physical Design" chapter for calculating the number of DBEFile pages needed to store table and index data.
Create the DBEFile using the CREATE DBEFILE statement. You can use the DEVICE clause to indicate the device on which the DBEFile will reside, as described in ALLBASE/SQL Reference Manual.
Add the DBEFile to the DBEFileSet using the ADD DBEFILE statement.




	NOTE: If you are adding a DBEFile to the SYSTEM DBEFileSet, your transaction must be the only active transaction. If there are other active transactions, your transaction will wait until they complete.

Make the changes permanent by using the COMMIT WORK statement.




	NOTE: If you use the ROLLBACK WORK statement, or if there is a system failure after you create the DBEFile and before you commit the transaction, a physical file will remain on the operating system without a corresponding entry in the system catalog. You should use the SQLUtil PURGEFILE command to remove this file before attempting to create the DBEFile again.

Changing DBEFile Type

DBEFiles are of type TABLE, INDEX, or MIXED. You can change a file from one to the other if necessary.

From TABLE or INDEX to MIXED

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:

   isql=> ALTER DBEFILE SomeDBEFile SET TYPE = MIXED;

From MIXED to TABLE or INDEX

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:

Unload all tables in the DBEFileSet with the INTERNAL option. Before unloading, you may wish to drop indexes on the tables so as to improve performance during the unload operation. Do not drop the tables.
Delete all rows from all tables in the DBEFileSet.
Use the ALTER DBEFile statement to change some of the DBEFiles to type INDEX and others to type TABLE.
Load the tables using the INTERNAL option. Recreate indexes if necessary.
Use the SQLUtil MOVEFILE command to locate the DBEFiles on separate devices.

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.

Dropping a DBEFile

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:

   isql=> REMOVE DBEFILE WareDataF1 FROM DBEFILESET WarehFS;




	NOTE: If you are removing a DBEFile from the SYSTEM DBEFileSet, your transaction must be the only active transaction. If there are other active transactions, your transaction will wait until they complete.

A DBEFile is dropped with the DROP DBEFILE statement. The following example drops the WareDataF1 DBEFile:

   isql=> DROP DBEFILE WareDataF1;

Once you drop the DBEFile, it cannot be used to store data. However, it still resides on the system as an MPE/iX file.