1

Does anyone know of a query that gives me details on the last time a Netezza table was accessed for any of the operations (select, insert or update) ?

Thanks

1

You could setup a history database (using nzhistcreatedb) which acts as a detailed access log and then consult the views and tables that it provides (e.g. $v_hist_table_access_stats).

If you haven't already got this in place then I'm not aware of any way to find out who accessed a table unless you trawl through the logs in /nz/kit/log/postgres/.

0

it would be...

SELECT FORMAT_TABLE_ACCESS(usage),
   hq.submittime
FROM "$v_hist_queries" hq
   INNER JOIN "$hist_table_access_3" hta
USING (NPSID, NPSINSTANCEID, OPID, SESSIONID)
WHERE hq.dbname    = 'URBN_TARGET'
AND hta.tablename  = 'CUSTOMER_ORDER_RANK'
AND hq.SUBMITTIME  > '01-01-2015'
AND hq.SUBMITTIME <= CURRENT_DATE
AND
   (
      instr(FORMAT_TABLE_ACCESS(usage),'ins') > 0
   OR instr(FORMAT_TABLE_ACCESS(usage),'upd') > 0
   OR instr(FORMAT_TABLE_ACCESS(usage),'del') > 0
   OR instr(FORMAT_TABLE_ACCESS(usage),'read') > 0
   )
AND status=0;
0

You can adapt as well. To check what is the last connexion per user per database

Put every database name on a file called db.txt Go into the log directory as said previously (/nz/kit/log/postgres) and then run this script :

#!/bin/ksh
for database in `cat db.txt`

do

echo "Base $database :"

cat pg.log*|grep ${database}|grep connection|grep user|awk ' { print $8 }'|sort -u|grep -v host>tempo_user.txt

for USER in `cat tempo_user.txt|awk -F"=" ' { print $2 } '`

  do

  export RESU=`cat pg.log*|grep ${database}|grep connection|grep ${USER}|sort -u|tail -1`

  echo "Last connexion of ${USER} on ${database} : ${RESU}"

  done

done
#

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.