How to do a DB2 backup

[netant]

Hi DB2 Guru,

I'm new in DB2. Can someone please give me some tips how to do a backup in DB2 database.
I create the backup from the backup wizard, when I run it, it always failed. The error message is "SQL0902C A system error (reason code="&quot occurred. Subsequent SQL statement cannot be processed. SQLSTATE=58005).
Is there anywhere tell you step by step to create a backup?

Regards,
Louis

 

[Hattusas]

That is an unusual situation.This error sholud be kept in your log file of your database.To find this file go to control center select your database right click your mouse and select "configure".Then in the logs tabs of the new windows,find where your log file resides in.It must be similar to e:\db2\node0000\sql0001\sqllogdir then search your log files.
It is obvious that there will be more than one event.To find which event is yours,go to journal and find your jobId. Salih Sipahi
Software Engineer.
City of Istanbul Turkey
openyourmind77@yahoo.com

 

[netant]

I check the log directory, they are 3 log files in there, and they are not a text file.

Sometimes, when I enter this command "backup db payroll to /tmp/PAYROLLBACKUP" then it give me this error message:
SQL2044N An error occurred while accessing a message queue. Reason code:"1".

I checked the message queue in the journal, and delete all of them, but this error still come out.

Louis

 

[Hattusas]

db2diag.log that file might also tell you what is wrong.In fact this file is more logical.You are right those were the files that log your whole transactions.Try that file if that doesn't work let me know again. Salih Sipahi
Software Engineer.
City of Istanbul Turkey
openyourmind77@yahoo.com

 

[netant]

I can do the backup now, cos I change it to SUSE IBM Database. Previously, I was using redhat with a try version of DB2 from IBM.

I have 2 question to ask you. In the Data Recovery and High Availability Guide and Reference Book from IBM. There is an example to do backup. The example is like this:
(Sun) db2 backup db kdr use tsm
(Mon) db2 backup db kdr online incremental delta use tsm
(Tue) db2 backup db kdr online incremental delta use tsm
(Wed) db2 backup db kdr online incremental use tsm
(Thu) db2 backup db kdr online incremental delta use tsm
(Fri) db2 backup db kdr online incremental delta use tsm
(Sat) db2 backup db kdr online incremental use tsm

If I want to create a job and schedule it, on Sunday, it is an offline backup, then I have to stop the database to do the backup, so is it possible to create a job that can automatically stop the database and do the offline backup?

2). After a backup, one file will create in the backup directory. I want to copy this backup and burn into CD. Except this file, Do I need to copy any other files, in case the whole harddisk crash, so I can do i full restore.


Regards,
Louis

 

[matchum]

For full recoverability, the backups will also need logs-files (for rollforward purposes).

This is easily achieved by checking via a:
db2 get db cfg for kdr

Look for LOGRETAIN and USEREXIT. If they are set to "NO", change them by these commands:
db2 update db cfg for kdr using logretain recovery
db2 update db cfg for kdr using userexit yes

TSM will then back-up the logs as well as the d/b. The number of logs and their names will be unpredictable of course, depending upon the activity.

Remember that (at last) IBM have provided an ARCHIVE command (UDB V7.2) so that you can precede your HK by taking up-to-date logs before the backup is done.

 

[netant]

Thank you matchum. For example, if I'm not using TSM, can you tell me which log I need to copy to my CD? Is it the one in the Log Location Path set in the Configuration?

After that, can you tell me how to restore the database to a new System.(Imagine that the database system is crash, and I only got the backup file store in the CD).

 

[matchum]

Piece of cake.

1 - Use the command:

db2 list history backup all for db kdr

You can embellish this to (for example) say:
db2 list history backup since 20020330 for kdr

where you 'derive' 20020330 by various means as 'n' days ago, but lets not get distracted. The command:
db2 list history backup since 20020330 for kdr > listh.txt
will obviously do the same thing but save the results in that file for later use.

The thing you are looking for is a SINGLE "F" and "D" signifying a Full backup at Database-level. i.e. not incremental, and not for a specific tablespace (or list of t/spaces). The list of history is in timestamp order (latest being the last to be shown). The same line also contains the 'inclusive' log names and the last line of each posting contains the word "Location" to show the physical location of the backup.

2 - The Restore is:

restore db kdr from xxxxx into xxnew replace existing

where xxxxx is derived from (1) above - it does not have to be that latest Full DB backup
Note that the RESTORE command can destroy/replace an existing db or if you do not specify "replace existing" and it IS a new db, it will be created automatically.
For other restores, UDB expects you to choose a recovery point, hence the restored db will be placed in the "ROLL FORWARD PENDING" state (you can confirm this by the "get db cfg for kdr" command) although you can exploit the "WITHOUT ROLLING FORWARD" option on the restore but that would give unpredictable results of course. If you are doing this in batch (unattended mode) you may want to use the "WITHOUT PROMPTING" option on the restore.
Take care over security/privileges that you are likely to re-apply or modify to the changed environment.

-----------------------------
By the way, don't forget the PRUNE LOGFILE and PRUNE HISTORY commands (say) monthly, just to ensure that the BHF (Backup History File) does not get too large.

Thats 2 pieces of cake!

 

[netant]

Hi Matchum,
Thank you for your help. Follow your instruction, I finally restore the database into a new system. Like u said, thats 2 pieces of cake!!
Now, I'm confused with backup command with incremental and incremental delta parameter. Delta is non-cummulative and without delta is cummulative. But after I did this 2 type of backup I can't see any different from the backup files.
Could you please tell me about this?

Thanks in advance.

 

[matchum]

netant,

You would not expect any differences between "Incremental" and "Incremental Delta" unless the d/b contents had changed in some way. i.e. If you did a "Full D/B" backup, then an "Incremental D/B" and "Incremental Delta D/B" without intervening D/B changes, then the contents of the last 2 backups would be catalog-only I presume (and thus identical).

When you use the command:
db2 list history backup all for db kdr
you should see under "Type", the letters F or I or D
(meaning Full, Incremental or Delta type of backup)

When you go to the directory holding the physical backups and list the contents, you will see that the backups are named according to the time they were made and you will also see their size in bytes.

If you could explain what the difference is that you EXPECTED to find, I will see if my humble knowledge can throw some light on it.

 

[netant]

Actually I just want to understand what is the different btw incremental or incremental delta, and when I need to use it. I'm quite confuse with these 2 type of backup.

 

[matchum]

One of the main reasons (for incrementals) is to save time when the db availability is restricted. Another reason is for compatibility with mainframe versions of DB2. I use incrementals to save space i.e. to keep the latest backups on-line but optimising disk-space whilst enabling rapid recovery.

The "delta" option has the potential for reducing the elapsed time for incrementals even further.

If your backup time is not causing contentions then you would probably not even consider incrementals.

 

[netant]

Hi Matchum, thank you very much for your help to let me understand the db2 backup and restore.