We have the following stored procedure that is performing poorly on one of our two sites. We have two sites for BCP purposes and this database is not replicated over the two sites but collect their own data independently. However the same schema, tables, indexes etc etc and maintenance are performed daily on the two databases, runstats, reorgs but still this problem persists on only one of the two sites. The two servers at the same OS level and patch (AIX 5.2) and level of DB2 8.1 FP2.

Here is the stored proc

-- step2 - direct web retrievals
--
insert into web_volume_lbx
(archive_site_id, volume_date, web_volume, calculation_datetime, client_id, line_of_service_id,
adjustment_flag, adjustment_description, user_id, customer_id, web_type, lockbox_number)

( select a.archive_site_id as archive_site_id,
date(datestr) as volume_date,
SUM(CAST(d.delivered_rows_count as integer)) as web_volume,
CURRENT TIMESTAMP as calculation_datetime,
a.client_id as client_id,
'WLBX' as line_of_service_id,
'N' as adjustment_flag,
' ' as adjustment_description,
a.user_id as user_id,
case
when a.customer_id is null
then char(' ')
else
cast(a.customer_id as char(4))
end
as customer_id,
char('WEB') as web_type,
cast(dir.field_value as varchar(255)) as lockbox_number

from avc_transaction a, delivery d, retrieval r, item i, document_index_retrieval dir

where (a.avc_trace_id = d.avc_trace_id
and a.avc_trace_id = r.avc_trace_id
and a.avc_trace_id = i.avc_trace_id
and a.avc_trace_id = dir.avc_trace_id
and d.item_id = r.item_id
and r.item_id = i.item_id
and i.item_id = dir.item_id
and cast(dir.field_name as varchar(255)) = 'Lockbox Number'
and cast(a.line_of_service_id as char(5)) = 'CHQ'
and cast(a.dla_action_id as varchar(30)) = 'ARI_GET_DOC'
and cast(r.retrieval_status as char(10)) = 'SUCCESS'
and cast(d.delivery_status as char(10)) = 'SUCCESS'
and cast(i.document_status as CHAR(10)) = 'disk'
and (cast(r.source_of_data as VARCHAR(255)) = 'CHQ'
or cast(r.source_of_data as VARCHAR(255)) = 'ARIDB')
and date(cast(r.retrieval_start_time as timestamp)) = date(datestr))

group by (client_id, line_of_service_id, archive_site_id, user_id, customer_id, dir.field_value));


COMMIT;

When I do a db2 list applications show detail this is the output:

$ db2 list applications show detail (with the third entry being the troublesome one)



CONNECT Auth Id Application Name Appl. Application Id Seq# Number of Coordinating DB Coordinator Status Status Change Time DB Name DB Path
Handle Agents partition number pid/thread
------------------------------ -------------------- ---------- ------------------------------ ---- ---------- ---------------- --------------- ------------------------------ -------------------------- -------- --------------------
AVCPA1 db2bp 76 *LOCAL.avcpa1.0D9BB5151533 0001 1 0 198920 Connect Completed Not Collected AVC /webdb1/hacmpapps/archive/proda/DB2/avcpa1/avc/avcpa1/NODE0000/SQL00001/
DSTEVEN db2bp 147 *LOCAL.avcpa1.0D98B5141519 0007 1 0 214584 UOW Waiting Not Collected AVC /webdb1/hacmpapps/archive/proda/DB2/avcpa1/avc/avcpa1/NODE0000/SQL00001/
AVCBILLA db2bp 16 GAF9460C.FF3F.039015100857 0002 1 0 249852 UOW Executing Not Collected AVC /webdb1/hacmpapps/archive/proda/DB2/avcpa1/avc/avcpa1/NODE0000/SQL00001/

ps -ef |grep on the server shows the following

symavc 119502 269826 0 05:08:52 - 0:00 /bin/sh /webdb1/hacmpapps/usr/tivoli/workload/maestro/jobmanrc BILLING_HOME=/archive/proda/symcor_custom/AVC/billing;$BILLING_HOME/bin/avc_db2_cli_sproc_invoker.sh 'InsertWebVolumeLBX(?,?,?)' ${BILLING_HOME}/reports/WLBX_InsertWebVolumeLBX.txt WLBX BILLING-EXTRACT avcbilla

symavc 166984 119502 0 05:08:52 - 0:00 /bin/ksh /archive/proda/symcor_custom/AVC/billing/bin/avc_db2_cli_sproc_invoker.sh InsertWebVolumeLBX(?,?,?) /archive/proda/symcor_custom/AVC/billing/reports/WLBX_InsertWebVolumeLBX.txt WLBX BILLING-EXTRACT avcbilla

It is now 10:23 am, it is still running, can anyone give me some advice on what I can do to make this perform better or do some other diagnosing of the problem?

thanks in advance