473,899 Members | 4,542 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to find table modification time?


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.
Oct 10 '06 #1
9 17038

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.
Oct 10 '06 #2
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.
Oct 10 '06 #3
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: 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.
Oct 10 '06 #4
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: 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.
Oct 10 '06 #5
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
Oct 11 '06 #6
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: 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.
Oct 11 '06 #7
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.
Oct 11 '06 #8
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: 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.
Oct 11 '06 #9
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
Oct 12 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
9700
by: Jeff J. | last post by:
The basic concept is a music playlist that I'm trying to write as a table inside a div. I have Song and Playlist 'classes'. The Song class has a Write() method, as does the Playlist. The Song.Write() boils down to this: var tr = document.createElement("TR"); var td = document.createElement("TD"); tr.id = this.ID;
3
2025
by: stan | last post by:
I am working on some documentation in html format and I would really like to display the date the html file, itself was modified. I am writing my documentation in vi and the html server involved is Apache. This is a conversion effort from MS Frontpage 2000. In Frontpage, there's a feature that allows the document's modification date to be displayed in the document, but I tried this with a javascript that I downloaded via the Internet, but...
3
6092
by: mahajan.sanjeev | last post by:
Hi All, I am using a SQLTransaction to insert records into a table. At one time, there are 5000 or more records to be inserted one by one. It takes some 20-25 mins for the entire process to run. Another application accesses the same table. As long as the insert process within the transaction isn't completed, the second application is not getting any response from the server. I
3
1885
by: John Baker | last post by:
Hi: At the outset let me admit that I screwed up! I have built a rather elaborate set of forms and sub forms starting with a client table, and going down to PO and Line item. This works very well EXCEPT that when I started the design I failed to consider that client records would be added to the client table in random order. The table is indexed on client ID which is an auto number field, and as a result the table is in random order,...
3
1503
by: Ray | last post by:
I had a query of table and achived it as a table a week's ago. Now I need to find out if there are any modifications on the records against the achived table. The modification might be one or several fields within the records. There are no id fields in the tables. Can someone advise how to accomplish this task. Thanks, Ray
3
9823
by: shahram.shirazi | last post by:
Hi guys, I was wondering if someone could help me a bit here. Im trying to desing an electronic register system for a school. In terms of the table design, I obviously need a Student Details table with such attributes as ID, FNAME, SNAME, Age, Tutor Group, Class, Dependant. The bit i'm having trouble conceptualising is the system needs to be
6
9780
by: Ivan | last post by:
Hello to all and thanks for answer to my topics. I made one stored procedure that delete one table, but when call/execute the procedure this show SQL0532N A parent row cannot be deleted because the relationship "TXN_TRANSACTION.FK_SCLI " restricts the deletion. Then in the procedure it adds one delete of the foreign keys. This it
9
4584
by: erick-flores | last post by:
If you have access to the database that the linked table is in, modify it there. You can't modify a linked table. Alternatively, you can import the linked table, then it won't be linked any more and you can modify it. There are potential problems with this strategy, as you will be working on a copy of the original table, so any new data that goes into the original table from elsewhere will not be reflected in your database. Hello all
3
7755
by: dmorand | last post by:
I'm very new to stored procedures so this is probably something very dumb. I want to pass a name of a table to be created to my stored procedure. I'm using the variable @tableName in the code below but I'm getting an error: Server: Msg 170, Level 15, State 1, Procedure usp_CodeGreyData, Line 22 Line 22: Incorrect syntax near '@tableName'. Server: Msg 137, Level 15, State 1, Procedure usp_CodeGreyData, Line 29 Must declare the variable...
0
9843
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11272
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10863
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9666
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5887
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6081
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4720
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4300
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3317
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.