Re: Question on File locking

On 13-Aug-2014 10:44 -0500, Vinay Gavankar wrote:

<<SNIP>>
Currently there is no "audit" information on the Member file about
which fields changed and when they changed etc.

Problem:

A new Client (US Federal Government) requires detailed audit
information on the Member file. They assess a penalty of $1 Million
for every missed or incorrect audit information (I don't know if and
how they enforce it, but we are told that is what the contract
says).

To be accurate, the audit records need to be in exact sequence of
how the updates to the Member file occurred. (@Buck: If they are not
in the update sequence, then they would be considered out of
sequence).

Approach to solving the problem:

The simplest way of capturing Audit information would be to capture
Before and/or After information on every update to the file. As the
Member File is updated from lots of programs, the most logical place
would be to use a Trigger on the file. <<SNIP>>

The most logical answer is *surely not* to use triggers. The most logical approach *surely is* to use the logging feature provided by [built into] the DB2 for i database. That feature is the Journal feature.

The simplest means to track the update and insert activity to the audited-file is by issuing the Start Journal Physical File (STRJRNPF) to effect logging [called journaling] of the file activity to a Journal (*JRN), for which all update activity can be logged to the logs call a Journal Receiver (*JRNRCV). That is, use the logging facility provided by the OS Database\Journal feature.

Anyone requiring a "detailed audit information" on a file is surely not going to accept a flat-file of entries that are claimed-to-be an audit-trail. Given anyone could create such a file using any editor on any system, the data of which can then be sent to the server, and then presented as an audit-trail seems ridiculous. Using a built-in logging facility that is at least somewhat tamper-proof, and is at least quite effectively read-only, seems the only appropriate means by which one could claim to have an /audit-trail/; and the only data reasonably accepted by another party as a valid audit-trail.