DB2 z/OS Auditing of Read-only Activity

Francis Leblanc

DB2 z/OS Auditing of Read-only Activity
Hi.

Does anyone know of any facility within DB2 to audit read activity against a specified table?

Thanks.

Fritz


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Kirk Hampton

Re: DB2 z/OS Auditing of Read-only Activity
(in response to Francis Leblanc)
Yes.

We have done ALTER TABLE tbname AUDIT ALL, on the tables we are
interest in tracking,

usually ones that we think are ready for retirement.

Then we have the Audit Trace turned on in ZPARMS,

AUDITST=(4,5,6),



And so SMF records are generated for ANY access to the table,

read or otherwise.



Then we run a daily report against our SMF file using DB2/PE,

AUDIT

REDUCE

REPORT LEVEL(DETAIL)

TYPE(ALL)

DB2PM EXEC



This shows who accessed it and the plan/package,

And if is was dynamic SQL, you can see the SQL statement they issued.



J Kirk Hampton

Sr. Specialist - Mainframe

HCL Technologies America

Mesquite Data Center

972-216-3119



________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Leblanc,
Fritz
Sent: Friday, January 28, 2011 12:31 PM
To: Hampton, Kirk
Subject: DB2 z/OS Auditing of Read-only Activity



Hi.



Does anyone know of any facility within DB2 to audit read activity
against a specified table?



Thanks.



Fritz




________________________________

Introducing IBM? DB2? 10 for z/OS
< http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Raymond Bell

Re: DB2 z/OS Auditing of Read-only Activity
(in response to Kirk Hampton)
One caveat. I haven't checked recently but it always used to be the Audit class trace would only catch the first read of an object in a UOW. So if the UOW read something perfectly acceptable but then went on to read other objects it wouldn't be recorded. As I said, not checked recently but that kinda took the shine of Audit classes for me - and the fact I seldom cared who successfully read an object.

Cheers,


Raymond
PS. There are apparently products out there that will audit reads. ;o)

________________________________
From: IDUG DB2-L [[login to unmask email] On Behalf Of Kirk Hampton [[login to unmask email]
Sent: 28 January 2011 20:36
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS Auditing of Read-only Activity

Yes.
We have done ALTER TABLE tbname AUDIT ALL, on the tables we are interest in tracking,
usually ones that we think are ready for retirement.
Then we have the Audit Trace turned on in ZPARMS,
AUDITST=(4,5,6),

And so SMF records are generated for ANY access to the table,
read or otherwise.

Then we run a daily report against our SMF file using DB2/PE,
AUDIT
REDUCE
REPORT LEVEL(DETAIL)
TYPE(ALL)
DB2PM EXEC

This shows who accessed it and the plan/package,
And if is was dynamic SQL, you can see the SQL statement they issued.


J Kirk Hampton

Sr. Specialist - Mainframe

HCL Technologies America

Mesquite Data Center

972-216-3119

________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Leblanc, Fritz
Sent: Friday, January 28, 2011 12:31 PM
To: Hampton, Kirk
Subject: DB2 z/OS Auditing of Read-only Activity

Hi.

Does anyone know of any facility within DB2 to audit read activity against a specified table?

Thanks.

Fritz


________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Francis Leblanc

Re: DB2 z/OS Auditing of Read-only Activity
(in response to Raymond Bell)
The version 9 manual shows the following for audit classes 4, 5, and 6:

4 Changes to audited tables. Only the first attempt to change a table, within a unit of recovery, is recorded. (If the agent or the transaction issues more than one COMMIT statement, the number of audit records increases accordingly.) The changed data is not recorded; only the attempt to make a change is recorded. If the change is not successful and is rolled back, the audit record remains; it is not deleted. This class includes access by the LOAD utility. Accesses to a dependent table that are caused by attempted deletions from a parent table are also audited. The audit record is written even if the delete rule is RESTRICT, which prevents the deletion from the parent table. The audit record is also written when the rule is CASCADE or SET NULL, which can result in deletions that cascade to the dependent table.

5 All read accesses to tables that are identified with the AUDIT ALL clause. As in class 4, only the first access within a DB2 unit of recovery is recorded. References to a parent table are also audited.

6 The bind of static and dynamic SQL statements of the following types:
INSERT, UPDATE, DELETE, CREATE VIEW, and LOCK TABLE statements for audited tables. Except for the values of host variables, the audit record contains the entire SQL statement.
SELECT statements on tables that are identified with the AUDIT ALL clause. Except for the values of host variables, the audit record contains the entire SQL statement.

Focusing on class 5, I have a few questions. For a UOW, is the trace record created for the first access to each table for which the audit is enabled? Or only to the first table encountered that has the audit enabled?

Thanks.

Fritz

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: Saturday, January 29, 2011 10:18 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS Auditing of Read-only Activity

One caveat. I haven't checked recently but it always used to be the Audit class trace would only catch the first read of an object in a UOW. So if the UOW read something perfectly acceptable but then went on to read other objects it wouldn't be recorded. As I said, not checked recently but that kinda took the shine of Audit classes for me - and the fact I seldom cared who successfully read an object.

Cheers,


Raymond
PS. There are apparently products out there that will audit reads. ;o)

________________________________
From: IDUG DB2-L [[login to unmask email] On Behalf Of Kirk Hampton [[login to unmask email]
Sent: 28 January 2011 20:36
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS Auditing of Read-only Activity

Yes.
We have done ALTER TABLE tbname AUDIT ALL, on the tables we are interest in tracking,
usually ones that we think are ready for retirement.
Then we have the Audit Trace turned on in ZPARMS,
AUDITST=(4,5,6),

And so SMF records are generated for ANY access to the table,
read or otherwise.

Then we run a daily report against our SMF file using DB2/PE,
AUDIT
REDUCE
REPORT LEVEL(DETAIL)
TYPE(ALL)
DB2PM EXEC

This shows who accessed it and the plan/package,
And if is was dynamic SQL, you can see the SQL statement they issued.


J Kirk Hampton

Sr. Specialist - Mainframe

HCL Technologies America

Mesquite Data Center

972-216-3119

________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Leblanc, Fritz
Sent: Friday, January 28, 2011 12:31 PM
To: Hampton, Kirk
Subject: DB2 z/OS Auditing of Read-only Activity

Hi.

Does anyone know of any facility within DB2 to audit read activity against a specified table?

Thanks.

Fritz


________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

This E-Mail has been scanned for viruses.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv