DGTTs and dynamic statement caching

Phil Grainger

DGTTs and dynamic statement caching
Folks - OK

Just shows - never assume anything about DB2

I have conferred with one of our DB2 experts (thanks Susan) and Dash is very right

Despite the SQL against DGTTs being dynamic it does NOT get cached

Apologies for any confusion (but you must admit, it was a fairly sensible assumption on my part!)

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Dash, Sushanta
Sent: 09 January 2004 05:40
To: [login to unmask email]
Subject: Re: Table Scans on Declared Temp Table with Indexes


Hi Phil, here is a clarification I need. I m not sure for any new
improvements are coming up for this in V8 for dynamic cache. You had
mentioned that 'I bet DB2 will use a cached access path (if you have enabled
dynamic statement caching)'. But as far as v7 goes and we are using DTT,
then statements are executed as dynamic sql statement but dynamic cache can
not be used as the table does not exist at bind time. I m sure you must
have more experiences and felt it practically. Could you give us more
insights in to it.

With thanks
Dash



---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NOMAIL command to [login to unmask email] 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

Suresh Sane

Re: DGTTs and dynamic statement caching
(in response to Phil Grainger)
Phil,

This makes sense when you think about it. Since the DTT (OK, who came up
with that DGTT? - there is CTT - created temporary table and DTT - delcated
temporary table; the global is gone) can have a different structure, SQL
like

select ... from session.t1

issued by you and issued by me accesses not only tables of differemt
physical charactersitcis (# of rows, indexes etc), but it can have different
columns too!

Based on this, it is clear that caching sql for DTTs is not just a
technology issue, it is a functionality issue and I dor't see how a global
cache (CACHEDYNAMIC) could even be implemented. Local cache (with
KEPPDYNAMIC) is another story.

Thanks,
Suresh


>From: "Grainger, Phil" <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: DGTTs and dynamic statement caching
>Date: Fri, 9 Jan 2004 15:44:03 -0000
>
>Folks - OK
>
>Just shows - never assume anything about DB2
>
>I have conferred with one of our DB2 experts (thanks Susan) and Dash is
>very right
>
>Despite the SQL against DGTTs being dynamic it does NOT get cached
>
>Apologies for any confusion (but you must admit, it was a fairly sensible
>assumption on my part!)
>
>Phil Grainger
>Computer Associates
>Product Manager, DB2
>Tel: +44 (0)161 928 9334
>Fax: +44 (0)161 941 3775
>Mobile: +44 (0)7970 125 752
>[login to unmask email]
>
>
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
>Behalf Of Dash, Sushanta
>Sent: 09 January 2004 05:40
>To: [login to unmask email]
>Subject: Re: Table Scans on Declared Temp Table with Indexes
>
>
>Hi Phil, here is a clarification I need. I m not sure for any new
>improvements are coming up for this in V8 for dynamic cache. You had
>mentioned that 'I bet DB2 will use a cached access path (if you have
>enabled
>dynamic statement caching)'. But as far as v7 goes and we are using DTT,
>then statements are executed as dynamic sql statement but dynamic cache can
>not be used as the table does not exist at bind time. I m sure you must
>have more experiences and felt it practically. Could you give us more
>insights in to it.
>
>With thanks
>Dash
>
>
>
>---------------------------------------------------------------------------------
>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". If you will be out of the office, send the SET
>DB2-L NOMAIL command to [login to unmask email] 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

_________________________________________________________________
Make your home warm and cozy this winter with tips from MSN House & Home.
http://special.msn.com/home/warmhome.armx

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NOMAIL command to [login to unmask email] 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

Phil Grainger

Re: DGTTs and dynamic statement caching
(in response to Suresh Sane)
Local caching is what I was thinking

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Suresh Sane
Sent: 09 January 2004 17:07
To: [login to unmask email]
Subject: Re: DGTTs and dynamic statement caching


Phil,

This makes sense when you think about it. Since the DTT (OK, who came up
with that DGTT? - there is CTT - created temporary table and DTT - delcated
temporary table; the global is gone) can have a different structure, SQL
like

select ... from session.t1

issued by you and issued by me accesses not only tables of differemt
physical charactersitcis (# of rows, indexes etc), but it can have different
columns too!

Based on this, it is clear that caching sql for DTTs is not just a
technology issue, it is a functionality issue and I dor't see how a global
cache (CACHEDYNAMIC) could even be implemented. Local cache (with
KEPPDYNAMIC) is another story.

Thanks,
Suresh


>From: "Grainger, Phil" <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: DGTTs and dynamic statement caching
>Date: Fri, 9 Jan 2004 15:44:03 -0000
>
>Folks - OK
>
>Just shows - never assume anything about DB2
>
>I have conferred with one of our DB2 experts (thanks Susan) and Dash is
>very right
>
>Despite the SQL against DGTTs being dynamic it does NOT get cached
>
>Apologies for any confusion (but you must admit, it was a fairly sensible
>assumption on my part!)
>
>Phil Grainger
>Computer Associates
>Product Manager, DB2
>Tel: +44 (0)161 928 9334
>Fax: +44 (0)161 941 3775
>Mobile: +44 (0)7970 125 752
>[login to unmask email]
>
>
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
>Behalf Of Dash, Sushanta
>Sent: 09 January 2004 05:40
>To: [login to unmask email]
>Subject: Re: Table Scans on Declared Temp Table with Indexes
>
>
>Hi Phil, here is a clarification I need. I m not sure for any new
>improvements are coming up for this in V8 for dynamic cache. You had
>mentioned that 'I bet DB2 will use a cached access path (if you have
>enabled
>dynamic statement caching)'. But as far as v7 goes and we are using DTT,
>then statements are executed as dynamic sql statement but dynamic cache can
>not be used as the table does not exist at bind time. I m sure you must
>have more experiences and felt it practically. Could you give us more
>insights in to it.
>
>With thanks
>Dash
>
>
>
>---------------------------------------------------------------------------------
>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". If you will be out of the office, send the SET
>DB2-L NOMAIL command to [login to unmask email] 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

_________________________________________________________________
Make your home warm and cozy this winter with tips from MSN House & Home.
http://special.msn.com/home/warmhome.armx

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NOMAIL command to [login to unmask email] 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

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NOMAIL command to [login to unmask email] 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

Min Zhang

Re: DGTTs and dynamic statement caching
(in response to Phil Grainger)
Phil and Suresh,

It is very interesting topic, that we are currently experiencing when we use DGTT. We did see what Bob Irving described in his e-mail.

But you are talking about SYSIBM.SYSPLAN and SYSIBM.SYSPACKAGE tables columns KEEPDYNAMIC, and
CACHEDYNAMIC for using DGTT. Which In Gabrielle's DB2 Development for Performance book, you will find clearly defined as NOT cataloged.

Description DGTT CGTT
Incremental bind at
run time Y N
Recorded in catalog tables N Y (6)


(6) A row is inserted into SYSTABLES with TYPE = 'G' as a result of the
CREATE TEMPORARY TABLE statement. However, each of many
programs can have an instance of the table. The rows are deleted when the
temporary table is dropped. You can manually update catalog statistics,
otherwise default statistics are used. Statistics are useful to the optimizer in
determining the table join sequence, for example. Do be cautious of the fact
that the statistics apply to all instances of global temporary tables some of
which can be large and others small. Regardless of whether you choose to
update the statistics, the number of rows and pages are accumulated and
kept in memory for use with dynamic SQL without use of the cache.

Indeed as we watched, the incremental bind is working as designed by IBM for the DB2 using Declared Global Temporary Table. And it is overhead. But the IBM DB2 document said as below:

Can anyone confirm if those incremental binds will cause system catalog contentions by using DGTT? If any log …...

Also in IBM http://publibfp.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/DSNAPH11/CCONTENTS
In section 1.2.1.2 Working with temporary tables, it described as followings:

SQL statements that use temporary tables can run faster because:

DB2 does no logging (for created temporary tables) or limited logging (for declared temporary tables).
DB2 does no locking (for created temporary tables) or limited locking (for declared temporary tables).

