Hi,
Every table in Netezza has createxid and deletexid column as per
https://www.ibm.com/docs/en/psfa/7.2.1?topic=types-netezza-internal-datacreatexid - transaction ID in which such row has been created / modified
deletexid - transaction ID in which such row has been deleted (this row will eventually disappear physically if GROOM is run on it)
This won't give you exact time when a row has been modified - it is just transaction ID but... you may use it to find if there are any new/modified rows with createxid higher than some transaction ID you have set as a threshold. For deleted rows it will work only before GROOM, as it will remove these rows physically.
To collect currently active transaction IDs you can run query like this:
SELECT session.conntime AS session_start_time
,to_char('1970-01-01'::DATE + (host.START_TS::BIGINT * '00:00:00.000001'::TIME) + (
SELECT tzoffset
FROM _vt_pg_time_offset
), 'yyyy-mm-dd hh24:mi:ss') AS transaction_start_time
,host.xid
,session.STATUS
,session.id
,host.read_only
,host.isexplicit
,session.dbname
,session.username
,session.ipaddr
,substr(session.command, 1, 100)
FROM _V_HOST_TX host
JOIN _V_SESSION session ON host.session = session.ID
ORDER BY xid;
I am afraid if you need exact time of last access - then query history collection is a must.
------------------------------
DARIUSZ KIELBASA
------------------------------
Original Message:
Sent: Mon October 17, 2022 12:49 PM
From: radhakrishnan thangamuthu
Subject: Finding last updated time of a netezza table
I have an usecase where I will have to find out if a Netezza table is modified or not.
I found a stackoverflow link which talks about history configuration Last accessed timestamp of a Netezza table?
From that link, it seems history configuration needs to be enabled in netezza. Is there a way without enabling history configuration for the query history, can we find when the table was modified by insert/update/delete statements . I m writing a ETL kind of job which needs to check for data duplication to see if the same data is placed in two different tables (of two different databases like MySQL, Oracle etc) by mistake . I may not have access to the DB server machine or DB admin privileges (need to ask for it , in the worst case).
Thanks in advanced for any help
------------------------------
radhakrishnan thangamuthu
------------------------------
#NetezzaPerformanceServer