PI16466: INCORRECT VALUE IN OUTPUT PARM OF A STORED PROCEDURE WHEN SET ENCRYPTION PASSWORD IS USED AND REALEASE DEALLOCATE IS ON

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Incorrect output when Native SQL stored procedure is
  decrypting data using RELEASE DEALLOCATE.
  The first execution of the stored procedure, the value returned
  is correct in the output value, however, subsequent executions
  results in incorrect value.
  Example:
  SET ENCRYPTION PASSWORD = 'ABCDEFG1';
  SELECT DECRYPT_BIT(PWD_COL)
   INTO
     PWD_COL_OUT
   FROM USER.TABLE1;
  
  ADDITIONAL SYMPTOMS/KEYWORDS:
  ============================
  Native SQL SP
  RELEASE DEALLOCATE
  SET PASSWORD
  DECRYPT_BIT
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 for z/OS, DB2 10 for z/OS,         *
  *                 and DB2 11 for z/OS users of the             *
  *                 SET ENCRYPTION PASSWORD special              *
  *                 register using a password string             *
  *                 constant.                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: When using the SET ENCRYPTION PASSWORD  *
  *                      special register with a password string *
  *                      constant, if the same SET ENCRYPTION    *
  *                      PASSWORD statement is executed multiple *
  *                      times, the wrong password is stored in  *
  *                      the ENCRYPTION PASSWORD special         *
  *                      register.  This can lead to incorrect   *
  *                      or unexpected results.                  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When executing the SET ENCRYPTION PASSWORD special register with
  a password string constant, it will execute correctly the first
  time.  However, if the same SET ENCRYPTION PASSWORD statement is
  executed again (like in a loop), the wrong password will be
  stored in the ENCRYPTION PASSWORD special register.  As a
  result, the password value is not honored when later using the
  encrypt ENCRYPT_TDES function or one of the decrypt functions
  (DECRYPT_BINARY, DECRYPT_BIT, DECRYPT_CHAR, or DECRYPT_DB)
  leading to an unexpected result.
  
  In the reported case, the DECRYPT_BIT function was returning
  an unexpected decrypted value.
  
  The following example illustrates a failing case.
  
   IX=1;
  
   DO WHILE (IX <= 2);
     EXEC SQL SET ENCRYPTION PASSWORD = 'PASSWORD' ;
     IX = IX + 1;
   END;
  
   EXEC SQL SELECT DECRYPT_BIT(COL1)
     INTO :HV1
     FROM TABL1;
  
  The second call to SET ENCRYPTION PASSWORD will store an
  unwanted password in the ENCRYPTION PASSWORD special register.
  Then, the following DECRYPT_BIT call will return an unexpected
  decrypted value because the wrong password is used.
  
  Additionally, it should be noted that if a password HINT is
  used the problem still occurs.
  

Problem conclusion

• The code in DB2 has been modified to ensure that the correct
  password is saved for the ENCRYPTION PASSWORD special register.
  
  Additional Keywords: SQLENCRYPT SQLDECRYPT SQLSPECIALREG
                       INCORROUT SQLINCORROUT SQLINCORR
                       DB2INCORR/K
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UI17870 UI17871 UI17872

Modules/Macros

• DSNXRBND DSNXRBN9 DSNXRSPG
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI17870

     UP14/05/28 P F405 ½

• RB10 PSY UI17871

     UP14/05/28 P F405 ½

• R910 PSY UI17872

     UP14/05/28 P F405 ½

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.