How to check the data and time that a table was transacted in IBM Netezza SQL from Aginity workbench?

Question

My question may be simple, but, I have been searching the answer for a long time.

It is very easy to find this in MS SQL server, but, I cannot figure this out in Netezza.

Any help would be appreciated.

thanks !

one small correction...How to check the data and time that a table was last transacted in IBM Netezza SQL from Aginity workbench? — user1644154, Jan 28 '15 at 12:09
What do you mean by "a table was transacted?" Are you talking about the last change to data in the table, the last time the table structure itself was changed, or something else? — ScottMcG, Jan 28 '15 at 21:52
Yes, last change to data in the table in IBM Netezza SQL from Aginity workbench. — user1644154, Feb 16 '15 at 13:08

score 1 · Answer 1 · answered Feb 17 '15 at 01:49

Unfortunately the system does not record the last time that the contents of a table were modified in the catalog metadata.

However, you can mine this information from the query history database if you have turned on query history. The USAGE column in the $hist_table_access_n table (where n is the version of the query history database you are using) contains information about what operations where performed against that table, as documented here.

You can use this column in a where clause to filter for cases when the table was updated, and you can also use the FORMAT_TABLE_ACCESS() function to display the USAGE information in a human-readable format, as documented here.

Here's an example where we look for inserts, updates, deletes, and truncates on a given table. In my case my history database is called HISTDB, and the history data is collected in the HISTUSER schema.

SELECT FORMAT_TABLE_ACCESS(usage),
   submittime
FROM histdb.histuser."$hist_table_access_3" a
   JOIN histdb.HISTUSER."$hist_query_prolog_3" b
   ON a.NPSID          = b.NPSID
   AND a.NPSINSTANCEID = b.NPSINSTANCEID
   AND a.OPID          = b.OPID
   JOIN histdb.HISTUSER."$hist_query_epilog_3" c
   ON a.NPSID                   = c.NPSID
   AND a.NPSINSTANCEID          = c.NPSINSTANCEID
   AND a.OPID                   = c.OPID
WHERE a.DBNAME                  = 'TESTDB'
AND a.SCHEMANAME                = 'ADMIN'
AND a.TABLENAME                 = 'HIST_TEST'
AND c.STATUS                    = 0
AND a.USAGE & (2 + 4 + 8 + 16) <> 0 ;

 FORMAT_TABLE_ACCESS |         SUBMITTIME
---------------------+----------------------------
 ins                 | 2015-01-21 19:00:10.448681
 del                 | 2015-01-21 19:00:30.094608
 upd                 | 2015-01-21 19:00:49.148814
 ins                 | 2015-01-21 19:00:01.106845
(4 rows)

SELECT MAX(submittime)
FROM histdb.histuser."$hist_table_access_3" a
   JOIN histdb.HISTUSER."$hist_query_prolog_3" b
   ON a.NPSID          = b.NPSID
   AND a.NPSINSTANCEID = b.NPSINSTANCEID
   AND a.OPID          = b.OPID
   JOIN histdb.HISTUSER."$hist_query_epilog_3" c
   ON a.NPSID                   = c.NPSID
   AND a.NPSINSTANCEID          = c.NPSINSTANCEID
   AND a.OPID                   = c.OPID
WHERE a.DBNAME                  = 'TESTDB'
AND a.SCHEMANAME                = 'ADMIN'
AND a.TABLENAME                 = 'HIST_TEST'
AND c.STATUS                    = 0
AND a.USAGE & (2 + 4 + 8 + 16) <> 0 ;

            MAX
----------------------------
 2015-01-21 19:00:49.148814
(1 row)

Note that this only works on data that has been loaded in the query history database, so depending on the activity and configuration of your system the results won't be real time. Also note that if you are going to be doing this regularly, and if performance is an issue, you would probably want to change the where clause to filter on object IDs rather than text values.

This looks nice, Is it possible to join them so I would get history of which table has been modified by what procedure? — marcin2x4, Jun 16 '20 at 20:00

How to check the data and time that a table was transacted in IBM Netezza SQL from Aginity workbench?

1 Answers1