This article mainly introduces the copy no/copy yes/NONRECOVERABLE provided by the DB2 LOAD command, as well as the functions and correct usage of the related registry variable DB2_LOAD_COPY_NO_OVERRIDE.
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 Unix, It is a file format: Databasealias. Type. Instancename. Nodename. Catnodename. Timestamp. number
In Windows, sub-directories and files are stored in the format of 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 for 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. 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. If you attempt to update the tablespace again, the operation will be successful: 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 restoring 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.