When are DB2 logs accessed?

jodie · #1 (**permalink**) 05-01-06, 16:14

This may seem like a strange question, so here's a little background.

Our DB2 UDB (unix) system generates about 3-5 gig of log files a day. We have to keep them for 14 days, so as you can see that's a bunch of disk space. To save space, we set up HSM on the system so that when it reaches a certain percent, the older files are sent to tape, leaving just a "stub". This file can then be retrieved if needed.

The first problem we saw was when we were doing a cold backup, it was looking for a file that was over 1 month old, and couldn't get it from tape. Why would it be looking for a file that old? We hadn't pruned the logs for some time, but we had full backups 1/week and incrementals every other day.

The second thing we noticed was when we changed the log directory (to a larger filesystem), it recalled ALL the files in the log directory (over 1000), only to move the 3 active files.

Why would DB2 need to look at all these files?

Thanks for any input.

Jodie

jthakrar · #2 (**permalink**) 05-02-06, 09:08

Just wondering, do you have LOGRETAIN db parameter set to yes and USEREXIT set to no ? What is your DB2 version ? Also do the following -
- execute the "get db cfg for <dbname>" to see the values for "First active log file", "LOGPRIMARY" and "LOGSECOND".
- execute the "get snapshot for database on <dbname>" to see the log related items. It will give you some insight if you have a long-running transaction.

Hope that helps.....

jodie · #3 (**permalink**) 05-02-06, 09:18

Quote:

Originally Posted by jthakrar

Just wondering, do you have LOGRETAIN db parameter set to yes and USEREXIT set to no ? What is your DB2 version ? Also do the following -
- execute the "get db cfg for <dbname>" to see the values for "First active log file", "LOGPRIMARY" and "LOGSECOND".
- execute the "get snapshot for database on <dbname>" to see the log related items. It will give you some insight if you have a long-running transaction.

Hope that helps.....

Here are some of the Log Related Cfg parms:

Code:

 Log file size (4KB)                         (LOGFILSIZ) = 5000
 Number of primary log files                (LOGPRIMARY) = 3
 Number of secondary log files               (LOGSECOND) = 125
 First active log file                                   = S0020983.LOG
 Block log on disk full                (BLK_LOG_DSK_FUL) = NO
 Percent of max active log space by transaction(MAX_LOG) = 0
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

 Log retain for recovery enabled             (LOGRETAIN) = RECOVERY
 User exit for logging enabled                (USEREXIT) = OFF

Here is the log information from the snapshot

Code:

Log space available to the database (Bytes)= 2606430378
Log space used by the database (Bytes)     = 4769622
Maximum secondary log space used (Bytes)   = 62460963
Maximum total log space used (Bytes)       = 123660963
Secondary logs allocated currently         = 0
Log pages read                             = 0
Log read time (sec.ns)                     = 0.000000004
Log pages written                          = 2984705
Log write time (sec.ns)                    = 975.000000004
Number write log IOs                       = 2137861
Number read log IOs                        = 0
Number partial page log IOs                = 1858413
Number log buffer full                     = 481
Log data found in buffer                   = 0
Appl id holding the oldest transaction     = 228
Log to be redone for recovery (Bytes)      = 25263760
Log accounted for by dirty pages (Bytes)   = 20493627

File number of first active log            = 20983
File number of last active log             = 20985
File number of current active log          = 20984
File number of log being archived          = Not applicable

It doesn't look like I have a long running transaction right now. We do have some stuff that run for long periods, but when it was trying to move the logs to the new filesystem, there was NO-ONE on the db, it had been deactivated. It only moved the 3 Active files, but it accessed over 1000 of them. I'm still so confused.

Thanks for your help!
Jodie

sathyaram_s · #4 (**permalink**) 05-02-06, 09:39

You have used HSM which is 'outside' the knowledge of DB2 ...

Here's my guess:

As you have not used userexit, from DB2's perspective all logs (both active and the older ones) are in the log directory(which in your case is HSM, I guess) .. So when you tried to move you log directory, db2 had to move all log files ... I'm not sure why it looked for old log files when doing a backup though ..

I would suggest that you use userexit to copy files from the log directory to a HSM filesystem .. therefore, the archived log files will be read only when there is a need (like rollforward)

HTH

Sathyaram

jodie · #5 (**permalink**) 05-02-06, 09:53

Quote:

Originally Posted by sathyaram_s

You have used HSM which is 'outside' the knowledge of DB2 ...

Here's my guess:

As you have not used userexit, from DB2's perspective all logs (both active and the older ones) are in the log directory(which in your case is HSM, I guess) .. So when you tried to move you log directory, db2 had to move all log files ... I'm not sure why it looked for old log files when doing a backup though ..

I would suggest that you use userexit to copy files from the log directory to a HSM filesystem .. therefore, the archived log files will be read only when there is a need (like rollforward)

HTH

Sathyaram

Sathyaram -
That sounds probable. I've never used USEREXIT before, so I will do some research on it. The weird thing is, it DIDN'T move all the files when it switch directories, it only moved the 3 active, but it read ALL of them. It makes me think it has to "check" each one for some reason. This is all SO strange for me!