Thanks in advance,

Min Zhang
DB2 Administration at
Northrop Grumman
703.876.4320


>>> [login to unmask email] 01/09/04 12:06PM >>>
Phil,

This makes sense when you think about it. Since the DTT (OK, who came up
with that DGTT? - there is CTT - created temporary table and DTT - delcated
temporary table; the global is gone) can have a different structure, SQL
like

select ... from session.t1

issued by you and issued by me accesses not only tables of differemt
physical charactersitcis (# of rows, indexes etc), but it can have different
columns too!

Based on this, it is clear that caching sql for DTTs is not just a
technology issue, it is a functionality issue and I dor't see how a global
cache (CACHEDYNAMIC) could even be implemented. Local cache (with
KEPPDYNAMIC) is another story.

Thanks,
Suresh


>From: "Grainger, Phil" <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: DGTTs and dynamic statement caching
>Date: Fri, 9 Jan 2004 15:44:03 -0000
>
>Folks - OK
>
>Just shows - never assume anything about DB2
>
>I have conferred with one of our DB2 experts (thanks Susan) and Dash is
>very right
>
>Despite the SQL against DGTTs being dynamic it does NOT get cached
>
>Apologies for any confusion (but you must admit, it was a fairly sensible
>assumption on my part!)
>
>Phil Grainger
>Computer Associates
>Product Manager, DB2
>Tel: +44 (0)161 928 9334
>Fax: +44 (0)161 941 3775
>Mobile: +44 (0)7970 125 752
>[login to unmask email]
>
>
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
>Behalf Of Dash, Sushanta
>Sent: 09 January 2004 05:40
>To: [login to unmask email]
>Subject: Re: Table Scans on Declared Temp Table with Indexes
>
>
>Hi Phil, here is a clarification I need. I m not sure for any new
>improvements are coming up for this in V8 for dynamic cache. You had
>mentioned that 'I bet DB2 will use a cached access path (if you have
>enabled
>dynamic statement caching)'. But as far as v7 goes and we are using DTT,
>then statements are executed as dynamic sql statement but dynamic cache can
>not be used as the table does not exist at bind time. I m sure you must
>have more experiences and felt it practically. Could you give us more
>insights in to it.
>
>With thanks
>Dash
>
>
>
>---------------------------------------------------------------------------------
>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". If you will be out of the office, send the SET
>DB2-L NOMAIL command to [login to unmask email] 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

_________________________________________________________________
Make your home warm and cozy this winter with tips from MSN House & Home.
http://special.msn.com/home/warmhome.armx

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NOMAIL command to [login to unmask email] 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

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NOMAIL command to [login to unmask email] 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

Min Zhang

Re: DGTTs and dynamic statement caching
(in response to Min Zhang)
Phil and Suresh,

It is very interesting topic, that we are currently experiencing when we use DGTT. We did see what Bob Irving described in his e-mail.

But you are talking about SYSIBM.SYSPLAN and SYSIBM.SYSPACKAGE tables columns KEEPDYNAMIC, and
CACHEDYNAMIC for using DGTT. Which In Gabrielle's DB2 Development for Performance book, you will find clearly defined as NOT cataloged.

Description DGTT CGTT
Incremental bind at
run time Y N
Recorded in catalog
tables N Y (6)


(6) A row is inserted into SYSTABLES with TYPE = 'G' as a result of the
CREATE TEMPORARY TABLE statement. However, each of many
programs can have an instance of the table. The rows are deleted when the
temporary table is dropped. You can manually update catalog statistics,
otherwise default statistics are used. Statistics are useful to the optimizer in
determining the table join sequence, for example. Do be cautious of the fact
that the statistics apply to all instances of global temporary tables some of
which can be large and others small. Regardless of whether you choose to
update the statistics, the number of rows and pages are accumulated and
kept in memory for use with dynamic SQL without use of the cache.

Indeed as we watched, the incremental bind is working as designed by IBM for the DB2 using Declared Global Temporary Table. And it is overhead. But the IBM DB2 document said as below:

