PM73314: CERTAIN PRECISION DECIMAL CALCULATION GETS SQLCODE406 WITH DECDIV3=YES DUE TO ONE BYTE OVERLAY

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Calculation of certain precision decimal may get SQLCODE406 with
  DECDIV3=YES due to one byte overlay.
  
  Example:
  SELECT (DEC1*DEC2)/(DEC3+0.00000000001)/0.01 AS COL1
  FROM TAB1;
  
  DSNT408I SQLCODE = -406, ERROR:  A CALCULATED OR DERIVED NUMERIC
  VALUE IS NOT WITHIN THE RANGE OF ITS OBJECT COLUMN
  DSNT418I SQLSTATE   = 22003 SQLSTATE RETURN CODE
  DSNT415I SQLERRP    = DSNXRDEC SQL PROCEDURE DETECTING ERROR
  DSNT416I SQLERRD    = 511  0  0  -1  0  0 SQL DIAGNOSTIC
  INFORMATION
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 for z/OS and DB2 10 for z/OS users *
  *                 of queries that contain decimal arithmetic   *
  *                 with system zparm DECDIV3 set to YES or      *
  *                 specified via SET CURRENT PRECISION.         *
  ****************************************************************
  * PROBLEM DESCRIPTION: When using the DECDIV3 (minimum divide  *
  *                      scale) subsystem parameter (zparm) for  *
  *                      decimal division or special register    *
  *                      SET CURRENT PRECISION = 'D31.3', a      *
  *                      query which contains decimal division   *
  *                      can return an unexpected SQLCODE -406   *
  *                      or SQLCODE406 from module DSNXRDEC      *
  *                      location 511.                           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When using the DECDIV3 (minimum divide scale) subsystem
  parameter (zparm) or special register SET CURRENT PRECISION =
  'D31.3', a query which contains decimal division can return an
  unexpected SQLCODE -406 or SQLCODE406 from module DSNXRDEC
  location 511.  The problem was reported for DB2 v10 and is not
  likely to occur for DB2 v9.
  
  
  The following example illustrates the problem.
  
  Step 1. Ensure that DECP sets DECARTH=DEC15 and that zparm
          DECDIV3=YES or that special register SET CURRENT
          PRECISION = 'D31.3' is specified.
  
  
  Step 2. Create the following Table T1.
  
    CREATE TABLE T1
          (C1     DECIMAL(18 , 3) NOT NULL
          ,C2     DECIMAL(15 , 6) NOT NULL
          ,C3     DECIMAL(18 , 3) NOT NULL
          )
          CCSID EBCDIC ;
  
  
  Step 3. Insert the following row of data into Table T1.
  
    INSERT INTO T1 VALUES(630104.780, 1.000000,  37500000.000) ;
  
  
  Step 4. Perform one of the following queries.
  
  
    SELECT A.* ,
     (C1 * C2)/(C3 + 0.00000000001) AS CALCOL
    FROM T1 A;
  
  
    SELECT A.* ,
     (C1 * C2)/(C3 + 0.00000000001)/0.01 AS CALCOL
    FROM T1 A;
  
  
  Step 5. The query returns an SQLCODE -406 from DSNXRDEC
          location 511 but should not.
  
  The problem occurs because of a one-byte overlay of the result
  buffer internally when performing the decimal division when
  DECDIV3 or decimal precision is set.  It does not occur for all
  values.
  
  Also, in DB2 v9, the overlay can occur but since it is not known
  to impact critical storage (or the internal result buffer as in
  v10), the failure does not occur.
  
  Please note that if the DECDIV3 parm is set to NO, an SQLCODE
  -419 (or SQLCODE419) should occur from DSNXOBFF location 910.
  

Problem conclusion

• The code in DB2 is modified to prevent the one-byte overlay when
  processing a decimal division if DECDIV3 or SET CURRENT
  PRECISION = 'D31.3' are set.  This will prevent the SQLCODE -406
  and allow the proper result to be returned.
  
  Additional Keywords: SQLSTORAGE DB2OVRLAY/K SQLDIVIDE SQLDECIMAL
                       SQLCODE406 SQLCODE419
  

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:

  UK83315 UK83316

Modules/Macros

• DSNXRDEC DSNXRDE9
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK83315

     UP12/11/27 P F211

• R910 PSY UK83316

     UP12/11/27 P F211

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.