How to find table modification time?

[Hemant Shah]

How do I find out when the table was modified?
When I look at syscat.tables it only lists creation time.



--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMailshah@ xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[Hemant Shah]

Re: How to find table modification time?


What I am meant was how to find out time when data in the table was modified
(insert/update/delete).

While stranded on information super highway Hemant Shah wrote:

>
How do I find out when the table was modified?
When I look at syscat.tables it only lists creation time.
>
>
>
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMailshah@ xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMailshah@ xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[Sathyaram Sannasi]

Re: How to find table modification time?

AFAIK, there is no 'inbuilt' feature to support this ..

You will have to maintain it 'manually', say using an additional field
in your table, or capture the data changes using triggers or may be one
row/table table to record data change times ..

Sathyaram



Hemant Shah wrote:

What I am meant was how to find out time when data in the table was modified
(insert/update/delete).
>
While stranded on information super highway Hemant Shah wrote:

How do I find out when the table was modified?
When I look at syscat.tables it only lists creation time.



--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMailshah@ xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

>
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMailshah@ xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[jefftyzzer]

Re: How to find table modification time?

I agree w/ Sathyaram, and just to amplify what he's said, a typical way
to do what you want is to create columns in each table such as the
following

ROW_CREATE_TS NOT NULL DEFAULT CURRENT_TIMESTA MP
ROW_CREATE_USER NOT NULL DEFAULT CURRENT_USER

ROW_UPDATE_TS NOT NULL DEFAULT CURRENT_TIMESTA MP
ROW_UPDATE_USER NOT NULL DEFAULT CURRENT_USER

the latter two being populated by a trigger on subsequent updates
(unless you're using ORM, your flavor of which may have a centralized,
proprietary, way of doing this).

--Jeff

Sathyaram Sannasi wrote:

AFAIK, there is no 'inbuilt' feature to support this ..
>
You will have to maintain it 'manually', say using an additional field
in your table, or capture the data changes using triggers or may be one
row/table table to record data change times ..
>
Sathyaram
>
>
>
Hemant Shah wrote:

What I am meant was how to find out time when data in the table was modified
(insert/update/delete).

While stranded on information super highway Hemant Shah wrote:

>
How do I find out when the table was modified?
When I look at syscat.tables it only lists creation time.
>
>
>
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMailshah@ xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMailshah@ xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[Hemant Shah]

Re: How to find table modification time?

While stranded on information super highway jefftyzzer wrote:

I agree w/ Sathyaram, and just to amplify what he's said, a typical way
to do what you want is to create columns in each table such as the
following
>
ROW_CREATE_TS NOT NULL DEFAULT CURRENT_TIMESTA MP
ROW_CREATE_USER NOT NULL DEFAULT CURRENT_USER
>
ROW_UPDATE_TS NOT NULL DEFAULT CURRENT_TIMESTA MP
ROW_UPDATE_USER NOT NULL DEFAULT CURRENT_USER
>
the latter two being populated by a trigger on subsequent updates
(unless you're using ORM, your flavor of which may have a centralized,
proprietary, way of doing this).
>
--Jeff

Thanks all for the info. My client is migrating from VSAM on mainframe to
DB2 on AIX. On mainframe they were able to tell when the file changed, and
they were looking for similar feature for DB2 table.

I think just one column with default timestamp should satisfy them.

>
Sathyaram Sannasi wrote:

>AFAIK, there is no 'inbuilt' feature to support this ..
>>
>You will have to maintain it 'manually', say using an additional field
>in your table, or capture the data changes using triggers or may be one
>row/table table to record data change times ..
>>
>Sathyaram
>>
>>
>>
>Hemant Shah wrote:

What I am meant was how to find out time when data in the table was modified
(insert/update/delete).
>
While stranded on information super highway Hemant Shah wrote:
>
How do I find out when the table was modified?
When I look at syscat.tables it only lists creation time.
>
>
>
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMailshah@ xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
>
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMailshah@ xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

