SEO

Is there recommended way to get the execution plan for queries involving
global temporary tables (from the UNIX command line or within a script)?

I run the queries in Perl scripts, and the only way that comes to my mind
is creating permanent tables that look like the temporary ones and run test
versions of the scripts that use the permanent tables; then I can let the
script stop wherever I want and start db2expln.

But this way looks a bit cumbersome and error-prone to me.

Joachim

Joachim Pense wrote:
> Is there recommended way to get the execution plan for queries involving
> global temporary tables (from the UNIX command line or within a script)?
>
> I run the queries in Perl scripts, and the only way that comes to my mind
> is creating permanent tables that look like the temporary ones and run test
> versions of the scripts that use the permanent tables; then I can let the
> script stop wherever I want and start db2expln.
Type db2expln -help
Check the -setup option

Cheers
Serge

PS: I have contacted the owner to inquire why the option is not in the docs.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Am Sun, 23 Apr 2006 07:40:52 -0400 schrieb Serge Rielau:

> Joachim Pense wrote:
>> Is there recommended way to get the execution plan for queries involving
>> global temporary tables (from the UNIX command line or within a script)?
>>
>> I run the queries in Perl scripts, and the only way that comes to my mind
>> is creating permanent tables that look like the temporary ones and run test
>> versions of the scripts that use the permanent tables; then I can let the
>> script stop wherever I want and start db2expln.
> Type db2expln -help
> Check the -setup option
>

I didn't find a -setup option.

Joachim

Joachim Pense wrote:

> I didn't find a -setup option.
>
> Joachim
D:\Working>db2expln -help

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool



SQL Explain describes the access plan selection for static SQL statements
in the packages stored in the DB2 Universal Database system catalogs.
Given a database name, package name, package creator, and section number,
SQL Explain will interpret and describe the information in these catalogs.

The syntax is:

.-----------.
V |
>>--db2expln----<option>--+--><

where <option> and <parameter> are taken from the list below. Each <option>
may appear only once, and they may be specified in any order.


Connection Options:
-database <db> = Connect to the database named <db>.
-d <db>

-user <name> <pw> = Connect as user <name> with password <pw>.
-u <name> <pw>

A database name must be specified.


Output Options:
-terminal = Send output to the terminal.
-t

-output <file> = Write output to the file named <file>.
-o <file>

Either terminal or file output must be specified.


Help Options:
-help = Display this help text.
-h
-?


Package Options:
-schema <pattern> = The package creator must match <pattern>.
-c <pattern>

-package <pattern> = The package name must match <pattern>.
-p <pattern>

-version <pattern> = The package version must match <pattern>. If not
specified, then the package with the version ''
(the empty string) will be explained.

-section <number> = The section number is <number>. Use 0 (zero) for
-s <number> all sections in the package.

-escape <charater> = Use <character> as the escape character when
-e <character> matching patterns.

-noupper = Do not upper case creator, package and version
-l before matching.

The creator and package information must be specified unless dynamic SQL is
being explained. If the section information is not specified, then all
sections will be displayed.

The <pattern> for creator, package, and version is in LIKE predicate form,
which allows the percent sign (%) and underscore (_) as pattern matching
characters. This allows multiple packages to be explained with one
invocation of db2expln. The escape character can be used to force the %
and _ characters to be treated literally. (See the SQL Reference for more
information on the LIKE predicate.) If multiple packages may be matched,
the section number is automatically set to 0 (all sections).


Dynamic Statement Options:
-statement <statement> = The dynamic statement <statement> will be
-q <sql> explained.

-stmtfile <file> = The dynamic statements contained in the file
-f <file> <file> will be explained. <File> must exist at
the client.

-noenv = By default, db2expln will invoke each dynamic SET
statement after it has been explained. This option
prevents the execution of these statements.


Explain Options:
-setup <file> = The SQL statements in <file> will be invoked
before any sections or statements are explained.
The SQL statements in <file> will not be
explained. Errors in the setup script are reported
but ignored.

-terminator <character> = Each SQL statement for -statement and -setup ends
-z <character> at <character>. If this option is not specified,
then each statement is assumed to be one line
long.

-graph = Reconstruct the original optimizer plan graph (as
-g presented by Visual Explain). Note that the
reconstructed graph may not exactly match the
original plan.

-opids = Show the operator ID numbers.
-i


The specific options available may vary by database server.
Use "db2expln -help -database <db>" to get the options available for
a specific server.


(2/-)

D:\Working>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08024" with
level identifier "03050106".
Informational tokens are "DB2 v8.1.11.973", "s060120", "WR21365", and FixPak
"11".
Product is installed at "D:\SQLLIB".


Jan M. Nelken

Am Tue, 25 Apr 2006 15:15:01 -0400 schrieb Jan M. Nelken:

