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
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

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.

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 )

An application will not timeout unless it is waiting on resource that is locked by someone else.

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?

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?

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).

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.