Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2003
    Location
    Ohio
    Posts
    38

    Question Unanswered: When are DB2 logs accessed?

    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

  2. #2
    Join Date
    Mar 2004
    Posts
    46
    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.....

  3. #3
    Join Date
    Aug 2003
    Location
    Ohio
    Posts
    38
    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

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Aug 2003
    Location
    Ohio
    Posts
    38
    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!

  6. #6
    Join Date
    Mar 2003
    Posts
    343
    You ahve not mentioned the version - it may make a difference. Also, is this a partitioned database?

  7. #7
    Join Date
    Aug 2003
    Location
    Ohio
    Posts
    38
    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".

  8. #8
    Join Date
    Mar 2004
    Posts
    46
    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

  9. #9
    Join Date
    Mar 2003
    Posts
    343
    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.

  10. #10
    Join Date
    Aug 2003
    Location
    Ohio
    Posts
    38
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •