how to code for: SQL0913N unsuccessful execution caused by deadlock or timeout

Eric Lehti

VIP Member
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-concurrency-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)
 

Eric Lehti

VIP Member
resolution provided by IBM tech support.

Solution we selected. We will edit our SQL statements to use Uncommitted Read (UR) in DB2 Connect.

Alternative solution 2. Use Systemi Access ODBC driver for Linux instead of DB2 Connect.

Alternative solution 3. Upgrade to V6R1 which allows isolation level *NONE
 
Top