Statistics on temporary tables?

Is it possible to have statistics on temporary tables? I frequently
put 10000 or more rows in a temporary table and would like to know it
the queries become faster with statistics on those tables.

w.l.fisc...@googlemail.com wrote:>
Absolutely.
If you want to programatically collect statistics with a procedure
declaring a temp use the ADMIN_CMD() procedure
>
Cheers
Serge
>
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Ok, that works. Now I want the explain with detailed information:
Which subsection works on what part of the SQL? This is provided by
"dynexplain" or "db2expln". But: I can't run statistics and then make
the explain, because for "db2expln" in the -setup option no runstats
are supported.
So I have NO chance to see the "optimzed" distribution of the SQL into
subsections when using runstats on DGTTs.
This is very helpful for further analysis: Which part of the running
SQL causes this high CPU/MEM-usage in subsection 6 (e.g.). This
information is given by snapshot for application for a running SQL.
But with runstats NO chance to see the part of SQL which is processed
in subsection 6...
>
On Sep 13, 8:31*pm, Serge Rielau <srie...@ca.ibm.comwrote:
>>>>>
>

Hi Jeff,
>
the problem is, that with db2exfmt I don't see the the subsections
involved. This is only available with db2expln.
And to do this, the runstats have to be put in the -setup section to
run, not to be explained (which is quite trivial).
But "runstats" is not supported in the -setup section... (but "declare
global temporary table..." is!)
>
Why I want to see the subsections:
When SQL runs, I see the CPU/RAM usage of the agents in all
subsections.
Sometimes one subsection uses very much CPU/RAM.
This I want to analyze and try to reduce this. Therefore I need to
know which part of the (complex) SQL is processed in this special
subsection... to change it and see what happens...

Hi Jeff,
>
the problem is, that with db2exfmt I don't see the the subsections
involved. This is only available with db2expln.
And to do this, the runstats have to be put in the -setup section to
run, not to be explained (which is quite trivial).
But "runstats" is not supported in the -setup section... (but "declare
global temporary table..." is!)

Hi Jeff, Ian,
>
we have an AIX box using DB2 v9.1 FP4 with DPF - thats our
environment.
>
I tried 3 different ways to run runstats on a DGTT:
>
1) "simple" runstats command
2) CALL ADMIN_CMD('... - as Ian mentioned that this Serge mentioned
3) we have also a stored procedure using an UDF with external C-
Programm for running runstats via API
>
All 3 ways fail with the same error.
I had PMR 78890.077.724 about this problem which lead to DCR
(DesignChangeRequest) MR0806087129.
>
No more informations about since then...
I posted this with the hope, that Serge or another IBMian could bring
a bit more activity to our problem.
Thanks.

Hi Jeff, Ian,
>
we have an AIX box using DB2 v9.1 FP4 with DPF - thats our
environment.
>
I tried 3 different ways to run runstats on a DGTT:
>
1) "simple" runstats command
2) CALL ADMIN_CMD('... - as Ian mentioned that this Serge mentioned
3) we have also a stored procedure using an UDF with external C-
Programm for running runstats via API
>
All 3 ways fail with the same error.
I had PMR 78890.077.724 about this problem which lead to DCR
(DesignChangeRequest) MR0806087129.
>
No more informations about since then...
I posted this with the hope, that Serge or another IBMian could bring
a bit more activity to our problem.
Thanks.