Simulating the EXPLAIN of an embedded SQL statement



IBM's flagship relational database management system

Simulating the EXPLAIN of an embedded SQL statement

Postby nikesh_rai » Mon Oct 28, 2013 11:09 pm

Hi Guys,

I got a question on net asked by an interviewer that how to simulate the EXPLAIN of an embedded SQL statements in SPUFI/QMF.
Everywhere I got the same answer with same query without any explanation.. :) (seems like copy-paste nothing else)

Use a question mark in place of a host variable (or an unknown value), e.g.
SELECT EMP_NAME
FROM EMP
WHERE EMP_SALARY >?


Can anyone please suggest me on this or explain me how a question mark can simulate EXPLAIN and before that if this answer is correct or not. Actually I was checking Mullin's developers guide.. but I didn't get anything about EXPLAIN on embedded SQL. So please suggest.. :)
Thanks
Nikesh Rai
nikesh_rai
 
Posts: 192
Joined: Tue Oct 18, 2011 1:27 am
Has thanked: 17 times
Been thanked: 0 time

Re: Simulating the EXPLAIN of an embedded SQL statement

 

Re: Simulating the EXPLAIN of an embedded SQL statement

Postby nikesh_rai » Thu Nov 21, 2013 10:19 pm

yet to get replies.. :(
Thanks
Nikesh Rai
nikesh_rai
 
Posts: 192
Joined: Tue Oct 18, 2011 1:27 am
Has thanked: 17 times
Been thanked: 0 time

Re: Simulating the EXPLAIN of an embedded SQL statement

Postby dick scherrer » Fri Nov 22, 2013 2:13 am

Hello,

What happens when you try this?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Simulating the EXPLAIN of an embedded SQL statement

Postby nikesh_rai » Fri Nov 22, 2013 9:31 am

Currently I don't have access to mainframe.. so couldn't check it..
Thanks
Nikesh Rai
nikesh_rai
 
Posts: 192
Joined: Tue Oct 18, 2011 1:27 am
Has thanked: 17 times
Been thanked: 0 time

Re: Simulating the EXPLAIN of an embedded SQL statement

Postby nikesh_rai » Mon Sep 08, 2014 5:53 pm

Hi,

If anyone knows about it, please help me.
Thanks
Nikesh Rai
nikesh_rai
 
Posts: 192
Joined: Tue Oct 18, 2011 1:27 am
Has thanked: 17 times
Been thanked: 0 time

Re: Simulating the EXPLAIN of an embedded SQL statement

Postby alexm » Thu Nov 27, 2014 12:54 am

Hi nikesh_rai,

you cannot simulate an EXPLAIN of an SQL statement, nor a question mark somewhere in an SQL statement simulates an EXPLAIN.

Provided you have access to a set of Explain Tables, you can execute an EXPLAIN statement for an explainable SQL statement.

E.g., to explain "SELECT IBMREQD FROM SYSIBM.SYSDUMMYE WHERE IBMREQD > ?" you would use something similar to
EXPLAIN PLAN SET QUERYNO=1234 FOR
SELECT IBMREQD                   
FROM   SYSIBM.SYSDUMMYE           
WHERE  IBMREQD > ?               

You can use this in Spufi, QMF, DSNTEP2, in your application program, wherever.
The output of that EXPLAIN is stored in the Explain Tables, from where you can query it, see Interpreting data access by using EXPLAIN

NB-1: In your context, the question mark is a parameter marker. Parameter markers are replaced by the values of host variables when the statement is executed. You may want to look at the Application Programming and SQL Guide for some related information.
NB-2: all links address DB2 10 for z/OS manuals.
User avatar
alexm
 
Posts: 33
Joined: Wed Oct 13, 2010 6:40 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Simulating the EXPLAIN of an embedded SQL statement

Postby nikesh_rai » Thu Nov 27, 2014 8:44 pm

Thanks a lot Alexm.. :)
Thanks
Nikesh Rai
nikesh_rai
 
Posts: 192
Joined: Tue Oct 18, 2011 1:27 am
Has thanked: 17 times
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post