[DB2-L] DB2 V8 New Keyword for Create/Alter Table

Walter Janißen

[DB2-L] DB2 V8 New Keyword for Create/Alter Table
Barbara

If you want to choose this option, you must be aware of the sometimes startling access paths. Basically, this option has it's origin from a SAP requirement, so it handles their request at best. This option favours index access and prohibts some other access path options, namely list prefetch. So if an update- or delete-statement requires list prefetch, the access path will be a tablespace scan and that could be a killer for performance.

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40198 Düsseldorf
mailto:[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996




________________________________

Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von [login to unmask email]
Gesendet: Freitag, 19. Dezember 2008 22:24
An: [login to unmask email]
Betreff: [DB2-L] DB2 V8 New Keyword for Create/Alter Table



Does anyone have any experience with the new table option VOLATILE with DB2 z/OS Version 8? If so, has the experience been positive or negative? I am in the process of defining
the control tables for IBM's Q Replication product and the job included comments about using this new parameter when defining tables that have indexes.

Barbara J Nigh
=====
CONSULTANT DATABASE MGMT | HSBC Technology Services (HTS)
1441 Schilling Place
Salinas, CA 93901
______________________________________________________

Phone 831 754 1400 2048
Fax 831-759-7073
Mobile 831-776-6194 Pager [login to unmask email]
Email [login to unmask email] <mailto:[login to unmask email]>
Web Site http://www.hsbc.com < http://www.hsbc.com/ >
______________________________________________________


________________________________




******************************************************************
This E-mail is confidential. It may also be legally privileged. If
you are not the addressee you may not copy, forward, disclose or
use any part of it. If you have received this message in error,
please delete it and all copies from your system and notify the
sender immediately by return E-mail.

Internet communications cannot be guaranteed to be timely, secure,
error or virus-free. The sender does not accept liability for any
errors or omissions.
******************************************************************
SAVE PAPER - THINK BEFORE YOU PRINT!


________________________________

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA < http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information <http://www.idug.org/lsconf> , and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms >


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

SUBSCRIBE DB2-L Muthuraj

Re: AW: [DB2-L] DB2 V8 New Keyword for Create/Alter Table
(in response to Walter Janißen)
Barbara,

Tables which are having widely varying number of rows should be having this
option enabled. the best example is 'physical temporary table' which will be
used to keep intermediate result sets. For this kind of tables, the table
might be empty when you run the runstats. when you access the table, it
might have a good number of records. Since the runstats says zero records,
accesspath ll be chosen as 'tablespace scan', which will kill the
performance of the SQL. By enabling this option, you can make sure db2 ll
use indexes whenever possible rather than tablespace scan. But that may not
be also always good. Sometimes the table might hold less number of records.
In that case also, accessing through index may not yield good performance.
Even if the SQL doesnt have predicates also, it will use the index for
processing. Apart from that, list prefetch, hybrid join and multi-index
access are disabled. So the SQLs which will be benifited by these
accesspaths will perform badly.

This option is very useful if the tables are used concurrently by many
applications. Because all ll try to access the records in the same order,
which ll reduce lock contentions( possibily dead locks sometimes).

Regards,
Muthu




1. Favour Index Access

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Roger Hecq

Re: DB2 V8 New Keyword for Create/Alter Table
(in response to SUBSCRIBE DB2-L Muthuraj)
We have used the Volatile parameter successfully on tables that are
emptied nightly and then repopulated during the next day. With the
volatile option, the bind generates index access paths, even if the
table was empty when the Runstats utility was run.

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: Friday, December 19, 2008 4:24 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 V8 New Keyword for Create/Alter Table



Does anyone have any experience with the new table option VOLATILE with
DB2 z/OS Version 8? If so, has the experience been positive or
negative? I am in the process of defining
the control tables for IBM's Q Replication product and the job included
comments about using this new parameter when defining tables that have
indexes.

Barbara J Nigh
=====
CONSULTANT DATABASE MGMT | HSBC Technology Services (HTS)
1441 Schilling Place
Salinas, CA 93901
______________________________________________________

Phone 831 754 1400 2048
Fax 831-759-7073
Mobile 831-776-6194 Pager [login to unmask email]
Email [login to unmask email] <mailto:[login to unmask email]>
Web Site http://www.hsbc.com < http://www.hsbc.com/ >
______________________________________________________

________________________________

******************************************************************
This E-mail is confidential. It may also be legally privileged. If
you are not the addressee you may not copy, forward, disclose or
use any part of it. If you have received this message in error,
please delete it and all copies from your system and notify the
sender immediately by return E-mail.

Internet communications cannot be guaranteed to be timely, secure,
error or virus-free. The sender does not accept liability for any
errors or omissions.
******************************************************************
SAVE PAPER - THINK BEFORE YOU PRINT!


________________________________

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA
< http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more. If you have not yet signed
up for Basic Membership in IDUG, available at no cost, click on Member
Services < http://www.idug.org/lsms >


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

John Amsden

Re: DB2 V8 New Keyword for Create/Alter Table
(in response to Roger Hecq)
We've done the same thing with successful results. A handy option for this type of table.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Roger Hecq
Sent: Friday, December 26, 2008 1:44 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 V8 New Keyword for Create/Alter Table


We have used the Volatile parameter successfully on tables that are emptied nightly and then repopulated during the next day. With the volatile option, the bind generates index access paths, even if the table was empty when the Runstats utility was run.

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492


_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of [login to unmask email]
Sent: Friday, December 19, 2008 4:24 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 V8 New Keyword for Create/Alter Table



Does anyone have any experience with the new table option VOLATILE with DB2 z/OS Version 8? If so, has the experience been positive or negative? I am in the process of defining
the control tables for IBM's Q Replication product and the job included comments about using this new parameter when defining tables that have indexes.

Barbara J Nigh
=====
CONSULTANT DATABASE MGMT | HSBC Technology Services (HTS)
1441 Schilling Place
Salinas, CA 93901
______________________________________________________

Phone 831 754 1400 2048
Fax 831-759-7073
Mobile 831-776-6194 Pager [login to unmask email]
Email <mailto:[login to unmask email]> [login to unmask email]
Web Site < http://www.hsbc.com/ > http://www.hsbc.com
______________________________________________________



_____




******************************************************************
This E-mail is confidential. It may also be legally privileged. If
you are not the addressee you may not copy, forward, disclose or
use any part of it. If you have received this message in error,
please delete it and all copies from your system and notify the
sender immediately by return E-mail.

Internet communications cannot be guaranteed to be timely, secure,
error or virus-free. The sender does not accept liability for any
errors or omissions.
******************************************************************
SAVE PAPER - THINK BEFORE YOU PRINT!


_____

< http://idug.org/lsNA > IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information <http://www.idug.org/lsconf> , and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member < http://www.idug.org/lsms > Services


_____

< http://idug.org/lsna > IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA

<http://www.idug.org> IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register < http://www.idug.org/component/juser/register.html > here.





Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged, confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or entity to
which it is addressed. If you are not the intended recipient of this E-mail, you are
hereby notified that any dissemination, distribution, copying, or action taken in
relation to the contents of and attachments to this E-mail is strictly prohibited
and may be unlawful. If you have received this E-mail in error, please notify the
sender immediately and permanently delete the original and any copy of this E-mail
and any printout. Thank You.**

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html