Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2004
    Posts
    306

    Unanswered: Log File has reached its saturation point

    Hey all

    DB2 8.2 FP9 (for SAP)
    AIX 5.3

    I had some scheduled jobs which ran really late or didn't start and when I look at the db2diag I see the following
    Code:
    2008-02-15-05.40.20.613629+660 E1448733A501       LEVEL: Error
    PID     : 4833396              TID  : 1           PROC : db2agent (ABC) 0
    INSTANCE: db2abc               NODE : 000         DB   : ABC
    APPHDL  : 0-107                APPID: *LOCAL.db2abc.080209154941
    FUNCTION: DB2 UDB, data protection, sqlpgResSpace, probe:2860
    MESSAGE : ADM1823E  The active log is full and is held by application handle
              "103".  Terminate this application by COMMIT, ROLLBACK or FORCE
              APPLICATION.
    
    2008-02-15-05.40.20.613978+660 I1449235A465       LEVEL: Error
    PID     : 4833396              TID  : 1           PROC : db2agent (ABC) 0
    INSTANCE: db2abc               NODE : 000         DB   : ABC
    APPHDL  : 0-107                APPID: *LOCAL.db2abc.080209154941
    FUNCTION: DB2 UDB, data protection, sqlpWriteLR, probe:6680
    RETCODE : ZRC=0x85100009=-2062548983=SQLP_NOSPACE
              "Log File has reached its saturation point"
              DIA8309C Log file was full.
    Now we have 20 Primary logs and 40 Secondary 65MB each, but we archive using TSM/TDP MySAP. The file system reached about 4GB (~60x65MB) at this time but didn't ever run out of disk space (5GB free)

    So I'm thinking that perhaps I had a UOW which logged 4GB worth operations without a commit thereby filling up all the available logs, and since they are all still active TSM can't archive them.

    So...
    A) Does the above sound like a reasonable theory?
    B) How can I find out what operation or SQL was associated with Application "103"?

    Cheers

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It is possible that application handle 103 is still holding a lock, but has not itself logged all of the 4GB in that same UOW. It may just mean that the oldest log file cannot be archived until the 103 has commited.

    If you do a snaphsot for locks on the database, you should be able to see what locks 103 is still holding. It will not tell you the SQL, but it will list the table name and what lock is being held.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2004
    Posts
    306
    Sorry I should have said, 103 is still alive, but has finished it's problem operation (probably by user cancellation) several hours ago.

    I'm just trying to figure out what i was doing so I can potentially get them to add some commits or a timeout or something.

    It's not holding any locks at the moment

    EDIT: The database snapshot shows no timeouts or deadlocks... the problem existed from just before midnight to about 5:15am. I'm surprised that 103 didn't timeout/rollback. (LOCKTIMEOUT=3600 )
    Last edited by meehange; 02-14-08 at 23:30.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    An application will not timeout unless it is waiting on resource that is locked by someone else.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jul 2004
    Posts
    306
    Right, but since this is an SAP system I kinda figured that the application might have a timeout for DB response... so I guess that means it's probably a user (unlikely at that time) or a non-standard internally developed app...

    Is there any way to see what 103 was doing during that time?

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Why would you expect a timeout? The application may have done something in DB2 but just not committed/rolled back the UOW. So it may not way for any response from DB2 at all.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jul 2004
    Posts
    306
    Quote Originally Posted by stolze
    Why would you expect a timeout? The application may have done something in DB2 but just not committed/rolled back the UOW. So it may not way for any response from DB2 at all.
    Aw I guess I'd just expect an application that was encountering a 5 hour wait because db2 couldn't log any more of its transactions to give up at some point :P

    So, no suggestions on finding out what 103 was doing at the time?

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you have connection pooling on the application server (most people do), then it is probably telling DB2 that the applicaiton is alive. It is up to the application server software to determine whether the application is still alive.

    Otherwise, you would probably get a TCP/IP timeout from the DB2 connection software (type 2 or type 4 drivers).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by meehange
    Aw I guess I'd just expect an application that was encountering a 5 hour wait because db2 couldn't log any more of its transactions to give up at some point :P

    So, no suggestions on finding out what 103 was doing at the time?
    As Marcus said, it is the application's responsibility to deal with that. How should DB2 know whether the 5 hour wait is acceptable or not? DB2 can't simply make some (potentially wrong) assumptions and terminate the connection itself - especially if there is an in-flight transaction still going on. It is the same as with your credit card, which you may not have been using for several months - should the card be invalidated just because of not being used?

    I think that you have to use some sort of trace/snapshot/monitor facilities in your application to figure out what it is doing. From the DB2 side, you could use something like the DB2 Log Analyzer to determine what the application with handle 103 did the last time that caused log records to be written 5 hours ago - or rather, you could see the effects of the SQL statement on the database. But that still won't give you the details of why the application doesn't do anything with DB2 anymore.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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