DB2 backup question

rangupt · #1 (**permalink**) 03-03-10, 14:04

OS : windows 2008
db2: db2 9.7
backups are going to tsm

I am backing up the databases using the following command:

"backup database test online use tsm"

When i query the history file i see 2 entries. I understand that the default behavior now is to include the logs and thats why the history file shows 2 entries, one as 001 and one as 002. And only 1 entry is diplayed when the backup is offline.

For Example:

Code:

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20100303093042001   N    A  S0000006.LOG S0000006.LOG
 ----------------------------------------------------------------------------
  Contains 4 tablespace(s):

  00001 SYSCATSPACE
  00002 USERSPACE1
  00003 SACBOTBS
  00004 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP TEST ONLINE
 Start Time: 20100303093042
   End Time: 20100303093047
     Status: A
 ----------------------------------------------------------------------------
  EID: 48 Location: bin\db2tsm.dll


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20100303093042002   N    A  S0000006.LOG S0000006.LOG
 ----------------------------------------------------------------------------
  Contains 4 tablespace(s):

  00001 SYSCATSPACE
  00002 USERSPACE1
  00003 SACBOTBS
  00004 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP TEST ONLINE
 Start Time: 20100303093042
   End Time: 20100303093047
     Status: A
 ----------------------------------------------------------------------------
  EID: 49 Location: bin\db2tsm.dll

But does the online backup also show 2 images when querying the tsm using "db2adutl query" for online backups?

I see 2 images for every online backup i took. Is this the normal behavior to show 2 images on tsm for every online backup? I have 2 management classes set up, one for archive logs and one for backup image

Here's my output for db2adutl query. The ones in red color are the online backups.

Code:

Query for database TEST


Retrieving FULL DATABASE BACKUP information.
1 Time: 20100303093309  Oldest log: S0000007.LOG  DB Partition Number: 0    Sessions: 1
2 Time: 20100303093207  Oldest log: S0000007.LOG  DB Partition Number: 0    Sessions: 1
3 Time: 20100303093042  Oldest log: S0000006.LOG  DB Partition Number: 0    Sessions: 1
4 Time: 20100303093042  Oldest log: S0000006.LOG  DB Partition Number: 0    Sessions: 1    
5 Time: 20100225105104  Oldest log: S0000004.LOG  DB Partition Number: 0    Sessions: 2
6 Time: 20100225104714  Oldest log: S0000004.LOG  DB Partition Number: 0    Sessions: 1
7 Time: 20100224154657  Oldest log: S0000003.LOG  DB Partition Number: 0    Sessions: 1
8 Time: 20100224154657  Oldest log: S0000003.LOG  DB Partition Number: 0    Sessions: 1
9 Time: 20100224154442  Oldest log: S0000002.LOG  DB Partition Number: 0    Sessions: 1
10 Time: 20100224154442  Oldest log: S0000002.LOG  DB Partition Number: 0    Sessions: 1   
11 Time: 20100224150118  Oldest log: S0000001.LOG  DB Partition Number: 0    Sessions: 1
12 Time: 20100224150118  Oldest log: S0000001.LOG  DB Partition Number: 0    Sessions: 1
13 Time: 20100224142231  Oldest log: S0000000.LOG  DB Partition Number: 0    Sessions: 1
14 Time: 20100224142231  Oldest log: S0000000.LOG  DB Partition Number: 0    Sessions: 1
15 Time: 20100219134313  Oldest log: S0000000.LOG  DB Partition Number: 0    Sessions: 1


Retrieving INCREMENTAL DATABASE BACKUP information.
  No INCREMENTAL DATABASE BACKUP images found for TEST


Retrieving DELTA DATABASE BACKUP information.
  No DELTA DATABASE BACKUP images found for TEST


Retrieving TABLESPACE BACKUP information.
  No TABLESPACE BACKUP images found for TEST


Retrieving INCREMENTAL TABLESPACE BACKUP information.
  No INCREMENTAL TABLESPACE BACKUP images found for TEST


Retrieving DELTA TABLESPACE BACKUP information.
  No DELTA TABLESPACE BACKUP images found for TEST


Retrieving LOAD COPY information.
  No LOAD COPY images found for TEST


Retrieving LOG ARCHIVE information.
   Log file: S0000000.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2010-02-24-14.22.36
   Log file: S0000001.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2010-02-24-15.01.24
   Log file: S0000002.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2010-02-24-15.44.48
   Log file: S0000003.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2010-02-24-15.47.03
   Log file: S0000004.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2010-03-03-09.30.31
   Log file: S0000005.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2010-03-03-09.30.31
   Log file: S0000006.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2010-03-03-09.30.48

would appreciate help!!!!

Thanks

sathyaram_s · #2 (**permalink**) 03-03-10, 14:12

Quote:

I understand that the default behavior now is to include the logs

No, EXCLUDE LOGS is the default unless it is a SNAPSHOT backup

rangupt · #3 (**permalink**) 03-03-10, 15:33

Quote:

