[DB2-L] GTT vs DGTT Performance (V8)

Walter Janißen

[DB2-L] GTT vs DGTT Performance (V8)
Hi Larry

You wrote:

So any existing SQL statements in that package (whether they use the DGTT or not) now become dynamic SQL.

I disagree. Only those statements referencing the DGTT are treated as dynamic statements. The others are still static.

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Hans-Böckler-Str. 36
D-40477 Düsseldorf
Tel.: +49 211 477-2928
Fax: +49 211 477-2615
mailto:[login to unmask email]
http:// www.itergo.com
Vorsitzender des Aufsichtsrats: Dr. Torsten Oletzky
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf | Handelsregister: Amtsgericht Düsseldorf, HRB 37996


-----Ursprüngliche Nachricht-----
Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Jardine, Lawrence J
Gesendet: Sonntag, 25. November 2007 17:10
An: [login to unmask email]
Betreff: Re: [DB2-L] GTT vs DGTT Performance (V8)

I haven't seen anyone mention this, but the use of DGTT in a package makes it dynamic. So any existing SQL statements in that package (whether they use the DGTT or not) now become dynamic SQL. This makes debugging performance more difficult (no explain data available) and also necessitates a Bind at every execution of the package.

BTW, I have to state my dislike of the names for the different types of temporary tables. Created Temp Tables are declared in a program and Declared Temp Tables are created in a program!

Or did I get it backwards?


Larry Jardine
Production DBA
TDM Website: http://opsvcs.aetna.com/oltpdb/

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of rdpratti
Sent: Wednesday, November 21, 2007 8:27 AM
To: [login to unmask email]
Subject: Re: [DB2-L] GTT vs DGTT Performance (V8)

James,

Thanks for the reply. I understand the index advantage, but my understanding is that it is a choice. A DGTT can be defined without indexes. If I didn't need the indexes or other features would it be beneficial to stick with GTTS. For example, is there overhead to plain vanila DGTTs that do not exist in GTTs .
For a simple application like I described, that wouldn't need the DGTT bells and whistles, would GTTs be the better choice from a perfor,mance perspective?

I was planning on setting up an empirical test, but was wondering if you had worked out that question before.

Thanks,

- Roland DePratti

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
This e-mail may contain confidential or privileged information. If you think you have received this e-mail in error, please advise the sender by reply e-mail and then delete this e-mail immediately.
Thank you. Aetna

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Myron Miller

Re: GTT vs DGTT Performance (V8)
(in response to Walter Janißen)
Lawrence,
I don't think that any SQL that exists in a program but which does not use
the DGTT now becomes dynamic. Only that SQL that actually uses the DGTT. I've
got a number of programs and SPs that use DGTTs and Created GTTs that have
other SQL that I can see the access paths from a batch bind in the plan table.
Only the GTTs accessed are not available as static SQL.

Myron
--- "Jardine, Lawrence J" <[login to unmask email]> wrote:

> I haven't seen anyone mention this, but the use of DGTT in a package
> makes it dynamic. So any existing SQL statements in that package
> (whether they use the DGTT or not) now become dynamic SQL. This makes
> debugging performance more difficult (no explain data available) and
> also necessitates a Bind at every execution of the package.
>
> BTW, I have to state my dislike of the names for the different types of
> temporary tables. Created Temp Tables are declared in a program and
> Declared Temp Tables are created in a program!
>
> Or did I get it backwards?
>
>
> Larry Jardine
> Production DBA
> TDM Website: http://opsvcs.aetna.com/oltpdb/
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
> Behalf Of rdpratti
> Sent: Wednesday, November 21, 2007 8:27 AM
> To: [login to unmask email]
> Subject: Re: [DB2-L] GTT vs DGTT Performance (V8)
>
> James,
>
> Thanks for the reply. I understand the index advantage, but my
> understanding is that it is a choice. A DGTT can be defined without
> indexes. If I didn't need the indexes or other features would it be
> beneficial to stick with GTTS. For example, is there overhead to plain
> vanila DGTTs that do not exist in GTTs .
> For a simple application like I described, that wouldn't need the DGTT
> bells and whistles, would GTTs be the better choice from a perfor,mance
> perspective?
>
> I was planning on setting up an empirical test, but was wondering if you
> had worked out that question before.
>
> Thanks,
>
> - Roland DePratti
>
> ------------------------------------------------------------------------
> ---------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
> page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG conferences
> at http://conferences.idug.org/index.cfm
> This e-mail may contain confidential or privileged information. If
> you think you have received this e-mail in error, please advise the
> sender by reply e-mail and then delete this e-mail immediately.
> Thank you. Aetna
>
> IMPORTANT NOTICE:
>
> IDUG is pleased to announce a series of upgrades to the DB2-L discussion
> listserv that are being implemented to improve reliability and the overall
> user experience of DB2-L. These changes are coming on November 30th.
> Details at http://www.idug.org
>
>
---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
> "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
> The IDUG List Admins can be reached at [login to unmask email] Find
> out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
>

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Larry Jardine

