HP 3000 Manuals HP 3000 Manuals
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 shows all the parameters and how they can be modified.
Table 7-1. DBECon Parameters
-------------------------------------------------------------------------------------------------
| | | | |
| Parameter | Default | Modify with | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| Maintenance Word | None | SQLUtil | is a password for SQLUtil. Defining a |
| | | SETDBEMAINT | 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 | NATIVE-3000 | Cannot be changed | is specified in the LANG= option of |
| Language | | | the START DBE NEW statement. Once the |
| | | | DBEnvironment is configured, you |
| | | | cannot change its language. |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBECreator ID | DBEUserid of | Cannot be | Ensures that there is a DBEUserID that |
| | the user who | changed. | has irrevocable DBA authority for each |
| | configures a | | DBEnvironment. In addition, the |
| | DBEnviron- | | DBECreator always has access to the |
| | ment by | | SQLUtil commands for DBEnvironment |
| | using START | | maintenance and security. |
| | DBE NEW. | | |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| AutoStart | ON | SQLUtil ALTDBE | automates DBEnvironment startup. You |
| | | | can set this parameter only with |
| | | | SQLUtil. |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| User Mode | SINGLE | SQLUtil ALTDBE to | is SINGLE to allow only one user, and |
| | | change; START DBE | MULTI to allow multiple users to |
| | | or START DBE | access the DBEnvironment |
| | | NEWLOG to | simultaneously. A DBA may want to |
| | | override | 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, | START DBE NEWLOG | are specified in the LOG DBEFILE |
| | DBELOG2 | to change | 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 enables archive logging |
| | | STOREONLINE; | for rollforward recovery. The ARCHIVE |
| | | ARCHIVE parameter | parameter in the START DBE NEWLOG |
| | | in START DBE | statement configures a new log for a |
| | | NEWLOG statement | DBEnvironment that is already using |
| | | | archive logging. |
| | | | |
-------------------------------------------------------------------------------------------------
Table 7-1. DBECon Parameters (cont.)
----------------------------------------------------------------------------------------------------------
| | | | |
| Parameter | Default | Modify with | Description |
| | | | |
----------------------------------------------------------------------------------------------------------
| | | | |
| 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 | 37 | SQLUtil ALTDBE to | The number of blocks of memory |
| Control Block | | change; or SQL | allocated for DBCore services such as |
| Pages | | START DBE or | locking and session management.[REV |
| | | START DBE NEWLOG | BEG] The maximum is 2000 pages.[REV |
| | | to override | END] |
| | | | |
----------------------------------------------------------------------------------------------------------
| | | | |
| Number of Data | 100 | SQLUtil ALTDBE to | are specified in the BUFFER option of |
| Buffer Pages | | change; START DBE | the START DBE NEW statement. You |
| | | or START DBE | should make sure that the buffers are |
| | | NEWLOG to | large enough to accommodate the number |
| | | override | 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 | NO | SQLUtil ALTDBE | When set to YES, this ensures that |
| Memory Resident | | | data buffer pages will remain memory |
| | | | resident and not be swapped out by the |
| | | | operating system. The default is NO. |
| | | | |
----------------------------------------------------------------------------------------------------------
| | | | |
| Number of Log | 24 | SQLUtil ALTDBE to | are specified in the BUFFER option of |
| Buffer Pages | | change; START DBE | the START DBE NEW statement. You |
| | | or START DBE | should make sure that the buffers are |
| | | NEWLOG to | large enough to accommodate the number |
| | | override | 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. |
| | | | |
----------------------------------------------------------------------------------------------------------
| [REV BEG] | | | |
| | | | |
| Maximum | 50 | SQLUtil ALTDBE to | is specified in the TRANSACTIONS |
| Transactions | | change; START DBE | option of the START DBE NEW statement. |
| | | or START DBE | The number of concurrent transactions |
| | | NEWLOG to | depends on the number of users that |
| | | override | will be concurrently accessing the |
| | | | DBEnvironment. The default number of |
| | | | transactions is 50; the maximum is |
| | | | 240.[REV END] |
| | | | |
----------------------------------------------------------------------------------------------------------
| | | | |
| Maximum Timeout | NONE | SQLUtil ALTDBE to | is specified in the MAXIMUM TIMEOUT |
| | | change; START DBE | clause of the START DBE NEW statement. |
| | | or START DBE | This value is the maximum permitted |
| | | NEWLOG to | timeout that can be established by a |
| | | override | user in the DBEnvironment. |
| | | | |
----------------------------------------------------------------------------------------------------------
Table 7-1. DBECon Parameters (cont.)
-------------------------------------------------------------------------------------------------
| | | | |
| Parameter | Default | Modify with | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| Default Timeout | MAXIMUM | SQLUtil ALTDBE to | is specified in the DEFAULT TIMEOUT |
| | | change; START DBE | clause of the START DBE NEW statement. |
| | | or START DBE | This value is the default user timeout |
| | | NEWLOG to | value in the DBEnvironment. |
| | | override | |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| Authorize Once | OFF | SQLUtil ALTDBE | When ON, authorization to run modules |
| Per Session | | | 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.