>

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMailshah@ xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[Knut Stolze]

Re: How to find table modification time?

Hemant Shah wrote:

Thanks all for the info. My client is migrating from VSAM on mainframe to
DB2 on AIX. On mainframe they were able to tell when the file changed,
and they were looking for similar feature for DB2 table.

You could do something similar on AIX as well: find the containers of the
tablespace in which the table resides and check the container's stats.
However, that is highly unreliable (and I would think the same applies to
mainframe as well) because not all data may have been written to disk yet,
or changes are written to disk that may be rolled back later on.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Phil Sherman]

Re: How to find table modification time?

Row timestamps show when the data was modified but, for large tables,
may not be a good way to track the last modified time. Either you will
need an index on the column or a tablescan to determine the most recent
update timestamp.

Phil Sherman



Hemant Shah wrote:

While stranded on information super highway jefftyzzer wrote:

>I agree w/ Sathyaram, and just to amplify what he's said, a typical way
>to do what you want is to create columns in each table such as the
>following
>>
>ROW_CREATE_T S NOT NULL DEFAULT CURRENT_TIMESTA MP
>ROW_CREATE_USE R NOT NULL DEFAULT CURRENT_USER
>>
>ROW_UPDATE_T S NOT NULL DEFAULT CURRENT_TIMESTA MP
>ROW_UPDATE_USE R NOT NULL DEFAULT CURRENT_USER
>>
>the latter two being populated by a trigger on subsequent updates
>(unless you're using ORM, your flavor of which may have a centralized,
>proprietary, way of doing this).
>>
>--Jeff

>
>
Thanks all for the info. My client is migrating from VSAM on mainframe to
DB2 on AIX. On mainframe they were able to tell when the file changed, and
they were looking for similar feature for DB2 table.
>
I think just one column with default timestamp should satisfy them.
>
>

>Sathyaram Sannasi wrote:

>>AFAIK, there is no 'inbuilt' feature to support this ..
>>>
>>You will have to maintain it 'manually', say using an additional field
>>in your table, or capture the data changes using triggers or may be one
>>row/table table to record data change times ..
>>>
>>Sathyaram
>>>
>>>
>>>
>>Hemant Shah wrote:
>>>What I am meant was how to find out time when data in the table was modified
>>>(insert/update/delete).
>>>>
>>>While stranded on information super highway Hemant Shah wrote:
>>>>How do I find out when the table was modified?
>>>>When I look at syscat.tables it only lists creation time.
>>>>>
>>>>>
>>>>>
>>>>--
>>>>Hemant Shah /"\ ASCII ribbon campaign
>>>>E-mail: NoJunkMailshah@ xnet.com \ / ---------------------
>>>> X against HTML mail
>>>>TO REPLY, REMOVE NoJunkMail / \ and postings
>>>>FROM MY E-MAIL ADDRESS.
>>>>-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
>>>>I haven't lost my mind, Above opinions are mine only.
>>>>it's backed up on tape somewhere. Others can have their own.
>>>--
>>>Hemant Shah /"\ ASCII ribbon campaign
>>>E-mail: NoJunkMailshah@ xnet.com \ / ---------------------
>>> X against HTML mail
>>>TO REPLY, REMOVE NoJunkMail / \ and postings
>>>FROM MY E-MAIL ADDRESS.
>>>-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
>>>I haven't lost my mind, Above opinions are mine only.
>>>it's backed up on tape somewhere. Others can have their own.

>

[Hemant Shah]

Re: How to find table modification time?

While stranded on information super highway Knut Stolze wrote:

Hemant Shah wrote:
>

> Thanks all for the info. My client is migrating from VSAM on mainframe to
> DB2 on AIX. On mainframe they were able to tell when the file changed,
> and they were looking for similar feature for DB2 table.

>
You could do something similar on AIX as well: find the containers of the
tablespace in which the table resides and check the container's stats.
However, that is highly unreliable (and I would think the same applies to
mainframe as well) because not all data may have been written to disk yet,
or changes are written to disk that may be rolled back later on.

