Re: Track all records read from a table

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

On Tue, Mar 22, 2016 at 5:17 PM, Nathan Andelin <nandelin@xxxxxxxxx> wrote:

Mark,

That assumption about backups firing READ triggers sounded fishy from the
beginning. Thanks for raising the question. Copy commands (CPYxxxxxxx) may
be different story. That would be good to test.

I don't get why anyone would transfer a read buffer to a journal, rather
than to a log file. Wouldn't that just make it harder for users to query?

Regarding trigger overhead, consider an RPG "read" opcode; Under the
covers, the RPG program makes procedure calls nested at least a couple
layers deep. Depending on how a read trigger is implemented, it may only
add microseconds.





On Tue, Mar 22, 2016 at 2:57 PM, Mark S Waterbury <
mark.s.waterbury@xxxxxxxxxxxxx> wrote:

Mike:

To my knowledge, there is no "issue" to do with "back-ups" when using
triggers.//IBM i (and OS/400) save and restore operate on the object as a
whole (the database *FILE) saving or restoring the entire "container"

(data

space, etc.); so it does not operate at a record or row level, and so,

NO__

triggers are ever "fired" during normal save or restore operations.

Vengoal Chang posted a program named "TRGREADJRN" on his web site here:

http://blog.xuite.net/vengoal/as400/61009651

The web page is in an Asian language but the ILE RPG IV source code is

in

English. The TRGREADJRN program writes a journal entry to record details
of each "read" of the file the trigger is attached to.

Hope that helps,

Mark S. Waterbury

On 3/22/2016 4:12 PM, Mike Cunningham wrote:

Thanks to everyone who has added to this thread. The backup issue could
be a problem for me as the file I need to do this on is one of our

largest

with 2.5M records. It gets backed up nightly so have 2.5M read triggers
fire could cause a problem. My trigger could ignore that job and not
journal (or data queue) that read but the trigger processing overhead
would still take place. I could also remove the trigger just before

backup

starts and then put it back after backup ends but that's something I

don't

like to do as it could lead to problems someday when adding it back

fails

and the error is hidden deep in the backup joblog.

My other option is change code in the apps that read this data and
presents it do a user (all in-house source code) and have each app

journal

the read.

And since I first asked this question nothing I do is going to meet the
need 100%. I remembered that we send the data I need to read journal

off

to a could service that one of our offices use and the user can view the
data that needs tracked from that service. I have no way to monitor or
control access to the data once its sent.

Mike Cunningham

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.