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

Reorganization Log with SAPDBA

Definition

SAPDBA for Informix writes a log containing detailed information about reorganization of a group of tables or a dbspace.

The log is called reorg_log , except for Reorganize Dbspace, when SAPDBA writes one log for the export phase ( reorg_log_exp ) and one for the import phase ( reorg_log_imp ).

You can find the log in directories held under $INFORMIXDIR/sapreorg (UNIX) or %INFORMIXDIR%\sapreorg (NT) with the following names:

Dbspace reorganization

DBS_REORG_<dbspace to reorganize>_<date/time stamp>

For example, DBS_REORG_psapload_980710165308

Reorganizations of groups of tables using the "alter fragment" method:

"Tables in dbspace" method

FRAG_<source dbspace name>_<date/time stamp>

For example, FRAG_psapload_980710165308 .

"Tables listed in file" method

FRAG_FILE_<date/time stamp>

For example, FRAG_FILE_980710165308 .

Reorganizations of groups of tables using the "insert into select from" method:

"Tables in dbspace" method

MOVE_<source dbspace name>_<date/time stamp>

For example, MOVE_psapload_980710165308 .

"Tables listed in file" method

MOVE_FILE_<date/time stamp>

For example, MOVE_FILE_980710165308 .

The date/time stamp is composed of the date (in reverse format) followed by the time. All the examples above were performed on 10th July 1998 at 16:53:08.

Use

You can use the log to see what happened during a reorganization in the event of failure.

Note

For more information about how to proceed, see Reorganization Troubleshooting with SAPDBA. You can also check the log if you want to make sure that a reorganization has completed successfully.

Structure

The Log After Successful Completion

Note

The best place to see if processing has completed successfully is to look at the summary section at the end of the log.

The log consists of the following sections (the examples below show a typical log where processing has completed successfully for all tables):

Header section at the start of the log

This contains a title and a date/time stamp, such as in the following example:

****************************************************
----------------------------------------------------
LOG FOR MOVING OF TABLES SELECTED FROM:
‘psapddic’
----------------------------------------------------
****************************************************
Start: 25. Jan. 99, 10:32

The title varies according to the type of processing and the object selected.

A central section containing a description of processing for a single table. The example below shows a normal (that is, successful) table reorganization using the "insert into select from" method:

----------------------------------------------------
Moving Table ‘vrsx’
Rows/views/indexes: 2790/0/1

Start: 25. Jan. 99, 10:32
Ready: 25. Jan. 99, 10:33

Reorganization Strategy: ‘Insert into Select from...’

Source dbspace: psapddic
Target dbspace: psapddic

ROWS Before/After: 2790/2790
VIEWS Before/After: 0/0
INDEXES Before/After 1/1

SPACE GAIN: 88%

Actual status of TABLE: MOVED
Status of MOVING: SUCCESS
----------------------------------------------------

Note the following:

The first line, Rows/views/indexes 2790/0/1, is always written before the table is processed, and is a safety precaution in case figures cannot be given later due to a processing problem.
If the number of rows, views or indexes Before and After does not match, this indicates a problem.

SPACE GAIN is a measure of whether the table now requires more or less space after the operation has finished. A negative value indicates that more space is required than previously.

Actual status of TABLE can be any of a number of possible values:

- REORGANIZED. This indicates successful completion for a table, as in this example: this term only appears when the "export/import" procedure is used.

- MOVED. This indicates successful completion for a table: this term only appears when the "insert into select from" or "alter fragment" procedure is used.

- ORIGINAL. This indicates that the table is in its original condition, without having been touched by the reorganization.

The following values indicate problems:

- TABLE LOST

- INDEXES, VIEWS AND CONSTRAINTS LOST

- UNKNOWN

Status of REORGANIZATION/MOVING can be any of the following values:

SUCCESS. The table has been successfully reorganized, as in this example.

FAILED. The reorganization has failed due to a critical problem (for example, Target dbspace is too small).

ERROR. There has been a (usually) non-critical problem with the reorganization.

WAITING. The table is due to be reorganized but has not been processed yet, presumably due to a problem with another table.

Summary section at the end of the log

