Question: I am using [db2](/questions/tagged/db2 "show questions tagged 'db2'") (if you have a solution with another database, I am still interested), and am trying to identify every row that is fetched from a specific table. The solution needs to be at the database level, because I do not have access to the actual `SELECT` statements that cause the fetch. I would at a minimum like to capture one or more column values into a log/table for every row that is fetched from a specific table.

Here's an example:

Table1 structure
----------------

CustNo (primary key)  

CustName

Table 1 (two rows)
------------------

12345, Joe's Crab Shack  

98765, Morton's The Steakhouse

Process
-------

1) Before select, log file is empty

2) Execute: SELECT CustName from Table1 where CustNo=12345

3) After select, log file contains:

```
LogFile1
---------
12345

```

4) Execute: SELECT \* from Table1

5) After select, log file contains:

```
LogFile1
---------
12345
12345
98765

```

Thank you for any advice/recommendations....

Answer: 
If you're willing to call a SP to log this info, you might want simply to add a \*READ trigger. It's rarely a good idea to try to get some function to run whenever any record is read from a file, but a \*READ trigger is possibly the most efficient way possible.

```
ADDPFTRG FILE(X) TRGTIME(*AFTER) TRGEVENT(*READ) PGM(Y)

```

Use that form of the command to add your "read-only" trigger program (Y) to a file (X). Program Y should probably do something fast like push the relevant data items onto a data queue. Then have multiple batch instances of a program that pulls entries off the queue and writes them to a log file. You really don't want a read-only trigger doing any more work than possible, and database I/O should be off the list.

Expect performance to suffer some.
You can get the operations on the database via db2audit, but you cannot get the values used. Using the values for audit or logging will compromise sensitive data.

<http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0002072.html>

Actually, if you put the ID of all read rows for a given table, it is like copying the ID column several times in the log table. At the same time, it does not give any order context, because the order in which the rows are inserted is not the same order as the rows will be stored or retrieved.

You have to rethink your logging strategy, because just inserting the 'fetched' ID is not enough. You also have to insert some context information, like who (user), when (date), from where (machine) in order to exploit that data.

Another thing you can do is to wrap the select in a stored procedure and insert the ID values in the log table, before returning the opening cursor to the caller.