I'm working on an application that logs every business transaction to an audit trail, and I have a requirement to be able to correlate events in this audit trail with records in the Journal of an IBM System i (AS400) that is used as an RDBMS by that application. Any idea how can this be achieved?
I was thinking if there is a way for the application to pass some sort of business transaction ID to the database and have it stored with it's corresponding journal entries. Is this possible?
The journal entries carry every column of the journalled table, so if there is a transaction ID in the row, it will be in the journal. It is very rare to encounter a business system without a transaction ID; it may be an invoice number, a purchase order number, a requisition number and so on. If you have come across one, there is almost always a primary / foreign key pair that identifies the transaction. Think customer number, item number for a sale, vendor number, check number for accounts payable, etc.
Frankly, the journal tends to be a better audit trail than any application-generated audit trail because the journal will catch 'fix it' situations like a programmer using an SQL UPDATE to repair rows incorrectly updated due to a program bug and the like.