Re: GTT vs DGTT Performance (V8)
(in response to Myron Miller)
Thanks to all for correcting me.

My points(1. dynamic and 2. no plan data) are still valid for the
statements using the DGTT.


Larry Jardine
Production DBA
TDM Website: http://opsvcs.aetna.com/oltpdb/

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Myron Miller
Sent: Monday, November 26, 2007 3:14 PM
To: [login to unmask email]
Subject: Re: [DB2-L] GTT vs DGTT Performance (V8)

Lawrence,
I don't think that any SQL that exists in a program but which does
not use the DGTT now becomes dynamic. Only that SQL that actually uses
the DGTT. I've got a number of programs and SPs that use DGTTs and
Created GTTs that have other SQL that I can see the access paths from a
batch bind in the plan table.
Only the GTTs accessed are not available as static SQL.

Myron
--- "Jardine, Lawrence J" <[login to unmask email]> wrote:

> I haven't seen anyone mention this, but the use of DGTT in a package
> makes it dynamic. So any existing SQL statements in that package
> (whether they use the DGTT or not) now become dynamic SQL. This makes
> debugging performance more difficult (no explain data available) and
> also necessitates a Bind at every execution of the package.
>
> BTW, I have to state my dislike of the names for the different types
> of temporary tables. Created Temp Tables are declared in a program
> and Declared Temp Tables are created in a program!
>
> Or did I get it backwards?
>
>
> Larry Jardine
> Production DBA
> TDM Website: http://opsvcs.aetna.com/oltpdb/
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
> Behalf Of rdpratti
> Sent: Wednesday, November 21, 2007 8:27 AM
> To: [login to unmask email]
> Subject: Re: [DB2-L] GTT vs DGTT Performance (V8)
>
> James,
>
> Thanks for the reply. I understand the index advantage, but my
> understanding is that it is a choice. A DGTT can be defined without
> indexes. If I didn't need the indexes or other features would it be
> beneficial to stick with GTTS. For example, is there overhead to plain

> vanila DGTTs that do not exist in GTTs .
> For a simple application like I described, that wouldn't need the DGTT

> bells and whistles, would GTTs be the better choice from a
> perfor,mance perspective?
>
> I was planning on setting up an empirical test, but was wondering if
> you had worked out that question before.
>
> Thanks,
>
> - Roland DePratti
>
> ----------------------------------------------------------------------
> --
> ---------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and

> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
> page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm
> This e-mail may contain confidential or privileged information. If you

> think you have received this e-mail in error, please advise the sender

> by reply e-mail and then delete this e-mail immediately.
> Thank you. Aetna
>
> IMPORTANT NOTICE:
>
> IDUG is pleased to announce a series of upgrades to the DB2-L
> discussion listserv that are being implemented to improve reliability
> and the overall user experience of DB2-L. These changes are coming on
November 30th.
> Details at http://www.idug.org
>
>
------------------------------------------------------------------------
---------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and

> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
> page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org.
> The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm
>

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion
listserv that are being implemented to improve reliability and the
overall user experience of DB2-L. These changes are coming on November
30th. Details at http://www.idug.org

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Vaughan

Re: GTT vs DGTT Performance (V8)
(in response to Larry Jardine)
One other thing that I think is relevant -- not only are statements
referencing a DGTT dynamic, but they are not eligible for either the
local or global dynamic statement cache, so depending on the SQL used
(and the number of commits issued, etc) the overhead of the incremental
bind could become noticable.

Mike.
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Jardine, Lawrence J
Sent: Monday, November 26, 2007 4:32 PM
To: [login to unmask email]
Subject: Re: [DB2-L] GTT vs DGTT Performance (V8)

