Row change timestamp default value

Hunter Cobb

Row change timestamp default value
Version 9.1 for z/OS: I've been working with a row change timestamp column
added to a table some time after table creation, after row insertion /
update, but before a REORG TABLESPACE has been done. This is in a non-data
sharing environment. When the row change timestamp column is retrieved, a
value is returned that appears to be the timestamp associated with the page
(as advertised). What I'm puzzled by is: where is this page timestamp coming
from? It can't be the PGLOGRBA, since that is a log RBA, and not an LRSN in
a non-data sharing environment.

Hunter Cobb

______________________________________________________________________

* 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

Phil Grainger

Re: Row change timestamp default value
(in response to Hunter Cobb)
Hi Hunter

So far as I know, it does indeed come from the page RBA - which DB2 manages to turn into a timestamp (somehow)

Also note something which may be important in your scenario

If you do a "null update" (where you set a column to the value it already has) DB2 WILL update the row change timestamp (for now - this will be "fixed" soon) but will NOT update the page RBA. So you can get inconsistent results

Did you know, you can also select a row change timestamp from a table that does NOT have a row change timestamp column? If you do that, then the timestamp always comes from the page rba!!

Phil Grainger
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Hunter Cobb
Sent: Sat 22/11/2008 23:45
To: [login to unmask email]
Subject: [DB2-L] Row change timestamp default value



Version 9.1 for z/OS: I've been working with a row change timestamp column
added to a table some time after table creation, after row insertion /
update, but before a REORG TABLESPACE has been done. This is in a non-data
sharing environment. When the row change timestamp column is retrieved, a
value is returned that appears to be the timestamp associated with the page
(as advertised). What I'm puzzled by is: where is this page timestamp coming
from? It can't be the PGLOGRBA, since that is a log RBA, and not an LRSN in
a non-data sharing environment.

Hunter Cobb

______________________________________________________________________

* 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




______________________________________________________________________

* 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

Hunter Cobb

Re: Row change timestamp default value
(in response to Phil Grainger)
Well, my question focuses on the (somehow) part of your response. The
only thing I could think of was that log records have a store clock
field, and the log control interval definition (LCID) has a timestamp.
It's hard to believe that DB2 would read the log to obtain a timestamp
for a page; after all the log could have been archived.

The question of the source of the timestamp becomes even more acute if
you are dealing with a table with no row change timestamp column.

Hunter

Grainger, Phil wrote:
> Hi Hunter
>
> So far as I know, it does indeed come from the page RBA - which DB2
> manages to turn into a timestamp (somehow)
>
> Also note something which may be important in your scenario
>
> If you do a "null update" (where you set a column to the value it
> already has) DB2 WILL update the row change timestamp (for now - this
> will be "fixed" soon) but will NOT update the page RBA. So you can get
> inconsistent results
>
> Did you know, you can also select a row change timestamp from a table
> that does NOT have a row change timestamp column? If you do that, then
> the timestamp always comes from the page rba!!
>
> Phil Grainger
> CA
>
> ------------------------------------------------------------------------
> *From:* DB2 Data Base Discussion List on behalf of Hunter Cobb
> *Sent:* Sat 22/11/2008 23:45
> *To:* [login to unmask email]
> *Subject:* [DB2-L] Row change timestamp default value
>
> Version 9.1 for z/OS: I've been working with a row change timestamp column
> added to a table some time after table creation, after row insertion /
> update, but before a REORG TABLESPACE has been done. This is in a non-data
> sharing environment. When the row change timestamp column is retrieved, a
> value is returned that appears to be the timestamp associated with the
> page
> (as advertised). What I'm puzzled by is: where is this page timestamp
> coming
> from? It can't be the PGLOGRBA, since that is a log RBA, and not an
> LRSN in
> a non-data sharing environment.
>
> Hunter Cobb
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA
> < 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
>
>
> ------------------------------------------------------------------------
>
> *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

Douwe van Sluis

Re: Row change timestamp default value
(in response to Hunter Cobb)
Hello Hunter,

If the row change timestamp column has no value of its own, DB2 gives
the PGLOGRBA. This represents the last change of that page. So if no
REORG is run and an other row on the same page is changed, the PGLOGRBA
is also changed. Retrieving the same row change timestamp column again
would give a different value than the first time.
So, my advice is to always try to run a REORG after adding a column.
And with a row change timestamp column, be very careful if using this
with a functional scope.

