... but Chuck, he's talking about a table created within the QTEMP that is
locked by another job!
AFAIK a table created within QTEMP cannot be registered within a journal.
And the QTEMP is unique for each job (even though I know the QTEMPs are not
really temporary and get managed somewhere).
May be the problem could be solved by using DECLARE GLOBAL TEMPORARY TABLE
instead of CREATE TABLE QTEMP/MYTABLE.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von CRPence
Gesendet: Sunday, 20. February 2011 20:09
An: midrange-l@xxxxxxxxxxxx
Betreff: Re: SQL updating qtemp table record lock trouble .
On 2/18/11 7:54 AM, Schutte, Michael D wrote:
I'm stumped...
I have an SQLRPGLE program that is trying to update a QTEMP table
but it's producing an error saying that a record in QTEMP/RCTDTL is
in use. The problem is that it says that it's coming from an
interactive job from another user. I know that QTEMP is unique to
each user, so I am certain that the user doesn't have the record
locked in QTEMP/RCTDTL. This user is responsible for maintaining
IMSPITM so it's likely that they have a record locked in this file
because they have it up in the maintenance program. I have verified
that they are in the maintenance program.
UPDATE QTEMP/RCTDTL
SET (ITMTYP, ITMMFR, ITMITG, ITMSFX,
ITMPUM, ITMIUM, ITMRUM, ITMPCF, ITMICF) =
(SELECT ITMTYP, ITMMFR, ITMITG, ITMSFX,
ITMPUM, ITMIUM, ITMRUM, ITMPCF, ITMICF
FROM IMSPITM
WHERE ECDCONO = ITMCONO
AND ECDCPY = ITMCPY
AND ECDITM = ITMITM)
How can I tell SQL not to gain update access to IMSPITM?
In the job log I do have a message saying "Subselects processed as
join query". I'd imagine that this is what is causing the update
access to IMSPITM.
Here's the actual error message in the job log.
Record 46927 in use by job 251535/USER/JOB
Cause . . . . . : You cannot get record number 46927 record format
FORMAT0001 member number 2 in member RCTDTL file RCTDTL in
library QTEMP because record number 46927 is being used by job
251535/USER/JOB. The error code is 2. If the error code is 1,
then this error occurred when record number 46927 was locked by
job 251535/USER/JOB which was waiting on a lock held by this
job. If the error code is 2, then job 251535/USER/JOB was not
waiting on a lock held by this job.
I also verify that the record number in the error message happens to
be the record that the user has up in the maintenance program.
Any ideas?
An UPDATE-lock held by A job will prevent a READ-lock by another job,
but will not prevent a "dirty read" which is effectively a request to
read with no-lock requested. I believe an SQL UPDATE request running
WITH NC [commit level *NONE] isolation requires the READ-lock on row
data [from a subselect] being used for updating a row against which an
UPDATE-lock would I expect would already be held. That is to say, I
believe the subselect query is implicitly [already] for-read-only rather
than for-update, as desired. And that the locking error arises from the
requirement for the subselect query to get a READ-lock, not due to the
subselect query asking to obtain an UPDATE-lock. That effect should be
visible in a DSPRCDLCK of the table while the WAITRCD time is in effect;
i.e. while the SQL UPDATE job is in LCKW for the RcdLck.
If the lock in the other job is under isolation, then request the SQL
UPDATE to have WITH UR [or WITH CHG] to run under isolation versus what
was presumably done WITH NC [as typical for files in QTEMP to avoid
journaling]. When running with isolation level *CHG, the "uncommitted
changes in other jobs can be seen" so that should allow the subquery of
the SQL UPDATE to access the data from the other TABLE without obtaining
a READ-lock; i.e. enable the dirty read, thus no lock conflict.
Having suggested that, I took a quick peek at some documentation for
row locks for commitment control. That confirms the requirement for the
READ-lock for "Subqueries (update or delete capable cursor or UPDATE or
DELETE non-cursor)" requests, but seems not to allude to the ability of
two *CHG jobs not conflicting for their locks; UPDATE-lock held by one,
and a READ-lock requested by another for its subquery. I tested on v5r3
both the UPDATE ... WITH CHG and the UPDATE ... WITH NC, the former
UPDATE completed with dirty data [the effect of a prior UPDATE in
another job having been performed but not committed being visible to the
subselect query], and the latter UPDATE terminated with the lock wait
timeout CPF5027 and SQL0913 [or -913].
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafydico
mm.htm
Regards, Chuck
midrange.com
