MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

SPUFI vs Stored PROC Access path

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
rsantosh
Beginner


Joined: 20 Dec 2014
Posts: 38
Topics: 9

PostPosted: Thu May 28, 2020 5:20 am    Post subject: SPUFI vs Stored PROC Access path Reply with quote

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

Code:
 SELECT ID
FROM EMP
WHERE ID = 100
AND NAME = 'SANTOSH'


SPUFI selects index X1

In SP

Code:

SELECT ID
FROM EMP
WHERE ID = :HOST-ID
AND NAME = :HOST-NAME


SP selects index X2

Why is DB2 choosing different access path?

Regards,
Santosh
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Thu May 28, 2020 12:01 pm    Post subject: Reply with quote

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 - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
rsantosh
Beginner


Joined: 20 Dec 2014
Posts: 38
Topics: 9

PostPosted: Sat May 30, 2020 8:41 am    Post subject: Reply with quote

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.

Code:

SPUFI                                         SP
Index X1                                     Index X2
INDEXED ACCESS                               Indexed Access
1 OF 1 COL MATCHED                           1 OF 2 COL MATCHED
                                             Sequential prefetch will be employed during this index only access
Back to top
View user's profile Send private message
haatvedt
Beginner


Joined: 14 Nov 2003
Posts: 66
Topics: 0
Location: St Cloud, Minnesota USA

PostPosted: Sat Jun 20, 2020 1:22 pm    Post subject: Reply with quote

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)
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group