Trigger blocks our AS400 applications
news.wplus.ne
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).
If an application takes a record in Update-lock (non exclusive lock) during
a transaction,
and at the same time there is a (read)-Trigger 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
reg.f...@wedgwood.com
object?
This should catch everything you need.
reg.f...@wedgwood.com
Chuck Ackerman
something new?
chuck
Opinions expressed are not necessarily those of my employer.
<reg.f...@wedgwood.com> wrote in message
news:1111768882.4...@g14g2000cwa.googlegroups.com...
Jonathan Ball
> I wasn't aware that journaling captured reads to a record. Is this
> something new?
He's talking about audit journaling
(http://tinyurl.com/65pkf), not database journaling.
I'm not convinced it would provide the necessary
information, but I can't seem to find easily a source
of what info *is* captured in audit journaling.
caura
audit journaling gives me that someone uses the file,
not what records of the file are being read.
So no, that wouldn't give me everything I need .
luc
<reg.f...@wedgwood.com> schreef in bericht
news:1111768913....@o13g2000cwo.googlegroups.com...
Dieter Bender
you didn't specify how you did create your trigger exactly, but even a read
trigger with lock level *all would allow concurrent reads. the behaviour
seems to me to be a bug. What Group PTF level do you have for the database
(ptf sf99502)?
Dieter Bender
caura
Not what record is read in that file.
So this is not an option for our problem.
We'rs still stuck with a read-trigger.
If it would work.
(see original post)
luc
"Jonathan Ball" <jon...@whitehouse.not> schreef in bericht
news:%5%0e.3909$gI5....@newsread1.news.pas.earthlink.net...
caura
the trigger is a simple RPGLE pgm.
(created with actgrp(*caller).
On the trigger I didn't even specify a locklevel *All.
It gets the record parm and writes a logfile containing some controlefields
(user/timestamp/...) plus the recordstring.
We changed the trigger pgm to a simple CLLE that just sends a message.
Same behaviour.
I too suppose it's a bug and we did contact IBM.
They said it was a highlevel issue for them,
but since then we didn't hear from them
(the only thing they have sent back was a msg with a description of the
problem
and the question wether they had well understood the problem)
So I'm looking for alternatives - if there would be any -.
PS: last PTF in the DB2 GroupPTFs is SI14558 (dd 13/03/2005)
luc
"Dieter Bender" <dieter...@t-online.de> schreef in bericht
news:aemfh2-...@modula.bender-dv.de...
Dieter Bender
first of all: there is no alternative to a read trigger to watch all
accesses to a record of a special file.
did you check, wether there might be a damage of this file? does the same
thing happen to other files with a read trigger on your machine? at the
moment I have no access to a V5R2 machine to have a try if read triggers
would work on my machine - anybody out there working with read trigger?
Dieter Bender
caura
the file is OK.
as soon as I remove the trigger our applications work again as they should.
the phenomenon is reproducable with other files.
I'm afraid read-triggers are nearly not used.
(performance and other reasons).
luc
"Dieter Bender" <dieter...@t-online.de> schreef in bericht
news:bo3gh2-...@modula.bender-dv.de...
Dieter Bender
I've just started up my V5R1 box and was playing around with a read trigger
and I didn't see any locking problems, even using higher lock levels, than
you did.
Dieter
Dieter Bender
i agree, that read triggers are not very frequently used (none of my
customers does and I'm working extensive with sql and java apps), but on my
little 170 with V5R1 it seems to work and the overhead is in the direction
of 1 to 2 milliseconds per read (tested with select count(*) from kunde)
with about 10000 records and maybe it would be faster in a more realistic
test, because the query optimizer changes its decision with the read
trigger compared to the same statement without trigger. But in your case
there is no other way to meet your requirements and ibm has to do its work
to bring read triggers to work on V5R2.
Thomas Raddatz
Please have a look at the following APAR. Probably it is related to the problem
you have:
"Problem Summary
A file with a READ trigger on it was not properly releasing a
previous UPDATE record lock on the read of the next record when
a file was opened for I/O in a COBOL application. It was
instead releasing the READ lock initiated by the trigger."
The PTF for V5R2 is SI08599, product 5722SS1.
Thomas Raddatz.
news.wplus.ne schrieb:
caura
thanks for the suggestion.
and the APAR seems related, although not totally the same.
I had found that PTF also, but it is on our machines already a long time.
as Dieter says he notices no problem on V5R1
it must be related with the release.
I'm afraid I still have to wait on any reaction from IBM.
Dieter,
thanks for your testing,
because I was told that performance would be terrible.
You reassure me that it migth be usable
(If our problem gets solved)
luc
"Thomas Raddatz" <thomas.r...@tools400.DOTde> schreef in bericht
news:4245825E...@tools400.DOTde...
caura
I have found a V5R1 machine to do some testing too :
result : indeed, when I take a record in lock for update,
another session can read the record .
Amazing:
the record read with a lock for update, can be read in the other session A
SECOND TIME with a lock for update when the trigger is active ???
(again, when I remove the trigger the second read for update gives me the
normal message that another session uses the record)
Probably IBM has tried to solve this problem in high need, and in their
panic restricted accesses to much ?
PS: When I try to read the file via a Join with another file in V5R1 I still
get the 'Pointer not set' error .
luc
"Dieter Bender" <dieter...@t-online.de> schreef in bericht
news:gvagh2-...@modula.bender-dv.de...
Dieter Bender
> I have found a V5R1 machine to do some testing too :
> result : indeed, when I take a record in lock for update,
> another session can read the record .
works as designed
>
> Amazing:
> the record read with a lock for update, can be read in the other session A
> SECOND TIME with a lock for update when the trigger is active ???
works as designed, read for update (with SQL) allows share read, the lock is
escalated before change occurs.
> (again, when I remove the trigger the second read for update gives me the
> normal message that another session uses the record)
for sql its a bug, for record level access it works as designed.
>
> Probably IBM has tried to solve this problem in high need, and in their
> panic restricted accesses to much ?
>
> PS: When I try to read the file via a Join with another file in V5R1 I
> still get the 'Pointer not set' error .
I didn't try up to yet.
Dieter
Dieter Bender
strange, very strange, I've read a file with a read trigger with an sql join
without any problems and with a join lf created by dds too, without any
error. Could you please elaborate with some more detail what you are triing
and what happens exactly.
Dieter
pandey....@gmail.com
Hi
We are facing the same issue. Can you please let us know how issue was resolved. Thanks
Utkarsh