IDS Forum

Re: How to get the timestamp value in the data pag

Posted By: Art Kagel
Date: Wednesday, 29 May 2013, at 6:52 a.m.

In Response To: How to get the timestamp value in the data page? (CHUAN LU)

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

Messages In This Thread

• How to get the timestamp value in the data page?
  CHUAN LU -- Wednesday, 29 May 2013, at 4:28 a.m.
  • Re: How to get the timestamp value in the data pag
    Art Kagel -- Wednesday, 29 May 2013, at 6:52 a.m.
  • Re: How to get the timestamp value in the data pag
    Fernando Nunes -- Wednesday, 29 May 2013, at 8:37 a.m.
  • Re: How to get the timestamp value in the data pag
    Khaled Bentebal -- Wednesday, 29 May 2013, at 11:14 a.m.
    • Re: How to get the timestamp value in the data....
      Madison Pruet -- Wednesday, 29 May 2013, at 11:51 a.m.
      • Re: How to get the timestamp value in the data....
        Paul Watson -- Wednesday, 29 May 2013, at 11:54 a.m.
        • Re: How to get the timestamp value in the data....
          Madison Pruet -- Wednesday, 29 May 2013, at 12:19 p.m.
        • Re: How to get the timestamp value in the data....
          Fernando Nunes -- Wednesday, 29 May 2013, at 7:10 p.m.
          • Re: How to get the timestamp value in the data....
            Madison Pruet -- Wednesday, 29 May 2013, at 7:53 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.