DB2 Triggers

[Satpal]

We have DB2 Triggers on a number of files. There has been a new requirement to find out the name of the Application/UBE which triggered the DB2 Trigger program to be fired.

I have no problems picking the Program ID of an AS400 program, but this utility does not work for OneWorld programs.

Is there anyone out there who has had similar requirements/problems. If so, I would appreciate it very much if you could share your findings/solutions.

Thanks in advance.

 

[DBohner-(db)]

In most cases, the best way to identify what JDE process updated the record is to look at the PID column of the updated/inserted record.

However, many BSFNs and other process fail to update the PID correctly.

I fought with this a great deal, too. The thing to do is for all of us to complain that JDE needs to do a better job presenting audit material for transactions.

Daniel

 

[Ian_Simmons]

I've had exactly the same issue with user ids on database table triggers, which are more reliable than TERs... the iSeries (AS/400) user profile is readily available but not the same as the JDE/PSFT user! Has anyone found a smart way of picking these up (e.g. interrogating job open tables, F0092, relative record number etc.)?

The AS/400 provides so much information and help it's a shame the software hides so much from you in the interests of being cross-platform.

 

[JEMILLER]

Ian,

To be fair, most tables contain a USER column which is updated by most OneWorld applications when the record is added or changed. (granted "most of the time" is not acceptable when trying to audit the database.) In the case of a delete this would not do you any good as you could not assure that the before-delete value of this field was the same as the user actually doing the delete.

I suggest two solutions:

1. Use separate OneWorld System User Ids for each OneWorld user. Obviously this requires effort in that you must create AS400 profiles for all your OW users. I have had some clients who insisted on this. They felt that the additional overhead in adding users was outweighed by the ability to use AS400 native tools to watch user activity.

2. From SP21 and on you can use the CFR21 audit capabilities. This will generate auditing triggers for you. When changes are made through the OneWorld applications the OW user making the change as well as pre and post images of the record being changed are logged. You may choose which columsn are audited. This feature works well once configured. The configuration process is a little delicate but once a table is configured for auditing everything work flawlessly. (In my experience, your mileage may vary.)

Regards,