Applications Hanging around ?? Is this normal ??

DBFinder · #1 (**permalink**) 07-17-09, 17:21

Hi guys,

I am not sure if this is normal, But we have problems in this database

Quote:

APP_HNDL STATUS_CHANGE_TIME THREAD_AGE APPL_STATUS APPL_NAME DBNAME
-------- -------------------------- -------------- ------------ -------------------- ----------
496 2009-07-14-12.03.38.850282 3051516.21 UOWWAIT db2jccpool-30718-thr GC_PROD
1516 2009-07-14-14.12.56.256344 3030558.80 UOWWAIT db2jcchttp-8080-9 GC_PROD
3199 2009-07-14-15.12.03.203916 3020651.86 UOWWAIT db2jcchttp-8080-5 GC_PROD
2814 2009-07-14-15.29.17.912566 3014937.15 UOWWAIT db2jcchttp-8080-4 GC_PROD
3246 2009-07-14-17.06.07.416211 3001247.64 UOWWAIT db2jcchttp-8080-4 GC_PROD
2636 2009-07-14-17.24.13.440958 2235441.62 UOWWAIT db2jccpool-30805-thr GC_PROD
3055 2009-07-14-17.48.59.320260 2232955.74 UOWWAIT db2jcchttp-8080-7 GC_PROD
2582 2009-07-14-19.12.41.837491 2220613.22 UOWWAIT db2jcchttp-8080-5 GC_PROD
1850 2009-07-14-19.36.42.887230 2214212.17 UOWWAIT db2jccpool-30839-thr GC_PROD
546 2009-07-14-21.07.32.493994 2201122.57 UOWWAIT db2jcchttp-8080-6 GC_PROD
1997 2009-07-14-21.36.12.683480 2194242.38 UOWWAIT db2jccpool-30885-thr GC_PROD
1165 2009-07-14-22.14.04.786354 2190450.27 UOWWAIT db2jcchttp-8080-7 GC_PROD
512 2009-07-14-22.35.07.888800 2184347.17 UOWWAIT db2jccpool-30904-thr GC_PROD
511 2009-07-14-22.58.29.449726 2182025.61 UOWWAIT db2jccpool-30912-thr GC_PROD
1538 2009-07-15-00.04.24.800297 2171430.26 UOWWAIT db2jccpool-30932-thr GC_PROD
75 2009-07-15-01.21.48.397901 2155706.66 UOWWAIT db2jcchttp-8080-8 GC_PROD
1992 2009-07-15-02.49.50.914629 2142904.15 UOWWAIT db2jccpool-30964-thr GC_PROD
1384 2009-07-15-04.25.51.880672 2125303.18 UOWWAIT db2jcchttp-8080-4 GC_PROD
2332 2009-07-15-04.38.52.978312 2124002.08 UOWWAIT db2jcchttp-8080-4 GC_PROD
1837 2009-07-15-06.00.05.099047 2111849.96 UOWWAIT db2jccpool-30990-thr GC_PROD
1540 2009-07-15-06.26.04.205197 2105250.86 UOWWAIT db2jcchttp-8080-4 GC_PROD

If this is normal, how can I know the genuine time before application goes away.

We are having problems like table row locks being held for long times and also sometimes transaction log held for extensive time.

Please help me know avarage values.

DBFinder

n_i · #2 (**permalink**) 07-17-09, 17:37

Applications don't "go away". They will remain connected until they disconnect. You can probably talk to the application server administrators to see if they can configure the connection pool to terminate idle connections, but then those connections that hold locks and run long transactions won't be idle and therefore will remain connected.

DBFinder · #3 (**permalink**) 07-17-09, 18:47

Thanks for reply,

So this means that application holding locks should have status only "UOWEXEC"

But what if DB2 tells (via diaglog) that appl xxxx is holding log. and the appl had status "UOWWAIT"

Same with other application that was holding lock on CLIENT_ACC while having status "UOWWAIT"

I mean if such situations occur what should I look for and where ??

Regards

DBFinder

db2girl · #4 (**permalink**) 07-17-09, 20:09

UOW Waiting means that the application is currently not running any SQL. However, it does not mean that the application has finished its transaction. May be, it finished its transaction (by committing or rolling back). Or may be, it's still in the middle of a transaction and is just waiting for user/appl to enter another SQL. So, an appl in UOW Waiting status can hold locks/active log space. You can use db2pd to see if it's holding locks, what it was executing last (in db2pd output, looks for Locks/Applications/Transactions sections)

Marcus_A · #5 (**permalink**) 07-17-09, 22:59

Most java application servers have connection pooling software which will permanently maintain a certain number of connections per app server based on the connection pooling configuration. This is usually not a problem.

If you have transactions that have not committed or not closed the transaction (a frequent problem with today's untrained programmers), then do a snapshot for locks to determine where the problems are. You will need to turn on the lock and statement monitors.

DBFinder · #6 (**permalink**) 07-18-09, 09:10

Quote:

Originally Posted by Marcus_A

Most java application servers have connection pooling software which will permanently maintain a certain number of connections per app server based on the connection pooling configuration. This is usually not a problem.

If you have transactions that have not committed or not closed the transaction (a frequent problem with today's untrained programmers), then do a snapshot for locks to determine where the problems are. You will need to turn on the lock and statement monitors.

Yes Marcus,
The applications that are giving me trouble are java apps.

Bella,

most probably db2pd may shed some light, Thanks.

DBFinder

Internet Services

Web Development

Digital Marketing

Consumer Tech