Symptom

Users experience several situations where DB2 z/OS processing goes into a loop, the system CPU increases to 100%, and the PRPC 6.2 SP1 system ultimately crashes.

Two DB2 z/OS stored procedures that were shipped with the PRPC installation media generate unique work object IDs: sppc_data_uniqueid (native) and UNIQID (external). A logic error in the SQL of these stored procedures causes them to mishandle certain DB2 error conditions, for example:

-501 THE CURSOR IS NOT OPEN
-904 UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE

If one of the improperly handled DB2 error conditions is encountered during stored procedure execution, the SQL logic will loop, severely impacting performance of the entire DB2 subsystem until the stored procedure is cancelled by the DB2 resource governors or by the system operator.

Revised SQL statements correct the logic flaw in the original stored procedures.

Solution

Apply the updated stored procedures to all PRPC environments.

Drop the current sppc_data_uniqueid stored procedure and replace it with the following SQL statements:

CREATE PROCEDURE sppc_data_uniqueid

(

     out ReturnKey varchar(255),

     in tpyPrefix varchar(32),

     in tpyOrganization varchar(32),

     in tpySuffix varchar(32)  

)

     RESULT SETS 0

     LANGUAGE SQL

     PARAMETER CCSID EBCDIC

     NOT DETERMINISTIC

     MODIFIES SQL DATA

     ASUTIME NO LIMIT

     COMMIT ON RETURN NO

-- Insert Velocity Header --

--    EXTERNAL NAME UNIQID

 P1: BEGIN

    DECLARE tpxInsName          VARCHAR(128);

    DECLARE tpyLastReservedID   INTEGER         DEFAULT 1;

    DECLARE tpzInsKey           VARCHAR(255);

    DECLARE tnewLastReservedID  INTEGER         DEFAULT 1;

    DECLARE tupdater            CHAR(1);

    DECLARE tpxObjClass         VARCHAR(96)

                             DEFAULT 'Data-UniqueID';

    DECLARE tpxCreateDateTime   TIMESTAMP       DEFAULT NULL;

    DECLARE tpxCreateOpName     VARCHAR(128)    DEFAULT NULL;

    DECLARE tpxCreateOperator   VARCHAR(128)    DEFAULT NULL;

    DECLARE tpxCreateSystemID   VARCHAR(32)     DEFAULT NULL;

    DECLARE tpxUpdateDateTime   TIMESTAMP       DEFAULT NULL;

    DECLARE tpxUpdateOpName     VARCHAR(128)    DEFAULT NULL;

    DECLARE tpxUpdateOperator   VARCHAR(128)    DEFAULT NULL;

    DECLARE tpxUpdateSystemID   VARCHAR(32)     DEFAULT NULL;

    DECLARE tpyLabel            VARCHAR(128)    DEFAULT NULL;

  /* -------------------------------------------------   */

    DECLARE InsertCase              SMALLINT DEFAULT 0;

    DECLARE SQLCODE                 INTEGER;

    DECLARE retcode                 INTEGER DEFAULT 0;

    DECLARE SQLSTATE                CHAR(5);

    DECLARE retstate                CHAR(5);

    DECLARE RETURNFLAG        INTEGER DEFAULT 0;

    DECLARE C1 CURSOR FOR

      SELECT PC_DATA_UNIQUEID.PYLASTRESERVEDID

      FROM   PC_DATA_UNIQUEID

      WHERE  PC_DATA_UNIQUEID.PZINSKEY = tpzinskey

      FOR    UPDATE OF PYLASTRESERVEDID;

    DECLARE CONTINUE HANDLER FOR NOT FOUND

             SET retcode = SQLCODE;

    DECLARE CONTINUE HANDLER FOR SQLWARNING

             SET retcode = SQLCODE;

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

             SET retcode = SQLCODE;

  /* -------------------------------------------------   */

  SET tpxInsName = UPPER(COALESCE(tpyOrganization,'') ||

            '!' || COALESCE(tpyPrefix, ''));

  SET tpzInsKey = 'DATA-UNIQUEID ' || tpxInsName;

  OPEN C1;

     FETCH C1 INTO tpyLastReservedID;

     SET RETURNFLAG = retcode;

     IF RETURNFLAG != 100 AND RETURNFLAG >= 0 THEN

     SET tnewLastReservedID = tpyLastReservedID + 1;

     UPDATE pc_data_uniqueid

                  SET pyLastReservedID = tnewLastReservedID

                  WHERE pzInsKey = tpzInsKey;

            SET RETURNFLAG = retcode;

     END IF;         

  CLOSE C1;

  IF RETURNFLAG = 100 THEN

        SET RETURNFLAG = 0;

        SET retcode = 0;

        SET InsertCase = 1;

  END IF;

  IF InsertCase = 1 THEN

      INSERT INTO pc_data_uniqueid

       ( PXCREATEDATETIME

       , PXCREATEOPNAME

       , PXCREATEOPERATOR

       , PXCREATESYSTEMID

       , PXINSNAME

       , PXOBJCLASS

       , PXUPDATEDATETIME

       , PXUPDATEOPNAME

       , PXUPDATEOPERATOR

       , PXUPDATESYSTEMID

       , PYLABEL

       , PYLASTRESERVEDID

       , PYORGANIZATION

       , PYPREFIX

       , PZINSKEY)

      VALUES

      (tpxCreateDateTime,

       tpxCreateOpName,

       tpxCreateOperator,

       tpxCreateSystemID,

       tpxInsName,

       tpxObjClass,

       tpxUpdateDateTime,

       tpxUpdateOpName,

       tpxUpdateOperator,

       tpxUpdateSystemID,

       tpyLabel,

       tnewLastReservedID,

       tpyOrganization,

       tpyPrefix,

       tpzInsKey

      );

      SET RETURNFLAG = retcode;

  END IF;

  IF RETURNFLAG = 0 THEN

    SET ReturnKey = COALESCE(tpyPrefix,'') ||

                        RTRIM(CHAR(tnewLastReservedID)) ||

                        COALESCE(tpySuffix,'');

  ELSE

    SET ReturnKey = COALESCE('SP_ERROR: DB2 EXCEPTION ','') ||

                    RTRIM(CHAR(RETURNFLAG))   ||

                    COALESCE(' SQLSTATE:','') || RTRIM(SQLSTATE);

  END IF;

END P1

Additional information

Working with the PegaRULES database - Triggers and stored procedures