I have a table EMPLOYEE.EMPLOYEE inside database HELLO which contains 3 records as listed below:
EMP_NO BIRTH_DATE FIRST_NAME LAST_NAME GENDER HIRE_DATE BANK_ACCOUNT_NUMBER PHONE_NUMBER
------- ---------- ------------------ -------------------- ------ ---------- ------------------- --------------
1. 06/05/1998 A B M 01/02/2019 026201521420 +91X
2. 10/14/1997 C D M 01/07/2019 034212323454 +91Y
3. 05/27/1997 E F F 01/14/2019 92329323123 +91Z
Then I first take an offline backup using the following commands
mkdir offlinebackup
db2 terminate
db2 deactivate database HELLO
db2 backup database HELLO to ~/offlinebackup/
After which I get this output:
Backup successful. The timestamp for this backup image is : 20190128115210
Now I take an online backup using the following commands
db2 update database configuration for HELLO using LOGARCHMETH1 'DISK:/database/config/db2inst1/onlinebackup'
db2 backup database HELLO online to /database/config/db2inst1/onlinebackup compress include logs
After this I get the output as:
Backup successful. The timestamp for this backup image is : 20190128115616
Now I go back to db2 and run CONNECT TO HELLO which connects me to my database. When I check for rows in the EMPLOYEE.EMPLOYEE table, I still get all my 3 rows.
Now I remove the row with EMP_NO 3. This gets succesfully removed. Then I run quit from the db2 terminal
Then I use this command to run the restore from my offline backup:
db2 restore db HELLO from ~/offlinebackup/ replace existing
It says DB20000I The RESTORE DATABASE command completed successfully
Now I try to connect to HELLO, it says SQL1117N A connection to or activation of database "HELLO" cannot be made because of ROLL-FORWARD PENDING. SQLSTATE=57019
To which I run db2 rollforward db HELLO to end of logs and stop
Then I connect to HELLO and try to find out the rows, I get only 2 rows, and not 3 as it was in the backup.
EMP_NO BIRTH_DATE FIRST_NAME LAST_NAME GENDER HIRE_DATE BANK_ACCOUNT_NUMBER PHONE_NUMBER
------- ---------- ------------------ --------------------- ------ ---------- ------------------- --------------
1. 06/05/1998 A B M 01/02/2019 026201521420 +91X
2. 10/14/1997 C D M 01/07/2019 034212323454 +91Y
The third record is not visible, which was present in the backup. Can anyone figure out why I am not able to restore the third record from the backup
The rollforward command that you ran:
db2 rollforward db HELLO to end of logs and stop
replayed all available logs, including the record corresponding to the delete statement.
If you wanted to restore the database to the state right after the backup was taken you could have run
db2 rollforward db HELLO to end of backup and stop
Alternatively, since you are restoring from an offline backup, rollforward is altogether not necessary and you could have used
db2 rollforward db HELLO stop
Alternatively, skip the rollforward completely (for offline backups only, of course):
db2 restore db HELLO from ~/offlinebackup/ replace existing without rolling forward