Thanks to all for correcting me.

My points(1. dynamic and 2. no plan data) are still valid for the
statements using the DGTT.


Larry Jardine
Production DBA
TDM Website: http://opsvcs.aetna.com/oltpdb/

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Myron Miller
Sent: Monday, November 26, 2007 3:14 PM
To: [login to unmask email]
Subject: Re: [DB2-L] GTT vs DGTT Performance (V8)

Lawrence,
I don't think that any SQL that exists in a program but which does
not use the DGTT now becomes dynamic. Only that SQL that actually uses
the DGTT. I've got a number of programs and SPs that use DGTTs and
Created GTTs that have other SQL that I can see the access paths from a
batch bind in the plan table.
Only the GTTs accessed are not available as static SQL.

Myron
--- "Jardine, Lawrence J" <[login to unmask email]> wrote:

> I haven't seen anyone mention this, but the use of DGTT in a package
> makes it dynamic. So any existing SQL statements in that package
> (whether they use the DGTT or not) now become dynamic SQL. This makes
> debugging performance more difficult (no explain data available) and
> also necessitates a Bind at every execution of the package.
>
> BTW, I have to state my dislike of the names for the different types
> of temporary tables. Created Temp Tables are declared in a program
> and Declared Temp Tables are created in a program!
>
> Or did I get it backwards?
>
>
> Larry Jardine
> Production DBA
> TDM Website: http://opsvcs.aetna.com/oltpdb/
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
> Behalf Of rdpratti
> Sent: Wednesday, November 21, 2007 8:27 AM
> To: [login to unmask email]
> Subject: Re: [DB2-L] GTT vs DGTT Performance (V8)
>
> James,
>
> Thanks for the reply. I understand the index advantage, but my
> understanding is that it is a choice. A DGTT can be defined without
> indexes. If I didn't need the indexes or other features would it be
> beneficial to stick with GTTS. For example, is there overhead to plain

> vanila DGTTs that do not exist in GTTs .
> For a simple application like I described, that wouldn't need the DGTT

> bells and whistles, would GTTs be the better choice from a
> perfor,mance perspective?
>
> I was planning on setting up an empirical test, but was wondering if
> you had worked out that question before.
>
> Thanks,
>
> - Roland DePratti
>
> ----------------------------------------------------------------------
> --
> ---------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and

> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
> page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm
> This e-mail may contain confidential or privileged information. If you

> think you have received this e-mail in error, please advise the sender

> by reply e-mail and then delete this e-mail immediately.
> Thank you. Aetna
>
> IMPORTANT NOTICE:
>
> IDUG is pleased to announce a series of upgrades to the DB2-L
> discussion listserv that are being implemented to improve reliability
> and the overall user experience of DB2-L. These changes are coming on
November 30th.
> Details at http://www.idug.org
>
>
------------------------------------------------------------------------
---------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and

> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
> page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org.
> The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm
>

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion
listserv that are being implemented to improve reliability and the
overall user experience of DB2-L. These changes are coming on November
30th. Details at http://www.idug.org

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion
listserv that are being implemented to improve reliability and the
overall user experience of DB2-L. These changes are coming on November
30th. Details at http://www.idug.org

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


-----Message Disclaimer-----

This e-mail message is intended only for the use of the individual or
entity to which it is addressed, and may contain information that is
privileged, confidential and exempt from disclosure under applicable law.
If you are not the intended recipient, any dissemination, distribution or
copying of this communication is strictly prohibited. If you have
received this communication in error, please notify us immediately by
reply email to [login to unmask email] and delete or destroy all copies of
the original message and attachments thereto. Email sent to or from the
Principal Financial Group or any of its member companies may be retained
as required by law or regulation.

Nothing in this message is intended to constitute an Electronic signature
for purposes of the Uniform Electronic Transactions Act (UETA) or the
Electronic Signatures in Global and National Commerce Act ("E-Sign")
unless a specific statement to the contrary is included in this message.

While this communication may be used to promote or market a transaction
or an idea that is discussed in the publication, it is intended to provide
general information about the subject matter covered and is provided with
the understanding that The Principal is not rendering legal, accounting,
or tax advice. It is not a marketed opinion and may not be used to avoid
penalties under the Internal Revenue Code. You should consult with
appropriate counsel or other advisors on all matters pertaining to legal,
tax, or accounting obligations and requirements.

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm