Do you Web programmers have a message or code routine for Record locks
due to the condition: SQL0913N Unsuccessful execution caused by deadlock
or timeout.
Can SQL be written so that SQL0913N does not result when web user runs
this select statement:
select VARCHAR(AMSDTA.F42019.SHAUTN,50,37) as SHAUTN,
sum(AMSDTA.F42119.SDITWT/10000) as NWEIGHT,
AMSDTA.F55TRK.TR$TKG as TRTKG, AMSDTA.F55TRK.TR$TKX as TRTKX,
AMSDTA.F55TRK.TR$TKQ as TRTKQ, AMSDTA.F55TRK.TR$TKR as TRTKR,
AMSDTA.F55TRK.TR$TKA as TRTKA, AMSDTA.F55TRK.TR$TKB as TRTKB,
AMSDTA.F55TRK.TR$TKD as TRTKD, AMSDTA.F55TRK.TR$TKT as TRTKT,
AMSDTA.F55TRK.TR$TKP as TRTKP, AMSDTA.F55TRK.TR$TKN as TRTKN,
AMSDTA.F55TRK.TR$TKZ as TRTKZ, AMSDTA.F55TRK.TR$TKU as TRTKU,
AMSDTA.F55TRK.TR$TKV as TOTAL, VARCHAR(AMSDTA.F42019.SHMOT,50,37) as
SHMOT,
min(AMSDTA.F42119.SDADDJ) as SDADDJ, ABALPH as CARRIER,
(select min(ZIAC04) from CLTSEC.F55ZIP
where ZIADDZ=AMSDTA.F55TRK.TR$TKJ
group by ZIAC04) as REGION
from AMSDTA.F55TRK
join AMSDTA.F42019 on AMSDTA.F42019.SHAUTN = AMSDTA.F55TRK.TRAUTN
(and when a record in F42119 is held for update by another user)
+++++++++++++++++++++++++++++
Or if SQL0913N is produced, do you have a "user-friendly" message to
display instead of this:
Warning: db2_exec() [function.db2-exec]: Statement Execute Failed in
/var/www/localhost/htdocs/modules/dbo/as400.class.php on line 24
Warning: Error " [IBM][CLI Driver][AS] SQL0913N Unsuccessful execution
caused by deadlock or timeout. Reason code "AMSDTA .F55TRK ".
SQLSTATE=57033 SQLCODE=-913 " with dbo_as400 Query :
select VARCHAR(AMSDTA.F42019.SHAUTN,50,37) as SHAUTN,
sum(AMSDTA.F42119.SDITWT/10000) as NWEIGHT,
AMSDTA.F55TRK.TR$TKG as TRTKG, AMSDTA.F55TRK.TR$TKX as TRTKX,
AMSDTA.F55TRK.TR$TKQ as TRTKQ, AMSDTA.F55TRK.TR$TKR as TRTKR,
AMSDTA.F55TRK.TR$TKA as TRTKA, AMSDTA.F55TRK.TR$TKB as TRTKB,
AMSDTA.F55TRK.TR$TKD as TRTKD, AMSDTA.F55TRK.TR$TKT as TRTKT,
AMSDTA.F55TRK.TR$TKP as TRTKP, AMSDTA.F55TRK.TR$TKN as TRTKN,
AMSDTA.F55TRK.TR$TKZ as TRTKZ, AMSDTA.F55TRK.TR$TKU as TRTKU,
AMSDTA.F55TRK.TR$TKV as TOTAL, VARCHAR(AMSDTA.F42019.SHMOT,50,37) as
SHMOT,
min(AMSDTA.F42119.SDADDJ) as SDADDJ, ABALPH as CARRIER,
(select min(ZIAC04) from CLTSEC.F55ZIP
where ZIADDZ=AMSDTA.F55TRK.TR$TKJ
group by ZIAC04) as REGION
from AMSDTA.F55TRK
join AMSDTA.F42019 on AMSDTA.F42019.SHAUTN = AMSDTA.F55TRK.TRAUTN
join /var/www/localhost/htdocs/modules/dbo/dbo.class.php on line 59
Warning: db2_fetch_assoc() expects parameter 1 to be resource, boolean
given in /var/www/localhost/htdocs/modules/dbo/as400.class.php on line
72
Warning: db2_free_result() expects parameter 1 to be resource, boolean
given in /var/www/localhost/htdocs/modules/dbo/as400.class.php on line
75
<end of message>
I am somewhat familiar with Record-Level Lock States and Compatibility,
based on my reading of article
http://www.mcpressonline.com/programming/java/db2-locking-and-concurrenc
y-for-java-developers.html
which has an excellent discussion of record lock states. If one process
has record 65 at Record-Level Lock State UPDATE, record 65 is locked for
update intent. Another job may read the record but may not obtain a
read or update lock on it until the lock is released. Therefore,
another job attempting READ is at an Incompatible Lock state.
In our JD Edwards World (RPG) applications, JDE programs will notify the
user of a record lock in a friendly way.
RPG program P98RLCK is invoked, telling the user the name of the person
and the job that is locking the record. But if program is not written
to call P98RLCK, the user will at least get this friendly message:
Msg 0007 Record in Use
CAUSE . . . . The master file record you are attempting to access is
currently being updated by someone else.
RESOLUTION. . Attempt to access the record at a later time
Today I discovered which record was held for update by running command:
DSPRCDLCK FILE(F42119)
File . . . . . . . . : F42119 Member . . . . . . . : F42119
Library . . . . . : AMSDTA
Record Lock
Number Job User Number Status Type
2151735 QPADEV001M MMATHIS 876442 HELD UPDATE
I am somewhat familiar with results available from running these:
STRPFRTRC SIZE(64 *MB)
ENDPFRTRC MBR(FREIGHT1 )
PRTLCKRPT MBR(FREIGHT1 ) RPTTYPE(*ALL) MINWAIT(1)
PRTLCKRPT MBR(FREIGHT1 ) RPTTYPE(*TOD) MINWAIT(1)
midrange.com