Also in IBM http://publibfp.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/DSNAPH11/CCONTENTS
In section 1.2.1.2 Working with temporary tables, it described as followings:

SQL statements that use temporary tables can run faster because:

DB2 does no logging (for created temporary tables) or limited logging (for declared temporary tables).
DB2 does no locking (for created temporary tables) or limited locking (for declared temporary tables).

Can someone confirm if those incremental binds will cause system catalog contentions by using DGTT? If any logging ...

Thanks in advance,

Min Zhang
DB2 Administration at
Northrop Grumman
703.876.4320


>>> [login to unmask email] 01/09/04 12:06PM >>>
Phil,

This makes sense when you think about it. Since the DTT (OK, who came up
with that DGTT? - there is CTT - created temporary table and DTT - delcated
temporary table; the global is gone) can have a different structure, SQL
like

select ... from session.t1

issued by you and issued by me accesses not only tables of differemt
physical charactersitcis (# of rows, indexes etc), but it can have different
columns too!

Based on this, it is clear that caching sql for DTTs is not just a
technology issue, it is a functionality issue and I dor't see how a global
cache (CACHEDYNAMIC) could even be implemented. Local cache (with
KEPPDYNAMIC) is another story.

Thanks,
Suresh


>From: "Grainger, Phil" <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: DGTTs and dynamic statement caching
>Date: Fri, 9 Jan 2004 15:44:03 -0000
>
>Folks - OK
>
>Just shows - never assume anything about DB2
>
>I have conferred with one of our DB2 experts (thanks Susan) and Dash is
>very right
>
>Despite the SQL against DGTTs being dynamic it does NOT get cached
>
>Apologies for any confusion (but you must admit, it was a fairly sensible
>assumption on my part!)
>
>Phil Grainger
>Computer Associates
>Product Manager, DB2
>Tel: +44 (0)161 928 9334
>Fax: +44 (0)161 941 3775
>Mobile: +44 (0)7970 125 752
>[login to unmask email]
>
>
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
>Behalf Of Dash, Sushanta
>Sent: 09 January 2004 05:40
>To: [login to unmask email]
>Subject: Re: Table Scans on Declared Temp Table with Indexes
>
>
>Hi Phil, here is a clarification I need. I m not sure for any new
>improvements are coming up for this in V8 for dynamic cache. You had
>mentioned that 'I bet DB2 will use a cached access path (if you have
>enabled
>dynamic statement caching)'. But as far as v7 goes and we are using DTT,
>then statements are executed as dynamic sql statement but dynamic cache can
>not be used as the table does not exist at bind time. I m sure you must
>have more experiences and felt it practically. Could you give us more
>insights in to it.
>
>With thanks
>Dash
>
>
>
>---------------------------------------------------------------------------------
>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". If you will be out of the office, send the SET
>DB2-L NOMAIL command to [login to unmask email] 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

_________________________________________________________________
Make your home warm and cozy this winter with tips from MSN House & Home.
http://special.msn.com/home/warmhome.armx

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NOMAIL command to [login to unmask email] 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

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NOMAIL command to [login to unmask email] 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

Suresh Sane

Re: DGTTs and dynamic statement caching
(in response to Min Zhang)
MIn,

We seem to have digressed from the original issue of dynamic sql caching to
temp table usage in general. But in any case:

Temp tables allow for limited/no logging (for savepoint only) and no locking
(since you have your private copy). The price you pay is that all SQL for
DTT must be dynamic, so the ibcremental binds, so the no dynamic sql
caching. Yes it is a tradeoff but the main reason for using the temp table
is not to elimate logging or locking - but a staging area that provides the
flexibility you need. The main reason, in fact, temp tables were introduced
was for result sets from stored procedures.

Can you cause contention with temp tables? Sure. Can dynamic sql cause
contention? Sure. But it is the DDL that will be an issue (how many shop
change lareg tables duing the day?).

