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