All Forums Database
anand82 6 posts Joined 04/16
05 May 2016
Query :: Find tables which are accessed by users

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?

dnoeth 4628 posts Joined 11/04
05 May 2016

Check if the DBQLObjTbl contains data.

Dieter

yuvaevergreen 93 posts Joined 07/09
06 May 2016

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.

anand82 6 posts Joined 04/16
06 May 2016

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'

Karthik_B 10 posts Joined 09/14
06 May 2016

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'

--Karthik

 

anand82 6 posts Joined 04/16
07 May 2016

Thank you all.

You must sign in to leave a comment.