Page 1 of 1

low query performance in db2 zos environment

PostPosted: Tue Dec 11, 2012 4:56 pm
by CS1496897
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??

Re: low query performance in db2 zos environment

PostPosted: Tue Dec 11, 2012 8:22 pm
by GuyC
create an index on (ADD_DT , QUOTE_ASSOC_NUM ) so it will be a Index-only query.

Re: low query performance in db2 zos environment

PostPosted: Tue Dec 11, 2012 9:11 pm
by CS1496897
i did create an index on ADD_DT but it didnt helped..it was still going on TS Scan

Re: low query performance in db2 zos environment

PostPosted: Tue Dec 11, 2012 9:18 pm
by GuyC
- 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

Re: low query performance in db2 zos environment

PostPosted: Wed Dec 12, 2012 4:24 pm
by CS1496897
i did created an index (ADD_DT , QUOTE_ASSOC_NUM ) but it is still not working..the procms and prosu still very high

Re: low query performance in db2 zos environment

PostPosted: Wed Dec 12, 2012 4:41 pm
by GuyC
show me an explain: Is it Index-only ?

Re: low query performance in db2 zos environment

PostPosted: Mon Dec 17, 2012 8:32 pm
by CS1496897
it is index only but the procsu and procms times are high

procsu---5888
procms-318789