Does anyone know how to get a number of times a table/view is used per userid per day, say, for the last 6-12 months?

I looked at audit events EXECUTE policy and it provides sql statements but it is difficult to break it down into table/view since it is text.
For the audit event SECMAINT, I do not see it provide, say, an event stating it systematically granted permission to the table/view. Or even a check if a user is allowed to use table/view.

Any tips appreciated.

Thanks.

------------------------------
PITIRIM C TAN
------------------------------

I am not sure what you are looking for; let's consider this example:

Database D contains table SCHEMA.TABLE.

Userid 1 connects to database using SQLID A and does select count(*) from schema.table;

Userid 1 connects to database using SQLID B and does select count(*) from schema.table;

Userid 2 connects to database using SQLID A and does select count(*) from schema.table;

Userid 3 connects to database using SQLID A and does select count(*) from schema.table;

What values for "number of times a table/view is used per userid" you expect?

------------------------------
Jan Nelken
------------------------------

Unfortunately, it's really not possible to get the values of SYSCAT.AUDITUSE.[OBJECTSCHEMA | OBJECTNAME] columns (the auditable object name) in the audit log.
That is, if we, say, have:

CREATE TABLE AUDIT1 (I INT);
CREATE VIEW AUDIT1_V AS SELECT * FROM AUDIT1;

CREATE AUDIT POLICY EXEC
CATEGORIES 
  EXECUTE  STATUS BOTH
ERROR TYPE NORMAL;

AUDIT TABLE AUDIT1 USING POLICY EXEC;

SELECT COUNT (1) FROM AUDIT1_V;

then we won't see the AUDIT1 string in the audit log. There is an original statement text referring different object in the auditlobs file only.
Too many fields which you won't ever need except the one really needed...

------------------------------
Mark Barinstein
------------------------------

Hi All, 

Thanks for reviewing my question.

In Jan's example, the value I need would be the # of times the table was used (ie: select stmt). 

I believe the audit.checking might be the answer.
The purpose is defined as "Generates records during authorization checking of attempts to access or manipulate Db2 database objects or functions."
My assumption is since an auth check is going to be done for every query then it might be close enough.

If I create a stored procedure with a for loop repeating a SELECT 5-times, will db2 be checking authorization 5 times or 1 time? 
I'll test.

 select USERID, AUTHID, OBJSCHEMA, OBJNAME from AUDIT.CHECKING where OBJTYPE = 'TABLE';

In my example SELECT was issued 4 times by 2 SQLID's from 3 OS userid's:

Userid 1 executed SELECT 2 times;

Userid 2 executed SELECT 1 time;

Userid 3 executed SELECT 1 time;

SQLID A executed SELECT 3 times;

SQLID B executed SELECT 1 time;

I am not sure Db2 is interested in OS userid - it cares about SQLID (a.k.a SQL Authorization Id).

An example:

C:\>whoami
azuread\janelnken

C:\>db2 connect to sample user db2admin
Enter current password for db2admin:

   Database Connection Information

 Database server        = DB2/NT64 12.1.0.0
 SQL authorization ID   = DB2ADMIN
 Local database alias   = SAMPLE

C:\>db2 values current user

1
-------------------------------------
DB2ADMIN

  1 record(s) selected.