Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2013
    Posts
    18

    Unanswered: more active log files appear in log_dir than specified with LOGPRIMARY

    Hi,

    We have set LOGPRIMARY=60, have LOGSECOND turned off, yet for some reason we are seeing more than 60 active log files in our directory. 66 to be precise.

    Number of primary log files (LOGPRIMARY) = 60
    Number of secondary log files (LOGSECOND) = 0
    First active log file = S0013378.LOG

    cd <to log_dir>

    ls -l | wc -l
    68

    so above we have an extra 6 *.LOG files (not including other files such as SQLLPATH.TAG and a dir in there). the culprets are: 72 - 77:


    -rw------- 1 db2bwq dbbwqadm 61440 Dec 11 08:28 S0013372.LOG
    -rw------- 1 db2bwq dbbwqadm 12288 Dec 11 08:28 S0013373.LOG
    -rw------- 1 db2bwq dbbwqadm 12288 Dec 11 08:28 S0013374.LOG
    -rw------- 1 db2bwq dbbwqadm 12288 Dec 11 08:28 S0013375.LOG
    -rw------- 1 db2bwq dbbwqadm 12288 Dec 11 08:28 S0013376.LOG
    -rw------- 1 db2bwq dbbwqadm 12288 Dec 11 08:28 S0013377.LOG

    as when running command: db2pd -db bwq -logs | grep S001337

    0x0A000100452C93C0 000000015F0A0EA8 1327602698945 0x00000000 30000 30000 S0013378.LOG
    0x0A0001004191E380 000000015F177DB4 1327724978945 0x00000000 30000 30000 S0013379.LOG

    the above 72 - 77 are not listed and hence not seen in the db.

    So at this point I moved these 6 files out of the log_dir into old_dir. though not yet deleted them.

    I have just compared them with the further below already archived equivalents & the ones that i have moved are larger in file size than the archived ones?

    moved to old_dir:

    -rw------- 1 db2bwq dbbwqadm 61440 Dec 11 08:28 S0013372.LOG
    -rw------- 1 db2bwq dbbwqadm 12288 Dec 11 08:28 S0013373.LOG
    -rw------- 1 db2bwq dbbwqadm 12288 Dec 11 08:28 S0013374.LOG
    -rw------- 1 db2bwq dbbwqadm 12288 Dec 11 08:28 S0013375.LOG
    -rw------- 1 db2bwq dbbwqadm 12288 Dec 11 08:28 S0013376.LOG
    -rw------- 1 db2bwq dbbwqadm 12288 Dec 11 08:28 S0013377.LOG



    archived:

    -rwx------ 1 db2bwq dbbwqadm 29215 Dec 11 08:28 S0013372.LOG
    -rwx------ 1 db2bwq dbbwqadm 8410 Dec 11 08:28 S0013373.LOG
    -rwx------ 1 db2bwq dbbwqadm 8410 Dec 11 08:28 S0013374.LOG
    -rwx------ 1 db2bwq dbbwqadm 8410 Dec 11 08:28 S0013375.LOG
    -rwx------ 1 db2bwq dbbwqadm 8410 Dec 11 08:28 S0013376.LOG
    -rwx------ 1 db2bwq dbbwqadm 8410 Dec 11 08:28 S0013377.LOG


    So my questions are,

    1, why are we seeing more active log_files in the log_dir that we have specified with logprimary (60)?

    2, is it ok to do what I have just done & then delete the culpret logs that have been moved old_dir eb even though they are larger in size than the already archived equivalents?

    any one come across this issue at all?

  2. #2
    Join Date
    Oct 2013
    Posts
    18
    I have just recalled that we are compressing the logs so this quashes q2. So if anyone can help with answering Q1 only this would be most helpful.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by AT14 View Post
    1, why are we seeing more active log_files in the log_dir that we have specified with logprimary (60)?
    One reason this can occur is that there are still open (uncommitted) transactions using those log files.

    Is there some reason why you don't have 10 primary and 50 secondary?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Oct 2013
    Posts
    18
    Quote Originally Posted by Marcus_A View Post
    One reason this can occur is that there are still open (uncommitted) transactions using those log files.

    Is there some reason why you don't have 10 primary and 50 secondary?
    we are using SAP & they don't recommend to use secondary log files.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by AT14 View Post
    we are using SAP & they don't recommend to use secondary log files.
    Probably because they have customers who can't do the math and they run out of log space:

    (LOGPRIMARY + LOGSECOND) x LOGFILSIZ x 4096 = Amount of space needed for active log mount point.

    If logs are not on a dedicated mount point (some disk can be used for other purposes) then I can see why one would not use secondary logs.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Marcus_A View Post
    One reason this can occur is that there are still open (uncommitted) transactions using those log files.
    Another reason can be too many dirty pages still living in your bufferpools. Got enough IOCLEANERS?

    Does anyone KNOW why SAP does not want secondary log files? They are not known for their stupid db2 config, are they?
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by dr_te_z View Post
    Another reason can be too many dirty pages still living in your bufferpools. Got enough IOCLEANERS?

    Does anyone KNOW why SAP does not want secondary log files? They are not known for their stupid db2 config, are they?
    As I implied, they probably get tired of stupid customers putting the active logs on a file system that runs out of space because the secondary allocation starts being used, but the same disk is being used for something else, and therefore no space is available. So making all the logs fit into primary allocation guarantees that this will not happen.

    There are some IBM recommendations that also assume that their customers will do something stupid, and they come up with a solution so that they won't have people calling them all the time, even if it is not the best idea to do it that way.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Oct 2013
    Posts
    18
    db2 get db cfg | grep -i iocleaners
    Number of asynchronous page cleaners (NUM_IOCLEANERS) = AUTOMATIC(1)

    yet again its been a pleasure reading your replies....

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by AT14 View Post
    db2 get db cfg | grep -i iocleaners
    Number of asynchronous page cleaners (NUM_IOCLEANERS) = AUTOMATIC(1)

    yet again its been a pleasure reading your replies....
    Normally it uses the number of CPU cores when AUTOMATIC. That looks suspicious.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Nov 2011
    Posts
    334
    hi , dr_te_z.
    Mr Marcus_A and your replies about this question have confused me.
    as when running command: db2pd -db bwq -logs | grep S001337

    0x0A000100452C93C0 000000015F0A0EA8 1327602698945 0x00000000 30000 30000 S0013378.LOG
    0x0A0001004191E380 000000015F177DB4 1327724978945 0x00000000 30000 30000 S0013379.LOG
    I think the first active log of the op's database is S0013378.log.The logs which sequence number before it (72 - 77) is offline。That means the transactions in these logs have been commited already and the associated bufferpool pages have been flushed to disk ,too。
    So I think it could not be possible because of too many dirty pages living in bufferpool.

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by fengsun2 View Post
    hi , dr_te_z.
    Mr Marcus_A and your replies about this question have confused me.

    I think the first active log of the op's database is S0013378.log.The logs which sequence number before it (72 - 77) is offline。That means the transactions in these logs have been commited already and the associated bufferpool pages have been flushed to disk ,too。
    So I think it could not be possible because of too many dirty pages living in bufferpool.
    I never said that it was because of the too many dirty pages in bufferpool. I don't think there is enough information provided here, such as log archive info, to comment on the cause.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by fengsun2 View Post
    That means the transactions in these logs have been commited already and the associated bufferpool pages have been flushed to disk ,too
    Interesting conclusion, based upon????
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  13. #13
    Join Date
    Nov 2011
    Posts
    334
    Quote Originally Posted by dr_te_z View Post
    Interesting conclusion, based upon????
    hi, dr_te_z:
    I remember if we dont worry about restore and rollforward , these logs before the first active log can be physical removed。if it is true, then the bufferpool dirty pages associated with these old logs must be writtent back to disk when the logs became inactive, otherwise once database crashed after they have been removed,these pages will become unrecoverable。

  14. #14
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Do you remove log files from the active log directory? Is that what you are implying? Nothing can be removed from there by 'hand'. LOGARCHMETH1 takes care of that. And even then those files are not touched. If you want to clean you just issue a "db2prune" command.
    If we do not worry about restore and rollforward, like you say, the database uses the default 'circular logging', right?
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  15. #15
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by AT14 View Post
    1, why are we seeing more active log_files in the log_dir that we have specified with logprimary (60)?
    The fact that a log file is in the current log directory doesn't mean it is active. DB2 may choose not to truncate an archived log file if it may be required for crash recovery.

    Quote Originally Posted by AT14 View Post
    2, is it ok to do what I have just done & then delete the culpret logs that have been moved old_dir eb even though they are larger in size than the already archived equivalents?
    Unless you're using LOGARCHMETH1=LOGRETAIN, you should not be messing with log files.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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