Trigger blocks our AS400 applications

["Caura" <luc.pittoors@xxxxxxx>]

Hi,

We need to log every access on a specific (main)-file of our system.
(not our choice, but a demand of a business partner)
The only way to accomplish this, that we know of, is using a read-trigger.

So we did some tests (V5R2).

We made a (read-) trigger pgm that writes the record and some ctrl data to a
logfile.

If an application takes a record in Update-lock (non exclusive lock) during
a transaction,
and at the same time the  (read)-Trigger is actif,
no Query, Read, ODBC or whatever can read the File beyond that record.

=> CPF5027 "Record in use" ,errorcode = 2
job ####/user/$$$$$$$ was not waiting on a lock held by this job

If I remove the trigger, everything works as expected.
The indicated job is the one that has read the record for update.
"This job" did not take any lock.


Even worse, with not any used record (not in update nor in read),
if we want to access the file using a Join Logical file ,
the Read instruction blocks before any read has been done .

=> MCH3601, pointer not set  in QSYS/QDBGETKY
RMVPFTRG  and all reads work fine again ...

If I want to access the file (FLI) via a simple Query and a record has been
taken for update in another session
"runqry qry(*NONE) qryfile(FLI)"  I get the error  QRY5050 .
Commitment control with lock level *ALL is active .

The read-trigger apparently changes the commitlevel to *ALL ,
as for me, there is not any reason to do so.

Is there another way to log all accesses on a file (without affecting other
files in the database)
Is there any circumvention for this misbehaviour of the triggerlogic?

luc