No, EXCLUDE LOGS is the default unless it is a SNAPSHOT backup

In that case, why would i see 2 entries in the history file (shown above). I am not using SNAPSHOT backup.

Marcus_A · #4 (**permalink**) 03-03-10, 16:06

Include logs is now the default (not sure what version that is effective with). I guess IBM support got tired of getting customer databases restored without the logs, so they include them by default now.

rangupt · #5 (**permalink**) 03-03-10, 16:09

Thanks Marcus,

That solves one part of the puzzle at least. So the output of the db2adutl query is the expected behavior?

ARWinner · #6 (**permalink**) 03-03-10, 16:49

You are getting 2 images because there are 2 session opened to TSM (I do not know why) and it is splitting the work between the two. It has nothing to do with whether the logs are included or not.

Andy

rangupt · #7 (**permalink**) 03-03-10, 17:53

Quote:

there are 2 session opened to TSM

I am not opening 2 sessions in my backup command. Are there any variables on the TSM side that would control the number of open sessions?

db2girl · #8 (**permalink**) 03-03-10, 18:44

In v9.5:

Whether logs are included or not is documented here:
IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

In your example, the logs are included.

I believe DB2 will open two sessions: one for data and then one for the logs. db2adutl will show one image with 2 sessions and list history will show two entries. This is for v9.5.

I'm not sure if db2adutl in v9.7 will show the same information as in v9.5 (1 entry with 2 sessions or 2 entries with 1 session for each). Based on your information, v9.7 shows 2 entries. TSM is not working for me at this time so I'll try this tomorrow.

rangupt · #9 (**permalink**) 03-04-10, 09:58

Thanks Bella,

I will wait for your findings. I tried the backup to tsm on 2 different servers and i get the same result. I just want to confirm if thats what others are seeing.

db2girl · #10 (**permalink**) 03-04-10, 15:08

Sorry, I still can't test db backup to TSM. There is no free space in the storage pool on TSM and I can't physically delete existing images so need to wait for our TSM guy to do it.

I believe what you're seeing is the way this works in v9.7. Try "db2 backup db test online use tsm exclude logs" and then check db2adutl/list history. I think you'll see one entry.

I found a technote for v9.5:
IBM - In DB2 V9.5 for LUW an Online Backup to TSM creates two entries in the history file.

rangupt · #11 (**permalink**) 03-05-10, 11:36

Quote:

I believe what you're seeing is the way this works in v9.7.

I think so too.

I tired the backups with exclude logs and it does show one entry in the db2adutl.
The list history describes the 2 images in a better way by adding the 001 and 002 at the end of the image but db2adutl result set is very confusing when the logs are included. Anyways thanks for all your help!!

db2girl · #12 (**permalink**) 03-05-10, 17:12

I'm using v9.7 fp1 on AIX. I performed two online backups - including the logs (default) and excluding. The one without the logs matches what you have. For the one that includes the logs, list history info is the same (2 entries), but the db2adutl query output is different (I see 1 image with 2 sessions). db2adutl verify output returns two images (001 and 002).

You can ask your TSM admin to issue a select statement on the TSM server to see what images exist there. Example:
select * from backups where node_name = '<node name>'

Also, check the output from db2adutl verify - check the extension (001 / 002)

I'm attaching a file with my results. It includes list history, db2adutl query/verify and TSM query.

Do you by any chance have another instance with db name test in it and perform backup of both databases at the same time? If you do, take a look at the following: IBM IY66077: RECOMMEND TO USE UNIQUE TSM NODENAME PER DB2 INSTANCE. - United States

I don't really think this applies to you... In your case, both images are active: one must be for data and second one - logs. But I still don't understand why the db2adutl query output is different for us.

rangupt · #13 (**permalink**) 03-11-10, 10:23

Thanks Bella for testing this

Quote:

Do you by any chance have another instance with db name test in it and perform backup of both databases at the same time?

i don't. there is only 1 instance on this server. Infact i did the test on a different windows server also and i see the same results.

I actually already verified the result of the query "select * from backups where node_nam=<node_name>" from our TSM admin guy and it is same as yours, so i see 2 images with the extension of .1 and .2.

The db2adutl verify command also shows the same result as yours:

$ db2adutl verify full taken at 20100311091418 db TEST

Query for database TEST

Retrieving FULL DATABASE BACKUP information. Please wait.

FULL DATABASE BACKUP image:
.\TEST.0.DB2.NODE0000.CATN0000.20100311091418.001, DB Partition Number: 0
.\TEST.0.DB2.NODE0000.CATN0000.20100311091418.002, DB Partition Number: 0

But still i see 2 images in my db2adutl query output.

What version of TSM client are you using? I am using version 6.1.

db2girl · #14 (**permalink**) 03-11-10, 19:40

We have TSM 5.3.5

Do you always see two images when logs are included? How was image #5 created? Offline with 2 sessions?

5 Time: 20100225105104 Oldest log: S0000004.LOG DB Partition Number: 0 Sessions: 2

What DB2 product are you using? (WSE, Express-C ...)?