By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,611 Members | 2,296 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,611 IT Pros & Developers. It's quick & easy.

EXPLAIN SQL against DGTTs

P: n/a
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*.
Thanks,

--Jeff

Oct 3 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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
Oct 3 '07 #2

P: n/a
On Oct 3, 11:01 am, Serge Rielau <srie...@ca.ibm.comwrote:
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.

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

Oct 3 '07 #3

P: n/a
jefftyzzer wrote:
On Oct 3, 11:01 am, Serge Rielau <srie...@ca.ibm.comwrote:
>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?
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
placed into "validate run". That is the statement will be compiled when
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
Oct 3 '07 #4

P: n/a
>
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...

Sincerely,
Mark Barinstein.

Oct 4 '07 #5

P: n/a
4.****@mail.ru wrote:
>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.
There are plans to make drop/create less invasive though.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Oct 5 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.