Vriendelijke groet,
Douwe van Sluis




-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
Hunter Cobb
Verzonden: zondag 23 november 2008 0:46
Aan: [login to unmask email]
Onderwerp: [DB2-L] Row change timestamp default value


Version 9.1 for z/OS: I've been working with a row change timestamp
column added to a table some time after table creation, after row
insertion / update, but before a REORG TABLESPACE has been done. This is
in a non-data sharing environment. When the row change timestamp column
is retrieved, a value is returned that appears to be the timestamp
associated with the page (as advertised). What I'm puzzled by is: where
is this page timestamp coming from? It can't be the PGLOGRBA, since that
is a log RBA, and not an LRSN in a non-data sharing environment.

Hunter Cobb

______________________________________________________________________

* 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

______________________________________________________________________

* 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

Hunter Cobb

Re: Row change timestamp default value
(in response to Douwe van Sluis)
Hi Douwe,

Thanks for your response. I understand that the PGLOGRBA represents the
latest change to a page. What I'm trying to understand is how we get
from a PGLOGRBA to an actual timestamp value.

Hunter

Douwe van Sluis wrote:
> Hello Hunter,
>
> If the row change timestamp column has no value of its own, DB2 gives
> the PGLOGRBA. This represents the last change of that page. So if no
> REORG is run and an other row on the same page is changed, the PGLOGRBA
> is also changed. Retrieving the same row change timestamp column again
> would give a different value than the first time.
> So, my advice is to always try to run a REORG after adding a column.
> And with a row change timestamp column, be very careful if using this
> with a functional scope.
>
> Vriendelijke groet,
> Douwe van Sluis
>
>
>
>
> -----Oorspronkelijk bericht-----
> Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
> Hunter Cobb
> Verzonden: zondag 23 november 2008 0:46
> Aan: [login to unmask email]
> Onderwerp: [DB2-L] Row change timestamp default value
>
>
> Version 9.1 for z/OS: I've been working with a row change timestamp
> column added to a table some time after table creation, after row
> insertion / update, but before a REORG TABLESPACE has been done. This is
> in a non-data sharing environment. When the row change timestamp column
> is retrieved, a value is returned that appears to be the timestamp
> associated with the page (as advertised). What I'm puzzled by is: where
> is this page timestamp coming from? It can't be the PGLOGRBA, since that
> is a log RBA, and not an LRSN in a non-data sharing environment.
>
> Hunter Cobb
>
> ______________________________________________________________________
>
> * 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
>
> ______________________________________________________________________
>
> * 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
>
>

______________________________________________________________________

* 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

Phil Grainger

Re: Row change timestamp default value
(in response to Hunter Cobb)
Well, I was sort of hoping that by stepping back, someone like Roger might come forward with the answer and enlighten us all......

But it is the weekend

Phil G

________________________________

From: DB2 Data Base Discussion List on behalf of Hunter Cobb
Sent: Sun 23/11/2008 07:41
To: [login to unmask email]
Subject: Re: [DB2-L] Row change timestamp default value


Well, my question focuses on the (somehow) part of your response. The only thing I could think of was that log records have a store clock field, and the log control interval definition (LCID) has a timestamp. It's hard to believe that DB2 would read the log to obtain a timestamp for a page; after all the log could have been archived.

The question of the source of the timestamp becomes even more acute if you are dealing with a table with no row change timestamp column.

Hunter

Grainger, Phil wrote:

Hi Hunter

So far as I know, it does indeed come from the page RBA - which DB2 manages to turn into a timestamp (somehow)

Also note something which may be important in your scenario

If you do a "null update" (where you set a column to the value it already has) DB2 WILL update the row change timestamp (for now - this will be "fixed" soon) but will NOT update the page RBA. So you can get inconsistent results

Did you know, you can also select a row change timestamp from a table that does NOT have a row change timestamp column? If you do that, then the timestamp always comes from the page rba!!

Phil Grainger
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Hunter Cobb
Sent: Sat 22/11/2008 23:45
To: [login to unmask email]
Subject: [DB2-L] Row change timestamp default value



