we are trying to find out the list of all tables being accessed by each user during DDL DML statements. we have access to PDCR.
I understand that we can query dbc.tablesv to find all table names in database. Username and query text, statement type could be extracted from pdcrinfo.dbqllogtbl_hst. but I am not sure how to join these two tables and get the table names associated which are being used by Users for DDL and DML operations.
any help or inputs on these please?
DBQLOGTBL - Contains the query and the queryid,username
DBQLOBJTBL - Contains the querid and the objects
Join based on QUERYID and get the info of the tables accessed by the users.
thanks Dieter and Yuva for the help. I have joined the both tables. But, for the object type DB, I am getting NULL as object tablename, columnname though the query text is for e.g. sel * from dbname.tblname. For some of the object type view, the select, insert statement tablename and object table name are not matching.. Am I missing something. Pls help..
sel distinct a.username, a.statementtype, a.querytext, b.objectdatabasename, b.objecttablename, b.objectcolumnname, b.objecttype
from pdcrinfo.dbqlogtbl_hst a inner join pdcrinfo.dbqlobj_hst b on a.queryid=b.queryid where b.objectdatabasename='XXXX'
You need to look for objecttype = 'tab' rather than 'DB', you can define the statementtypes you are looking for
select A.LOGDATE, A.PROCID, A.QUERYID, OBJECTDATABASENAME, OBJECTTABLENAME
FROM PDCRDATA.DBQLOGTBL_HST A , PDCRDATA.DBQLOBJTBL_HST B
WHERE A.LOGDATE = B.LOGDATE
AND A.PROCID = B.PROCID
AND A.QUERYID = B.QUERYID
AND STATEMENTTYPE IN ( 'INSERT','SELECT') )
AND OBJECTTYPE = 'TAB'