Re: AW: SQL updating qtemp table record lock trouble .

On 2/20/11 9:12 PM, Birgitta Hauser wrote:

<<SNIP>> he's talking about a table created within the QTEMP
that is locked by another job!

But clarified within the same post, that the other table was where the locked record actually was. And that other TABLE was not in QTEMP, and\because the row was verified to have been accessed by another job. That was sufficient information for me, to overlook the replacement text details of the message. Also easy for me to overlook, as I know the UPDATE statement will aim to diagnose the TABLE being updated for any problems encountered; adjusting its presumptions, only after learning what the condition was that prevented the update. So maybe I am just too nonchalant about less-than-accurate messaging.

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

It can, and must be able to, for various implementations running under isolation. The original scenario, for example, resembles such a case.

May be the problem could be solved by using DECLARE GLOBAL TEMPORARY
TABLE instead of CREATE TABLE QTEMP/MYTABLE.

In fact the GLOBAL TEMPORARY TABLE has a clause NOT LOGGED that IIRC specifically prevents that TABLE from being journaled. In fact that is about the only reason I know for that feature to be any more useful than an otherwise mostly equivalent CREATE TABLE into QTEMP; i.e. the GTT effects automatic journaling to the journal QSQTTJRN in library QRECOVERY.

If the problem really were a locked row in the QTEMP of the job, held by another job, I do not expect an alternate means to effect the same [i.e. a TABLE in QTEMP] would prevent that other job from gaining access; another job should not have had access with just a CREATE TABLE, nor should another job have access with a GTT.

Regards, Chuck

CRPence on Sunday, 20. February 2011 20:09 wrote:

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/rbafydicomm.htm