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.

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

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

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

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.

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

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