Will they run faster? Depends on how often you bind and how often you
eliminate the logging/locking (# of rows). As I said before, it is not free
- it is a tradeoff.

Hope that clarifies rather than confuses the issue.

Thanks,
Suresh


>From: Min Zhang <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: DGTTs and dynamic statement caching
>Date: Fri, 9 Jan 2004 16:06:13 -0500
>
>Phil and Suresh,
>
>It is very interesting topic, that we are currently experiencing when we
>use DGTT. We did see what Bob Irving described in his e-mail.
>
>But you are talking about SYSIBM.SYSPLAN and SYSIBM.SYSPACKAGE tables
>columns KEEPDYNAMIC, and
>CACHEDYNAMIC for using DGTT. Which In Gabrielle's DB2 Development for
>Performance book, you will find clearly defined as NOT cataloged.
>
>Description DGTT CGTT
>Incremental bind at
>run time Y N
>Recorded in catalog
>tables N Y (6)
>
>
>(6) A row is inserted into SYSTABLES with TYPE = 'G' as a result of the
>CREATE TEMPORARY TABLE statement. However, each of many
>programs can have an instance of the table. The rows are deleted when the
>temporary table is dropped. You can manually update catalog statistics,
>otherwise default statistics are used. Statistics are useful to the
>optimizer in
>determining the table join sequence, for example. Do be cautious of the
>fact
>that the statistics apply to all instances of global temporary tables some
>of
>which can be large and others small. Regardless of whether you choose to
>update the statistics, the number of rows and pages are accumulated and
>kept in memory for use with dynamic SQL without use of the cache.
>
>Indeed as we watched, the incremental bind is working as designed by IBM
>for the DB2 using Declared Global Temporary Table. And it is overhead. But
>the IBM DB2 document said as below:
>
>Also in IBM
>http://publibfp.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/DSNAPH11/CCONTENTS
>In section 1.2.1.2 Working with temporary tables, it described as
>followings:
>
>SQL statements that use temporary tables can run faster because:
>
>DB2 does no logging (for created temporary tables) or limited logging (for
>declared temporary tables).
>DB2 does no locking (for created temporary tables) or limited locking (for
>declared temporary tables).
>
>Can someone confirm if those incremental binds will cause system catalog
>contentions by using DGTT? If any logging ...
>
>Thanks in advance,
>
>Min Zhang
>DB2 Administration at
>Northrop Grumman
>703.876.4320
>
>
> >>> [login to unmask email] 01/09/04 12:06PM >>>
>Phil,
>
>This makes sense when you think about it. Since the DTT (OK, who came up
>with that DGTT? - there is CTT - created temporary table and DTT - delcated
>temporary table; the global is gone) can have a different structure, SQL
>like
>
>select ... from session.t1
>
>issued by you and issued by me accesses not only tables of differemt
>physical charactersitcis (# of rows, indexes etc), but it can have
>different
>columns too!
>
>Based on this, it is clear that caching sql for DTTs is not just a
>technology issue, it is a functionality issue and I dor't see how a global
>cache (CACHEDYNAMIC) could even be implemented. Local cache (with
>KEPPDYNAMIC) is another story.
>
>Thanks,
>Suresh
>
>
> >From: "Grainger, Phil" <[login to unmask email]>
> >Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
> >To: [login to unmask email]
> >Subject: DGTTs and dynamic statement caching
> >Date: Fri, 9 Jan 2004 15:44:03 -0000
> >
> >Folks - OK
> >
> >Just shows - never assume anything about DB2
> >
> >I have conferred with one of our DB2 experts (thanks Susan) and Dash is
> >very right
> >
> >Despite the SQL against DGTTs being dynamic it does NOT get cached
> >
> >Apologies for any confusion (but you must admit, it was a fairly sensible
> >assumption on my part!)
> >
> >Phil Grainger
> >Computer Associates
> >Product Manager, DB2
> >Tel: +44 (0)161 928 9334
> >Fax: +44 (0)161 941 3775
> >Mobile: +44 (0)7970 125 752
> >[login to unmask email]
> >
> >
> >
> >-----Original Message-----
> >From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
> >Behalf Of Dash, Sushanta
> >Sent: 09 January 2004 05:40
> >To: [login to unmask email]
> >Subject: Re: Table Scans on Declared Temp Table with Indexes
> >
> >
> >Hi Phil, here is a clarification I need. I m not sure for any new
> >improvements are coming up for this in V8 for dynamic cache. You had
> >mentioned that 'I bet DB2 will use a cached access path (if you have
> >enabled
> >dynamic statement caching)'. But as far as v7 goes and we are using
>DTT,
> >then statements are executed as dynamic sql statement but dynamic cache
>can
> >not be used as the table does not exist at bind time. I m sure you must
> >have more experiences and felt it practically. Could you give us more
> >insights in to it.
> >
> >With thanks
> >Dash
> >
> >
> >
> >---------------------------------------------------------------------------------
> >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". If you will be out of the office, send the SET
> >DB2-L NOMAIL command to [login to unmask email] 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
>
>_________________________________________________________________
>Make your home warm and cozy this winter with tips from MSN House & Home.
>http://special.msn.com/home/warmhome.armx
>
>---------------------------------------------------------------------------------
>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". If you will be out of the office, send the SET
>DB2-L NOMAIL command to [login to unmask email] 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
>
>---------------------------------------------------------------------------------
>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". If you will be out of the office, send the SET
>DB2-L NOMAIL command to [login to unmask email] 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

_________________________________________________________________
Working moms: Find helpful tips here on managing kids, home, work — and
yourself. http://special.msn.com/msnbc/workingmom.armx

---------------------------------------------------------------------------------
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 IDUDG 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

Min Zhang

Re: DGTTs and dynamic statement caching
(in response to Suresh Sane)
Suresh,
Thanks for your reply.

Regards,

>>> [login to unmask email] 01/09/04 06:36PM >>>
MIn,

We seem to have digressed from the original issue of dynamic sql caching to
temp table usage in general. But in any case:

Temp tables allow for limited/no logging (for savepoint only) and no locking
(since you have your private copy). The price you pay is that all SQL for
DTT must be dynamic, so the ibcremental binds, so the no dynamic sql
caching. Yes it is a tradeoff but the main reason for using the temp table
is not to elimate logging or locking - but a staging area that provides the
flexibility you need. The main reason, in fact, temp tables were introduced
was for result sets from stored procedures.

Can you cause contention with temp tables? Sure. Can dynamic sql cause
contention? Sure. But it is the DDL that will be an issue (how many shop
change lareg tables duing the day?).

Will they run faster? Depends on how often you bind and how often you
eliminate the logging/locking (# of rows). As I said before, it is not free
- it is a tradeoff.

Hope that clarifies rather than confuses the issue.

Thanks,
Suresh


>From: Min Zhang <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: DGTTs and dynamic statement caching
>Date: Fri, 9 Jan 2004 16:06:13 -0500
>
>Phil and Suresh,
>
>It is very interesting topic, that we are currently experiencing when we
>use DGTT. We did see what Bob Irving described in his e-mail.
>
>But you are talking about SYSIBM.SYSPLAN and SYSIBM.SYSPACKAGE tables
>columns KEEPDYNAMIC, and
>CACHEDYNAMIC for using DGTT. Which In Gabrielle's DB2 Development for
>Performance book, you will find clearly defined as NOT cataloged.
>
>Description DGTT CGTT
>Incremental bind at
>run time Y N
>Recorded in catalog
>tables N Y (6)
>
>
>(6) A row is inserted into SYSTABLES with TYPE = 'G' as a result of the
>CREATE TEMPORARY TABLE statement. However, each of many
>programs can have an instance of the table. The rows are deleted when the
>temporary table is dropped. You can manually update catalog statistics,
>otherwise default statistics are used. Statistics are useful to the
>optimizer in
>determining the table join sequence, for example. Do be cautious of the
>fact
>that the statistics apply to all instances of global temporary tables some
>of
>which can be large and others small. Regardless of whether you choose to
>update the statistics, the number of rows and pages are accumulated and
>kept in memory for use with dynamic SQL without use of the cache.
>
>Indeed as we watched, the incremental bind is working as designed by IBM
>for the DB2 using Declared Global Temporary Table. And it is overhead. But
>the IBM DB2 document said as below:
>
>Also in IBM
>http://publibfp.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/DSNAPH11/CCONTENTS
>In section 1.2.1.2 Working with temporary tables, it described as
>followings:
>
>SQL statements that use temporary tables can run faster because:
>
>DB2 does no logging (for created temporary tables) or limited logging (for
>declared temporary tables).
>DB2 does no locking (for created temporary tables) or limited locking (for
>declared temporary tables).
>
>Can someone confirm if those incremental binds will cause system catalog
>contentions by using DGTT? If any logging ...
>
>Thanks in advance,
>
>Min Zhang
>DB2 Administration at
>Northrop Grumman
>703.876.4320
>
>
> >>> [login to unmask email] 01/09/04 12:06PM >>>
>Phil,
>
>This makes sense when you think about it. Since the DTT (OK, who came up
>with that DGTT? - there is CTT - created temporary table and DTT - delcated
>temporary table; the global is gone) can have a different structure, SQL
>like
>
>select ... from session.t1
>
>issued by you and issued by me accesses not only tables of differemt
>physical charactersitcis (# of rows, indexes etc), but it can have
>different
>columns too!
>
>Based on this, it is clear that caching sql for DTTs is not just a
>technology issue, it is a functionality issue and I dor't see how a global
>cache (CACHEDYNAMIC) could even be implemented. Local cache (with
>KEPPDYNAMIC) is another story.
>
>Thanks,
>Suresh
>
>
> >From: "Grainger, Phil" <[login to unmask email]>
> >Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
> >To: [login to unmask email]
> >Subject: DGTTs and dynamic statement caching
> >Date: Fri, 9 Jan 2004 15:44:03 -0000
> >
> >Folks - OK
> >
> >Just shows - never assume anything about DB2
> >
> >I have conferred with one of our DB2 experts (thanks Susan) and Dash is
> >very right
> >
> >Despite the SQL against DGTTs being dynamic it does NOT get cached
> >
> >Apologies for any confusion (but you must admit, it was a fairly sensible
> >assumption on my part!)
> >
> >Phil Grainger
> >Computer Associates
> >Product Manager, DB2
> >Tel: +44 (0)161 928 9334
> >Fax: +44 (0)161 941 3775
> >Mobile: +44 (0)7970 125 752
> >[login to unmask email]
> >
> >
> >
> >-----Original Message-----
> >From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
> >Behalf Of Dash, Sushanta
> >Sent: 09 January 2004 05:40
> >To: [login to unmask email]
> >Subject: Re: Table Scans on Declared Temp Table with Indexes
> >
> >
> >Hi Phil, here is a clarification I need. I m not sure for any new
> >improvements are coming up for this in V8 for dynamic cache. You had
> >mentioned that 'I bet DB2 will use a cached access path (if you have
> >enabled
> >dynamic statement caching)'. But as far as v7 goes and we are using
>DTT,
> >then statements are executed as dynamic sql statement but dynamic cache
>can
> >not be used as the table does not exist at bind time. I m sure you must
> >have more experiences and felt it practically. Could you give us more
> >insights in to it.
> >
> >With thanks
> >Dash
> >
> >
> >
> >---------------------------------------------------------------------------------
> >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". If you will be out of the office, send the SET
> >DB2-L NOMAIL command to [login to unmask email] 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
>
>_________________________________________________________________
>Make your home warm and cozy this winter with tips from MSN House & Home.
>http://special.msn.com/home/warmhome.armx
>
>---------------------------------------------------------------------------------
>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". If you will be out of the office, send the SET
>DB2-L NOMAIL command to [login to unmask email] 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
>
>---------------------------------------------------------------------------------
>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". If you will be out of the office, send the SET
>DB2-L NOMAIL command to [login to unmask email] 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

_________________________________________________________________
Working moms: Find helpful tips here on managing kids, home, work — and
yourself. http://special.msn.com/msnbc/workingmom.armx

---------------------------------------------------------------------------------
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 IDUDG 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

---------------------------------------------------------------------------------
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