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

Execution plans and temporary tables

P: n/a
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
Apr 23 '06 #1
Share this Question
Share on Google+
12 Replies


P: n/a
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
Apr 23 '06 #2

P: n/a
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
Apr 25 '06 #3

P: n/a
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
Apr 25 '06 #4

P: n/a
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
Apr 25 '06 #5

P: n/a
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
Apr 25 '06 #6

P: n/a
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
Apr 26 '06 #7

P: n/a
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
Apr 26 '06 #8

P: n/a
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
Apr 26 '06 #9

P: n/a
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
Apr 28 '06 #10

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


They tell that our old Business Objects version doesn't run with Stinger.

DB2 is backward compatible? Well, I experienced that an ODBC procedure call
that ended with an (illegal) extra close bracket was accepted and
successfully executed with a 7.2 client, but rejected with an 8.* client.
Which is good, but not backward compatible.

Joachim
Apr 28 '06 #11

P: n/a
Joachim Pense wrote:
DB2 is backward compatible? Well, I experienced that an ODBC procedure call
that ended with an (illegal) extra close bracket was accepted and
successfully executed with a 7.2 client, but rejected with an 8.* client.
Which is good, but not backward compatible.

*lol* Well guilty as charged...
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 29 '06 #12

P: n/a
Joachim Pense wrote:
Knut Stolze wrote:
What's going to break? DB2 is backward compatible so everything should
"just" work.


They tell that our old Business Objects version doesn't run with Stinger.

DB2 is backward compatible? Well, I experienced that an ODBC procedure
call that ended with an (illegal) extra close bracket was accepted and
successfully executed with a 7.2 client, but rejected with an 8.* client.
Which is good, but not backward compatible.


True. The compatibility reaches only so far as no errors/bugs are
concerned.

Also, other features and behavior may change between versions. But the
developers try hard to keep things stable. So if you encounter something
where things start to break (and are not a user-error as in your example),
you should take it to IBM support to get the problem fixed in DB2.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Apr 29 '06 #13

This discussion thread is closed

Replies have been disabled for this discussion.