Learn how to recover a table to its normal state after a LOAD operation has failed. This article presents the alternatives, terminating the load or restarting it from last consistency point, and has been updated for IBM® DB2® 9.

Introduction

This article is intended as an aid to help users recover a table to its normal state after a LOAD operation has failed. Since the LOAD command has many options for a variety of load possibilities, it is not the intention of this paper to solve problems relating to the actual syntax. Those not familiar with the LOAD command can learn more about it at IBM's DB2®Info Center, which has keyword search capabilities for keywords such as "load" and "command."

To follow along, you should have access to the following:

Linux®, UNIX® or Windows® operating system

DB2 version 9 or higher

DB2 Personal, Workgroup, Enterprise Edition or Enterprise Edition with DPF

The LOAD utility is favored by users who need to move large amounts of data between databases or within a single database. It is faster than the IMPORT utility because it writes pages directly into the database while IMPORT does SQL INSERTS. Also, since the LOAD utility does not check referential integrity, constraints, or fire triggers, the LOAD operation becomes much faster than an IMPORT command. Logging is also almost completely eliminated, thereby reducing the time-consuming task of writing log records to a file.

There are three phases in the LOAD operation:

The LOAD phase takes the source data from the input file and loads it into the table specified in the LOAD command. This phase is mandatory.

The BUILD phase is only performed if there are indexes on the table. For instance, if the table t1 is to be loaded with data and the table has a primary key, the BUILD phase will be performed.

The DELETE phase only occurs if loading rows cause unique key violations. If this phase is performed, rows which violate the unique key constraint are removed. If you wish to keep track of the deleted rows, specify the for EXCEPTION <TABLE NAME> with the LOAD command. All unique key violations are logged to the exception table for records.

In order to load data you must have SYSADM, DBADM, or LOAD authority with one of the following:

INSERT privilege on the table when the LOAD utility is invoked in INSERT mode, TERMINATE mode (to terminate a previous load replace operation), or RESTART mode (to restart a previous load insert operation)

INSERT and DELETE privilege on the table when the LOAD utility is invoked in REPLACE mode, TERMINATE mode, or RESTART mode

INSERT privilege on the exception table, if such a table is used as part of the LOAD operation

To load data into a table that has protected columns, the session authorization ID must have LBAC credentials that allow write access to all protected columns in the table. Otherwise, the load fails and an error (SQLSTATE 5U014) is returned

Symptoms of a failed LOAD operation

There can be a number of reasons a LOAD fails. Situations that may cause a LOAD operation to fail include the following:

If a table is created in a DMS tablespace and it has not been enabled for automatic resizing, it would fail if this table space becomes full during a load operation.

The filesystem for the table's tablespace becomes full

The workstation session executing the LOAD is interrupted, killed, or locked

A system is shut down during the LOAD operation

When a table is put into a Load-pending state, it can not be accessed until it is returned to its normal state. This will be reflected when a user tries to access the table:

D:\Work>db2 select * from sales

SQL0668N Operation not allowed for reason code "3" on table "YASIR.SALES".

SQLSTATE=57016

And if the user tries to perform a LOAD with the INSERT option, the same error message will be returned.

Note: In order to determine the specifics of SQL0668N SQL error code, a user may type the following:

> DB2 ? SQL0668N

Listing 1 is a partial listing of the output from a detailed explanation of a SQL0668N message.

Listing 1. Output from an explanation of a SQL0668N message

SQL0668N Operation not allowed for reason code "<reason-code$gt;" on table "<table-name>".

Explanation:

Access to table "<table-name>" is restricted. The cause is based on the

following reason codes "<reason-code>":

......

3 The table is in the Load Pending state. A previous LOAD attempt

on this table resulted in failure. No access to the table is

allowed until the LOAD operation is restarted or terminated.

User response:

.....

3 Restart or terminate the previously failed LOAD operation on

this table by issuing LOAD with the RESTART or TERMINATE option

respectively.

Recovering from a failed LOAD operation

If the load utility does not start because of a user error such as a nonexistent data file or invalid column names, the operation terminates and leaves the target table in a normal state. In such a case, the table is already in normal state and no further action is needed.

When the load operation begins, the target table is placed in the "Load in Progress" state. In the event of a failure, the table state changes to "Load Pending." To remove the table from this state, you can issue a LOAD TERMINATE to roll back the operation, issue a LOAD REPLACE to reload the entire table, or issue a LOAD RESTART.

The REPLACE option deletes all existing data from the table and inserts the source data. The TERMINATE option rolls back the failed LOAD operation to its original state even if consistency points were passed. The RESTART option can be used when the SAVECOUNT parameter is specified. The SAVECOUNT parameter provides consistency points to prevent total loss of table data that has been inserted into the table. This option is useful for large load operations since the LOAD time may take hours. If this option is used and the LOAD operation fails, the RESTART option may be used to continue the LOAD at the last consistency point it reached.

