Which transaction log files are obsoleted by BACKUP ONLINE?

[Konstantin Andreev]

Hello, all.

I can't figure out <subjectfrom documentation.

I had configured
LOGARCHMETH1 = DISK:/bak/logs

so all full log files are copied *out* of the active log path. Let I issue

| # DB2 BACKUP DB MYDB ONLINE TO '/bak/images'
| Backup successful. The timestamp for this backup image is : 20070507023935

Now I need to determine which log file should be read *first* by RESTORE when rolling forward from this backup. I need it to remove earlier (obsoleted) log files.

But how can I do this ? It's very likely that the log file in question is the one given by

| # DB2 GET DATABASE CONFIG FOR MYDB | sed -n 's/.*First active log file.*= *//gp'

issued just before backup. But this is not always true, because BACKUP procedure tries to flush dirty pages especially to reduce rollforward window, so the first active log file number could increase.

Of course, I could see the log file should be read *first* after database restore:

| # DB2 ROLLFORWARD DATABASE MYDB QUERY STATUS
| Rollforward Status
| Next log file to be read = S0000023.LOG

but I need this info after backup, not after restore. Also "db2ckbkp" does not help much.

Please, direct me.
--
Konstantin Andreev.

[4.spam]

Hello.

Look at the
LIST HISTORY BACKUP COMMAND

Sincerely,
Mark B.

Hello, all.

I can't figure out <subjectfrom documentation.

I had configured
LOGARCHMETH1 = DISK:/bak/logs

so all full log files are copied *out* of the active log path. Let I issue

| # DB2 BACKUP DB MYDB ONLINE TO '/bak/images'
| Backup successful. The timestamp for this backup image is : 20070507023935

Now I need to determine which log file should be read *first* by RESTORE when rolling forward from this backup. I need it to remove earlier (obsoleted) log files.

But how can I do this ? It's very likely that the log file in question is the one given by

| # DB2 GET DATABASE CONFIG FOR MYDB | sed -n 's/.*First active log file.*= *//gp'

issued just before backup. But this is not always true, because BACKUP procedure tries to flush dirty pages especially to reduce rollforward window, so the first active log file number could increase.

Of course, I could see the log file should be read *first* after database restore:

| # DB2 ROLLFORWARD DATABASE MYDB QUERY STATUS
| Rollforward Status
| Next log file to be read = S0000023.LOG

but I need this info after backup, not after restore. Also "db2ckbkp" does not help much.

Please, direct me.
--
Konstantin Andreev.

[darko]

On May 7, 2:25 am, Konstantin Andreev <[e-mail]>
wrote:

Hello, all.

I can't figure out <subjectfrom documentation.

I had configured
LOGARCHMETH1 = DISK:/bak/logs

so all full log files are copied *out* of the active log path. Let I issue

| # DB2 BACKUP DB MYDB ONLINE TO '/bak/images'
| Backup successful. The timestamp for this backup image is : 20070507023935

Now I need to determine which log file should be read *first* by RESTORE when rolling forward from this backup. I need it to remove earlier (obsoleted) log files.

But how can I do this ? It's very likely that the log file in question is the one given by

| # DB2 GET DATABASE CONFIG FOR MYDB | sed -n 's/.*First active log file.*= *//gp'

issued just before backup. But this is not always true, because BACKUP procedure tries to flush dirty pages especially to reduce rollforward window, so the first active log file number could increase.

Of course, I could see the log file should be read *first* after database restore:

| # DB2 ROLLFORWARD DATABASE MYDB QUERY STATUS
| Rollforward Status
| Next log file to be read = S0000023.LOG

but I need this info after backup, not after restore. Also "db2ckbkp" does not help much.

Please, direct me.
--
Konstantin Andreev.

Have you considered option "INCLUDE LOGS" that was introduced in DB2
LUW 8.2 (I think)? You can add "COMPRESS" as well to save some space,
if needed. For example:

$ db2 backup database $DATABASE online to $BACKUPPATH compress include
logs