> Joachim Pense wrote:
>
>> I didn't find a -setup option.
>>
>> Joachim
> D:\Working>db2expln -help
>
>
> Explain Options:
> -setup <file> = The SQL statements in <file> will be invoked
> before any sections or statements are explained.
> The SQL statements in <file> will not be
> explained. Errors in the setup script are reported
> but ignored.
>
> -terminator <character> = Each SQL statement for -statement and -setup ends
> -z <character> at <character>. If this option is not specified,
> then each statement is assumed to be one line
> long.
>
> -graph = Reconstruct the original optimizer plan graph (as

....

Strange. On our Solaris system it says:

$ db2expln -help

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991,
2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool



SQL Explain describes the access plan selection for static SQL statements
in the packages stored in the DB2 Universal Database system catalogs.
Given a database name, package name, package creator, and section number,
SQL Explain will interpret and describe the information in these catalogs.

The syntax is:

.-----------.
V |
>>--db2expln----<option>--+--><

....

Explain Options:
-graph = Reconstruct the original optimizer plan graph
(as
-g presented by Visual Explain). Note that the
reconstructed graph may not exactly match the
original plan.

-opids = Show the operator ID numbers.
-i

And grepping for setup yields nothing.

Joachim

Joachim Pense wrote:

> Strange. On our Solaris system it says:
>
> $ db2expln -help
>
> DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991,
> 2002
> Licensed Material - Program Property of IBM
> IBM DB2 Universal Database SQL Explain Tool
>
>
>
> SQL Explain describes the access plan selection for static SQL statements
> in the packages stored in the DB2 Universal Database system catalogs.
> Given a database name, package name, package creator, and section number,
> SQL Explain will interpret and describe the information in these catalogs.
>
> The syntax is:
>
> .-----------.
> V |
> >>--db2expln----<option>--+--><
>
> ...
>
> Explain Options:
> -graph = Reconstruct the original optimizer plan graph
> (as
> -g presented by Visual Explain). Note that the
> reconstructed graph may not exactly match the
> original plan.
>
> -opids = Show the operator ID numbers.
> -i
>
> And grepping for setup yields nothing.
>
> Joachim

What does db2level say on *your* Solaris box?

Jan M. Nelken

Jan M. Nelken wrote:

>
> What does db2level say on *your* Solaris box?
>

DB21085I Instance "xxxinst1" uses "32" bits and DB2 code release "SQL08016"
with level identifier "02070106".
Informational tokens are "DB2 v8.1.0.58", "s040914", "U800266", and FixPak
"6".
Product is installed at "/opt/IBM/db2/V8.1".

Joachim

Joachim Pense wrote:

> DB21085I Instance "xxxinst1" uses "32" bits and DB2 code release "SQL08016"
> with level identifier "02070106".
> Informational tokens are "DB2 v8.1.0.58", "s040914", "U800266", and FixPak
> "6".
> Product is installed at "/opt/IBM/db2/V8.1".
>
> Joachim

So - your DB2 is 1 version, 5 fixpacks and almost two years older. Upgrade to
latest fixpack and you will find a lot of changes.

Jan M. Nelken

Am Wed, 26 Apr 2006 11:19:15 -0400 schrieb Jan M. Nelken:

> Joachim Pense wrote:
>
>> DB21085I Instance "xxxinst1" uses "32" bits and DB2 code release "SQL08016"
>> with level identifier "02070106".
>> Informational tokens are "DB2 v8.1.0.58", "s040914", "U800266", and FixPak
>> "6".
>> Product is installed at "/opt/IBM/db2/V8.1".
>>
>> Joachim
>
> So - your DB2 is 1 version, 5 fixpacks and almost two years older. Upgrade to
> latest fixpack and you will find a lot of changes.
>

This would include Stinger, wouldn't it? We'd love going there, but some
other software we still depend on would break :-(

Joachim

Joachim Pense wrote:

> Am Wed, 26 Apr 2006 11:19:15 -0400 schrieb Jan M. Nelken:
>
>> Joachim Pense wrote:
>>
>>> DB21085I Instance "xxxinst1" uses "32" bits and DB2 code release
>>> "SQL08016" with level identifier "02070106".
>>> Informational tokens are "DB2 v8.1.0.58", "s040914", "U800266", and
>>> FixPak "6".
>>> Product is installed at "/opt/IBM/db2/V8.1".
>>>
>>> Joachim
>>
>> So - your DB2 is 1 version, 5 fixpacks and almost two years older.
>> Upgrade to latest fixpack and you will find a lot of changes.
>>
>
> This would include Stinger, wouldn't it? We'd love going there, but some
> other software we still depend on would break :-(

What's going to break? DB2 is backward compatible so everything should
"just" work.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany