Using CRCOLS w/o CDR
Dan (IDS 11.50.FC5 on AIX 5.3) wanted to track when rows were last inserted/updated and tried adding CRCOLS (the CDR shadow columns), but the timestamp column appeared to be just an integer. Replies explained the value is a UTC epoch time (seconds since 1 Jan 1970/71), as returned by time(), and can be converted with a short C program using ctime_r or, more simply, in SQL with dbinfo('UTC_TO_DATETIME', column) — illustrated against sysmaster:sysshmvals. One poster also suggested triggers as the better way to do fine-grained row auditing, since CRCOLS and onaudit aren't designed for it.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Good Morning, IDS 11.50.FC5 AIX 5.3 I want to be able to track the last time a row was updated or inserted in a table. I tested altering a table to add CRCOLS and can see the value of that column after an insert or update however is there any way to tie that output to an actual time? It appears on the outside to be just an int column. thanx, Dan
Hello Dan.
The best method recommended to fine grain audit on insert, updates, etc
on your version (and easiest way) is doing it with triggers.
(Neither crcols, nor onaudit will give you the audit levels you need).
Best regards.
Em 17/03/2011 09:12, DAN MUELLER escreveu:
> Good Morning,
>
> IDS 11.50.FC5
> AIX 5.3
>
> I want to be able to track the last time a row was updated or inserted in a
> table. I tested altering a table to add CRCOLS and can see the value of that
> column after an insert or update however is there any way to tie that output
> to an actual time? It appears on the outside to be just an int column.
>
> thanx,
> Dan
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Alexandre Marini
Tecnologia da Informação - DBA
msn: alexandre_marini@hotmail.com
SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix
Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg>
IBM Certified System Administrator - Informix Dynamic Server V10 / V11
<http://www.iiug.org/conf/2011/iiug/>;
It is the value in seconds since midnight Jan 1, 1971. This is the value returned from the time() function. If you want to see the converted time, you can use the following rather simple 'c' program. #include <time.h> #include <stdlib.h> #include <stdio.h> int main(int nparm, char *parm[]) { int tnum; char buff[128]; time_t tim2Cnv; char *timeStr; if (nparm < 2) { printf("you must enter the time\\ "); return 1; } tim2Cnv = atol(parm[1]); ctime_r(&tim2Cnv, buff); printf("%s\\ ", buff); return 0; } From: "DAN MUELLER" <dan.mueller@trnswrks.com> To: ids@iiug.org Date: 03/17/2011 07:15 AM Subject: Using CRCOLS w/o CDR [23143] Sent by: ids-bounces@iiug.org Good Morning, IDS 11.50.FC5 AIX 5.3 I want to be able to track the last time a row was updated or inserted in a table. I tested altering a table to add CRCOLS and can see the value of that column after an insert or update however is there any way to tie that output to an actual time? It appears on the outside to be just an int column. thanx, Dan ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Or in SQL use the info('etc_to_datetime', column) On Mar 17, 2011 9:45 AM, "Madison Pruet" <mpruet@us.ibm.com> wrote: > It is the value in seconds since midnight Jan 1, 1971. This is the value > returned from the time() function. > > If you want to see the converted time, you can use the following rather > simple 'c' program. > > #include <time.h> > #include <stdlib.h> > #include <stdio.h> > > int > main(int nparm, char *parm[]) > { > > int tnum; > > char buff[128]; > > time_t tim2Cnv; > > char *timeStr; > > if (nparm < 2) > > { > > printf("you must enter the time\\ "); > > return 1; > > } > > tim2Cnv = atol(parm[1]); > > ctime_r(&tim2Cnv, buff); > > printf("%s\\ ", buff); > > return 0; > } > > From: "DAN MUELLER" <dan.mueller@trnswrks.com> > > To: ids@iiug.org > > Date: 03/17/2011 07:15 AM > > Subject: Using CRCOLS w/o CDR [23143] > > Sent by: ids-bounces@iiug.org > > Good Morning, > > IDS 11.50.FC5 > AIX 5.3 > > I want to be able to track the last time a row was updated or inserted in a > > table. I tested altering a table to add CRCOLS and can see the value of > that > column after an insert or update however is there any way to tie that > output > to an actual time? It appears on the outside to be just an int column. > > thanx, > Dan > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > --bcaec5015d31ce87ee049eb08d2c
I think what Art meant to say is
dbinfo("utc_do_datetime", column)
select
dbinfo('UTC_TO_DATETIME', sh_boottime) as boottime,
dbinfo('UTC_TO_DATETIME', sh_curtime) as current,
dbinfo('UTC_TO_DATETIME', sh_curtime) -
dbinfo('UTC_TO_DATETIME', sh_boottime) as uptime
from sysmaster:sysshmvals;
boottime current
uptime
2011-03-07 16:44:33 2011-03-17 10:31:27 9 17:46:54
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 03/17/2011 09:57:34 AM:
> From:
>
> "Art Kagel" <art.kagel@gmail.com>
> ids-bounces@iiug.org
>
> Or in SQL use the info('etc_to_datetime', column)
> On Mar 17, 2011 9:45 AM, "Madison Pruet" <mpruet@us.ibm.com> wrote:
> > It is the value in seconds since midnight Jan 1, 1971. This is the
value
> > returned from the time() function.
> >
> > If you want to see the converted time, you can use the following rather
> > simple 'c' program.
> >
> > #include <time.h>
> > #include <stdlib.h>
> > #include <stdio.h>
> >
> > int
> > main(int nparm, char *parm[])
> > {
> >
> > int tnum;
> >
> > char buff[128];
> >
> > time_t tim2Cnv;
> >
> > char *timeStr;
> >
> > if (nparm < 2)
> >
> > {
> >
> > printf("you must enter the time\\
");
> >
> > return 1;
> >
> > }
> >
> > tim2Cnv = atol(parm[1]);
> >
> > ctime_r(&tim2Cnv, buff);
> >
> > printf("%s\\
", buff);
> >
> > return 0;
> > }
> >
> > From: "DAN MUELLER" <dan.mueller@trnswrks.com>
> >
> > To: ids@iiug.org
> >
> > Date: 03/17/2011 07:15 AM
> >
> > Subject: Using CRCOLS w/o CDR [23143]
> >
> > Sent by: ids-bounces@iiug.org
> >
> > Good Morning,
> >
> > IDS 11.50.FC5
> > AIX 5.3
> >
> > I want to be able to track the last time a row was updated or inserted
in
> a
> >
> > table. I tested altering a table to add CRCOLS and can see the value of
> > that
> > column after an insert or update however is there any way to tie that
> > output
> > to an actual time? It appears on the outside to be just an int column.
> >
> > thanx,
> > Dan
> >
> >
> >
>
>
*******************************************************************************
>
> >
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
> --bcaec5015d31ce87ee049eb08d2c
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Almost ;)
Art
On Mar 17, 2011 1:39 PM, "John Miller iii" <miller3@us.ibm.com> wrote:
> I think what Art meant to say is
>
> dbinfo("utc_do_datetime", column)>
> select
> dbinfo('UTC_TO_DATETIME', sh_boottime) as boottime,
> dbinfo('UTC_TO_DATETIME', sh_curtime) as current,
> dbinfo('UTC_TO_DATETIME', sh_curtime) -
> dbinfo('UTC_TO_DATETIME', sh_boottime) as uptime
> from sysmaster:sysshmvals;>
> boottime current
> uptime
>
> 2011-03-07 16:44:33 2011-03-17 10:31:27 9 17:46:54
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 03/17/2011 09:57:34 AM:
>
>> From:
>>
>> "Art Kagel" <art.kagel@gmail.com>
>
>> ids-bounces@iiug.org
>>
>> Or in SQL use the info('etc_to_datetime', column)
>> On Mar 17, 2011 9:45 AM, "Madison Pruet" <mpruet@us.ibm.com> wrote:
>> > It is the value in seconds since midnight Jan 1, 1971. This is the
> value
>> > returned from the time() function.
>> >
>> > If you want to see the converted time, you can use the following rather
>
>> > simple 'c' program.
>> >
>> > #include <time.h>
>> > #include <stdlib.h>
>> > #include <stdio.h>
>> >
>> > int
>> > main(int nparm, char *parm[])
>> > {
>> >
>> > int tnum;
>> >
>> > char buff[128];
>> >
>> > time_t tim2Cnv;
>> >
>> > char *timeStr;
>> >
>> > if (nparm < 2)
>> >
>> > {
>> >
>> > printf("you must enter the time\\
");
>> >
>> > return 1;
>> >
>> > }
>> >
>> > tim2Cnv = atol(parm[1]);
>> >
>> > ctime_r(&tim2Cnv, buff);
>> >
>> > printf("%s\\
", buff);
>> >
>> > return 0;
>> > }
>> >
>> > From: "DAN MUELLER" <dan.mueller@trnswrks.com>
>> >
>> > To: ids@iiug.org
>> >
>> > Date: 03/17/2011 07:15 AM
>> >
>> > Subject: Using CRCOLS w/o CDR [23143]
>> >
>> > Sent by: ids-bounces@iiug.org
>> >
>> > Good Morning,
>> >
>> > IDS 11.50.FC5
>> > AIX 5.3
>> >
>> > I want to be able to track the last time a row was updated or inserted
> in
>> a
>> >
>> > table. I tested altering a table to add CRCOLS and can see the value of
>
>> > that
>> > column after an insert or update however is there any way to tie that
>> > output
>> > to an actual time? It appears on the outside to be just an int column.
>> >
>> > thanx,
>> > Dan
>> >
>> >
>> >
>>
>>
>
>
*******************************************************************************
>
>>
>> >
>> > Forum Note: Use "Reply" to post a response in the discussion forum.
>> >
>> >
>> >
>>
>>
>
>
*******************************************************************************
>
>>
>> > Forum Note: Use "Reply" to post a response in the discussion forum.
>> >
>>
>> --bcaec5015d31ce87ee049eb08d2c
>>
>>
>>
>
>
*******************************************************************************
>
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
--20cf3054aa49759f9a049eb321ee