Once you have fixed the situation that caused the load operation to fail, reissue the load command. Ensure that you specify exactly the same parameters as in the original command, so that the load utility can find the necessary temporary files.

XML considerations

If you are loading XML documents, the behavior is slightly different. Because the SAVECOUNT option is not supported with loading XML data, load operations that fail during the load phase restart from the beginning of the operation. Just as with other data types, if the load fails during the build phase, indexes are built in REBUILD mode, so the table is scanned to pick up all index keys from each row; however, each XML document must also be scanned to pick up the index keys. This process of scanning XML documents for keys requires them to be re-parsed which is an expensive operation.

Example:

One hundred thousand records are currently being loaded into a table, and the SAVECOUNT option is being used. The SAVECOUNT is set to create a consistency point at every 20,000 rows that are loaded. Five minutes pass, and the LOAD operation fails putting the table into a "Load pending" state. Fortunately, because the SAVECOUNT option was used, eighty thousand of the records that were loaded will not be rolled back. The LOAD operation can then be performed again with the RESTART option to continue inserting rows at the last saved consistency point, which happens to be eighty thousand.

Note: The lower your SAVECOUNT is, the more I/O operations will have to be performed to synchronize consistency point activities, thus impacting LOAD performance.

Listing 2 shows the output from a scenario where SAVECOUNT is used and the LOAD operation fails.

Listing 2. Output from a failing LOAD operation

D:\work>db2 load from exported_data.ixf of ixf savecount 20000 insert into SALES

SQL3501W The table space(s) in which the table resides will not be placed in

backup pending state since forward recovery is disabled for the database.

SQL3109N The utility is beginning to load data from file

"D:\work\exported_data.ixf".

SQL3500W The utility is beginning the "LOAD" phase at time "05/01/2008

19:01:11.544397".

SQL3150N The H record in the PC/IXF file has product "DB2 02.00", date

"20080501", and time "190055".

SQL3050W Conversions on the data will be made between the IXF file code page

"1252" and the application code page "1208".

SQL3153N The T record in the PC/IXF file has name "exported_data.ixf",

qualifier "", and source " ".

SQL3519W Begin Load Consistency Point. Input record count = "0".

SQL3520W Load Consistency Point was successful.

SQL3519W Begin Load Consistency Point. Input record count = "20152".

SQL3520W Load Consistency Point was successful.

SQL3519W Begin Load Consistency Point. Input record count = "40458".

SQL3520W Load Consistency Point was successful.

SQL3519W Begin Load Consistency Point. Input record count = "60771".

SQL3520W Load Consistency Point was successful.

SQL3519W Begin Load Consistency Point. Input record count = "80777".

SQL3520W Load Consistency Point was successful.

SQL3005N Processing was interrupted.

SQL3532I The Load utility is currently in the "LOAD" phase.

Number of rows read = 80777

Number of rows skipped = 0

Number of rows loaded = 80777

Number of rows rejected = 0

Number of rows deleted = 0

Number of rows committed = 80777

Number of warnings = 0

Tablestate:

Load Pending

Since SAVECOUNT option was used, you can simply restart the load by using the LOAD RESTART option:

D:\WORK>DB2 "LOAD FROM EXPORTED_DATA.IXF OF IXF SAVECOUNT 20000 RESTART INTO YASIR.SALES"

Note: If the LOAD and BUILD phases have already completed and the DELETE phase is the only phase left, the RESTART option may be used even though SAVECOUNT is not used to continue the LOAD operation.

Failed loads that cannot be restarted

A failed or interrupted load operation can not be restarted if the table being loaded is in the "Not Load Restartable" table state. Tables are put in that state for the following reasons:

A rollforward operation is performed after a failed load operation that has not been successfully restarted or terminated

A restore operation is performed from an online backup that was taken while the table was in the "Load in Progress" or "Load Pending" table state

A Load restart is not possible in these situations, but you can still issue either a LOAD TERMINATE or a LOAD REPLACE command.

Load temporary files

During a Load operation, DB2 creates some binary files control files. Theses are also known as load temporary files. These files are used for load crash recovery, load terminate operations, warning and error messages, and runtime control data. The temporary files are written to a path that can be specified through the temp-pathname parameter of the LOAD command. The default path is a "load" subdirectory of the database directory. Load temporary files are removed when the load operation completes without error. Load utility should have complete read and write access to these files. Users should not tamper with these temporary files under any circumstance. Doing so may cause the load operation to fail and you may not even be able to perform a Load restart, replace or terminate against the table which may leave the table in an non-recoverable state.

Conclusion

In summary, the LOAD command provides a fast way to move data from one location to another. The solutions above can help make your task easier when confronted with failed LOAD operations. But it also important to recognize that not all tables can be brought back to a normal state. This is true if the LOAD temporary files are lost due to user error or some other issue. That is why it is important to perform database and tablespace backups frequently to preserve important data that reside in the tablespaces.