We have DB2 v9 running on AIX platform.
We are implementing a multi threaded delete process that will run stored procs executing delete's in parallel.
We ran into some deadlock issues when running 4 concurrent threads. I stumbled across DB2_EVALUNCOMMITTED and DB2_SKIPDELETED. We set them to "YES" and then restarted the DB2 instances.
AFter testing again we see the same issues.
Do we have the right values? Should they be set to YES or ON or does it matter?

9.5 InfoCenter says:
Default: NO, Values: YES, NO

Although this will help with lock contention, it will not solve all issues. Try using WITH UR on your select statements and Cursors.

i have UR on all select statements.
This proc is doing heavy deletes in a multithreaded fashion. The evaluncommitted didn't seem to help.
I'm even doing a commit after every delete stmt.
My next move is to handle the deadlock error and build in a retry. Does that make sense?

Below is the stored procedure to give you an idea of what i'm trying to do. There is a etl program calling the proc and controlling the multi threading aspect:

CREATE PROCEDURE STG_DATAPURGE_PURGE_BYPOLICY (in inRunId integer, in inSetId integer, out intReturnStatus smallint )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
--COMMIT ON RETURN NO
SPECIFIC STG_DATAPURGE_PURGE_BYPOLICY
LANGUAGE SQL

BEGIN
.
.
.

DECLARE deadlock_error CONDITION FOR SQLSTATE '40001';


DECLARE polList_cursor CURSOR WITH HOLD FOR
SELECT ...
FROM ..
where ..
WITH UR ;

DECLARE tableList_cursor CURSOR WITH HOLD FOR
SELECT ..
FROM ..
WHERE ..
WITH UR ;

DECLARE CONTINUE HANDLER FOR deadlock_error
BEGIN
SET iDeadlockCtr = iDeadlockCtr + 1;
SET sDeadlockFlag = 'Y';
END;


/* GENERIC ERROR HANDLER */
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN

DECLARE sMsg VARCHAR(8000) DEFAULT '';

GET DIAGNOSTICS EXCEPTION 1 sMsg = MESSAGE_TEXT;
SET sPolicyMsgs = sTableNm || ':' || sOperationType || ' Failed: ' || sMsg;
set tPolicyEndTime = current timestamp;
UPDATE ...
COMMIT;

SET intReturnStatus = -1;

END;
/* END ERROR HANDLER */


SET ISOLATION UR;
SET p_CODE_VERSION = 1.0;
OPEN polList_cursor;
FETCH FROM polList_cursor INTO intPolicyId, iPolicyListPK,intBGRPOid, sPolicyNbr, iPolicyMod, sPurgeStatus, iAcctOid, sPolicyNbrSeqCombo, sAcctActivityFlg;
policyLoop: WHILE (SQLSTATE = '00000') DO
SET tPolicyStartTime = current timestamp;
OPEN tableList_cursor;
FETCH FROM tableList_cursor INTO sWhereClause, iSQLPK, sDBNm, sTableNm, sExecType, sSchemaNm, sArchiveEnabledFlg, sLoggingEnabledFlg ;
purgeLoop: WHILE (SQLSTATE = '00000') DO
SAVEPOINT policyUOW ON ROLLBACK RETAIN CURSORS;
SET iDeadlockCtr = 0;


/* DELETE/UPDATE LOGIC */
SET tStartTime = current timestamp;
IF sExecType = 'UPDATE' THEN
SET sPurgeSQL = sWhereClause;
ELSE
SET sPurgeSQL = 'DELETE from ' || sSchemaNm || '.' || sTableNm || ' ';
SET sPurgeSQL = sPurgeSQL || sWhereClause;
END IF;
tryDelete:
set sOperationType = 'DELETE';
set sDeadlockFlag = 'N';
EXECUTE IMMEDIATE sPurgeSQL;
IF sDeadlockFlag = 'Y' THEN
IF iDeadlockCtr > 100 THEN
/*exceed the max deadlock retry count..need to mark it as an error and get out */
SIGNAL SQLSTATE '38T00' SET MESSAGE_TEXT = 'MAX DEADLOCK EXCEEDED';
ELSE
GOTO tryDelete;
END IF;
END IF;
GET DIAGNOSTICS intRowCount = ROW_COUNT;
commit;
FETCH FROM tableList_cursor INTO sWhereClause, iSQLPK, sDBNm, sTableNm, sExecType, sSchemaNm, sArchiveEnabledFlg, sLoggingEnabledFlg ;
END WHILE; /* purge loop */


CLOSE tableList_cursor;

SET tPolicyEndTime = current timestamp;

set sOperationType = 'LOG';
UPDATE ...
FETCH FROM polList_cursor INTO intPolicyId, iPolicyListPK,intBGRPOid, sPolicyNbr, iPolicyMod, sPurgeStatus, iAcctOid,sPolicyNbrSeqCombo, sAcctActivityFlg;

END WHILE; /* end policy loop */


CLOSE polList_cursor;
SET intReturnStatus = 0;

END