I read a lot of documentations for db2 restore but I could not find how to perform online restore from the last database backup but without roll forwarding of logs?

I will appreciate command example.

On example my last online backup is made 1st february. I want to do ONLINE RESTORE of that backup but without logs after 1st February (similar with offline restore option WITHOUT ROLL FORWARD).

I am using db2 9.7

Thank you in advance

1 Answer 1


As per Information Center:

"If this option is specified when restoring from an online backup image, error SQL2537N will be returned."

So you you are left with rolling forward. That being said, you could try the following

db2 rollforward db mydb to end of backup and complete

According to the information for rollforward command

END OF BACKUP Specifies that all partitions in the partitioned database should be rolled forward to the minimum recovery time. See Examples section below for an example.

This should restore whatever it is you are attempting to restore up to the point of the end of the backup and not include any rolling forward through transactions logs that may be occurring after the backup. Normally you still have to issue at least this statement for a restore from an offline backup where you use archived logs.

I personally haven't tried nor had the need to do an online and only rollforward to the end of the backup. Give it a shot and let us all know what happens.

EDIT: To answer your questions below: You must have archival logging enabled in order to take online backups. You can still take offline backups with archival logging. You do not have to switch back to circular to do so. However, once you have archival logging enabled, regardless of whether you are restoring from an offline or an online backup, you must perform a rollforward when you are done. No way around it. See my own previous question on this.

In order to set up archival logging, you will need to alter the database configuration parameter LOGARCHMETH1. By default, this is set to OFF, which means circular logging. Your choices for it are as follows:

  • DISK
  • TSM

LOGRETAIN tells DB2 to leave the archived logs in the logging directory. It will not clean up any of these logs for you (leaving you as the DBA to do so). USEREXIT leaves it up to a non-IBM third party tool to take care of storing and archiving your logs (usually hand coded in C). Not too many people use this anymore from what I understand. DISK and TSM seem to be used a fair amount. DISK tells DB2 where you want to archive your logs off to (although with a few other parameters set, DB2 can automatically clean up logs no longer needed-I'll show you that below). TSM tells DB2 to let IBM's Tivoli Storage Manager to archive off the logs. There are a few other settings you have to get into for that to work. Where I work, we haven't tried that yet. And lastly, VENDOR is used to use a third-party vendor product to archive off your logs.

To set up archival logging of to a different disk spot and have DB2 automatically clean things for you, you can do the following (NOTE:these are in Unix path)

db2 connect to mydb user <admin user> using <admin user password>
#NOTE THE double and single quotes needed on the command line in Unix
db2 "update db cfg using logarchmeth1 'DISK:/dbarch/dblogs'"
db2 update db cfg using num_db_backups 2
db2 update db cfg using rec_his_retentn 2
db2 update db cfg using auto_del_rec_obj on
db2 terminate

This will tell DB2 to keep 2 full backups for at least two full days. Two backups have been kept for two days and another one is taken, the oldest backup will be deleted by DB2. DB2 will also then clean up any logs that were between the now deleted backup and the next oldest retained backup.

Also note: once you switch to archival logging, DB2 will require you to immediately take an offline backup as your next action (ie, the database will be placed into BACKUP PENDING mode) and you will be unable to do anything else until you do so.

Hope this helps!

  • Hello Chris tahnk you very much for your usefull answers (this is the third orr foruth I think) I wish I could tahnk you enough. One question- my db2 database is now in curcullar logging mode. My cutsomer wants to have online backups so we must change it to archive logging. If they do restore of database they can do it with online restore+roll forwarding. But if they just want to return backup without logging is it better option to have online restore + END OF BACKUP or to just return last backup of database but in that case for offline restore we must change the logging back to ciruclar yes?
    – Dejan
    Feb 23, 2013 at 18:23
  • if you could just clarify me this? If I want to make offline restore after I changed from circullar to archive should I return it back to circullar? thank you
    – Dejan
    Feb 23, 2013 at 21:12
  • Thank you Chris this is great. You are real db2 guru! Ny customer will use Veritas NetBackup tool so we will use VENDOR option in LOGARCHMETH1 parametere. Tell me how to know what is the optimal number for OPEN SESSIONS option? My customer is asking me for recommended number but I do not know
    – Dejan
    Feb 24, 2013 at 12:36
  • How can I calculate value for OPEN SESSIONS? thank you
    – Dejan
    Feb 24, 2013 at 12:37
  • Also please sorry but for changing from CIRUCLLAR to ARCHIVE logging I set LOGARCHMETH1 paramter to location of VENDOR:....so64 etc (library file). That is for archive logging. What path shoud I set for active logging? on example my LOGARCHEMTH1 is set to LOGARCHMETH1=VENDOR:/usr/openv/netbackup/bin/nbdb2.sl64 waht value should have ACTIVE LOG parameter? on local disk or also on some VENDOR location? what does it depends on?
    – Dejan
    Feb 24, 2013 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.