How to get the timestamp value in the data page?

HI, When I update a row, the timestampe value in the page will increase? How to get the timestamp value in the application, I need this value. thanks.

You cannot get the timestamp value from the page on which a row resides, at least not easily. Here's the basic method you would have to go through: You would have to get the row's ROWID, convert it to an ordinal page number, convert the ordinal page to a physical page number by going through the table's extent list in sysmaster, determining which entent it was on and the offset within the extent. Then you would have to fetch the table's partition header page's header records from sysmaster and extract the timestamp from there. Once you have that, you DO NOT known when the page was modified because the timestamp on a page is just like a serial number it is a sequential number that is incremented every time any page in the server instance is modified, so you cannot map it to a clock time. And, even if you could, you only know that SOME row on the page was modified not which row (unless of course there is only one row on a page). Why would you want the page header timestamp? If what you want is a timestamp of when a row was modified, then do the following: - Add a timestamp column to your table type DATETIME YEAR TO FRACTION(5), set USEOSTIME in your ONCONFIG file to '1', make the DEFAULT for the columns CURRENT YEAR TO FRACTION(5), create a FOR UPDATE trigger to change the timestamp in the FOR EVERY ROW section whenever the row is updated. - - ALTER TABLE mytable ADD CRCOLS; - Adds two hidden columns, an indicator of which server modified a row (for updates from updatable secondary servers), and a timestamp. The engine will maintain this timestamp and you won't have to modify your applications to know about it if they don't have to since it is hidden. If you want to have a cheap way to see IF a row was modified to easily support optimistic locking, then either: - ALTER TABLE mytable ADD VERCOLS; - Adds two hidden columns, a checksum and a version number - ALTER TABLE mytable ADD CRCOLS; - As above. Check out the hidden columns in the Guide to SQL Syntax, Administrator's Reference Guide, and Enterprise Replication manuals. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, May 29, 2013 at 4:28 AM, CHUAN LU <luchuan@cn.ibm.com> wrote: > HI, > > When I update a row, the timestampe value in the page will increase? How to > get the timestamp value in the application, I need this value. > > thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0141aa8a88c29504ddd92bef

