Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2005
    Posts
    21

    Angry Unanswered: DB2 Performance Issue when same sql is run one after another

    I have a source table containing about 3,800,00 records or so. In DB2 warehouse manager, I have 6 SQL steps in my ETL using this table as a source. Each sql is the same except for the dates. SQL1 is for day 1 to 5 and SQL2 is for day 6 to 10 etc.. Each sql is set to run when the previous one completes.

    The first 2 sqls process about 38,000 rows per minute while the third sql is only 1,000 per minute. The forth and fifth sql regains performance but the sixth one has a performance drop just like the third.

    Any idea as to why there is a significant performance drop? It's not the sql because all is the same except for the date. The number of records each process is around the same. Each sql inserts in a different table and runs fine when tested separately.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    What version of DB2 are you on? Looks like DB2 is writing dirty pages from the bufferpool to the disks...

    How big are the bufferpools ?

    Search for DB2_USE_ALTERNATE_PAGE_CLEANING and num_iocleaners in the manuals.

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2005
    Posts
    21
    The version of DB2 I'm using is 7.1 . I'm not sure which fixpack but I know it's not 8.

    The bufferpool being used is 131,072 pages (4kb pages). As soon as I get a chance I will try your recommendation. Thanks

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    there is no DB2_USE_ALTERNATE_PAGE_CLEANING in db2 v7.

    You can get the version and fixpack level from "db2level" command executing in DB2 Command Prompt.
    You can get edition name from "db2licm -l" command from DB2 Command Prompt.

    Can you post the SQLs, table definitions and index definitions. What is data distribution - is there the same amount of data for each day? Have you applied statistics "with distribution" option like (runstats on table table_name with distribution and detailed indexes all)? Please also post SQL explains for fast running SQL and for slow running SQL.

    Hope this helps,
    Grofaty

  5. #5
    Join Date
    Feb 2005
    Posts
    21

    Thumbs up

    Thanks. I'll get that info for you as soon as our database is up. The system admin is doing some maintenance on our unix box.

    One thing I thought I should mention; before the database was shutdown I ran the SQLs once more. All had poor performance issues except for the last one (day 26 to 31). Each SQL processes around the same amount of data. I really need to figure out what is causing these performance issues.
    Last edited by rhayabusa; 10-09-07 at 11:40.

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    you are using v7 witch is out of support from IBM, so PMR is not an option, you are on your own.

    But to get any help you need provide more info. Explains, table definitions, index definitions.

    Hope this helps,
    Grofaty

  7. #7
    Join Date
    Feb 2005
    Posts
    21

    Angry

    Hi,

    I have the different data requested attached as text files. I did not paste them into the post as they are lengthy. However, below is the sql I'm using. Your help is greatly appreciated.





    SELECT
    STREAM_ID,
    STREAMROW,
    EVENTBEGINDATE,
    EVENTBEGINTIME,
    EVENTTIME,
    sum(coalesce(EVENTFINALAMOUNT,0.00)),
    sum(coalesce(QUANTITY,0.00)),
    cast(replace(CALLINGNUMBER, '+', '') as char(20)),
    Case
    when gcalledprefix_id is not null and gconnectedprefix_id is not null and gcalledprefix_id != gconnectedprefix_id
    then cast(replace(CONNECTEDNUMBER, '+', '') as char(20))
    else cast(replace(CALLEDNUMBER, '+', '') as char(20))
    End,
    gcallingprefix_id,
    Case
    when gcalledprefix_id is not null and gconnectedprefix_id is not null and gcalledprefix_id != gconnectedprefix_id
    then gconnectedprefix_id
    when gcalledprefix_id is not null then gcalledprefix_id
    else gconnectedprefix_id
    End,
    EVENTCLASS_ID,
    TARIFFPLANVARIANT_ID,
    substr(accountnumber,1,20)
    TYTAN_ACT_TP,
    (select distinct calendarclass
    from tytan.post_pstn usg2
    where usg2.eventbegindate = usg.eventbegindate and
    usg2.stream_id = usg.stream_id and
    usg2.streamrow=usg.streamrow and
    entrytype = 'P' and usg2.resultmessage_id is null) Calendar,
    GUIDINGCPARTY_ID,
    GUIDINGCPACCOUNT_ID,
    GUIDINGAPNAME_ID,
    GUIDINGCITEM_ID,

    (select coalesce(sum(usg2.chargetime),0.00)
    from tytan.post_pstn usg2
    where usg2.eventbegindate = usg.eventbegindate and
    usg2.stream_id = usg.stream_id and
    usg2.streamrow = usg.streamrow and
    usg2.calendarclass = 1 and
    usg2.resultmessage_id is null and
    usg2.quantity is not null) Peak_Act_Minutes,

    (select coalesce(sum(usg2.quantity),0.00)
    from tytan.post_pstn usg2
    where usg2.eventbegindate = usg.eventbegindate and
    usg2.stream_id = usg.stream_id and
    usg2.streamrow = usg.streamrow and
    usg2.calendarclass = 1 and
    usg2.resultmessage_id is null) Peak_Bill_Minutes,


    (select coalesce(sum(usg2.chargetime),0.00)
    from tytan.post_pstn usg2
    where usg2.eventbegindate = usg.eventbegindate and
    usg2.stream_id = usg.stream_id and
    usg2.streamrow = usg.streamrow and
    usg2.calendarclass = 2 and
    usg2.resultmessage_id is null and
    usg2.quantity is not null) OPeak_Act_Minutes,

    (select coalesce(sum(usg2.quantity),0.00)
    from tytan.post_pstn usg2
    where usg2.eventbegindate = usg.eventbegindate and
    usg2.calendarclass = 2 and
    usg2.resultmessage_id is null and
    usg2.stream_id = usg.stream_id and
    usg2.streamrow = usg.streamrow) OPeak_Bill_Minutes,


    (select coalesce(sum(usg2.chargefinalpricingamount),0.00)
    from tytan.post_pstn usg2
    where usg2.eventbegindate = usg.eventbegindate and
    usg2.stream_id = usg.stream_id and
    usg2.streamrow = usg.streamrow and
    usg2.calendarclass = 1 and
    usg2.resultmessage_id is null) Peak_Charge,

    (select coalesce(sum(usg2.chargefinalpricingamount),0.00)
    from tytan.post_pstn usg2
    where usg2.eventbegindate = usg.eventbegindate and
    usg2.stream_id = usg.stream_id and
    usg2.streamrow = usg.streamrow and
    usg2.calendarclass = 2 and
    usg2.resultmessage_id is null) OPeak_Charge,

    (select coalesce(sum(usg2.chargetime),0.00)
    from tytan.post_pstn usg2
    where usg2.eventbegindate = usg.eventbegindate and
    usg2.stream_id = usg.stream_id and
    usg2.streamrow = usg.streamrow and
    usg2.segmentclass_id = 5 and
    usg2.resultmessage_id is null) Seg1_Act_Minutes,

    (select coalesce(sum(usg2.quantity),0.00)
    from tytan.post_pstn usg2
    where usg2.eventbegindate = usg.eventbegindate and
    usg2.stream_id = usg.stream_id and
    usg2.streamrow = usg.streamrow and
    usg2.segmentclass_id = 5 and
    usg2.resultmessage_id is null) Seg1_Bill_Minutes,

    (select coalesce(sum(usg2.eventfinalamount),0.00)
    from tytan.post_pstn usg2
    where usg2.eventbegindate = usg.eventbegindate and
    usg2.stream_id = usg.stream_id and
    usg2.streamrow = usg.streamrow and
    usg2.segmentclass_id = 5 and
    usg2.resultmessage_id is null) Seg1_Charge,

    (select coalesce(sum(usg2.chargetime),0.00)
    from tytan.post_pstn usg2
    where usg2.eventbegindate = usg.eventbegindate and
    usg2.stream_id = usg.stream_id and
    usg2.streamrow = usg.streamrow and
    usg2.segmentclass_id = 4 and
    usg2.resultmessage_id is null) Seg2_Act_Minutes,

    (select coalesce(sum(usg2.quantity),0.00)
    from tytan.post_pstn usg2
    where usg2.eventbegindate = usg.eventbegindate and
    usg2.stream_id = usg.stream_id and
    usg2.streamrow = usg.streamrow and
    usg2.segmentclass_id = 4 and
    usg2.resultmessage_id is null) Seg2_Bill_Minutes,

    (select coalesce(sum(usg2.eventfinalamount),0.00)
    from tytan.post_pstn usg2
    where usg2.eventbegindate = usg.eventbegindate and
    usg2.stream_id = usg.stream_id and
    usg2.streamrow = usg.streamrow and
    usg2.segmentclass_id = 4 and
    usg2.resultmessage_id is null) Seg2_Charge,
    timestamp(char(usg.eventbegindate), char(usg.eventbegintime))
    FROM
    tytan.post_pstn usg
    WHERE
    datatype_id in (808005,808045,808046,808050,808018,808019)
    and Resultmessage_id is null and eventbegindate between '05/01/2007' and '05/05/2007' and
    eventclass_id not in (500109,500110,500111,500112,500113,500114,500115, 500116,500120,500107,500108,500106,500086)



    GROUP BY STREAM_ID,
    STREAMROW,
    EVENTBEGINDATE,
    EVENTBEGINTIME,
    EVENTTIME,
    cast(replace(CALLINGNUMBER, '+', '') as char(20)),
    Case
    when gcalledprefix_id is not null and gconnectedprefix_id is not null and gcalledprefix_id != gconnectedprefix_id
    then cast(replace(CONNECTEDNUMBER, '+', '') as char(20))
    else cast(replace(CALLEDNUMBER, '+', '') as char(20))
    End,
    gcallingprefix_id,
    Case
    when gcalledprefix_id is not null and gconnectedprefix_id is not null and gcalledprefix_id != gconnectedprefix_id
    then gconnectedprefix_id
    when gcalledprefix_id is not null then gcalledprefix_id
    else gconnectedprefix_id
    End,
    EVENTCLASS_ID,
    TARIFFPLANVARIANT_ID,
    substr(accountnumber,1,20),
    TYTAN_ACT_TP,
    GUIDINGCPARTY_ID,
    GUIDINGCPACCOUNT_ID,
    GUIDINGAPNAME_ID,
    GUIDINGCITEM_ID
    Attached Files Attached Files
    Last edited by rhayabusa; 10-10-07 at 19:21.

Posting Permissions

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