CODEWITHSUNDEEP
netezza
user1644154
user1644154

Reputation: 54

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

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

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

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 !

Upvotes: 0

Views: 4273

Answers (1)

ScottMcG
ScottMcG

Reputation: 3887

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.

Upvotes: 1

Related Questions