low query performance in db2 zos environment



IBM's flagship relational database management system

low query performance in db2 zos environment

Postby CS1496897 » Tue Dec 11, 2012 4:56 pm

The application team is running a query which is taking long time--

SELECT * FROM TWEB WHERE ADD_DT <( DATE ( CURRENT DATE )
- 13 MONTHS )

i did explain on the query and it shows the cost is very high .also it is going in tablespace scan.
the table staructure is as follows--

CREATE TABLE DBV008.TWEB
(QUOTE_ASSOC_NUM INTEGER NOT NULL,
ADD_DT DATE NOT NULL,
CONSTRAINT QUOTE_ASSOC_NUM
PRIMARY KEY (QUOTE_ASSOC_NUM))
IN CP0D003P.CP0S0087
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE
APPEND NO ;

there are around 15746732 rows in table.

there is a unique clustering index defined on QUOTE_ASSOC_NUM
while there is no index defined on ADD_DT.

the distinct number of rows in QUOTE_ASSOC_NUM and ADD_DT are 15746732 and 384 respectively.so creating an index on ADD_DT would be of no help.
i tried creating index on ADD_DT same on test but still it goes in tablepsace scan.
could someone let me know how to tweak the query??
CS1496897
 
Posts: 5
Joined: Tue Dec 11, 2012 4:52 pm
Has thanked: 0 time
Been thanked: 0 time

Re: low query performance in db2 zos environment

Postby GuyC » Tue Dec 11, 2012 8:22 pm

create an index on (ADD_DT , QUOTE_ASSOC_NUM ) so it will be a Index-only query.
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: low query performance in db2 zos environment

Postby CS1496897 » Tue Dec 11, 2012 9:11 pm

i did create an index on ADD_DT but it didnt helped..it was still going on TS Scan
CS1496897
 
Posts: 5
Joined: Tue Dec 11, 2012 4:52 pm
Has thanked: 0 time
Been thanked: 0 time

Re: low query performance in db2 zos environment

Postby GuyC » Tue Dec 11, 2012 9:18 pm

- reread my post,
- think about what was different between your index and the one I suggested
- think what would be the impact on the accesspath
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: low query performance in db2 zos environment

Postby CS1496897 » Wed Dec 12, 2012 4:24 pm

i did created an index (ADD_DT , QUOTE_ASSOC_NUM ) but it is still not working..the procms and prosu still very high
CS1496897
 
Posts: 5
Joined: Tue Dec 11, 2012 4:52 pm
Has thanked: 0 time
Been thanked: 0 time

Re: low query performance in db2 zos environment

Postby GuyC » Wed Dec 12, 2012 4:41 pm

show me an explain: Is it Index-only ?
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: low query performance in db2 zos environment

Postby CS1496897 » Mon Dec 17, 2012 8:32 pm

it is index only but the procsu and procms times are high

procsu---5888
procms-318789
CS1496897
 
Posts: 5
Joined: Tue Dec 11, 2012 4:52 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post