Fixing Difficult Reorganization Problems with SAPDBA (SAP Library - SAP Database Guide: Informix (BC-DB-INF-DBA))

Fixing Difficult Reorganization Problems with SAPDBA

Use

SAPDBA sometimes cannot offer a recommendation due to a difficult reorganization problem. The problem can normally be easily fixed. For more information about the reorganization log, see Reorganization Log with SAPDBA.

Procedure

Caution

Do not delete any data and do not perform Delete Uncompleted Requests. If you are using the "export/import" procedure and you have a difficult problem, do not delete the download file containing the table data. Also do not delete SAPDBA’s working directory or any of the files in it. This directory is date-stamped directory with the prefix EXIM_ , MOVE_ or DBS_REORG_ (for more information, see explanation at start of this section).

If you perform Delete Uncompleted Requests this removes entries from the control table used by SAPDBA. These entries might contain valuable diagnostic information.

Note

If your database host machine crashes or a disk crashes, see the final step.

Proceed as follows for each table where SAPDBA has issued the message Check SAPDBA documentation for help.

What you need to do for each table depends on Actual status of TABLE as listed in reorg_log (or, in the case of Reorganize Dbspace, either reorg_log_exp or reorg_log_imp ):

TABLE LOST, MISSING ROWS INDEXES/VIEWS/TRIGGERS/CONSTRAINTS LOST

To repeat, do not delete any data. Call the Informix hotline.

UNKNOWN

Check existence of the table and current number of rows

You can do this choosing Reorganization ® Analyze Table Reports ® List Table Information in SAPDBA. If this fails, you can also use DB-Access , entering one of the following commands:

UNIX

Select count(*) from <table_name>

dbaccess select count(*) from <table_name>

The result returned is the number of the rows in the table or a message that the table does not exist. For more information about how to use DB-Access , see the Informix documentation.

Check reorg_log (or, in the case of Reorganize Dbspace, either reorg_log_exp or reorg_log_imp ) for number of rows before reorganization

Look for an entry in reorg_log for Rows of table BEFORE reorg. If this is missing, look in the first line of the log (for example Moving Table ‘vrsx’ Rows/views/indexes: 2790/0/1).

Check the script with the suffix . out

See the table "Types of Reorganization Files" below to find out the names of the various output files. These files can contain valuable information to help you identify the problem before calling the Informix hotline.

If the table exists and the current number of rows matches the number of rows before reorganization, then no data has been lost.

If the table does not exist or the current number of rows does not match number of rows before reorganization, then call the Informix hotline. To repeat, do not delete any data.

If you need to call the Informix hotline, use the following resources to help find the problem:

The contents of the date-stamped directory with the prefix EXIM_ , MOVE_ or DBS_REORG_ (for further details, see explanation at start of this section).

This directory contains the following categories of file:

A log file (called reorg_log or, in the case of Reorganize Dbspace, reorg_log_exp and reorg_log_imp ) which SAPDBA uses to keep a central record of its processing actions (including details of errors).

The individual script files used to execute the steps that make up the overall process. A different set of files is created for each table.

The individual script files used to reorganize each table in turn can be grouped into the following types (the name is always prefixed with the table name):

Types of Reorganization Files

	UNIX	NT
Unload	<table name>.unload Example: euinfo.unload	<table name>.unload.sql Example: euinfo.unload.sql
Schema	<table name>.schema Example: euinfo.schema	<table name>.schema.sql Example: euinfo.schema.sql
Load	<table name>.load Example, euinfo.load	<table name>.load.sql Example: euinfo.load.sql

For some of these types, there is a corresponding file with the same name stem but suffixed .out containing output details from the relevant operation. For example, euinfo.load.out (UNIX) or euinfo.load.sql.out (NT) contains output details from the load operation.

The unload files are only present if you are doing an "export/import" type of reorganization.

The download file that contains database data for the selected tables and is held in the directory specified in download path. This is only relevant if you are doing an "export/import" type of reorganization.

If you are sure that you have identified the problem and want to re-run particular steps in the reorganization of a table, execute the following command:

If your operating system is UNIX:

<filename>

For example, to re-run a step for the file euinfo :

euinfo.schema.views

If your operating system is NT:

Caution

Do not forget the -X parameter for dbaccess with NT. If you forget this parameter, you risk losing data.

dbaccess -e -X <sid> <filename>

For example, to re-run the unload step for the file euinfo :

dbaccess -e -X jp2 euinfo.schema.views<.sql>

If you have had a database host or disk crash (for example, due to a power failure) proceed with special caution. If in any doubt as to how to proceed, call the Informix hotline.

This kind of problem often means that shared memory buffers could not be flushed to disk correctly. The result is that data might have been lost.

Even tables for which the reorg_log file (or, in the case of Reorganize Dbspace, either reorg_log_exp or reorg_log_imp ) reports a successful completion can be affected in this situation. Note that this file might also have been affected by the failure and might not be fully up-to-date. Everything that happened after the last checkpoint is possibly lost. A checkpoint is taken after every ten tables in SAPDBA reorganizations, so you can be sure that the data on disk is fully up-to-date at this point.

Check all tables that have been reorganized after the last checkpoint for completeness. You should check the number of rows, views, and indexes and then proceed as follows:

If data has been lost, a restore is the only possible way to get the data back. The danger of this possibility highlights the need to always create a full database backup ( ON-Bar ) or archive ( ON-Archive or ontape ) immediately before you start a reorganization.

If data has not been lost, you can try to restart the request, but do not perform an "update request." See the "caution" below. SAPDBA then warns you if data has indeed been lost. Before reorganization starts, SAPDBA writes a reference count of the number of rows in each table. If you update the request, this reference count is overwritten.

Caution

Do not use the "update request" feature in the restart processing. If you use "update request" during restart processing when data has been lost, you run the risk of overlooking the problem and continuing as if nothing had happened.