|
|
Last Updated: June 2, 2006 |
The purpose of this section is provide an introduction to housekeeping for the Ingres MDB and guidelines for working with the MDB - the central database element for the r11 releases.
Followings are several recommendations for performing housekeeping and maintenance of the Ingres Management Database ("the MDB"). Each function includes a description that explains what is required and why it is important along with information about how to implement the function.
Topics include:
Similar (but not
identical) recommendations can also be found in the "Ingres MDB Proper
Care and Feeding" presentation. To view a slightly more detailed version of
the Ingres presentation in Microsoft Word format (.doc), click here.
Click here to download a
batch file that will execute the most commonly recommended Ingres housekeeping commands:
usermod, optimizedb and ckpds. This is intended to be run daily. Read MDB_Housekeeping.ppt for a detailed explanation of
what the batch file does.
This section provides an understanding of the relationship of files to the MDB database and why monitoring file sizes and disk space is necessary. It also provides a look at the file sizes supported by various operating systems, the issues associated with them and what to do when reaching the size limits.
Ingres uses the term "location" to define the directory structures which are assigned to store one or more of the following:
Refer to the "Ingres r3 Database Administrators Guide", Chapter 3: Creating Databases and Alternate Locations", Section entitled "Types of Files in an Ingres database" for additional information about these files.
By default each of these locations is created under the Ingres home directory II_SYSTEM. For example, if II_SYSTEM is set to "C:\Program Files\CA\Ingres [EI]" and the default database location is defined within this directory the database "location" or directory for the MDB is "C:\Program Files\CA\Ingres [EI]\ingres\data\default\mdb".
Within the database directory a file is created for every table or index. Refer to the "Ingres r3 Database Administrators Guide", Chapter 8: Maintaining Databases, Section entitled "Databases Shared Among Multiple Users" to understand the relationship between table and indexes to the files that hold their data.
As the number of rows in the table increases, the table size increases and so does the space it uses on the disk. As an example, consider the location %II_SYSTEM%/ingres/data/mdb that contains the data files for the database called as MDB'. Every file in this location corresponds to a table name or an index name in the MDB database. The size of such a file cannot exceed the max file size allowed for the operating system in use. On some operating systems this limit is 2GB.
When the maximum file size is reached it becomes impossible to add more rows to a table in the MDB. On 32-bit operating systems this can be an issue where it is easy to reach the 2GB file size limit.
The size of files in the database location need to be monitored, so that as files in the location approach a size of 2 gigabytes on 32 bit OS, the appropriate action can be taken to reorganize the table(file) across additional locations. The Ingres modify command can be used for the following:
Note that the modify command will destroy any indexes and not recreate them unless the indexes were defined as "with persistence". For indexes that are not created as "with persistence" it is necessary to create the index after "modify" has completed.
If you need to relocate tables within the MDB you should contact Technical Support first for additional guidance to avoid compromising the integrity of your MDB.
This section covers a description of why disk space is necessary for MDB and needs to be monitored as well as the activities to successfully monitor disk space
The limit on how much disk space an Ingres database location can use based on the underlying operating system and its file size limitations. Databases need disk space for more than just the database's tables and procedures. It requires sufficient disk space for work locations which are used for sorting, temporary and transient files.
Available disk space is easy to monitor; it is the amount of unused space on a disk. There are no special tools required (unless a raw location has been used). OS tools can be used for this purpose. E.g. the df command on UNIX
Refer to"Relocating databases" and "Extending databases" to learn how to resolve disk space issues.
Additional information on using the 'modify' command can be found in the SQL Reference Guide from the Ingres installation, however, you should contact Technical Support first to ensure the integrity of your MDB.
Ingres Net Security:
This section provides information on reorganization of tables in the MDB, including why and when it is required.
The Ingres usermod command is used to modify tables in the MDB.
What does table reorganization mean?
The MDB includes a large number of tables each of which have different:
Each table will have a different modification cycle e.g. some may need to be modified once a year others may need to be modified a frequently as once a day. Each product's documentation will provide guidance with respect to the need to reorganize the tables they use.
The usermod command modifies the user-defined tables of a MDB to their currently defined storage structure and recreates any secondary indexes that are defined. This command runs on the MDB tables and not on the Ingres system catalog tables The command fixes the overflow pages in a table which improves performance of query processing.
If a table does not need to be modified but an index does, either the index can be dropped and recreated or the index can be modified. Dropping and recreating the index may be quicker than reorganizing the table and all indexes.
When should the usermod command be run?
Note: To determine whether tables are in overflow, please refer to the "Ingres r3 Database Administrators Guide", Chapter 12: Maintaining Storage Structures, Section Overflow Management.
To modify the mdb to its current storage structure and recreate the secondary indexes:
usermod -online -umdbadmin mdb
where -online means that this command is run online.
Note that if this option is not specified the command is expected to be run offline and
it is expected that there will be no active connections to the database.
For more information on the 'usermod' command, please refer to the "Ingres r3 Command
Reference Guide".
This section describes the process of statistics gathering, why it is necessary and how to collect statistics for the MDB.
The Ingres optimizedb command is used for generation of statistics used by the query optimizer for efficient query processing.
Statistic Gathering Explained
Statistics need to be calculated in order to reflect the changes of values in tables. This is especially important after restructuring the tables with the modify command. The Query Optimizer Facility (OPF) makes use of statistic and histogram data gathered against table columns that are used in queries.
The absence of statistics is more likely to result in sub-optimal query performance.
Statistics are stored in system catalog tables such as (iistats and iihistograms).
When to gather statistics using optimizedb:
As with table modification, the frequency of running optimizedb on a table column depends on the rate of change of:
Within a multi-column table each column may have a different requirement with respect to frequency of execution of the optimizedb command. There can be columns in a table that never need to be optimized, as these columns are never used in the "where clause" of an SQL statement.
It is possible that the number of rows in the table is so large that using the optimizedb option of sampling against a column is as effective as running optimizedb against every column of every row within the table.
Example of when optimizedb may be desired
A table that has columns {transaction_id, amount, credit_or_debit_flag, account_number} where the table is growing at the rate of 100,000 rows per day may need to have statistics gathered on transaction_id every day as it is an incremental number, on credit_or_debit_flag once a year, on amount never and account_number once a week when the table is initially used and then every siz months once the table reaches a sufficient size where the histogram for account_number no longer changes as often.
optimizedb -zk -zw -umdbadmin mdb
where:
For more information on the 'optimizedb' command, please refer to the "Ingres r3 Command Reference Guide".
This section covers how and why system catalog reorganization is needed
The Ingres sysmod command is used to reorganize and optimize the tables that make up the Ingres system catalog.
When to reorganize the Ingres system catalog
Note that the MDB creation process issues a sysmod command when it completes
What does the sysmod command do?
Prerequisites
The sysmod command requires there be no active sessions connected to the database.
The following commands can be run to determine whether there are active sessions:
If a count of 0 is returned there are no active sessions. If a value greater than 0 is returned then there are active sessions and the sysmod command should not be run.
The Ingres catalog should be optimized whenever there are significant changes to the database or after the optimizedb command is run.
To modify all the system catalogs for a database:
sysmod mdb
To modify the system catalogs and wait for the database to be free of sessions:
sysmod mdb +w
A database name can be specified or a list of tables. If a table list is not provided all tables are considered. The command can be made to wait (+w) or not wait (-w) until the database is free before executing. The default is -w.
For more information on the 'sysmod' command, please refer to the "Ingres r3 Command Reference Guide".
This section covers how the MDB database location can be changed and what parts of a database are able to be relocated
The Ingres relocatedb command is used to relocate a database.
When to change a database location
A database location should be changed when a disk or file partition used for the various database locations (journal, checkpoint, dump and work) becomes full. A location should be changed when a database had been defined as residing on a single disk and due to performance issues e.g. disk I/O bottlenecks or space issues, it is necessary to move or split across more database locations.
There are two options available for changing database locations:
What is relocated by relocatedb ?
Checkpoint, journal, or dump files are relocated. The existing files are moved to the new location and any new files that result from subsequent database activity are created in the new location. E.g. Journal files that were in location1 are relocated to location2, any new journals will also be created in location2.
Prerequisites for relocation of a database location
The location(s) that are specified on the relocatedb command must exist prior to command execution. and the location must be specified with the appropriate usage type (journal, checkpoint, or dump).
Note: The Ingres command extenddb can be used to add new locations and extend databases to new locations
The following examples describe how to use the Ingres tool relocatedb to move a database location and copy an entire database.
To relocate a journal location to a new location:
relocatedb mdb -new_jnl_location=newjnl
To relocate a dump location to a new location:
relocatedb mdb -new_dump_location=newdump
To relocate the database to a new location
relocatedb mdb -new_database=mdb1
For more information on the 'relocatedb' command, please refer to the "Ingres r3 Command Reference Guide".
This section describes the how to extending the MDB and the parts of a database that can be relocated
The Ingres extenddb command is used to extend an Ingres database.
When to extend a database?
A database should be extended when there is a need for additional database or work locations.
What locations are extended?
Database and work locations can be extended. Checkpoints, journals and dump files can only be relocated or moved to a new location but cannot be extended.
Pre-requisites for extending a database location
The new disk locations must exist before this command is run.
To extend the MDB database
extenddb -lextraloc3 -Udata mdb
To create a new location but not extending the databases
extenddb -lnewloc2 -a/disk2/loc2 -Uckp, jnl -nodb
For more information on the 'extenddb' command, please refer to the "Ingres r3 Command Reference Guide".
This section describes the process of taking database backups, understanding the backup process and discusses the different approaches for taking backups
The Ingres ckpdb command is used to back up databases. Backing up an Ingres database is often referred to as "checkpointing" the database.
The backup process described
There are two mechanisms for removing old checkpoints.
To checkpoint a database :
ckpdb mdb -umdbadmin
For more information on the 'ckpdb' command, please refer to the "Ingres r3 Command Reference Guide".
This section covers information on when to do a recovery, how to proceed with recovery and how the recovery tool works
The Ingres rollforwarddb command is used to recover database or table(s) from backups.
Prerequisites for Database Recovery
How the database recovery process works
Note: Before database recovery using rollforwarddb can occur, it is imperative that a backup occur. This provides the ability to restore in the case of errors during recovery. In addition, all log files including the transaction log should be saved. Please refer to the section of this document that discusses Troubleshooting for additional information.
To recover database MDB from the last known checkpoint
rollforwarddb -umdbadmin mdb -v
To recover the above database from the fourth oldest checkpoint
rollforwarddb #c4 -umdbadmin mdb -v
For more information on the 'rollforward' command, please refer to the "Ingres r3 Command Reference Guide".
This section describes how to get information on
the databases you own and to understand how to get DBA related information for the MDB
The Ingres infodb command is used to retrieve
information on databases in an Ingres instance.
Why,
what and when database information can be required
Provides a simple way to get high level information on a
database
When details about a database’s status, the location
of its files and history of checkpoints and journals is required.
This command requires a user with DBA or system
administrator privlidges
Includes useful information for checkpoints such as if and
when they were taken and whether journaling is turned on or not.
Displays problems with the databases such as the state and
information about underlying causes.
Whenever there is a need to get high level
information about the MDB
To run this command from a command line
infodb mdb
infodb mdb #4
Output
Explained:
This section covers how to export MDB table(s)
and to create reports
The Ingres Export Assistant (iea) and Microsoft Excel can be used to export the MDB
tables and create reports.
Exporting data is useful when there is a need to create reports on a set of table(s)
from the MDB.
On Windows Ingres provides an intuitive tool called the Ingres Export Assistant. This tool has the ability to export a table(s) data
into a file. Supported file formats are csv, xml and dbf. It can be run as stand-alone
from the command line using the command ‘iea’ or from Visual DBA (VDBA).
Another simple way to export data is with Microsoft Excel using Ingres ODBC
connectivity. In order to use Microsoft Excel a ODBC DSN needs to be setup.
Use the Ingres Export Assistant – Data from one or more tables can be exported
using the export utility. A user running this utility has to have the permissions to
access the data in those tables. The export utility can be run by running the command
‘iea’ on the command line..
To run the Ingres Export utility from the command
line type:
Microsoft
Excel
Using the Ingres 3.0 ODBC with Microsoft Excel -
Using MS Excel and Ingres ODBC Connectivity, data can be extracted from tables in the MDB.
Some basic knowledge on how to connect to an ODBC DSN is needed. Once an ODBC DSN is
created use Excel to do the following:
Then select the DSN created using the Ingres 3.0 ODBC driver and the
rest should be fairly intuitive.
This section
provides information on the log files related to the MDB.
All the information related to the database is stored in log files as explained above. Whenever there is a need to troubleshoot a problem with the MDB, the first place to look at are these log files. The location of these files is in the II_SYSTEM\ingres\files folder. The log files are:
Whenever you contact CA Technical Support
regarding issues with the database, these log files would always be needed in addition to
any other files.
It may be necessary to know the version of the
MDB database that is currently installed. This is functionality that can be accessed by
the owner of the MDB database ‘mdbadmin’ or a user with permissions defined for
impersonation of the user ‘mdbadmin’.
MDB Version via SQL Command
Issue the following command:
sql –umdbadmin mdb
Type the following to retrieve data from the ‘mdb’ table:
select * from mdb \g
MDB
Version from the Ingres configuration files
Issue the following commands:
iigetres
ii.<host-name>.mdb.mdb.version.build
iigetres ii.<host-name>.mdb.mdb.version.major
iigetres ii.<host-name>.mdb.mdb.version.minor
You can also get the version information about
the MDB by looking at the install_mdb.log file that was written when the MDB was created. This file is located in the II_SYSTEM/ingres/files/
directory and is named install_mdb.log.
| Product | Function | Tool/command |
| Unicenter Service Desk | This will reorganize the tables and recreate all indexes |
|
| Unicenter Service Desk | Optimization |
|
| Unicenter Service Desk | Backup | pdm_backup |
| Unicenter Service Desk | Restore | pdm_restore |