Excessive sorting...running out of ideas...

[Bruce]

All -

DB2 V9.1.3 on AIX 6.1

I am experiencing excessive sorts and connections on the main database
(DB2 has been up 84 days)...'ow_prod'...I've tried to find the
offending SQl...etc but am just having very little luck finding the
cause.

Here's the output from a quick 'every 5 second' look at sorts...And
the same for 'application connects' down below.

We have an app out-of-control I believe...We ARE down from the 8,000
new connections an hour (I can't explain why this is) but total
current connections are stable at 383. Sorts are really high still.

I'm at a loss...I ran a quick peek into sorts withi snapshot...high-
sort count for this DB is 615,538 and it has essentially remained
constant +5 every few minutes...yet my counter for 'sorts' below is
'off the chart'...

db2 "SELECT NUM_EXECUTIONS, STMT_SORTS , SUBSTR(STMT_TEXT, 1, 60) AS
STMT_TEXT FROM SYSIBMADM.SNAPDYN_SQL where stmt_sorts > 0 ORDER BY
STMT_SORTS desc" | more

NUM_EXECUTIONS STMT_SORTS STMT_TEXT
-------------------- --------------------
------------------------------------------------------------
615538 615538 SELECT * FROM
PRODDTA.F4943 WHERE ( SPSHPN = ? ) ORDER
254472 254472 SELECT * FROM
PRODDTA.F00165 WHERE ((GDOBNM = ?
198168 198168 SELECT CFAN8, CFCS32, CFITM,
CFIT82, CFEFTJ, CFEXDJ, CFMNQ,
196253 79824 UPDATE PRODDTA.F4211
56743 56743 SELECT * FROM
PRODDTA.F4105 WHERE ( COCSIN = ? AND COITM


while : ; do db2 get snapshot for db on ow_prod | grep "Total sorts" ;
date; sleep 5; done
Total sorts = 163211935
Mon Feb 6 12:44:01 CST 2012
Total sorts = 163212101
Mon Feb 6 12:44:06 CST 2012
Total sorts = 163212918
Mon Feb 6 12:44:11 CST 2012
Total sorts = 163213652
Mon Feb 6 12:44:16 CST 2012
Total sorts = 163214164
Mon Feb 6 12:44:21 CST 2012
Total sorts = 163214783
Mon Feb 6 12:44:26 CST 2012
Total sorts = 163215532
Mon Feb 6 12:44:31 CST 2012
Total sorts = 163216418
Mon Feb 6 12:44:36 CST 2012
Total sorts = 163216880
Mon Feb 6 12:44:42 CST 2012
Total sorts = 163217281
Mon Feb 6 12:44:47 CST 2012
Total sorts = 163217742
Mon Feb 6 12:44:52 CST 2012
Total sorts = 163218250
Mon Feb 6 12:44:57 CST 2012
Total sorts = 163218676
Mon Feb 6 12:45:02 CST 2012
Total sorts = 163219139
Mon Feb 6 12:45:07 CST 2012
Total sorts = 163219724
Mon Feb 6 12:45:12 CST 2012
Total sorts = 163220325
Mon Feb 6 12:45:17 CST 2012
Total sorts = 163220797
Mon Feb 6 12:45:22 CST 2012
Total sorts = 163221287
Mon Feb 6 12:45:27 CST 2012
Total sorts = 163221682
Mon Feb 6 12:45:33 CST 2012

-----

And the same thoughts with 'Application Connects'...every 5 seconds

while : ; do db2 get snapshot for db on ow_prod | grep "Application
connects" ; date ; sleep 5; done
Application connects = 11358151
Mon Feb 6 12:48:08 CST 2012
Application connects = 11358170
Mon Feb 6 12:48:13 CST 2012
Application connects = 11358213
Mon Feb 6 12:48:18 CST 2012
Application connects = 11358236
Mon Feb 6 12:48:23 CST 2012
Application connects = 11358262
Mon Feb 6 12:48:28 CST 2012
Application connects = 11358270
Mon Feb 6 12:48:34 CST 2012
Application connects = 11358278
Mon Feb 6 12:48:39 CST 2012
Application connects = 11358296
Mon Feb 6 12:48:44 CST 2012
Application connects = 11358307
Mon Feb 6 12:48:49 CST 2012
Application connects = 11358321
Mon Feb 6 12:48:54 CST 2012
Application connects = 11358326
Mon Feb 6 12:48:59 CST 2012
Application connects = 11358352
Mon Feb 6 12:49:04 CST 2012
Application connects = 11358384
Mon Feb 6 12:49:09 CST 2012
Application connects = 11358406
Mon Feb 6 12:49:14 CST 2012
Application connects = 11358415
Mon Feb 6 12:49:19 CST 2012
Application connects = 11358422
Mon Feb 6 12:49:25 CST 2012
Application connects = 11358438
Mon Feb 6 12:49:30 CST 2012