Version 9.1 for z/OS: I've been working with a row change timestamp column
added to a table some time after table creation, after row insertion /
update, but before a REORG TABLESPACE has been done. This is in a non-data
sharing environment. When the row change timestamp column is retrieved, a
value is returned that appears to be the timestamp associated with the page
(as advertised). What I'm puzzled by is: where is this page timestamp coming
from? It can't be the PGLOGRBA, since that is a log RBA, and not an LRSN in
a non-data sharing environment.

Hunter Cobb

______________________________________________________________________

* 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




________________________________

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

Roger Miller

Re: Row change timestamp default value
(in response to Phil Grainger)
I'm happy to help where I can with questions about how to use DB2, but I
avoid answers to questions about how DB2 works or DB2 internals. There are
lots of reasons, but the big one is that we may need to make a change for a
release or a PTF, and then your program does not work any more. Please use
the externals whenever possible, so your programs keep working.

If you ask questions about the externals, then you can get better answers.

Roger Miller, DB2 for z/OS

On Mon, 24 Nov 2008 00:27:49 -0000, Grainger, Phil <[login to unmask email]>
wrote:

>Well, I was sort of hoping that by stepping back, someone like Roger might
come forward with the answer and enlighten us all......
>
>But it is the weekend
>
>Phil G
>
>________________________________
>
>From: DB2 Data Base Discussion List on behalf of Hunter Cobb
>Sent: Sun 23/11/2008 07:41
>To: [login to unmask email]
>Subject: Re: [DB2-L] Row change timestamp default value
>
>
>Well, my question focuses on the (somehow) part of your response. The only
thing I could think of was that log records have a store clock field, and the log
control interval definition (LCID) has a timestamp. It's hard to believe that DB2
would read the log to obtain a timestamp for a page; after all the log could
have been archived.
>
>The question of the source of the timestamp becomes even more acute if
you are dealing with a table with no row change timestamp column.
>
>Hunter
>
>Grainger, Phil wrote:
>
> Hi Hunter
>
> So far as I know, it does indeed come from the page RBA - which
DB2 manages to turn into a timestamp (somehow)
>
> Also note something which may be important in your scenario
>
> If you do a "null update" (where you set a column to the value it
already has) DB2 WILL update the row change timestamp (for now - this will
be "fixed" soon) but will NOT update the page RBA. So you can get
inconsistent results
>
> Did you know, you can also select a row change timestamp from a
table that does NOT have a row change timestamp column? If you do that,
then the timestamp always comes from the page rba!!
>
> Phil Grainger
> CA
>
>________________________________
>
> From: DB2 Data Base Discussion List on behalf of Hunter Cobb
> Sent: Sat 22/11/2008 23:45
> To: [login to unmask email]
> Subject: [DB2-L] Row change timestamp default value
>
>
>
> Version 9.1 for z/OS: I've been working with a row change
timestamp column
> added to a table some time after table creation, after row insertion /
> update, but before a REORG TABLESPACE has been done. This is in a
non-data
> sharing environment. When the row change timestamp column is
retrieved, a
> value is returned that appears to be the timestamp associated with
the page
> (as advertised). What I'm puzzled by is: where is this page
timestamp coming
> from? It can't be the PGLOGRBA, since that is a log RBA, and not an
LRSN in
> a non-data sharing environment.
>
> Hunter Cobb
>
>

______________________________________________________________________

* 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

Hunter Cobb

Re: Row change timestamp default value
(in response to Roger Miller)
Hi Roger,

I appreciate your taking the time to respond to my question, and also
appreciate the sensitivity to questions about internals. My main concern
with un-REORGed tables to which a row change timestamp has been added,
or requests for row change timestamp information for tables that don't
contain a row change timestamp column, was primarily one of performance.
If the source of the row change information for rows not containing a
row change timestamp is some data on the underlying page, then
(presumably) the impact of requesting the (admittedly dubious quality)
information is probably minimal. If, on the other hand, DB2 has to do
something involving additional I/O, or accessing some other resource,
then the importance of REORGing goes beyond the concern of merely
retrieving low-quality data.

Thanks for any additional insight you might be able to provide.