This contains a summary of the processing. The following example illustrates what this looks like when processing has finished successfully:

****************************************************
Summary for MOVING of selected tables from:
‘psapddic’
****************************************************

Tables with status FAILED: 0
Tables with status ERROR: 0
Tables with status WAITING: 0
Tables with status SUCCESS: 152

Total number of reorganized tables: 152
Total number of tables to reorganize: 152

SPACE GAIN of dbspace reorganization: 25 KB (88%)

LOST TABLES/tables with DATA LOSS: 0
Tables with LOST INDEXES/VIEWS/CONSTRAINTS... : 0

End of MOVING: 25. Jan. 99. 10:55
****************************************************

Caution

If the values appearing next to LOST TABLES/tables with DATA LOSS and Tables with LOST INDEXES/VIEWS/CONSTRAINTS are not zero, you have a serious problem (although usually you can fix it without doing a restore). Call the Informix hotline.

The Log After Problems

First, look at the summary section of the log (appearing at the end of the log) since this contains valuable information, alerting you to the fact that a problem has occurred:

****************************************************
Summary for MOVING of selected tables from:
‘psapddic’
****************************************************

Tables with status FAILED: 2
Tables with status ERROR: 0
Tables with status WAITING: 149
Tables with status SUCCESS: 1

Total number of reorganized tables: 1
Total number of tables to reorganize: 152

LOST TABLES/tables with DATA LOSS: 1
Tables with LOST INDEXES/VIEWS/CONSTRAINTS... : 0

End of MOVING: 25. Jan. 99. 1045
****************************************************

Compare this with the summary section for the normal case, described above in "The Log after Processing Successfully Completed". If the following criteria are not true, then SAPDBA has hit a problem:

The value for Tables with status SUCCESS should be the same as that for Total number of reorganized tables and for Total number of tables to reorganize.

All other fields should contain zero.

Caution

If the values appearing next to LOST TABLES/tables with DATA LOSS, Tables with LOST INDEXES/VIEWS/CONSTRAINTS are not zero, you have a serious problem (although usually you can fix it without doing a restore). Call the Informix hotline.

Second, to identify exactly where the problem has occurred (that is, which tables are affected and why) look at the central section of the log, containing details of individual tables, scanning for the keywords FAILED and ERROR. The following entry shows a typical situation where reorganization on a particular table has failed:

----------------------------------------------------
Moving Table ‘tadir’’:
Rows/views/indexes: 239227/72/3

Start: 25. Jan. 99, 10:32
Ready: 25. Jan. 99. 10:33

Reorganization Strategy: ‘Insert into Select from...’

Source dbspace: psapddic
Target dbspace: psapddic

Actual status of TABLE: ORIGINAL
Status of MOVING: FAILED
Location of error: Target dbspace is too small

Recommendation: Provide more space in target dbspace! -> Restart!
----------------------------------------------------

Note the following:

Actual status of TABLE should be MOVED if processing was successful. See the discussion of this field as given above, "The Log after Processing Successfully Completed".

Status of MOVING should be SUCCESS if processing was successful. See the discussion of this field above in "The Log after Processing Successfully Completed".

Two new messages appear when problems arise:

Location of error: This message tells you in what area SAPDBA has identified a problem.

Recommendation: This message tells you what action SAPBDA recommends you take to recover from the error. This usually consists of doing something to fix the problem and then restarting the processing. In the example above, you need to add a chunk to the target dbspace (you can use SAPDBA to do this) and then restart the processing.

There are a few very awkward cases in which SAPDBA cannot make a proper recommendation and therefore there is no restart recommendation made. These errors are described below in "Awkward Problems".

In exceptional cases, SAPDBA has to completely abort processing because of some fatal error. In this case, an emergency message is written to the log (immediately before the summary section at the end), as in the following example:

EMERGENCY PROTOCOLL ENTRY:

Caused by SQL Error during update of log table (sapdba_reorg)!

Status of Operation:

FAILED (get target dbspace), table is ORIGINAL -> Restart!

In awkward cases, where SAPDBA can not make a proper restart recommendation, you see the following message in the field Recommendation:

Check SAPDBA documentation for help!