Restarting an interrupted load operation

If a failure or interruption occurs during a load operation, you can use the load utility to terminate the operation, reload the table, or restart the load operation.

If the load utility does not even 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.

When the load operation begins, the target table is placed in the Load in Progress table state. In the event of a failure, the table state will change 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.

Typically, restarting the load operation is the best choice in this situation. It saves time because the load utility restarts the load operation from the last successfully reached point in its progress, rather than from the beginning of the operation. Where exactly the operation restarts from depends upon the parameters specified in the original command. If the SAVECOUNT option was specified, and the previous load operation failed in the load phase, the load operation restarts at the last consistency point it reached. Otherwise, the load operation restarts at the beginning of the last phase successfully reached (the load, build, or delete phase).

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 reparsed, which is an expensive operation. Furthermore, the internal XML indexes, such as the regions and paths indexes, need to be rebuilt first, which also requires a scan of the XDA object.

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. An exception to this is if you want to disallow read access. A load operation that specified the ALLOW READ ACCESS option can also be restarted as an ALLOW NO ACCESS option.

Note: Do not delete or modify any temporary files created by the load utility.
If the load operation resulting from the following command fails, 
LOAD FROM file_name OF file_type  	
SAVECOUNT n 
MESSAGES message_file  
load_method 
INTO target_tablename
you would restart it by replacing the specified load method (load_method) with the RESTART method: 
LOAD FROM file_name OF file_type  	
SAVECOUNT n 
MESSAGES message_file  
RESTART
INTO target_tablename

Failed loads that cannot be restarted

You cannot restart failed or interrupted load operations if the table involved in the operation 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
You should issue either a LOAD TERMINATE or a LOAD REPLACE command.

Failed load limitations

The BACKUP DATABASE command might return an I/O error if the LOAD command fails on a table in SMS tablespace and the table is left in Load Pending state.

Table data might not appear consistent when a table is in Load Pending state. Inconsistent table data will cause the BACKUP DATABASE command to fail. The table will remain inconsistent until a subsequent LOAD TERMINATE, LOAD RESTART, or LOAD REPLACE command is completed.

You must remove the table from the Load Pending state before backing up your database.