I am working on db2 V9.1 on AIX server. setup:
1. Userexit= ON
2. logsecond= -1
3. db2 backup database xxx online to <path> Problem:
1. how do i determine the isotime at the time of performing rollforward operation
2. Get SQL4970N error, how do i resolve this? I have copied all the active logs before restoration into archive log path, still facing the same problem.
Please help me with the rollforward command...Don't want to include logs in the backup image itself.
Getting the same SQL4970N error which says processing has halted at logfile <logfilename>. I have checked in the archive log path for the existence of that logfile which say it is there.
I am experimenting this on a test machine now. Request you to take the trouble to check step-by-step and help me know what wrong am i doing.
2. DB cfg modifications:
db2 update db cfg for sample using userexit on
db2 update db cfg for salmple using logsecond -1
db2 update db cfg for sample using logfilsiz 200(parameter changed to fill the log files quickly during testing)
3. db2 backup database sample to <backup_path> (full backup)
4. connect to sample
5. db2 "insert into staff(select * from staff)" (perform this operation 10 times to archive the log file automatically)
6. db2 backup database sampe online to <backup_path>
7. db2 drop table staff
client asked to restore database from last backup avialable (here, its the online backup taken at step number6)
8. copy all active logs into the archive log path
9. db2 restore database sample taken at <online_backup_image_time>
10. db2 rollforward database sample to <current time> using local time and stop
SQL4970N:Roll-forward recovery on database "SAMPLE" cannot reach the
specified stop point .....
Its really on an urgent basis i need to start implementing userexit.
I suppose you want to restore to a PIT prior to step #7 (drop table)?
What you can do is:
- execute "db2 list history backup all for db sample"
- look for the timestamp you're going to restore in step #9 and check "Earliest Log" and "Current Log". This is the log range you need in order to rollforward to the end of online backup.
- Create a new dir and copy this log range to this dir
- execute rollforward using:
db2 "rollforward db sample to end of logs and stop overflow log path <dir where you copied the logs in the previous step> noretrieve"
But the rollforward should also work if you don't copy/move the logs. DB2 will invoke the userexit (don't specify noretrieve in this case) to retrieve the required logs. The only requirement is that you rollforward to at least the minimum PIT which is the end of online backup.
can you just help me point what was going wrong in my case. As you said, i dont need to copy the active logs, but even if i do, it shouln't be a problem. Doesn't db2 find on its own which logs it has to use to perform a rollforward operation from all the logs which were made available to it, i mean, why create a separate directory and put only the required logs in it?
thanks for your quick reply...
I tried it that way...not working.
What i understand is when userexit is enabled it automatically retrives required logs during rollforward from the archived path specified but its not working that way in my case.
Anyways, yours was the perfect solution provided. Thanks again.
Stay happy always!!!