Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002

    Unanswered: poor performing stored procedure

    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,
    when a.customer_id is null
    then char(' ')
    cast(a.customer_id as char(4))
    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));


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

  2. #2
    Join Date
    May 2003
    I would check a couple of things first:

    1. Do an explain on each system of the SQL statements in the SP.
    2. Check all your db and dbm configurtion parms.
    3. Do some application snaphots.

    Also, check this out:
    Last edited by Marcus_A; 01-25-06 at 13:46.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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