Antwort: Logging select access to DB2 tables

Rodney KRick

Antwort: Logging select access to DB2 tables
Robert,

what about using the DDL "AUDIT ALL" option (CREATE/ALTER TABLE) and start
a trace?

Mit freundlichen Grüßen / Best regards,

Rodney Krick
Hänchen & Partner GmbH
Calwer Straße 1
D-71034 Böblingen




"Bandy, Robert" <[login to unmask email]>
Gesendet von: DB2 Data Base Discussion List <[login to unmask email]>
07.01.2003 14:20
Bitte antworten an
DB2 Data Base Discussion List <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
Logging select access to DB2 tables






We are currently running DB2 version 7 on a mainframe running OS 390
version
2.10 and have a requirement to log information any time someone selects
data
from our application's DB2 tables. Does anyone know of any products that
have the capability to log select access to DB2 tables or has anyone had
to
do this before?

Robert Bandy
ACS State Healthcare
Database Administrator
[login to unmask email]
770-829-1093



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can

Steve Whittaker

Re: Logging select access to DB2 tables
(in response to Rodney KRick)
This might not be what your looking for but CA has a Log Analyzer tool that
allows
you to look at activity against a specific object and does alot more too.
I use it occasionally to find out who's done what to some of our tables etc
based
on day, time etc....


--Steve....

-----Original Message-----
From: Bandy, Robert [mailto:[login to unmask email]
Sent: Tuesday, January 07, 2003 8:20 AM
To: [login to unmask email]
Subject: Logging select access to DB2 tables


We are currently running DB2 version 7 on a mainframe running OS 390 version
2.10 and have a requirement to log information any time someone selects data
from our application's DB2 tables. Does anyone know of any products that
have the capability to log select access to DB2 tables or has anyone had to
do this before?

Robert Bandy
ACS State Healthcare
Database Administrator
[login to unmask email]
770-829-1093





Elaine Morelli

Re: Logging select access to DB2 tables
(in response to Steve Whittaker)
SELECTS are not stored in the DB2 log. As far as I know you would have to
turn an an audit trace in DB2.

Elaine Morelli
Certified Technical Sales Specialist S/390 DB2 Data Base Tools
3031 North Rocky Point Drive West
Tampa, Fl. 33607
Phone: 813 792-5981 - Is always forwarded to my cell phone
E-Mail: [login to unmask email]



Phil Grainger

Re: Antwort: Logging select access to DB2 tables
(in response to Elaine Morelli)

At first glance AUDIT CLASS 5 appears to do what you want, but then in the
Admin Guide (chapter 14) it says "All read accesses to tables that are
identified as AUDIT ALL. As in class 4, only the first access within a DB2
unit of recovery is recorded, and
references to a parent table are audited." which, to me, says that only the
FIRST SELECT against a particular table in any one unit of work will be
audited, and I guess this is only the start of what you need.

Just for the record whilst I am here, normally Unicenter Log Analyzer
reports on the DB2 log and can therefore only report on changes to data.
However, we do have the ability within Log Analyzer to report on SMF data
generated by the DB2 AUDIT traces.

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]=20


Jim Wankowski

Re: Logging select access to DB2 tables
(in response to Phil Grainger)
On Tue, 7 Jan 2003 08:20:19 -0500, Bandy, Robert <[login to unmask email]>
wrote:

>We are currently running DB2 version 7 on a mainframe running OS 390
version
>2.10 and have a requirement to log information any time someone selects
data
>from our application's DB2 tables. Does anyone know of any products that
>have the capability to log select access to DB2 tables or has anyone had to
>do this before?
>
>Robert Bandy
>ACS State Healthcare
>Database Administrator
>[login to unmask email]
>770-829-1093
>
>
>
the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can

Hi Robert
The only way to track select activity is to use the Audit trace. You need
to alter the table and add the AUDIT keyword then activate the trace.
Regards
Jim Wankowski
Quest Software



James Campbell

