473,883 Members | 1,685 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Execution plans and temporary tables

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
12 3112
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
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
Joachim Pense wrote:
I didn't find a -setup option.

Joachim

D:\Working>db2e xpln -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>db2l evel
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
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>db2e xpln -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
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
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
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
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2949
by: Dan | last post by:
I am new to tuning and I am having some trouble with my Oracle 9.2.0.1. I have a database that I am trying to gain consistent performance out of but I seem to have changing execution plans. Due to a number of factors outside my control, I have to handle an application that does not send bind variables, I handle them as they come in. To do this, I have set the init.ora file to have an entry of 'cursor_sharing=similiar'. I know this is a...
1
2023
by: Vinny | last post by:
Can anyone help me with this strange problem please? I have a stored procedure, with a parameter defined as a uniqueidentifier. The procedure does a select with a number of joins, and filters within the Where clause using this parameter. (@orderHeader_id uniqueidentifier) SELECT *
11
16303
by: randi_clausen | last post by:
Using SQL against a DB2 table the 'with' key word is used to dynamically create a temporary table with an SQL statement that is retained for the duration of that SQL statement. What is the equivalent to the SQL 'with' using TSQL? If there is not one, what is the TSQL solution to creating a temporary table that is associated with an SQL statement? Examples would be appreciated. Thank you!!
6
2078
by: Christine Wolak | last post by:
I'm looking for assistance on a problem with SQL Server. We have a database where a particular query returns about 3000 rows. This query takes about 2 minutes on most machines, which is fine in this situation. But on another machine (just one machine), it can run for over 30 minutes and not return. I ran it in Query Analyzer and it was returning about 70 rows every 45-90 seconds, which is completely unacceptable. (I'm a developer, not...
5
4169
by: mas | last post by:
I have a Stored Procedure (SP) that creates the data required for a report that I show on a web page. The SP does all the work and just returns back a results set that I dump in an ASP.NET DataGrid. The SP takes a product area and a start and end date as parameters. Here are the basics of the SP. 1. Create temp table to store report results, all columns are created that will be needed at this point. 2. Select products and general...
2
2067
by: Jenny Zhang | last post by:
Hi, I am running OSDL-DBT3 test against PostgreSQL. I found performance difference between the runs even though the data and queries are the same. I tried to study this problem by getting execution plans at the beginning of each test. The following script gets execution plan for 22 queries, except query 15. i=1 while
2
2278
by: Jenny Zhang | last post by:
The osdl-dbt3 test starts with building and vacuuming the database. The execution plans were taken after the vacuuming. I did two tests with the same database parameters: 1. run two osdl-dbt3 runs on one box without rebooting the stystem. Though the execution plans are the same, the costs are different. The system status are different for the two runs, for example, some page cache are not released after the first one. Does that make...
2
3741
by: Ina Schmitz | last post by:
Hi NG, does IBM Universal Database 8.2 make any difference between actual and estimated execution plans like in SQL Server ("set showplan_all on" for estimated execution plan and "set statistics profile on" for actual execution plan)? Does "explain plan selection for" generate the *estimated* execution plan? Didn't find any distinction of actual or estimated execution plans in the information center. Thanks for help,
5
10764
by: sqlgirl | last post by:
Hi, We are trying to solve a real puzzle. We have a stored procedure that exhibits *drastically* different execution times depending on how its executed. When run from QA, it can take as little as 3 seconds. When it is called from an Excel vba application, it can take up to 180 seconds. Although, at other times, it can take as little as 20 seconds from Excel.
0
9933
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11114
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10730
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10835
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9563
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7960
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5787
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5982
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3230
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.