Copy no/COPY in the DB2 LOAD command (1)

When the Rollback Recovery mode is enabled for the DB2 database, the log is changed from the cyclic log mode to the archive log mode, so that the user can restore the database or tablespace after the backup, roll back the transactions in the archive log to restore the transactions committed after the database backup time point to maximize the protection of database data.

In order to quickly import data, the LOAD utility of DB2 not only loads data by Directly Writing formatted data pages to the database, but also does not activate the trigger during the import process, in addition to the methods such as integrity check and table check constraints, the operations for recording transaction logs are minimized. In the LOAD, BUILD, DELETE, and index copy processing phases of LOAD, only logs are recorded for each DELETE event in the DELETE phase, that is, only the deletion operation logs for each row that violates the unique constraint are recorded. Therefore, the entire LOAD operation only records a small number of logs.

Because LOAD minimizes the log record, a database with rollback and recovery enabled needs to archive logs for online backup recovery. For this database LOAD operation, after the LOAD operation is avoided, the table is set to abnormal due to the lack of logs when the ROLLFORWARD command is used to roll back the archived logs. DB2 provides the following options for the LOAD command:

· Copy no (default)

· COPY YES

· NONREVERABLE

To better illustrate the functions of these options, we will illustrate them here in an example. Before starting the operation, first understand the form and naming features of the image files generated by the DB2 backup operation:

In a UNIX environment, the format is file:

Databasealias. Type. Instancename. Nodename. Catnodename. Timestamp. number

In Windows, subdirectories and files are used:

Databasealias. Type \ Instancename \ Node0000 \ Catn0000 \ yyyymmdd \ hhmmss. number

The Type varies depending on the Backup Type:

0 -- full database backup

3 -- tablespace backup

4 -- backup generated by the LOAD operation

1. perform a full database backup:

First, perform a full backup of the SAMPLE database with the Rollback Recovery Mode Enabled:

E: \ TEST> db2 backup db sample

The backup is successful. The time stamp of this backup image is: 20051230174105

At this time, a subdirectory SAMPLE.0 is generated under the current directory, indicating that a full database backup is generated. The following examples illustrate these phenomena one by one:

2. About copy no:

At the end of the LOAD operation, place the tablespace in which the table is located in the "backup temporarily suspended" State. At this time, although the table can be selected, the UPDATE and DELETE operations cannot be performed. To restore the table to normal, you must manually execute a BACKUP command on its tablespace except for the BACKUP pending state. Because this option is the default option, if not specified in the LOAD command, this option is used by default, for example:

E: \ TEST> db2 connect to sample E: \ TEST> db2 load from staff. del of del insert into staff E: \ TEST> db2 list tablespaces : Tablespace id = 2 Name = USERSPACE1 Type = system management space Content = any data Status = 0x0020 Explanation: Backup pending : E: \ TEST> db2 select count (*) from staff 1 ----------- 70 1 record selected. E: \ TEST> db2 update staff set id = 335 where id = 340

DB21034E this command is processed as an SQL statement because it is not a valid "command line processor" command. During SQL processing, it returns:

SQL0290N does not allow access to tablespaces. SQLSTATE = 55039

After you manually perform a backup operation on the USERSPACE1 tablespace, The tablespace status will be normal and the update operation will be successful again:

E: \ TEST> db2 backup db sample tablespace (userspace1)

The backup is successful. The time stamp of this backup image is: 20051230184841

After the command is complete, a subdirectory SAMPLE.3 is generated in the current directory, indicating that a tablespace-level backup is generated.

E: \ TEST> db2 connect to sample E: \ TEST> db2 list tablespaces Tablespace id = 2 Name = USERSPACE1 Type = system management space Content = any data Status = 0x0000 Explanation: Normal

The resulting tablespace backup can be used to restore the tablespace to normal when the database sets the tablespace to "restoring temporarily suspended" due to the rollback operation, and resume the modification to the table by the LOAD operation. If the current rolling database exceeds the LOAD time point, the tablespace will be set to restore the temporary suspension status:

E: \ TEST> db2 restore db sample taken at 20051230174105 The DB20000I restore database command is successfully completed. E: \ TEST> db2 rollforward db sample to end of logs and stop Sql1_1 W recovered Database "SAMPLE", but one or more tablespace offline on node "0" E: \ TEST> db2 connect to sample E: \ TEST> db2 list tablespaces Tablespace id = 2 Name = USERSPACE1 Type = system management space Content = any data Status = 0x0100 Explanation: Recovery pending

At this time, you can use this tablespace-level backup for recovery:

E: \ TEST> db2 restore db sample tablespace (userspace1) taken at 20051230184841 The DB20000I restore database command is successfully completed. E: \ TEST> db2 connect to sample E: \ TEST> db2 list tablespaces : Tablespace id = 2 Name = USERSPACE1 Type = system management space Content = any data Status = 0x0080 Explanation: Roll Forward : E: \ TEST> db2 rollforward db sample to end of logs and stop tablespace (userspace1) Roll-forward status Input Database alias = sample Number of nodes returned status = 1 Node number = 0 Roll Forward status = not suspended Next log file to be read = Processed log files =- Last implemented transaction = 2005-12-30-10.47.10.000000 The DB20000I ROLLFORWARD command is successfully completed. E: \ TEST> db2 connect to sample E: \ TEST> db2 list tablespaces : Tablespace id = 2 Name = USERSPACE1 Type = system management space Content = any data Status = 0x0000 Explanation: Normal : E: \ TEST> db2 update staff set id = 335 where id = 340 The DB20000I SQL command is successfully completed.

It can be seen that the tablespace status is normal and the table can be updated.