I though about that, but they are using only one tablespace for all the
tables.

>
--
Knut Stolze
DB2 Information Integration Development
IBM Germany

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMailshah@ xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[Hemant Shah]

Re: How to find table modification time?

While stranded on information super highway Phil Sherman wrote:

Row timestamps show when the data was modified but, for large tables,
may not be a good way to track the last modified time. Either you will
need an index on the column or a tablescan to determine the most recent
update timestamp.
>
Phil Sherman

Yes, I would need to create index on the column.

I have another question about the column. If I create a column with
default value of current timestamp. Will the timestamp get automatically
updated when the data in the row is updated?

I know it will get default value when the row is inserted.

>
>
>
Hemant Shah wrote:

>While stranded on information super highway jefftyzzer wrote:

>>I agree w/ Sathyaram, and just to amplify what he's said, a typical way
>>to do what you want is to create columns in each table such as the
>>following
>>>
>>ROW_CREATE_ TS NOT NULL DEFAULT CURRENT_TIMESTA MP
>>ROW_CREATE_US ER NOT NULL DEFAULT CURRENT_USER
>>>
>>ROW_UPDATE_ TS NOT NULL DEFAULT CURRENT_TIMESTA MP
>>ROW_UPDATE_US ER NOT NULL DEFAULT CURRENT_USER
>>>
>>the latter two being populated by a trigger on subsequent updates
>>(unless you're using ORM, your flavor of which may have a centralized,
>>proprietary , way of doing this).
>>>
>>--Jeff

>>
>>
> Thanks all for the info. My client is migrating from VSAM on mainframe to
> DB2 on AIX. On mainframe they were able to tell when the file changed, and
> they were looking for similar feature for DB2 table.
>>
> I think just one column with default timestamp should satisfy them.
>>
>>

>>Sathyaram Sannasi wrote:
>>>AFAIK, there is no 'inbuilt' feature to support this ..
>>>>
>>>You will have to maintain it 'manually', say using an additional field
>>>in your table, or capture the data changes using triggers or may be one
>>>row/table table to record data change times ..
>>>>
>>>Sathyaram
>>>>
>>>>
>>>>
>>>Hemant Shah wrote:
>>>>What I am meant was how to find out time when data in the table was modified
>>>>(insert/update/delete).
>>>>>
>>>>While stranded on information super highway Hemant Shah wrote:
>>>>>How do I find out when the table was modified?
>>>>>When I look at syscat.tables it only lists creation time.
>>>>>>
>>>>>>
>>>>>>
>>>>>--
>>>>>Hemant Shah /"\ ASCII ribbon campaign
>>>>>E-mail: NoJunkMailshah@ xnet.com \ / ---------------------
>>>>> X against HTML mail
>>>>>TO REPLY, REMOVE NoJunkMail / \ and postings
>>>>>FROM MY E-MAIL ADDRESS.
>>>>>-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
>>>>>I haven't lost my mind, Above opinions are mine only.
>>>>>it's backed up on tape somewhere. Others can have their own.
>>>>--
>>>>Hemant Shah /"\ ASCII ribbon campaign
>>>>E-mail: NoJunkMailshah@ xnet.com \ / ---------------------
>>>> X against HTML mail
>>>>TO REPLY, REMOVE NoJunkMail / \ and postings
>>>>FROM MY E-MAIL ADDRESS.
>>>>-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
>>>>I haven't lost my mind, Above opinions are mine only.
>>>>it's backed up on tape somewhere. Others can have their own.

>>

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMailshah@ xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[Knut Stolze]

Re: How to find table modification time?

Hemant Shah wrote:

I have another question about the column. If I create a column with
default value of current timestamp. Will the timestamp get automatically
updated when the data in the row is updated?

No, it won't. Either the UPDATE statements take care of it (unreliable) or
you add a trigger (reliable).

--
Knut Stolze
DB2 Information Integration Development
IBM Germany