Netezza Performance Server

 View Only
  • 1.  Finding last updated time of a netezza table

    Posted Tue October 18, 2022 09:46 AM
    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
    ------------------------------


  • 2.  RE: Finding last updated time of a netezza table

    Posted Thu October 20, 2022 09:28 AM
    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
    ------------------------------



  • 3.  RE: Finding last updated time of a netezza table

    Posted Fri October 21, 2022 07:45 AM
    Thank you for your teaching.

    Saludos, Regards 

    IBM Expert Labs, Data Consultant: Generalist 

    Db2 Advanced Database Administrator LUW 11.5.5.0 

    IBM Certified Database Associate - Db2 8.1,10.1 Fundamentals 

    IBM Associate Certified DBA - Db2 12 for z/OS Fundamentals 

    IBM i Applications Architect 

    _________________________________________
    Nicolás Rafael Ascanio Peña 

     

     

     IBM Professions and COGNITIVE CLASS (45) badges 

    https://www.credly.com/users/nicolas-ascanio

    Cell phone: +58 4241406472         
    Tie-Line: 777-8685         
    e-mail: nascanio@ve.ibm.com







  • 4.  RE: Finding last updated time of a netezza table

    Posted Thu October 20, 2022 09:29 AM
    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
    ------------------------------