Re: Logging select access to DB2 tables
(in response to Jim Wankowski)
As others have pointed out, the best native DB2 is to have AUDIT ALL
turned on, which generates an IFCID trace when the table is selected.
However it only tells you that someone has selected from the table -
not what they have selected.

If you want to know what someone has selected, the only way I know is
to write a table UDF, and set your security so that no-one (apart, of
course from SYSADMs, the table's DBADMs, and the UDF bind OWNER) can
select from the table. The UDF code can then log, in another table,
whatever's required.

James Campbell

On 7 Jan 2003 at 8:20, Bandy, Robert wrote:

> We are currently running DB2 version 7 on a mainframe running OS 390 version
> 2.10 and have a requirement to log information any time someone selects data
> from our application's DB2 tables. Does anyone know of any products that
> have the capability to log select access to DB2 tables or has anyone had to
> do this before?
>
> Robert Bandy
> ACS State Healthcare
> Database Administrator
> [login to unmask email]
> 770-829-1093
>
>
>



Dave Nance

Re: Logging select access to DB2 tables
(in response to James Campbell)
Robert,
As far as capturing what is logged you can do it yourself or purchase products from a few of the third party vendors. We, currently, use CA's Log Analyzer, it works out quite well for us. We have a job that runs against the logs, capturing changes to a few tables and puts them out to a flat file, due to the amount of data. We do take the primary key of the row that was updated and write that to a DB2 table along with the file name and timestamp that the data was written to. This allows us to query the table looking to see if data changed for a particular person on given dates, then be able to view the exact file(s) that have the pertinent info.
Your original question was asked a couple of months ago, and I believe the answer was you would have to change your application to write out some type of log file or table. Check the archives to be sure.

Dave Nance
First Health Services, Corp.
(804)527-6841

>>> [login to unmask email] 1/7/03 8:47:03 AM >>>
Is there any way to snag what is being logged for an app and insert that to
a different table?

-----Original Message-----
From: Bandy, Robert [mailto:[login to unmask email]
Sent: Tuesday, January 07, 2003 7:20 AM
To: [login to unmask email]
Subject: Logging select access to DB2 tables


We are currently running DB2 version 7 on a mainframe running OS 390 version
2.10 and have a requirement to log information any time someone selects data
from our application's DB2 tables. Does anyone know of any products that
have the capability to log select access to DB2 tables or has anyone had to
do this before?

Robert Bandy
ACS State Healthcare
Database Administrator
[login to unmask email]
770-829-1093


the






This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.



Rob Barbour

Re: Logging select access to DB2 tables
(in response to Dave Nance)
Hi Robert,

You may want to look at P-Tracker.
http://www.esaigroup.com/products/applications.htm

It allows you to monitor application access and data access to z/OS,
OS/390 program and files. The data access reporting is a custom
offering. This product is typically used for programmer analysis,
asset management, and reporting.

HTH
--
Best Regards,
Rob Barbour
ESAi
Central Florida Research Park
12565 Research Pkwy, Suite 300
Orlando, FL 32826
1-866-GO-4-ESAI
http://www.ESAIGroup.com
Web Enablement... Legacy Integration... Services...
NETPHANTOM - Rapid S/390 & AS/400 Web and Wireless Integration
HOTCOPY - Eliminate Batch FTP Headaches with Intelligent Data Forwarding
BCV4 - Make DB2 Clone or SAP HSC Available in 15 Minutes
Browser Terminal Emulation - Save on PC Maintenance with TTWeb



"Bandy, Robert" wrote:
>
> We are currently running DB2 version 7 on a mainframe running OS 390 version
> 2.10 and have a requirement to log information any time someone selects data
> from our application's DB2 tables. Does anyone know of any products that
> have the capability to log select access to DB2 tables or has anyone had to
> do this before?
>
> Robert Bandy
> ACS State Healthcare
> Database Administrator
> [login to unmask email]
> 770-829-1093
>
>
>