Natural Transaction is holding DB2 Table

, ,
1

Hi,

I am getting issue when performing DELETION of records from DB2 Table while calling from Online Natural Program . There is no index on DB2 table. It is working fine in TEST region but gettting issues while executing in Production. I read manual that
in TEST SQL run in dynamic mode whereas in production it is static.

  • Delete the records from the table TBPOPP28

SELECT REC_CNT
INTO VIEW TBPOPP28
FROM TBPOPP28
DELETE FROM TBPOPP28
END-SELECT
END TRANSACTION
*

When I check in Table Editor to check the updates, getting following message as table is on hold be DELETE statement. Can one help me why it is happening so.

ETI801E Pgm: ETI@PREP Stmt: 00462 Type: FETCH

DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK
DUE TO DEADLOCK OR TIMEOUT. REASON 00C9008E, TYPE OF RESOURCE
00000D01, AND RESOURCE NAME 00000356.00000235

2

When you make a SELECT without clause where, all the records has selected in the memory of the DB2.

So, when the DB2 try make a instruction DELETE, the records has in use (hold in the clausule SELECT) and has the error.

Try make the DELETE instruction after close the SELECT (after the END-SELECT).

"
SELECT REC_CNT
INTO VIEW TBPOPP28
FROM TBPOPP28
END-SELECT
DELETE FROM TBPOPP28
END TRANSACTION
"

But, caution because this instrutions deleting all the records of table TBPOPP28. Are you sure this?