Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Unanswered: Db2_evaluncommitted

    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?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2004
    Posts
    9
    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?

  4. #4
    Join Date
    Jan 2004
    Posts
    9

    caterves 1960-70

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •