MVSFORUMS.com

[rsantosh]

Hi,

I am running same query through batch SPUFI and SP but they use different access path when I EXPLAIN. The only difference is SPUFI has host variable values while SP has host variables.

e.g. EMP table has say 2 indexes X1 X2

in SPUFI

[kolusu]

rsantosh,

You haven't shown the entire output from the EXPLAIN. One of the reason is that SPUFI case is using One-fetch index access which requires retrieving only one row. That is because you specifically provided the info that would result in 1 row.

When you are using host variables, it might be using index-only access.

I would start the analysis using this link.

Interpreting data access by using EXPLAIN and then Questions for investigating data access
_________________
Kolusu
www.linkedin.com/in/kolusu

[rsantosh]

Hi Kolusu,

Thank you. I will go through the link.

To provide further detail. Unique Index X1 is on column ID while Duplicate Index X2 on NAME and ID(please ignore how can different name have same ID, what I provided is just to replicate the actual query which has many columns in index).

Apologies if this is not very useful.Unfortunately I can not share the EXPLAIN as it contains the customer query. What I provided was the identical query.

What makes SPUFI go for X1 and not for X2 like SP.

[haatvedt]

Santosh,

the access path for SPUFI is accessing the current DB2 stats to determine the access path

the access path for the SP is using the DB2 stats at the time the BIND was done on the SP

most likely the DB2 stats have changed between the time the SP BIND was done and when the SPUFI was run.
_________________
Chuck Haatvedt

email --> clastnameatcharterdotnet

(replace lastname, at, dot with appropriate
characters)