Re: Trigger blocks our AS400 applications

Dieter,

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.bender@xxxxxxxxxxx> schreef in bericht
news:gvagh2-or7.ln1@xxxxxxxxxxxxxxxxxxxxxx
Luc,

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.

Dieter Bender


caura wrote:

Dieter,

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.bender@xxxxxxxxxxx> schreef in bericht
news:bo3gh2-067.ln1@xxxxxxxxxxxxxxxxxxxxxx
Hi,

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 wrote:

Dieter,

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.bender@xxxxxxxxxxx> schreef in bericht
news:aemfh2-eg6.ln1@xxxxxxxxxxxxxxxxxxxxxx
Hi,

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

news.wplus.ne wrote:

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).

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





.

Relevant Pages

  • Re: Trigger blocks our AS400 applications
    ... indeed, when I take a record in lock for update, ... another session can read the record. ... SECOND TIME with a lock for update when the trigger is active ??? ...
    (comp.sys.ibm.as400.misc)
  • Re: Quick question re: locks and triggers
    ... TableX has insert trigger TableXInsertTrigger that includes the following ... It might be that it does not take a table lock, ... or at least use holdlock hints on the critical ...
    (microsoft.public.sqlserver.programming)
  • Re: Trigger refinement for a Springfield 1884 45-70 trapdoor
    ... The problem is not the trigger. ... The sear holds the tumbler and hammer till you press the ... If you take the lock out of the gun and cock the hammer. ... at the notch in the tumbler with the sear holding it. ...
    (rec.guns)
  • Re: NFS Locking Issue
    ... to am-utils running into some race condition the other problem is related to throughput, freebsd is slower than linux, and while freebsd/nfs/tcp is faster on Freebsd than udp, on linux it's the same. ... If you can help to produce simple test cases to reproduce the bugs you're seeing, ... First, architectural issues, some derived from architectural problems in the NLM protocol: for example, assumptions that there can be a clean mapping of process lock owners to locks, which fall down as locks are properties of file descriptors that can be inheritted. ... Once you've established whether it can be reproduced with a single client, you have to track down the behavior that triggers it -- normally, this is done by attempting to narrow down the specific program or sequence of events that causes the bug to trigger, removing things one at a time to see what causes the problem to disappear. ...
    (freebsd-stable)
  • Re: Quick question re: locks and triggers
    ... Yeah, without an exclusive lock held during the trigger, if you decided to ... held for the length of the transaction in terms of blocking other WRITERS to ...
    (microsoft.public.sqlserver.programming)