Netezza Performance Server

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

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-data

createxid - 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

Original Message:
Sent: 10/19/2022 10:57:00 AM
From: DARIUSZ KIELBASA
Subject: RE: Finding last updated time of a netezza table

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-data

createxid - 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

Hello,

Without any kind of query history database you can not find last update time. But you can find transaction ID which altered the table's row.
It is as simple as query createxid and deletexid columns of the table.

see: https://www.ibm.com/docs/en/psfa/7.2.1?topic=types-netezza-internal-data

Knowing this you can find if the highest transaction ID in a table is higher than some other transaction. That is if there are any records modified later.

note: deleted rows with non-zero deletexid values exists only till GROOM table is run. It will physically remove deleted rows.


------------------------------
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