Help on index.

[h***@db.com]

Below are the Db2 version details

UAT
--------

DB21085I Instance "udbuat" uses "64" bits and DB2 code release "SQL08020" with
level identifier "03010106".
Informational tokens are "DB2 v8.1.1.72", "s040914", "U498350", and FixPak "7".
Product is installed at "/usr/opt/db2_08_01".


PROD
--------------
DB21085I Instance "udbprod" uses "64" bits and DB2 code release "SQL08020"
with level identifier "03010106".
Informational tokens are "DB2 v8.1.1.72", "s040914", "U498350", and FixPak "7".
Product is installed at "/usr/opt/db2_08_01".



Below is the SQL query that I am executing :-

"
with min_date
as (select max(st.fst_mth_pro) as date
from db2p.stddbase st
where st.process_date < current_date),
max_date
as (select max(st.process_date) as date
from db2p.stddbase st
where st.process_date < current_date),

t
as (select *
from db2.vpertrad t
where t.prcsg_dt between (select date
from min_date)
and (select date
from max_date)
and t.cancl_cd <> '1'
and t.sec_type_cd = '0'
and t.sec_modifier_cd in ('0','3'))

select * from t "



In UAT :-
---------

This query takes a minute to execute and gives me the results.

The EXPLAIN on this query shows that it uses index " XIE2TPERTRAD" .



In PROD :-
----------------

This query takes a very long time.

The EXPLAIN on this query shows that it does not uses the index " XIE2TPERTRAD" and does a entire table scan.


Question:-
--------------

Would you able to give me some directions or information about why the same query is behaving different in different environment.

The data in UAT as well as the production in same ( i.e volume of data in table VPERTRAD is comparatively same).

Same index has been created in UAT as well as PROD, but both use a different access plan.

Is there a way to force the query to use the index in production ?

Also when I change the query a bit, it uses the index,. For e.g :-



with min_date
as (select max(st.fst_mth_pro) as date
from db2p.stddbase st
where st.process_date < current_date),
max_date
as (select max(st.process_date) as date
from db2p.stddbase st
where st.process_date < current_date),

t
as (select *
from db2.vpertrad t
where t.prcsg_dt between '09-24-2007'
and (select date
from max_date)
and t.cancl_cd <> '1'
and t.sec_type_cd = '0'
and t.sec_modifier_cd in ('0','3'))

select * from t "


This query when executed in prodution uses the index " XIE2TPERTRAD".

[Knut Stolze]

Post by h***@db.com
Below are the Db2 version details
UAT
--------
DB21085I Instance "udbuat" uses "64" bits and DB2 code release "SQL08020"
with level identifier "03010106".
Informational tokens are "DB2 v8.1.1.72", "s040914", "U498350", and FixPak
"7". Product is installed at "/usr/opt/db2_08_01".
PROD
--------------
DB21085I Instance "udbprod" uses "64" bits and DB2 code release
"SQL08020" with level identifier "03010106".
Informational tokens are "DB2 v8.1.1.72", "s040914", "U498350", and FixPak
"7". Product is installed at "/usr/opt/db2_08_01".
Below is the SQL query that I am executing :-
"
with min_date
as (select max(st.fst_mth_pro) as date
from db2p.stddbase st
where st.process_date < current_date),
max_date
as (select max(st.process_date) as date
from db2p.stddbase st
where st.process_date < current_date),
t
as (select *
from db2.vpertrad t
where t.prcsg_dt between (select date
from min_date)
and (select date
from max_date)
and t.cancl_cd <> '1'
and t.sec_type_cd = '0'
and t.sec_modifier_cd in ('0','3'))
select * from t "
In UAT :-
---------
This query takes a minute to execute and gives me the results.
The EXPLAIN on this query shows that it uses index " XIE2TPERTRAD" .
In PROD :-
----------------
This query takes a very long time.
The EXPLAIN on this query shows that it does not uses the index "
XIE2TPERTRAD" and does a entire table scan.
Question:-
--------------
Would you able to give me some directions or information about why the
same query is behaving different in different environment.
The data in UAT as well as the production in same ( i.e volume of data in
table VPERTRAD is comparatively same).
Same index has been created in UAT as well as PROD, but both use a different access plan.
Is there a way to force the query to use the index in production ?
Also when I change the query a bit, it uses the index,. For e.g :-
with min_date
as (select max(st.fst_mth_pro) as date
from db2p.stddbase st
where st.process_date < current_date),
max_date
as (select max(st.process_date) as date
from db2p.stddbase st
where st.process_date < current_date),
t
as (select *
from db2.vpertrad t
where t.prcsg_dt between '09-24-2007'
and (select date
from max_date)
and t.cancl_cd <> '1'
and t.sec_type_cd = '0'
and t.sec_modifier_cd in ('0','3'))
select * from t "
This query when executed in prodution uses the index " XIE2TPERTRAD".

Do you have accurate statistics in both systems, i.e. run RUNSTATS recently?