Thanks!

cchattoraj · #6 (**permalink**) 05-02-06, 10:42

You ahve not mentioned the version - it may make a difference. Also, is this a partitioned database?

jodie · #7 (**permalink**) 05-02-06, 11:11

Quote:

Originally Posted by cchattoraj

You ahve not mentioned the version - it may make a difference. Also, is this a partitioned database?

Oops, sorry. Version 8, fixpack 8 (64 bit) NOT partitioned.

Code:

DB21085I  Instance "db2inst1" uses "64" bits and DB2 code release "SQL08021" 
with level identifier "03020106".
Informational tokens are "DB2 v8.1.1.80", "s041221", "U800400", and FixPak "8".
Product is installed at "/usr/opt/db2_08_01".

jthakrar · #8 (**permalink**) 05-02-06, 11:39

It seems you are in good shape - and can probably also explain some of your observations....

First, since you are using DB2 v8 with fixpack 8 (which is DB2 v8.2.1), you can use the automatic/system log archiving (which replaces the archaic USEREXIT). It can be set using "update db cfg using LOGARCHMETH1 'DISK:<full_path_for_archive_logs>'".

I would suggest doing some "read up" on this new logging feature before implementing it.

Then you can have the archived log path under HSM (Hierarchy Storage Management).

Second, the reason it moved only the 3 log files is because it (DB2) will only touch/move/know about the primary and secondary log files in your case (since you have LOGRETAIN set to RECOVERY).

Third, you mentioned that when you changed the log directory, it "recalled the all the log files and then moved only 3 log files". I haven't done this in a while, but when I had LOGRETAIN set to RECOVERY (because I did not have C compiler to compile the USEREXIT program), I used to PRUNE the "old" log files periodically. If you don't prune, DB2 "knows" about their existence - and I am guessing that DB2 wanted to verify that all those "old files existed" before you changed your LOGPATH. I would suggesting using PRUNE to safely delete old log files. Look up the DB2 Command Reference for the PRUNE command.

To sum up, I would suggest using the DB2's new LOGARCHIVAL feature and do away with log retention and pruning. Next use HSM for the archive log directory and its very likely that you will never need to use HSM for recalling files (except during a point-in-time recovery or a rollforward recovery).

Hope that helps.....

Thanks,

Jayesh

cchattoraj · #9 (**permalink**) 05-02-06, 11:40

I have had an experience where some catalog update was not committed and hence it was looking for an older log. Not sure if this is the case for you. But that was in a partitioned database.

Logs are not only read for Rollforward, but also for rollback, restart database and the handling of indoubt transactions to name some.

I notice that Logprimary is 3 whereas logsecond is 125. This is an interesting configuration. Usually, logprimary should be set to be as high as your regular transactions require and logsecond should be set high enough to handle high water marks. It does appear that you have a high volume transaction workload if the database is producing 3-5 GB of logs per day. Therefore, transaction performance may be impacted by the file handling overheads of creating archive logs. It might be insightful to lookup the performance guide section on archive logs.

The database must be backed up after archive logging is turned on.

jodie · #10 (**permalink**) 05-02-06, 12:18

Quote:

Originally Posted by jthakrar

It seems you are in good shape - and can probably also explain some of your observations....

First, since you are using DB2 v8 with fixpack 8 (which is DB2 v8.2.1), you can use the automatic/system log archiving (which replaces the archaic USEREXIT). It can be set using "update db cfg using LOGARCHMETH1 'DISK:<full_path_for_archive_logs>'".

I would suggest doing some "read up" on this new logging feature before implementing it.

Then you can have the archived log path under HSM (Hierarchy Storage Management).

Second, the reason it moved only the 3 log files is because it (DB2) will only touch/move/know about the primary and secondary log files in your case (since you have LOGRETAIN set to RECOVERY).

Third, you mentioned that when you changed the log directory, it "recalled the all the log files and then moved only 3 log files". I haven't done this in a while, but when I had LOGRETAIN set to RECOVERY (because I did not have C compiler to compile the USEREXIT program), I used to PRUNE the "old" log files periodically. If you don't prune, DB2 "knows" about their existence - and I am guessing that DB2 wanted to verify that all those "old files existed" before you changed your LOGPATH. I would suggesting using PRUNE to safely delete old log files. Look up the DB2 Command Reference for the PRUNE command.

To sum up, I would suggest using the DB2's new LOGARCHIVAL feature and do away with log retention and pruning. Next use HSM for the archive log directory and its very likely that you will never need to use HSM for recalling files (except during a point-in-time recovery or a rollforward recovery).

Hope that helps.....

Thanks,

Jayesh

Jayesh -
Thanks so much for the information. I will look into the automatic/system log archiving. And thanks for explaining why it was looking for those files, that was pretty much my assumption, but couldn't find any information validating my thoughts. I do Prune every week (> 8 days old), but we generate SO MANY logs, that we still had over 1000 for it to check.

Thanks!!
Jodie

Internet Services

Web Development

Digital Marketing

Consumer Tech