 | |
#1
 
| |||
| |||
|
EXPLAIN SQL against DGTTs -
10-03-2007
, 12:27 PM
#2
| |||
| |||
|
|
Friends: Anyone know how I can EXPLAIN a dynamic SQL statement that SELECTs from DGTT's in a stored procedure? I don't want to create permanent versions of the DGTTs and run an explain against them, as I want to see what the optimizer's *really doing*. I think you need to use EXPLAIN ALL and actually execute the procedure. |
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
#3
| |||
| |||
|
|
jefftyzzer wrote: Friends: Anyone know how I can EXPLAIN a dynamic SQL statement that SELECTs from DGTT's in a stored procedure? I don't want to create permanent versions of the DGTTs and run an explain against them, as I want to see what the optimizer's *really doing*. I think you need to use EXPLAIN ALL and actually execute the procedure. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab |
restarting) but it didn't seem to do the trick, as the only SQL I saw
in EXPLAIN_INSTANCE after running db2exfmt (after kicking-off the SP)
was earlier-run (and static) SQL. However, as the SQL was only dynamic
because of the use of DGTT's, I was able to get what I needed by
declaring the DGTT and explaining the query against it in CLP.
On the issue of DGTTs requiring that any SQL run against them be
dynamic, what's the story on that? Unless I misunderstand, the SQL/PL
book by Janmohammed, et al. says that, on LUW, SQL that SELECTs from
DGTTs must be dynamic due to "object dependencies [being] resolved at
procedure build time," while I've also read that all that's actually
needed is that the SQL be located within a separate BEGIN...END block
from the one the DGTT is declared in. What say you?
As always, thanks.
--Jeff Tyzzer
#4
| |||
| |||
|
|
On Oct 3, 11:01 am, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote: jefftyzzer wrote: Friends: Anyone know how I can EXPLAIN a dynamic SQL statement that SELECTs from DGTT's in a stored procedure? I don't want to create permanent versions of the DGTTs and run an explain against them, as I want to see what the optimizer's *really doing*. I think you need to use EXPLAIN ALL and actually execute the procedure. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab Thanks, Serge. I had tried that earlier, (complete with stopping and restarting) but it didn't seem to do the trick, as the only SQL I saw in EXPLAIN_INSTANCE after running db2exfmt (after kicking-off the SP) was earlier-run (and static) SQL. However, as the SQL was only dynamic because of the use of DGTT's, I was able to get what I needed by declaring the DGTT and explaining the query against it in CLP. Did you set the routine options? And drop/create the proecdure? |
Quote:
|
On the issue of DGTTs requiring that any SQL run against them be dynamic, what's the story on that? Unless I misunderstand, the SQL/PL book by Janmohammed, et al. says that, on LUW, SQL that SELECTs from DGTTs must be dynamic due to "object dependencies [being] resolved at procedure build time," while I've also read that all that's actually needed is that the SQL be located within a separate BEGIN...END block from the one the DGTT is declared in. What say you? Any static SQL which refers to the SESSION schema immediately gets |
it gets executed for the first time.
That is DB2 is happy if the statement is syntactically correct.
Now in SQL Procedures that is not good enough because DB2 needs to
figure out what's a column and what's a variable. Thus when creating the
procedure the DGTT must either exist or it must be declared within the
stored procedure itself.
What I recommend is that DGTT are collectively defined in an "init"
procedure.
This way, before you create a procedure using such beasts you simply
CALL INIT() and everything is set up properly the way it will be when
the procedure actually runs.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
#5
| |||
| |||
|
|
Did you set the routine options? And drop/create the proecdure? Cheers Serge |
the procedure to switch explain behaviour for "static" sql with DGGT?
It's very inconvenient.
I would like to do it with rebind statement, for example...
Sincerely,
Mark Barinstein.
#6
| |||
| |||
|
|
Did you set the routine options? And drop/create the proecdure? Cheers Serge I always wanted to know: are there plans in future not to drop/create the procedure to switch explain behaviour for "static" sql with DGGT? It's very inconvenient. I would like to do it with rebind statement, for example... Reasonable request... no plans that I'm aware of. |
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.