Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2009
    Posts
    42

    Unanswered: log space database is using

    What is the best way to determine how much of the active log space my database is using at any given moment?

    DB2 9.7 fix pack 4

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It depends on your definition of "the best". One way is to use db2pd -d yourdb -log. Another -- SELECT * FROM SYSIBMADM.LOG_UTILIZATION

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Or another way is to take a db snapshot - db2 get snapshot for database on yourdb

  4. #4
    Join Date
    Aug 2009
    Posts
    42
    The information presented by SELECT * FROM SYSIBMADM.LOG_UTILIZATION and db2 get snapshot for database on yourdb is the same, but it doesn't match the LOGFILESIZ X (LOGPRIMARY + LOGSECOND) value we set for the database. I compare this value with TOTAL_LOG_AVAILABLE_KB + TOTAL_LOG_USED_KB value. Why?

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Please provide:
    db2 get db cfg for <db name>
    db2 get snapshot for database on <db name>

  6. #6
    Join Date
    Aug 2009
    Posts
    42
    database cfg:

    Log file size (4KB) (LOGFILSIZ) = 1000
    Number of primary log files (LOGPRIMARY) = 10
    Number of secondary log files (LOGSECOND) = 10
    Changed path to log files (NEWLOGPATH) =
    Path to log files = /db2/db2inst1/NODE0000/SQL00001/SQLOGDIR/
    Overflow log path (OVERFLOWLOGPATH) =
    Mirror log path (MIRRORLOGPATH) =
    First active log file = S0001381.LOG
    Block log on disk full (BLK_LOG_DSK_FUL) = NO
    Percent max primary log space by transaction (MAX_LOG) = 0
    Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0


    LOGFILESIZ X (LOGPRIMARY + LOGSECOND) = (1000 x 4096) x (10 + 10) = 81920000



    database snapshot:

    Log space available to the database (Bytes)= 81177170
    Log space used by the database (Bytes) = 422830
    Maximum secondary log space used (Bytes) = 0
    Maximum total log space used (Bytes) = 8638764
    Secondary logs allocated currently = 0
    Log pages read = 0
    Log read time (sec.ns) = 0.000000004
    Log pages written = 174696
    Log write time (sec.ns) = 351.000000004
    Number write log IOs = 166214
    Number read log IOs = 0
    Number partial page log IOs = 120845
    Number log buffer full = 11997
    Log data found in buffer = 0
    Log to be redone for recovery (Bytes) = 3902693
    Log accounted for by dirty pages (Bytes) = 3902693


    81177170 + 422830 = 81600000


    They don't match. Why?

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by rdba View Post


    81177170 + 422830 = 81600000


    They don't match. Why?
    I suspect you should subtract the two, not add. 400K used out of 80M available

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    [ Log space available to the database + Log space used by the database ] should be equal to [ LOGFILESIZ * (LOGPRIMARY + LOGSECOND) ]

    If you use 4080 instead of 4096 (each log page has a 16 byte overhead) in your calculation, the numbers will match.

Posting Permissions

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