Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2013
    Posts
    20

    Question Unanswered: DB2 LUW 9.7.6 Why is db2 allocating more logs than primary+secondary

    Hi all,

    I have a partitioned database (4 partitions) on SUSE linux which is configured to archive log files via user exit program. The number of logs (primary+secondary) available for each partition is:
    N0 225
    N1 110
    N2 110
    N3 110

    Theoretically, there cannot be any more logs created than the sum of primary and secondary logs.

    However, during peak hours when I take a db snapshot I can see, that the 'File number of current active log' - 'File number of log being archived' is more than 'logprimary'+'logsecond'.
    For example, on partition N1 the sum of primary and secondary logs is 110 and the 'File number of current active log' - 'File number of log being archived' = 138.
    How is this possible? I thought that the sum of primary and secondary logs is a 'hard' limit which cannot be breached, but somehow it is..

    Any ideas why?


    Thanks and Regards,

    Tomas

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It probably just means that your userexit program does not copy archived logs as quickly as they are being archived by DB2.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2013
    Posts
    20

    hmm..

    I thought when userexit is used, db2 is archiving through userexit program, so if the log is not archived it is considered to be active (online) and therefore cannot be renamed and re-used.

    Is db2 still archiving logs in the background before the userexit picks it up?
    If db2 is archiving faster than the userexit program - does it mean that if userexit program crashes - logs would grow indefinitely? Because I thought that logprimary and logsecon (and num_log_span) are meant to prevent that from happening..



    All the documentation that I have found about log archiving was so vague that I still cannot fully understand some processes of log archiving..

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Logs are queued for archiving as they are filled, but they may still be required for crash recovery. If userexit fails log files will keep accumulating. The active log space (logprimary + logsecond times logfilsiz) limits the volume of concurrently active transactions, but not the space taken by archived logs that could not be reused for some reason.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2013
    Posts
    20

    final pieces of the puzzle :)

    Thanks for helping me out, n_i!

    Could you please help me understand that completely:

    In my case, I have a log_dir directory, where active log files are put. For simplicity, lets assume that I am starting fresh and first log file is called S000000.LOG. When logs are being generated and they reach 225 (logprimary+logsecond), then db2 would force the oldest application off if the first active log is S000000.LOG, but if the first active log is S000005.LOG it will continue to allocate S000225.LOG, S000226.LOG .. S000229.LOG. Then if 'current active log'-'first active log' is equal to 225 and db2 needs to allocate another log file - db2 will force the oldest application to either commit or rollback. At this point, if userexit program is down, we have 230 log files in log_dir, from which 5 are meant for archiving and 225 are locked by applications (active logs), right?
    And finally it would mean that db2 is comfortable with keeping more log files in log_dir than logprimary+logsecond as long as applications keep commiting and logs needed for crash recovery does not breach the logprimary+logsecond limit?

    Your help is much appreciated.


    Kind Regards,

    Tomas

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    That's not correct. DB2 will not "force the oldest application" or anything like that. If all active log space is occupied by running transactions, and another transaction requires more log space, an error will be returned to the application.

    You can have any number of log files in the active log directory, but at most 256 of them can be active.

    You may want to read this Log file management and this Log file allocation and removal
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Jan 2013
    Posts
    20
    Hi n_i,

    Yes, it was my mistake, I mixed up NUM_LOG_SPAN violation with log full situation.

    "You can have any number of log files in the active log directory, but at most 256 of them can be active." - this sentence explained me everything
    I can have not more than logprimary+logsecond logs active, but I still can have online logs residing in primary log directory (log_dir) if userexit archives too slowly.

    IBM should put that into one of their redbooks

    Many thanks n_i!


    Kind Regards,

    Tomas

Tags for this Thread

Posting Permissions

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