IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to explain an embedded dynamic SQL?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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: 6966
Location: porcelain throne

PostPosted: Fri Jul 17, 2009 12:19 pm
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
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Using Dynamic file handler in the Fil... COBOL Programming 2
No new posts JCL Dynamic System Symbols JCL & VSAM 3
No new posts DB2 Statistics - Using EXPLAIN and qu... DB2 1
No new posts Synctool-dynamic split job for varyin... JCL & VSAM 7
No new posts Dynamic file allocation using JCL JCL & VSAM 8
Search our Forums:

Back to Top