Hunter Cobb
-----------------------
Roger Miller wrote:
> I'm happy to help where I can with questions about how to use DB2, but I
> avoid answers to questions about how DB2 works or DB2 internals. There are
> lots of reasons, but the big one is that we may need to make a change for a
> release or a PTF, and then your program does not work any more. Please use
> the externals whenever possible, so your programs keep working.
>
> If you ask questions about the externals, then you can get better answers.
>
> Roger Miller, DB2 for z/OS
>
> On Mon, 24 Nov 2008 00:27:49 -0000, Grainger, Phil <[login to unmask email]>
> wrote:
>
>
>> Well, I was sort of hoping that by stepping back, someone like Roger might
>>
> come forward with the answer and enlighten us all......
>
>> But it is the weekend
>>
>> Phil G
>>
>> ________________________________
>>
>> From: DB2 Data Base Discussion List on behalf of Hunter Cobb
>> Sent: Sun 23/11/2008 07:41
>> To: [login to unmask email]
>> Subject: Re: [DB2-L] Row change timestamp default value
>>
>>
>> Well, my question focuses on the (somehow) part of your response. The only
>>
> thing I could think of was that log records have a store clock field, and the log
> control interval definition (LCID) has a timestamp. It's hard to believe that DB2
> would read the log to obtain a timestamp for a page; after all the log could
> have been archived.
>
>> The question of the source of the timestamp becomes even more acute if
>>
> you are dealing with a table with no row change timestamp column.
>
>> Hunter
>>
>> Grainger, Phil wrote:
>>
>> Hi Hunter
>>
>> So far as I know, it does indeed come from the page RBA - which
>>
> DB2 manages to turn into a timestamp (somehow)
>
>>
>> Also note something which may be important in your scenario
>>
>> If you do a "null update" (where you set a column to the value it
>>
> already has) DB2 WILL update the row change timestamp (for now - this will
> be "fixed" soon) but will NOT update the page RBA. So you can get
> inconsistent results
>
>>
>> Did you know, you can also select a row change timestamp from a
>>
> table that does NOT have a row change timestamp column? If you do that,
> then the timestamp always comes from the page rba!!
>
>>
>> Phil Grainger
>> CA
>>
>> ________________________________
>>
>> From: DB2 Data Base Discussion List on behalf of Hunter Cobb
>> Sent: Sat 22/11/2008 23:45
>> To: [login to unmask email]
>> Subject: [DB2-L] Row change timestamp default value
>>
>>
>>
>> Version 9.1 for z/OS: I've been working with a row change
>>
> timestamp column
>
>> added to a table some time after table creation, after row insertion /
>> update, but before a REORG TABLESPACE has been done. This is in a
>>
> non-data
>
>> sharing environment. When the row change timestamp column is
>>
> retrieved, a
>
>> value is returned that appears to be the timestamp associated with
>>
> the page
>
>> (as advertised). What I'm puzzled by is: where is this page
>>
> timestamp coming
>
>> from? It can't be the PGLOGRBA, since that is a log RBA, and not an
>>
> LRSN in
>
>> a non-data sharing environment.
>>
>> Hunter Cobb
>>
>>
>>
>
> ______________________________________________________________________
>
> * 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
>
>

______________________________________________________________________

* 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

Roger Miller

Re: Row change timestamp default value
(in response to Hunter Cobb)
If you have alter added a column to a table and not reorged, then
performance is likely to suffer. Here is my thought about the primary cause.
Every update now is expanding the row size. If the table is compressed, the
dictionary was built without the new column, so this data is unlikely to
compress well. If the rows were already long, with adequate free space and
a fairly low update frequency, then you might survive.

The second issue would be not gaining the benefit expected for optimistic
locking, as your granularity is only the page, not the row. Again, reorg.

Your concern is probably not a significant one, but our performance testing
concentrates on the standard path, with the row timestamps populated.
In a performance situation, the response is likely to ask for standard practice,
doing the reorg, before looking much into the situation. Problem solved.

<last workday of the week attempt at humor in opinion>
Adding a row timestamp without reorging? The response I'd like to make
would be Clint Eastwood's voice saying, "Do you feel lucky?"
</last workday of the week attempt at humor in opinion>

Roger Miller

On Mon, 24 Nov 2008 16:26:33 -0700, Hunter Cobb
<[login to unmask email]> wrote:

