DB2 load option

DB2 Load Use

Recently a good friend because the load problem caused the production failure, so write an article to summarize the use of load and considerations.

1.load Overview

Data import method has Insert,import and load three kinds, where load does not need to write log (or very few logs), do not check constraints and referential integrity constraints, do not trigger trigger, lock time is relatively short, it is particularly suitable for large data volume import.

4 Stages of 2.load

The load process is divided into 4 stages: Load/build/delete/index copy. The load phase is the parser of the source file into a physical data store, loaded directly into the page, and not through the DB2 engine, and the load phase examines the table definition, which violates the defined data and is not loaded into the table. The build stage suggests that indexes (if loaded tables have indexes) check for uniqueness constraints, and data that violates uniqueness is deleted in the delete phase. The index copy phase copies the index data from the specified temporary table space into the original tablespace, and index copy is only adapted to the Allow Read access scenario. The 4 phases of load are recorded in the messages file.

3.online and offline load

By default, the load procedure does not allow other apps to access the table, which allows no access, or called Offline load (offline loading). Allow read access, or call online load (on-line load), only allowed when insert into, other applications read data is pre-loaded data, load ... replace into will delete the data first, Load again, can only be loaded offline.

4.load Example

In this example, to import the Calpar.del file data into a table Calpar, the first field of the Calpar table is primary key, and the last field is defined as a non-empty numeric type. The load command is as follows:

Load from Calpar.del of del Modified by Dumpfile=/dump.fil warningcount messages Par.msgs Inser to Calpar for except Ion Calpar.exp

This command produces four outputs, CALPAR is the target table, messages records the 4 stages of load, DumpFile records data that violates the table definition, exception table records data that violates the uniqueness constraint (exception The table needs to be created before load, and is defined as the target table +timestamp type Column+clob type column). In this example, lines 2nd and 5th violate the table definition, so they are stored in the DumpFile file, and lines 4th and 7th violate the uniqueness constraint and are stored in the exception table, and the remaining normal data is stored in the target table.

5. Status of the Load table

Load may appear in several states at a time that may be in several states at the same time. Only when the table is the normal state, the table can be modified and deleted.

Normal: Healthy state

Set integrity pending: If the target table has a CHECK constraint or reference constraint, then the table is in set integrity pending after load, indicating that the table constraint has not been checked and explained later.

Load in Progress:load is in the process of data loading.

Load pending: There was a failure before the data was submitted and needed to pass load. Terminate,load. Replace or Load: Restart release Pending status

Read access only: The target table data is readable, and when load is specified allow read access, the table is in the Read access only state

Unavailable: The table may have been deleted or recovered from backup.

Unknown: through load. The query command does not know the status of the table.

Can be passed by load: Query command to view the status of the table,

(1). When the table is in load pending, you need to check the reason for pending and then pass load. Terminate terminates the load operation, or through load: Replace clears the table data or passes the load: Restart Restart the load operation. Remember not to delete the temporary data file for load. There are many reasons for load pending, such as there is not enough space in the table space.

(2). When the table is in the set integrity pending state, it means that the user is required to check the consistency of the data (check constraint and reference constraint). Set integrity pending can be removed by set integrity for <table_name>immediate checked.

(3). Load: Replace will empty the data before the load data. If there is a failure in the middle, then load: After the terminate command, the data in the table becomes empty, so it is recommended that you perform a database backup or a single-table backup before load.

6. The Copy option for load

Copy can be understood as a backup, primarily for roll-forward recovery, where the database is in the archive log. Because load does not log, a backup is required to recover.

Load supports three copy options, copy NO (default), copy YES, and nonrecoverable.

COPY NO: This option causes the table space where the load table is located to be in the backup pending state, which can read the data, but cannot be added to the table. Once the load operation is started, the table will be in the backup pending state, and the table space will not be detached from this state even if load is terminated. After the load is complete, you need to make a table space backup.

COPY YES: This option will automatically back up the data for load changes. During the roll-forward recovery phase (Rollforward), this backup is used to reconstruct the data generated by the load process.

Nonrecoverable: This option indicates that this table cannot be recovered by Rollforward, and when Rollforward is complete, the table can only be deleted or recovered from backup media. This option does not cause the tablespace to be in the backup pending state, nor does it result in a backup of the data.

My friend a few days ago because of the load data during normal trading hours, resulting in the table space in the backup pending state, the trading system can only check the update, although the problem immediately terminated load, but because the table space is very large, backup took nearly 1.5 hours, Serious impact on the normal operation of the transaction, a mistake may lead to the year-end award. He tested the system with no problems, but because the test system was using circular logs, the archive logs were used in production.

7. Load replace quick empty large table

For large table data emptying, if you use Delete, because it generates a large number of logs, resulting in a slow, and easy to log full. You can consider the load from/dev/null of del Replace into

(Unix/linux) or load from Empty.del to del replace into(Windows,empty.del is an empty text file.)

8. Load Summary

The steps for load can be summarized as follows:

(1) Creating Tables and indexes

(2) Construction of the Exception table (Exception table)

(3) Backing up DB or Tablespace

(4) Load: DumpFile. Messages. exception

(5) Verifying dumpfile,messages and Exception tables

(6) Backup tablespace if Logretain=on and copy NO

(7) Set integrity (if table has check or reference constraints)

(8) Update the statistics to facilitate the optimizer to select the optimal execution plan.

DB2 load option