When the DB2 database roll-forward recovery mode is enabled, the log will log the cycle way to archive logs to restore the user during operation, can restore the database or table space backup, then roll through the archive log services, restore the database after the backup time to submit the transaction to maximize the protection of databases of data.
The DB2's LOAD utility to import data for the fast function, except used to write directly to the database through the data formatted pages load data, import process does not activate the trigger, do not check the reference to integrity and table check constraints When such means, but also minimizes the operation of the transaction log record. In the LOAD of LOAD, BUILD, DELETE, and INDEX COPY four processing stages, only stage recorded in the DELETE to delete the event record for each log that is only the only constraint for each violation of the line of deletion of logging, so the whole LOAD operation is only rarely recorded log.
Since LOAD minimizes log records, due to pre-roll is enabled to restore the database in restoring online backup archive logs when the characteristics of the LOAD operation for this database, in order to avoid implementation of the LOAD operation, the table before using the ROLLFORWARD command the process of rolling the log file the log which is set by the lack of non-normal state, DB2 LOAD command to provide the following options:
COPY NO (default)
COPY YES
NONREVERABLE
To more clearly the role of these options, here an example of the way will be explained. Before beginning operation in the first look at DB2 backup image file generated by operation of the form and name the features:
In the UNIX environment is a form of the document:
Databasealias.Type.Instancename.Nodename.Catnodename.Timestamp.number
Is a subdirectory in the Windows environment and file of the form:
Databasealias.TypeInstancenameNode0000Catn0000yyyymmddhhmmss.number
Type of which backup type due to differ:
0 - Database full backup
3 - table space backup
4 - produced by the LOAD operation backup
1. To conduct a full backup of the database:
First of all, roll on the recovery mode is enabled the SAMPLE database to conduct a full backup:
E: TEST> db2 backup db sample
Backup successful. The timestamp of the backup image is: 20051230174105
Then generated in the current directory to see a SAMPLE.0 subdirectory, that produced a full backup of the database. The following will be an example of these phenomena one by one:
2. On the COPY NO:
LOAD operation at the end of the table where the table space placed in the "backup pending" state, although this time the table can be SELECT operation, but UPDATE and DELETE operations can not. To make the table status return to normal, except for the backup pending state, you must manually execute a BACKUP command table space. Since the option is the default option, if the LOAD command is not specified, the default is to use the options, such as:
E: TEST> db2 connect to sample
E: TEST> db2 load from staff.del of del insert into staff
E: TEST> db2 list tablespaces
:
Table space ID = 2
Name = USERSPACE1
Type = System managed space
Content = no data
Status = 0x0020
Explained in detail:
Backup pending
:
E: TEST> db2 select count (*) from staff
1
-----------
70
A record is selected.
E: TEST> db2 update staff set id = 335 where id = 340
DB21034E the order was handled as an SQL statement because it was not valid "command line processor" command. In the SQL processing it returned:
SQL0290N Table space does not allow access. SQLSTATE = 55039
In the manual on the table space USERSPACE1 a backup operation, the table space state will be normal again try to update the operation will succeed:
E: TEST> db2 backup db sample tablespace (userspace1)
Backup successful. The timestamp of the backup image is: 20051230184841
Command completion can be generated in the current directory to see a SAMPLE.3 subdirectory that is generated by a table space level backup.
E: TEST> db2 connect to sample
E: TEST> db2 list tablespaces
Table space ID = 2
Name = USERSPACE1
Type = System managed space
Content = no data
Status = 0x0000
Explained in detail:
Normal
The resulting backup of the space on the table in the database because the table space rollforward operation will be set to "recovery pending" state is used to restore the normal state of the table space and to restore the LOAD operation of the table changes. If the current database of more than LOAD time roll, the table space will be set to recover pending state:
E: TEST> db2 restore db sample taken at 20051230174105
DB20000I RESTORE DATABASE command completed successfully.
E: TEST> db2 rollforward db sample to end of logs and stop
SQL1271W has been restored database "SAMPLE", but in the node "0" on one or more table space offline
E: TEST> db2 connect to sample
E: TEST> db2 list tablespaces
Table space ID = 2
Name = USERSPACE1
Type = System managed space
Content = no data
Status = 0x0100
Explained in detail:
Recovery pending
At this point can use the table space level backup for recovery operations:
E: TEST> db2 restore db sample tablespace (userspace1) taken at 20051230184841
DB20000I RESTORE DATABASE command completed successfully.
E: TEST> db2 connect to sample
E: TEST> db2 list tablespaces
:
Table space ID = 2
Name = USERSPACE1
Type = System managed space
Content = no data
Status = 0x0080
Explained in detail:
Rollforward pending
:
E: TEST> db2 rollforward db sample to end of logs and stop tablespace (userspace1)
Roll status
Input database alias = sample
Number of nodes have returned to the state = 1
Node number = 0
Roll status = not pending
To read the next log file =
Processed log files = -
Last implement the transaction = 2005-12-30-10.47.10.000000
DB20000I ROLLFORWARD command completed successfully.
E: TEST> db2 connect to sample
E: TEST> db2 list tablespaces
:
Table space ID = 2
Name = USERSPACE1
Type = System managed space
Content = no data
Status = 0x0000
Explained in detail:
Normal
:
E: TEST> db2 update staff set id = 335 where id = 340
DB20000I SQL command completed successfully.
See table space state is normal, the table also perform the update operation.