Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to explain an embedded dynamic SQL?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Tue Jul 14, 2009 12:47 pm    Post subject: How to explain an embedded dynamic SQL?
Reply with quote

I tried to explain an embedded dynamic SQL in PL/1 using the following statement:
Code:
EXEC SQL                                       
     PREPARE STMT                             
     FROM :STMTBUF                             
 ;                                             
                                               
   SELECT (SQLCODE);                           
       WHEN (0);                               
       OTHERWISE                               
            CALL P9000_DBERROR;               
   END;                                       
                                               
   EXEC SQL                                   
        EXPLAIN PLAN FOR  STMT                 
   ;                                           
   EXEC SQL                                   
        EXECUTE STMT                           
   ;                                           

But when I compile it with SCLM, the following error message occurred.
Code:

1DB2 SQL PRECOMPILER         MESSAGES                                         
0DSNH104I E     DSNHPARS LINE 357 COL 16  ILLEGAL SYMBOL "STMT". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: SET WITH DELETE INSERT UPDATE
 VALUES REFRESH DSNHATTR                                                     


If I change the statement to follows, compile succeed.
Code:
EXEC SQL                                               
     PREPARE STMT                                     
     FROM :STMTBUF                                     
 ;                                                     
                                                       
   SELECT (SQLCODE);                                   
       WHEN (0);                                       
       OTHERWISE                                       
            CALL P9000_DBERROR;                       
   END;                                               
                                                       
   EXEC SQL                                           
        EXPLAIN PLAN FOR  SELECT * FROM LJ84TTEST     
   ;                                                   
   EXEC SQL                                           
        EXECUTE STMT                                   
   ;                                                   

But my requirement is , that the input dynamic SQL must be input from input file, not resides in PL/1 sourcecode.

How should I resolve this problem?
Back to top
View user's profile Send private message

dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Fri Jul 17, 2009 9:45 am    Post subject:
Reply with quote

Can anybody provide me some valuable information?

I used to refer to DB2 manuals, but I cannot find any useful information.

You know, asking help from the forum is always the last way.


Thanks in advance.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Jul 17, 2009 12:19 pm    Post subject:
Reply with quote

Here is a Redbook DB2 for z/OS and OS/390 : Squeezing the Most Out of Dynamic SQL

and here is a list of redbooks that speak to different aspects, especially tuning tips.
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Fri Jul 17, 2009 6:44 pm    Post subject:
Reply with quote

hi, Dick,

I have referred the books you provided above but still not got any useful information, and then I put the question here for your help.

Can you help me with the source code I provided ?

Appreciate it.
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Wed Jul 22, 2009 12:49 am    Post subject:
Reply with quote

I haven't done a lot of embedded dynamic SQLs so probably not much help. And not sure if this is possible.

But you said compile successful for the second attempt when you gave the whole select statement in explain. Did you try to run it? Did it really do the explain?

From version 8, all dynamic SQl statements that are in Dynamic statement cache, can be explained directly. The steps are
1. First bring all statements to statement cache table
2. Then find your statement in it and with statement number and other plan info, explain that statement. This is lot more real time path.
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Wed Jul 22, 2009 9:47 am    Post subject:
Reply with quote

wanderer wrote:


But you said compile successful for the second attempt when you gave the whole select statement in explain. Did you try to run it? Did it really do the explain?

Positive. statistics have been written to Plan_table after execution of the PGM.

Thanks for your suggestion.
Reacently, I found another way to realize this:
1. In the input file to the PGM, write the wanted SQL statement.
2. In the source code of the PGM, concatenate "Explain all for " with the SQL statement in the input file, and the execute the explain statement.
The method has to be proved works.

But, after I execute the explain statement, I can not prepare & execute the SQL statement in the input file. (Exactly speaking, if it is a delete statement, no error occurred whenprepared & executed, but it does no effect to DB2 table, it cannot actually delete record from the specific table.)

I'm still confused with the above phenomenon.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts REXX - Dynamic file Creation d_sarlie CLIST & REXX 7 Tue Jun 27, 2017 7:30 pm
No new posts Dynamic output file creation in cobol... smileheal COBOL Programming 7 Thu Jun 15, 2017 10:53 pm
No new posts Dynamic split of files under groups sril.krishy DFSORT/ICETOOL 4 Mon Apr 17, 2017 1:09 pm
No new posts Dynamic array request/response contai... Suja.Sai CICS 2 Tue Jan 24, 2017 11:37 am
No new posts REXX DB2: Dynamic allocation of DB2.D... BHAS CLIST & REXX 3 Mon Dec 19, 2016 8:26 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us