Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 performance issue

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sandra

New User


Joined: 06 Apr 2005
Posts: 27

PostPosted: Wed Jan 30, 2008 6:09 am    Post subject: DB2 performance issue
Reply with quote

There is table which contains 200 rows where as the (RR/Count = Reads pr. Row) is 20000000. This is causing a system performance issue?
Do any one have any suggestion how this frequencey can be lowered?
Back to top
View user's profile Send private message

stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed Jan 30, 2008 7:47 am    Post subject:
Reply with quote

Can you explain the problem more? You are saying that there are only 200 rows, but the reads/row = 20,000,000? IF that is the case it sounds like the process used for reading is horribly inefficient...
Back to top
View user's profile Send private message
jaishus

New User


Joined: 20 Jul 2007
Posts: 9
Location: Bangalore

PostPosted: Wed Jan 30, 2008 10:03 am    Post subject:
Reply with quote

Hi Sandra,

This is my understanding.
You have a table to read which has 200 rows. And, you will be reading each of these rows 20,000,000 times. This will slow down the execution.

This is my solution.
We need to reduce the number of database access.
You may read the 200 rows only once and put them into an internal table.
Read each row 20,000,000 times from the internal table.

-JaiShu
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jan 30, 2008 5:45 pm    Post subject:
Reply with quote

you might try sorting your input/TRIGGER file, open and ordered cursor then use 2 file match logic. That way you only fetch each row once.
Back to top
View user's profile Send private message
sandra

New User


Joined: 06 Apr 2005
Posts: 27

PostPosted: Thu Jan 31, 2008 6:58 am    Post subject: Reply to: DB2 performance issue
Reply with quote

Thank u all for your reply. Please see the query below.

SELECT
T2.ERROR_CODE, T3.FILEREF, T3.TRANS_NO, T4.SEGMENT_NO, T4.SEGMENT_ID, T4.SEGMENT_VALUE, T2.NO_OF_RETRIES, T3.MARK
INTO
:ERR_CODE, :FILEREF, :TRANS_NO, :SEG_NO, :SEG_ID, :SEG_VALUE, :NO_OF_RETRIES, :MARK
FROM
EDI.FILESTATUSTEXT T1
INNER JOIN MAL.PARK_ERR_INFO T2 ON
CHAR(T2.ERROR_CODE) = SUBSTR(T1.STATUSKEY,9,4)
INNER JOIN MAL.PARK_MAIN_INFO T3 ON T2.FILEREF = T3.FILEREF AND T2.TRANS_NO_MAIN = T3.TRANS_NO
INNER JOIN MAL.PARK_SEG_INFO T4 ON T2.FILEREF = T4.FILEREF AND T2.TRANS_NO_MAIN = T4.TRANS_NO_MAIN AND T2.SEGMENT_NO = T4.SEGMENT_NO
WHERE T1.LOADABLE = 'Y' AND T2.NO_OF_RETRIES < T1.MAX_TRIES AND T3.MARK = ' '


***************
I am not sure if something like SUBSTR(T1.STATUSKEY, 9, 4) is indexable...
I think that the clause mentioned below is responsible for low system
performance.
INNER JOIN MAL.PARK_ERR_INFO T2 ON
CHAR(T2.ERROR_CODE) = SUBSTR(T1.STATUSKEY,9,4)

I feel that the possible solution to this problem is to - Split the query into two parts.
1. To get the loadable errors from EDI.FILESTATUSTEXT
2. Search for the each error code in PARK_ERR_INFO.

Could someone guide me on this please.
Back to top
View user's profile Send private message
bbessa

New User


Joined: 03 Aug 2006
Posts: 13
Location: Brazil

PostPosted: Fri Feb 01, 2008 2:04 am    Post subject: Re: Reply to: DB2 performance issue
Reply with quote

sandra wrote:

CHAR(T2.ERROR_CODE) = SUBSTR(T1.STATUSKEY,9,4)

I am not sure if something like SUBSTR(T1.STATUSKEY, 9, 4) is indexable...
I think that the clause mentioned below is responsible for low system
performance.
INNER JOIN MAL.PARK_ERR_INFO T2 ON
CHAR(T2.ERROR_CODE) = SUBSTR(T1.STATUSKEY,9,4)



CHAR() and SUBSTR() are stage 2 predicates thus they're not indexable. If you need to use SUBSTR to get meaningful data, then it's not normalized. And in this particular case, normalization will do you good. You can try to breakedown the STATUSKEY field into distinct columns and create a index for the error code part. Any data conversion function is a stage 2 predicate, don't use them if you need index access.

Also, you don't need to guess where's the problem in your query. Re-write your SQL from the ground up, putting one predicate at a time and see what's going on. That way you can pinpoint the issues.

Finally, try Visual Explain for DB2. It's useful and free.

My regards,

Bernardo
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts BC, BCR, BRC, BRCL performance steve-myers PL/I & Assembler 0 Fri Dec 23, 2016 7:44 am
No new posts Execessive parameter issue Sumeendar JCL & VSAM 5 Mon Dec 19, 2016 4:35 pm
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts DFHRESPONSE returns issue divated CICS 3 Wed Nov 02, 2016 6:32 pm
No new posts What are the way we can improve CPU p... Gunapala CN DB2 10 Mon Oct 24, 2016 2:16 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us