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: No************@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]

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: No************@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: No************@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]

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: No************@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: No************@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]

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_TIMESTAMP
ROW_CREATE_USER NOT NULL DEFAULT CURRENT_USER

ROW_UPDATE_TS NOT NULL DEFAULT CURRENT_TIMESTAMP
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: No************@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: No************@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]

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_TIMESTAMP
ROW_CREATE_USER NOT NULL DEFAULT CURRENT_USER

ROW_UPDATE_TS NOT NULL DEFAULT CURRENT_TIMESTAMP
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: No************@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: No************@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: No************@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]

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]

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_TS NOT NULL DEFAULT CURRENT_TIMESTAMP
ROW_CREATE_USER NOT NULL DEFAULT CURRENT_USER

ROW_UPDATE_TS NOT NULL DEFAULT CURRENT_TIMESTAMP
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: No************@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: No************@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]

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: No************@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]

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_TIMESTAMP
ROW_CREATE_USER NOT NULL DEFAULT CURRENT_USER

ROW_UPDATE_TS NOT NULL DEFAULT CURRENT_TIMESTAMP
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: No************@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: No************@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: No************@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]

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