PM65722: OVERLAY OR VARIOUS ABENDS WHEN USING THE VARCHAR BUILT-IN FUNCTION WITH DECIMAL INPUT

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The following shows an example of a possible overlay with the
  VARCHAR(decimal) built-in function.  The problem occurs when a
  literal value is negative and a value is not specified to the
  left of the decimal point.
  
  
  SELECT HEX(VARCHAR(-.1001)),VARCHAR(-.1001), IBMREQD
  
  FROM SYSIBM.SYSDUMMY1;
  
  
  
              +---------------------------------+
              |              |        | IBMREQD |
              +---------------------------------+
            1_| 60F04BF1F0F0.| -0.100Y| Y       |
              +---------------------------------+
  
  
  In this example, the overlay causes a subsequent 0C7 on the same
  SQL statement.
  
  SELECT HEX(VARCHAR(-.1)),VARCHAR(-.1), IBMREQD
    FROM SYSIBM.SYSDUMMY1;
  
  DUMP00 TITLE=V91A,ABND=0C7-00000000,U=SYSADM  ,M=(N),C=910.RDS
               -SQL     ,M=DSNTFRCV,LOC=DSNXGRDS.DSNXRSBC+546C
      DUMP TAKEN TIME=09.06.46 DATE=04/24/2012
  

Local fix

• Specify a literal value with 0 before the decimal point.  For
  the above example, '-0.1001' will avoid the overlay.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 and DB2 10 for z/OS users of the   *
  *                 built-in function VARCHAR with decimal       *
  *                 input or the CAST AS specification with      *
  *                 decimal input and VARCHAR or CHAR output.    *
  ****************************************************************
  * PROBLEM DESCRIPTION: Problem 1 ( Version 9 only )            *
  *                      ABEND0C7 at DSNXRSBC +546C for an SQL   *
  *                      query using the VARCHAR function with   *
  *                      decimal input.  Alternately, an overlay *
  *                      of storage may occur.  This problem     *
  *                      only occurs on Version 9. The overlay   *
  *                      is 1 byte. This problem can also        *
  *                      occur with CAST (decimal as VARCHAR)    *
  *                                                              *
  *                      Problem 2 ( Version 10 only )           *
  *                      DB2 Version 10 currently returns the    *
  *                      new incompatible semantics for the      *
  *                      VARCHAR(decimal) function for static    *
  *                      SQL bound on a prior release of DB2.    *
  *                      The new semantics are also returned     *
  *                      for CAST (decimal as VARCHAR or CHAR).  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Problem 1 ( Version 9 only ) :
  The overlay or ABEND0C7 at DSNXRSBC +546C may occur when the
  VARCHAR function has a negative decimal number between 0 and -1
  as input and the input does not specify a 0 to the left of the
  decimal point. For example, this SQL may overlay 1 byte of
  storage :
  
  SELECT VARCHAR(-.1001) FROM SYSIBM.SYSDUMMY1;
  
  DB2 currently allocates 6 bytes for the result buffer for
  this example but the result value '-0.1001' is 7 bytes.
  
  If the VARCHAR function is used as input to another function
  then the abend symptom may occur.  For example :
  
  SELECT HEX(VARCHAR(-.1)), VARCHAR(-.1), IBMREQD
    FROM SYSIBM.SYSDUMMY1;
  
  Various abend conditions may occur.  The DSNXRSBC abend is one
  example.  This problem can also occur if the CAST(decimal AS
  VARCHAR) syntax is used.
  
  Problem 2 ( Version 10 only )
  DB2 9 returns one leading zero for the examples above and
  in the following example :
  
    VARCHAR('00.10') returns the result '0.10'
  
  DB2 10 returns no leading zeroes:
  
   VARCHAR('00.10') returns the result '.10'
  
  As documented in the DB2 Application Programming and SQL Guide
  section "Application and SQL release incompatibilities for
  migration from Version 9.1 ", the Version 10 result for the
  same input will be '.10'.  Version 10 returns the new result
  after migration for static and dynamic SQL for packages bound
  on any release.
  
  To help manage the migration of Version 10, the result
  returned for static SQL bound on Version 9 (or before), the V9
  result with one leading zero will be returned after this apar
  is applied.  For the above example, '0.10' will be returned
  on Version 10 ONLY FOR STATIC SQL BOUND ON A RELEASE PRIOR TO
  VERSION 10. For SQL bound on Version 10, see apar PM66095.
  This also applies to the CAST (decimal AS CHAR or VARCHAR)
  specification.
  

Problem conclusion

• Problem 1 (Version 9):
  To fix the problem, the result length of VARCHAR(decimal) will
  be precision plus 3 if the precision and scale are the same.
  Currently the result length is precision plus 2.  This APAR
  affects the result length of the VARCHAR function.  The change
  only applies to Version 9. A similiar change is made for
  CAST(decimal AS VARCHAR) to an internal temporary buffer.
  
  Information about this change will be reflected in the IBM
  Information Management Software for z/OS Solutions Information
  Center.
  
  Problem 2 (Version 10)
  This change only applies to Version 10.  The VARCHAR built-in
  function or CAST specification will return one leading zero if
  the SQL is statically bound on a release prior to Version 10.
  If the package is is bound on Version 10, the leading zero will
  not be returned.
  

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:

  UK81001 UK81002

Modules/Macros

• DSNXGSFN DSNXOBFA DSNXOBFF DSNXRSB9
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK81001

     UP12/08/25 P F208

• R910 PSY UK81002

     UP12/08/25 P F208

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.