View previous topic :: View next topic
|
Author |
Message |
dejunzhu
Active User
Joined: 08 May 2008 Posts: 390 Location: China
|
|
|
|
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 |
|
|
dejunzhu
Active User
Joined: 08 May 2008 Posts: 390 Location: China
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008 Posts: 390 Location: China
|
|
|
|
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 |
|
|
wanderer
Active User
Joined: 05 Feb 2007 Posts: 199 Location: Sri Lanka
|
|
|
|
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 |
|
|
dejunzhu
Active User
Joined: 08 May 2008 Posts: 390 Location: China
|
|
|
|
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 |
|
|
|