Darko Krstic

[Konstantin Andreev]

Darko Krstic wrote:

On May 7, 2:25 am, Konstantin Andreev <[e-mail]:

>| # DB2 BACKUP DB MYDB ONLINE TO '/bak/images'
| Backup successful. The timestamp for this backup image is : 20070507023935

Now I need to determine which log file should be read *first* by RESTORE when rolling forward from this backup.
But how can I do this ?

Have you considered option "INCLUDE LOGS" that was introduced in DB2 LUW 8.2 (I think)? You can add "COMPRESS" as well to save some space, if needed. For example:
$ db2 backup database $DATABASE online to $BACKUPPATH compress include logs

Yes, I have. "INCLUDE LOGS" is good for a version backup/restore, whereas I need a point in time, restorable to any time in future (for the case of a disk fault). Using "INCLUDE LOGS" in my case would just replace the log file in question:

For now, I am looking which file RESTORE would read lst for rolling forward "this" backup image. When using "INCLUDE LOGS" I will look for the log file RESTORE would read lst *after* rolling forward all logs stored in the backup image. It's very likely that it will be "First active log file" at the moment of backup completion, but I can't be sure: there is a race condition between the moment when backup completes, and the moment when "GET DB CFG" command issued.
--
Konstantin Andreev.

[Konstantin Andreev]

Mark B. wrote:

Look at the
LIST HISTORY BACKUP COMMAND

>| # DB2 BACKUP DB MYDB ONLINE TO '/bak/images'
| Backup successful. The timestamp for this backup image is : 20070507023935

Now I need to determine which log file should be read *first* by RESTORE when rolling forward from this backup.
But how can I do this ?

Thank you, this is a helpful hint. I'm just unpleasantly surprised, that information returned by 'LIST HISTORY' command is not documented neither 'LIST HISTORY' nor 'SYSIBMADM.DB_HISTORY' information center topics. The only place it's documented is 'db2HistoryData' data structure of the DB2 API. For those who care:

| FirstLog - The earliest log file ID (ranging from S0000000 to S9999999): - Required to apply rollforward recovery for an online backup

So, 'LIST HISTORY' is a solution.

But... looking at the 'history' table is an oblique method to associate the backup image with the earliest log file ID. For instance, history may be pruned or unavailable (by either condition) at the moment when I am trying to create association.

I'm still interested in straightforward method of finding earliest log file ID: by looking only at the backup image file and set of log files. Something like retrieving and comparing LSN's and/or timestamps.
--
Konstantin Andreev.

[4.spam]

Look at the output of the db2ckbkp with "-l" option.
It seems (but may be it's not correct) that number of the first active
log is in the row with phrase "Initial extent number".
I've never seen detailed description of the output of the db2ckbkp
command with different options and I'll appreciate if somebody gives
me links to such info.

Mark B. wrote:

Look at the
LIST HISTORY BACKUP COMMAND

| # DB2 BACKUP DB MYDB ONLINE TO '/bak/images'
| Backup successful. The timestamp for this backup image is : 20070507023935

Now I need to determine which log file should be read *first* by RESTORE when rolling forward from this backup.
But how can I do this ?

Thank you, this is a helpful hint. I'm just unpleasantly surprised, that information returned by 'LIST HISTORY' command is not documented neither 'LIST HISTORY' nor 'SYSIBMADM.DB_HISTORY' information center topics. The only place it's documented is 'db2HistoryData' data structure of the DB2 API. For those who care:

| FirstLog - The earliest log file ID (ranging from S0000000 to S9999999): - Required to apply rollforward recovery for an online backup

So, 'LIST HISTORY' is a solution.

But... looking at the 'history' table is an oblique method to associate the backup image with the earliest log file ID. For instance, history may be pruned or unavailable (by either condition) at the moment when I am trying to create association.

I'm still interested in straightforward method of finding earliest log file ID: by looking only at the backup image file and set of log files. Something like retrieving and comparing LSN's and/or timestamps.
--
Konstantin Andreev.