>Hi Roger,
>
>I appreciate your taking the time to respond to my question, and also
>appreciate the sensitivity to questions about internals. My main concern
>with un-REORGed tables to which a row change timestamp has been added,
>or requests for row change timestamp information for tables that don't
>contain a row change timestamp column, was primarily one of performance.
>If the source of the row change information for rows not containing a
>row change timestamp is some data on the underlying page, then
>(presumably) the impact of requesting the (admittedly dubious quality)
>information is probably minimal. If, on the other hand, DB2 has to do
>something involving additional I/O, or accessing some other resource,
>then the importance of REORGing goes beyond the concern of merely
>retrieving low-quality data.
>
>Thanks for any additional insight you might be able to provide.
>
>Hunter Cobb
>-----------------------
>Roger Miller wrote:
>> I'm happy to help where I can with questions about how to use DB2, but I
>> avoid answers to questions about how DB2 works or DB2 internals. There
are
>> lots of reasons, but the big one is that we may need to make a change for
a
>> release or a PTF, and then your program does not work any more. Please
use
>> the externals whenever possible, so your programs keep working.
>>
>> If you ask questions about the externals, then you can get better answers.
>>
>> Roger Miller, DB2 for z/OS
>>
>> On Mon, 24 Nov 2008 00:27:49 -0000, Grainger, Phil
<[login to unmask email]>
>> wrote:
>>
>>
>>> Well, I was sort of hoping that by stepping back, someone like Roger might
>>>
>> come forward with the answer and enlighten us all......
>>
>>> But it is the weekend
>>>
>>> Phil G
>>>
>>> ________________________________
>>>
>>> From: DB2 Data Base Discussion List on behalf of Hunter Cobb
>>> Sent: Sun 23/11/2008 07:41
>>> To: [login to unmask email]
>>> Subject: Re: [DB2-L] Row change timestamp default value
>>>
>>>
>>> Well, my question focuses on the (somehow) part of your response. The
only
>>>
>> thing I could think of was that log records have a store clock field, and the
log
>> control interval definition (LCID) has a timestamp. It's hard to believe that
DB2
>> would read the log to obtain a timestamp for a page; after all the log could
>> have been archived.
>>
>>> The question of the source of the timestamp becomes even more acute if
>>>
>> you are dealing with a table with no row change timestamp column.
>>
>>> Hunter
>>>
>>> Grainger, Phil wrote:
>>>
>>> Hi Hunter
>>>
>>> So far as I know, it does indeed come from the page RBA - which
>>>
>> DB2 manages to turn into a timestamp (somehow)
>>
>>>
>>> Also note something which may be important in your scenario
>>>
>>> If you do a "null update" (where you set a column to the value it
>>>
>> already has) DB2 WILL update the row change timestamp (for now - this
will
>> be "fixed" soon) but will NOT update the page RBA. So you can get
>> inconsistent results
>>
>>>
>>> Did you know, you can also select a row change timestamp from a
>>>
>> table that does NOT have a row change timestamp column? If you do that,
>> then the timestamp always comes from the page rba!!
>>
>>>
>>> Phil Grainger
>>> CA
>>>
>>> ________________________________
>>>
>>> From: DB2 Data Base Discussion List on behalf of Hunter Cobb
>>> Sent: Sat 22/11/2008 23:45
>>> To: [login to unmask email]
>>> Subject: [DB2-L] Row change timestamp default value
>>>
>>>
>>>
>>> Version 9.1 for z/OS: I've been working with a row change
>>>
>> timestamp column
>>
>>> added to a table some time after table creation, after row insertion /
>>> update, but before a REORG TABLESPACE has been done. This is in a
>>>
>> non-data
>>
>>> sharing environment. When the row change timestamp column is
>>>
>> retrieved, a
>>
>>> value is returned that appears to be the timestamp associated with
>>>
>> the page
>>
>>> (as advertised). What I'm puzzled by is: where is this page
>>>
>> timestamp coming
>>
>>> from? It can't be the PGLOGRBA, since that is a log RBA, and not an
>>>
>> LRSN in
>>
>>> a non-data sharing environment.
>>>
>>> Hunter Cobb
>>>
>>>
>>>
>>
>>
__________________________________________________________________

______________________________________________________________________

* 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