Query :: Find tables which are accessed by users

Database
Enthusiast

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?

Tags (2)
6 REPLIES
Senior Apprentice

Re: Query :: Find tables which are accessed by users

Check if the DBQLObjTbl contains data.

Enthusiast

Re: Query :: Find tables which are accessed by users

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.

Enthusiast

Re: Query :: Find tables which are accessed by 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'

Enthusiast

Re: Query :: Find tables which are accessed by users

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

Enthusiast

Re: Query :: Find tables which are accessed by users

Thank you all.

Re: Query :: Find tables which are accessed by users

Hi Dieter, If the DBQLOBJTBL is not activated, then Is there are workaround?