Performace tuning on db2 query



IBM's flagship relational database management system

Performace tuning on db2 query

Postby anjireddy4u » Fri Sep 20, 2013 8:37 pm

HI,

could u someone give the idea to improve performace for below query.

WHERE A.BAT_PROMO_NBR = B.BAT_PROMO_NBR
AND A.BAT_PROMO_NBR = C.BAT_PROMO_NBR
AND A.BAT_SRC_VNDR_NO = :WS-TAG-ITEM-VENDOR
AND A.BAT_SRC_ITEM_NO = :WS-TAG-ITEM-CODE
AND A.BAT_UPC_CODE = :WS-TAG-REDEF-12
AND A.BAT_STORE IN (:WS-TAG-STORE-NUM,0)
AND A.BAT_ZONE = :WS-TAG-STORE-NUM
AND A.BAT_SRC_CODE = 2
AND ((A.BAT_PULL_IND = 'Y')
OR (A.BAT_EFF_DATE = :WS-SELL-START-DATE
AND A.BAT_PULL_IND IN ('R','S'))
AND A.BAT_CNFLT_FLAG = ' ')
AND A.BAT_CNFLT_PROMO = 0
AND B.BAT_PROMO_CLASS <> 'T'
AND C.BAT_CLU_LEVEL =
(SELECT MAX(BAT_CLU_LEVEL) FROM BATCH_TIERDTL D
WHERE D.BAT_PROMO_NBR = B.BAT_PROMO_NBR)
WITH UR
END-EXEC. we are fetching the data from 3 tables , its around 10lacs records were fething; its tooks almost 20 to 30 mints.

so someone should provide the better way to reduce cpu consumtion///

thanks,
ram
anjireddy4u
 
Posts: 5
Joined: Fri Sep 20, 2013 8:19 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Performace tuning on db2 query

 

Re: Performace tuning on db2 query

Postby dick scherrer » Fri Sep 20, 2013 8:43 pm

Hello and welcome to the forum,

The first part of the query is missing . . .

From the description of your problem it sounds like you have caused the creation of multiple cartesian products.

What does an Explain show?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Performace tuning on db2 query

Postby anjireddy4u » Fri Sep 20, 2013 8:57 pm

yes your correct ; its like multiple cartesian products.