Are you aware that the page "timestamp" is not a real timestamp (meaning it's not the clock time of when the insert happens)? On May 29, 2013 9:29 AM, "CHUAN LU" <luchuan@cn.ibm.com> wrote: > HI, > > When I update a row, the timestampe value in the page will increase? How to > get the timestamp value in the application, I need this value. > > thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec548a4932c792b04dddaa3a5

If you are trying to determine when a row was changed like in DB2: CREATE TABLE T1 (C1 INTEGER NOT NULL); INSERT INTO T1 VALUES (1); ALTER TABLE T1 ADD COLUMN C2 NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP; SELECT T1.C2 FROM T1 WHERE T1.C1 = 1; Because the ROW CHANGE TIMESTAMP column was added after the data was inserted, the following statement returns the time that the page was last modified: SELECT T1.C2 FROM T1 WHERE T1.C1 = 1; Informix does not have this functionnality directly. You could implement it using an extra column with a trigger. However, you should be careful if you are using an existing application since it will be an extra column that you have to deal with in inserts, selects and updates. The timestamp in each page is internal to Informix and is not accessible directly from an application. It is mainly used to verify the integrity of a page since it is located at the bottom of the page and in the page header and is also used for backups to decide whether a page is elligible to be backed up or not; if the timestamp is higher than the time the backup was launched, the system has to get the page from the physical log or the temporary space that contains the copy of the physical log if a checkpoint has gone by. basically, it is for internal data management use. What you may do is use is VERCOLS. When row versioning is enabled, ifx_row_version is incremented by one each time the row is updated. This might not me really what you want. Cordialement, Regards, Khaled Bentebal Directeur Général - ConsultiX Président UGIF - User Group Informix France IIUG - Board of Directors Tél: 33 (0) 1 39 12 18 00 Fax: 33 (0) 1 39 12 18 18 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 29/05/13 10:28, CHUAN LU a écrit : > HI, > > When I update a row, the timestampe value in the page will increase? How to > get the timestamp value in the application, I need this value. > > thanks. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

You can use CRCOLS From: "Khaled Bentebal" <khaled.bentebal@consult-ix.fr> To: ids@iiug.org, Date: 05/29/2013 10:23 AM Subject: Re: How to get the timestamp value in the data.... [30383] Sent by: ids-bounces@iiug.org If you are trying to determine when a row was changed like in DB2: CREATE TABLE T1 (C1 INTEGER NOT NULL); INSERT INTO T1 VALUES (1); ALTER TABLE T1 ADD COLUMN C2 NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP; SELECT T1.C2 FROM T1 WHERE T1.C1 =3D 1; Because the ROW CHANGE TIMESTAMP column was added after the data was inserted, the following statement returns the time that the page was last modified: SELECT T1.C2 FROM T1 WHERE T1.C1 =3D 1; Informix does not have this functionnality directly. You could implement it using an extra column with a trigger. However, you should be careful if you are using an existing application since it= will be an extra column that you have to deal with in inserts, selects and updates. The timestamp in each page is internal to Informix and is not accessibl= e directly from an application. It is mainly used to verify the integrity= of a page since it is located at the bottom of the page and in the page= header and is also used for backups to decide whether a page is elligible to be backed up or not; if the timestamp is higher than the time the backup was launched, the system has to get the page from the physical log or the temporary space that contains the copy of the physical log if a checkpoint has gone by. basically, it is for internal= data management use. What you may do is use is VERCOLS. When row versioning is enabled, ifx_row_version is incremented by one each time the row is updated. Thi= s might not me really what you want. Cordialement, Regards, Khaled Bentebal Directeur G=E9n=E9ral - ConsultiX Pr=E9sident UGIF - User Group Informix France IIUG - Board of Directors T=E9l: 33 (0) 1 39 12 18 00 Fax: 33 (0) 1 39 12 18 18 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 29/05/13 10:28, CHUAN LU a =E9crit : > HI, > > When I update a row, the timestampe value in the page will increase? = How to > get the timestamp value in the application, I need this value. > > thanks. > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum. > > ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

Only with the correct license surely ? Paul Watson Oninit www.oninit.com +1 913 387 7529 On May 29, 2013, at 10:51, "Madison Pruet" <mpruet@us.ibm.com> wrote: > You can use CRCOLS > > From: "Khaled Bentebal" <khaled.bentebal@consult-ix.fr> > To: ids@iiug.org, > Date: 05/29/2013 10:23 AM > Subject: Re: How to get the timestamp value in the data.... [30383] > Sent by: ids-bounces@iiug.org > > If you are trying to determine when a row was changed like in DB2: > CREATE TABLE T1 (C1 INTEGER NOT NULL); > INSERT INTO T1 VALUES (1); > ALTER TABLE T1 ADD COLUMN C2 NOT NULL GENERATED ALWAYS> > FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP; > SELECT T1.C2 FROM T1 WHERE T1.C1 =3D 1;> > Because the ROW CHANGE TIMESTAMP column was added after the data was > inserted, the following statement returns the time that the page was > last modified: > > SELECT T1.C2 FROM T1 WHERE T1.C1 =3D 1;> > Informix does not have this functionnality directly. > > You could implement it using an extra column with a trigger. However, > you should be careful if you are using an existing application since it= > > will be an extra column that you have to deal with in inserts, selects > and updates. > > The timestamp in each page is internal to Informix and is not accessibl= > e > directly from an application. It is mainly used to verify the integrity= > > of a page since it is located at the bottom of the page and in the page= > > header and is also used for backups to decide whether a page is > elligible to be backed up or not; if the timestamp is higher than the > time the backup was launched, the system has to get the page from the > physical log or the temporary space that contains the copy of the > physical log if a checkpoint has gone by. basically, it is for internal= > > data management use. > > What you may do is use is VERCOLS. When row versioning is enabled, > ifx_row_version is incremented by one each time the row is updated. Thi= > s > might not me really what you want. > > Cordialement, Regards, > > Khaled Bentebal > Directeur G=E9n=E9ral - ConsultiX > Pr=E9sident UGIF - User Group Informix France > IIUG - Board of Directors > T=E9l: 33 (0) 1 39 12 18 00 > Fax: 33 (0) 1 39 12 18 18 > Mobile: 33 (0) 6 07 78 41 97 > Email: khaled.bentebal@consult-ix.fr > Site Web: www.consult-ix.fr > > Le 29/05/13 10:28, CHUAN LU a =E9crit : >> HI, >> >> When I update a row, the timestampe value in the page will increase? = > How > to >> get the timestamp value in the application, I need this value. >> >> thanks. > ***********************************************************************= > ******** > >> Forum Note: Use "Reply" to post a response in the discussion forum. > > ***********************************************************************= > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > = > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.

CRCOLS should work on all. ER might not, however... From: "Paul Watson" <paul@oninit.com> To: ids@iiug.org, Date: 05/29/2013 10:57 AM Subject: Re: How to get the timestamp value in the data.... [30386] Sent by: ids-bounces@iiug.org Only with the correct license surely ? Paul Watson Oninit www.oninit.com +1 913 387 7529 On May 29, 2013, at 10:51, "Madison Pruet" <mpruet@us.ibm.com> wrote: > You can use CRCOLS > > From: "Khaled Bentebal" <khaled.bentebal@consult-ix.fr> > To: ids@iiug.org, > Date: 05/29/2013 10:23 AM > Subject: Re: How to get the timestamp value in the data.... [30383] > Sent by: ids-bounces@iiug.org > > If you are trying to determine when a row was changed like in DB2: > CREATE TABLE T1 (C1 INTEGER NOT NULL); > INSERT INTO T1 VALUES (1); > ALTER TABLE T1 ADD COLUMN C2 NOT NULL GENERATED ALWAYS> > FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP; > SELECT T1.C2 FROM T1 WHERE T1.C1 =3D3D 1;> > Because the ROW CHANGE TIMESTAMP column was added after the data was > inserted, the following statement returns the time that the page was > last modified: > > SELECT T1.C2 FROM T1 WHERE T1.C1 =3D3D 1;> > Informix does not have this functionnality directly. > > You could implement it using an extra column with a trigger. However,= > you should be careful if you are using an existing application since = it=3D > > will be an extra column that you have to deal with in inserts, select= s > and updates. > > The timestamp in each page is internal to Informix and is not accessi= bl=3D > e > directly from an application. It is mainly used to verify the integri= ty=3D > > of a page since it is located at the bottom of the page and in the pa= ge=3D > > header and is also used for backups to decide whether a page is > elligible to be backed up or not; if the timestamp is higher than the= > time the backup was launched, the system has to get the page from the= > physical log or the temporary space that contains the copy of the > physical log if a checkpoint has gone by. basically, it is for intern= al=3D > > data management use. > > What you may do is use is VERCOLS. When row versioning is enabled, > ifx_row_version is incremented by one each time the row is updated. T= hi=3D > s > might not me really what you want. > > Cordialement, Regards, > > Khaled Bentebal > Directeur G=3DE9n=3DE9ral - ConsultiX > Pr=3DE9sident UGIF - User Group Informix France > IIUG - Board of Directors > T=3DE9l: 33 (0) 1 39 12 18 00 > Fax: 33 (0) 1 39 12 18 18 > Mobile: 33 (0) 6 07 78 41 97 > Email: khaled.bentebal@consult-ix.fr > Site Web: www.consult-ix.fr > > Le 29/05/13 10:28, CHUAN LU a =3DE9crit : >> HI, >> >> When I update a row, the timestampe value in the page will increase?= =3D > How > to >> get the timestamp value in the application, I need this value. >> >> thanks. > *********************************************************************= **=3D > ******** > >> Forum Note: Use "Reply" to post a response in the discussion forum. > > *********************************************************************= **=3D > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > =3D > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

As with any feature, only if license permits. ER is available in all but Innovator-C and only has limits on Express. If CRCOLS requires ER I cannot tell. On Wed, May 29, 2013 at 4:54 PM, Paul Watson <paul@oninit.com> wrote: > Only with the correct license surely ? > > Paul Watson > Oninit www.oninit.com > +1 913 387 7529 > > On May 29, 2013, at 10:51, "Madison Pruet" <mpruet@us.ibm.com> wrote: > > > You can use CRCOLS > > > > From: "Khaled Bentebal" <khaled.bentebal@consult-ix.fr> > > To: ids@iiug.org, > > Date: 05/29/2013 10:23 AM > > Subject: Re: How to get the timestamp value in the data.... [30383] > > Sent by: ids-bounces@iiug.org > > > > If you are trying to determine when a row was changed like in DB2: > > CREATE TABLE T1 (C1 INTEGER NOT NULL); > > INSERT INTO T1 VALUES (1); > > ALTER TABLE T1 ADD COLUMN C2 NOT NULL GENERATED ALWAYS> > > > FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP; > > SELECT T1.C2 FROM T1 WHERE T1.C1 =3D 1;> > > > Because the ROW CHANGE TIMESTAMP column was added after the data was > > inserted, the following statement returns the time that the page was > > last modified: > > > > SELECT T1.C2 FROM T1 WHERE T1.C1 =3D 1;> > > > Informix does not have this functionnality directly. > > > > You could implement it using an extra column with a trigger. However, > > you should be careful if you are using an existing application since it= > > > > will be an extra column that you have to deal with in inserts, selects > > and updates. > > > > The timestamp in each page is internal to Informix and is not accessibl= > > e > > directly from an application. It is mainly used to verify the integrity= > > > > of a page since it is located at the bottom of the page and in the page= > > > > header and is also used for backups to decide whether a page is > > elligible to be backed up or not; if the timestamp is higher than the > > time the backup was launched, the system has to get the page from the > > physical log or the temporary space that contains the copy of the > > physical log if a checkpoint has gone by. basically, it is for internal= > > > > data management use. > > > > What you may do is use is VERCOLS. When row versioning is enabled, > > ifx_row_version is incremented by one each time the row is updated. Thi= > > s > > might not me really what you want. > > > > Cordialement, Regards, > > > > Khaled Bentebal > > Directeur G=E9n=E9ral - ConsultiX > > Pr=E9sident UGIF - User Group Informix France > > IIUG - Board of Directors > > T=E9l: 33 (0) 1 39 12 18 00 > > Fax: 33 (0) 1 39 12 18 18 > > Mobile: 33 (0) 6 07 78 41 97 > > Email: khaled.bentebal@consult-ix.fr > > Site Web: www.consult-ix.fr > > > > Le 29/05/13 10:28, CHUAN LU a =E9crit : > >> HI, > >> > >> When I update a row, the timestampe value in the page will increase? = > > How > > to > >> get the timestamp value in the application, I need this value. > >> > >> thanks. > > ***********************************************************************= > > ******** > > > >> Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ***********************************************************************= > > ******** > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > = > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --089e0118421045519a04dde37a9e

I can. ;-) Sent from my iPad On May 29, 2013, at 6:11 PM, "Fernando Nunes" <domusonline@gmail.com> wrote: > As with any feature, only if license permits. > ER is available in all but Innovator-C and only has limits on Express. > If CRCOLS requires ER I cannot tell. > > On Wed, May 29, 2013 at 4:54 PM, Paul Watson <paul@oninit.com> wrote: > > > Only with the correct license surely ? > > > > Paul Watson > > Oninit www.oninit.com > > +1 913 387 7529 > > > > On May 29, 2013, at 10:51, "Madison Pruet" <mpruet@us.ibm.com> wrote: > > > > > You can use CRCOLS > > > > > > From: "Khaled Bentebal" <khaled.bentebal@consult-ix.fr> > > > To: ids@iiug.org, > > > Date: 05/29/2013 10:23 AM > > > Subject: Re: How to get the timestamp value in the data.... [30383] > > > Sent by: ids-bounces@iiug.org > > > > > > If you are trying to determine when a row was changed like in DB2: > > > CREATE TABLE T1 (C1 INTEGER NOT NULL); > > > INSERT INTO T1 VALUES (1); > > > ALTER TABLE T1 ADD COLUMN C2 NOT NULL GENERATED ALWAYS> > > > > > FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP; > > > SELECT T1.C2 FROM T1 WHERE T1.C1 =3D 1;> > > > > > Because the ROW CHANGE TIMESTAMP column was added after the data was > > > inserted, the following statement returns the time that the page was > > > last modified: > > > > > > SELECT T1.C2 FROM T1 WHERE T1.C1 =3D 1;> > > > > > Informix does not have this functionnality directly. > > > > > > You could implement it using an extra column with a trigger. However, > > > you should be careful if you are using an existing application since it= > > > > > > will be an extra column that you have to deal with in inserts, selects > > > and updates. > > > > > > The timestamp in each page is internal to Informix and is not accessibl= > > > e > > > directly from an application. It is mainly used to verify the integrity= > > > > > > of a page since it is located at the bottom of the page and in the page= > > > > > > header and is also used for backups to decide whether a page is > > > elligible to be backed up or not; if the timestamp is higher than the > > > time the backup was launched, the system has to get the page from the > > > physical log or the temporary space that contains the copy of the > > > physical log if a checkpoint has gone by. basically, it is for internal= > > > > > > data management use. > > > > > > What you may do is use is VERCOLS. When row versioning is enabled, > > > ifx_row_version is incremented by one each time the row is updated. Thi= > > > s > > > might not me really what you want. > > > > > > Cordialement, Regards, > > > > > > Khaled Bentebal > > > Directeur G=E9n=E9ral - ConsultiX > > > Pr=E9sident UGIF - User Group Informix France > > > IIUG - Board of Directors > > > T=E9l: 33 (0) 1 39 12 18 00 > > > Fax: 33 (0) 1 39 12 18 18 > > > Mobile: 33 (0) 6 07 78 41 97 > > > Email: khaled.bentebal@consult-ix.fr > > > Site Web: www.consult-ix.fr > > > > > > Le 29/05/13 10:28, CHUAN LU a =E9crit : > > >> HI, > > >> > > >> When I update a row, the timestampe value in the page will increase? = > > > How > > > to > > >> get the timestamp value in the application, I need this value. > > >> > > >> thanks. > > > ***********************************************************************= > > > ******** > > > > > >> Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ***********************************************************************= > > > ******** > > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > = > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --089e0118421045519a04dde37a9e > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >