Re: Track all records read from a table

Hi, Charles and Nathan:

All good points.

With IBM i 7.1, IBM introduced "field procs" in DB2 -- these are SQL-defined functions that act on only certain fields -- in some ways, these behave similar to a "read" trigger, and they can act on data before the application "sees" the result. AFAIK, if a given SQL query or FETCH does not "touch" that column, the "field proc" is not invoked at all, so "no overhead" in that case.

Perhaps "field procs" could be used on "sensitive" columns to also log access to a journal, similar to what has been discussed in this thread?

Also, I would think that good database design (normalization, etc.) could segregate the sensitive columns into their own table(s) that could be secured separately, and access to those tables monitored more closely, e.g. with a "read" trigger or "field procs" etc. -- and that way, the normal access to the rest of the data could continue to run "as usual." Only when the application needs the "sensitive" data would it access those other tables.

Mark S. Waterbury

> On 3/22/2016 11:30 PM, Charles Wilt wrote:

Nathan,

Nope, read triggers are a pretty big hit..

"Read triggers have been implemented in OS/400 to satisfy a U.S. federal
statute (HIPAA) requiring that all access to patient records in the medical
profession be logged and auditable.

Read triggers are supported only via external triggers and are implemented
via the new read trigger event, and there is no support for a read trigger
with SQL triggers. The reason for this is that a read trigger has a very
specific function; it is not intended for general use because
indiscriminate use can have a significant negative impact on system
performance and throughput due to System i and iSeries performance
characteristics being altered.

Any read-only access to a file with a read trigger defined—either user
program or system function—will cause the trigger to fire. This means that
using the Copy File (CPYF) command, the Display Physical File Member
(DSPPFM) command, query products like Query/400 or SQL, or user-written
display or report programs will cause a read trigger to fire every time a
record is accessed in each of the above scenarios. If you access one
million records, you just fired the read trigger one million times. Get the
picture?

The use of a read trigger will disable some system functions that are used
to optimize system performance and throughput. For example, a read trigger
prevents adaptive blocking and double buffering when processing a file
sequentially (arrival sequence) and asynchronous fetch of the next record
in keyed sequence when processing a file sequentially by key or index. This
means that records are read from the file one at time instead of in
optimized blocks, dramatically increasing file I/O time.

The effect is that batch programs using these processing techniques on a
file with a read trigger defined will run orders of magnitude longer. This
negative impact on performance can be mitigated to a certain extent by
increasing CPU power, adding memory, and adding disk arms."
See more at:
http://www.mcpressonline.com/programming/sql/sql-triggers-and-other-trigger-enhancements-in-v5.html#sthash.AFger7P4.dpuf


Now, I could have sworn that way back when they were introduced, IBM docs
had some details of the hits. But there's nothing I could find in the
current docs. I did find this in the v5r2 infocenter
https://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/dbp/rbafomst423.htm
"In addition, you should use extreme caution when using read triggers.
Using a read trigger could cause a trigger to be called for every record
that is read. During a query, this means that triggers could be called many
times as records are processed multiple times by the query. This could
impact system performance."

Lastly, I found this note saying that even at 7.2, a query over a table
with a read trigger is forced to use the CQE instead of the SQE.
https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/Classic%20Query%20Engine%20%28CQE%29%20and%20SQL%20Query%20Engine%20%28SQE%29%20Differences

Charles