SELECT
A.BAT_PROMO_NBR
,A.BAT_ITEM_NO
,A.BAT_SRC_VNDR_NO
,A.BAT_SRC_ITEM_NO
,A.BAT_SRC_DEAL_CODE
,A.BAT_ZONE
,A.BAT_STORE
,A.BAT_SRC_CODE
,A.BAT_END_DATE
,A.BAT_CNFLT_FLAG
,A.BAT_CNFLT_PROMO
,A.BAT_ITEM_DESC
,A.BAT_CLASS
,A.BAT_SIZE
,A.BAT_MEAS
,A.BAT_UM
,A.BAT_FIN_RETAIL
,A.BAT_BEG_RETAIL
,A.BAT_BEG_TWO_FOR
,A.BAT_CALC_SAVE_AMT
,A.BAT_FIN_TWO_FOR
,A.BAT_TAG_SEND_FLAG
,A.BAT_UPC_CODE
,A.BAT_PACK
,A.BAT_REPL_FLAG
,A.BAT_QTY_ON
,A.BAT_SAVE_ON
,A.BAT_CHAIN_ID
,B.BAT_TYPE_THRESHLD
,B.BAT_TYPE_DISCOUNT
,B.BAT_PRIMARY_PROMO
,B.BAT_SPECIAL_OFFER
,B.BAT_DESCRIPTION
,B.BAT_PROMO_TYPE
,B.BAT_PROMO_CLASS
,B.BAT_PULL_IND
,B.BAT_REDEEM_FLAG
,B.BAT_BEST_DEAL
,B.BAT_WGT_REQ
,C.BAT_THRESHLD_AMT
,C.BAT_THRESHLD_PTS
,C.BAT_DISC_AMT
,C.BAT_DISC_PERCENT
,C.BAT_DISC_POINTS
,C.BAT_DISC_RETAIL
,C.BAT_DISC_TWO_FOR
,C.BAT_FREE_FLAG
,C.BAT_BOGO_FLAG
,C.BAT_QTY_LIMIT
,C.BAT_QTY_REQ
,C.BAT_INCR
,C.BAT_EXCL
INTO
:BATITEM.BAT-PROMO-NBR
,:BAT-ITEM-NO
,:BAT-SRC-VNDR-NO
,:BAT-SRC-ITEM-NO
,:BAT-SRC-DEAL-CODE
,:BAT-ZONE
,:BAT-STORE
,:BAT-SRC-CODE
,:BATITEM.BAT-END-DATE
,:BAT-CNFLT-FLAG
,:BAT-CNFLT-PROMO
,:BAT-ITEM-DESC
,:BAT-CLASS
,:BAT-SIZE
,:BAT-MEAS
,:BAT-UM
,:BAT-FIN-RETAIL
,:BAT-BEG-RETAIL
,:BAT-BEG-TWO-FOR
,:BAT-CALC-SAVE-AMT
,:BAT-FIN-TWO-FOR
,:BAT-TAG-SEND-FLAG
,:BAT-UPC-CODE
,:BAT-PACK
,:BAT-REPL-FLAG
,:BAT-QTY-ON
,:BAT-SAVE-ON
,:BAT-CHAIN-ID
,:BAT-TYPE-THRESHLD
,:BAT-TYPE-DISCOUNT
,:BAT-PRIMARY-PROMO
,:BAT-SPECIAL-OFFER
,:BAT-DESCRIPTION
,:BAT-PROMO-TYPE
,:BAT-PROMO-CLASS
,:BATITEM.BAT-PULL-IND
,:BAT-REDEEM-FLAG
,:BAT-BEST-DEAL
,:BAT-WGT-REQ
,:BAT-THRESHLD-AMT
,:BAT-THRESHLD-PTS
,:BAT-DISC-AMT
,:BAT-DISC-PERCENT
,:BAT-DISC-POINTS
,:BAT-DISC-RETAIL
,:BAT-DISC-TWO-FOR
,:BAT-FREE-FLAG
,:BAT-BOGO-FLAG
,:BAT-QTY-LIMIT
,:BAT-QTY-REQ
,:BAT-INCR
,:BAT-EXCL
FROM BATCH_TIERITEM A,
BATCH_TIERPROM B,
BATCH_TIERDTL C

how we can optimize the query with minimal cpu perfromance
anjireddy4u
 
Posts: 5
Joined: Fri Sep 20, 2013 8:19 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Performace tuning on db2 query

Postby anjireddy4u » Fri Sep 20, 2013 9:50 pm

i ran the same query in dev region; actually it fetching 1000 records for every 1 minute.
in daily batch around we have to process 10 lacs records ,so it took more time consumption..
and also trying to reduce io operations in that pgm,
waiting for someone response to get a better suggestion
anjireddy4u
 
Posts: 5
Joined: Fri Sep 20, 2013 8:19 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Performace tuning on db2 query

Postby dick scherrer » Fri Sep 20, 2013 11:31 pm

Hello,

Unload the data into sequential files and join them using your sort product.

Not everything needs to be done by SQL alone.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Performace tuning on db2 query

Postby anjireddy4u » Sat Sep 21, 2013 2:28 am

thanks for the response..

Do we have any other solution instead of loading into sequntial files.

is there any possiblity to tune the DB2 query?

Regards,
Ram
anjireddy4u
 
Posts: 5
Joined: Fri Sep 20, 2013 8:19 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Performace tuning on db2 query

Postby Akatsukami » Sat Sep 21, 2013 2:40 am

anjireddy4u wrote:thanks for the response..

Do we have any other solution instead of loading into sequntial files.

is there any possiblity to tune the DB2 query?

No.

Why do people write hideously complex and abysmally non-performant SQL queries when an ETL could be done with a few lines of COBOL or PL/I, or a few *Sort control statements?
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1053
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: Performace tuning on db2 query

Postby anjireddy4u » Fri Sep 27, 2013 3:12 pm

its existing logic..i am agreed your process.

i have to splitcode 2 to 3 programs as per your process..

before we are fetching the records from table,based oncertain certiera , if the condition is statisfied we are updating table itself in same program..

so please let knoe your suggestion
anjireddy4u
 
Posts: 5
Joined: Fri Sep 20, 2013 8:19 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Performace tuning on db2 query

Postby dick scherrer » Fri Sep 27, 2013 9:18 pm

After matching the unloaded entriies, read the new file and update the database info with that data.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post