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

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PM65722

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-05-29

  • Closed date

    2012-08-10

  • Last modified date

    2012-09-05

  • 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.



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

910

Reference #:

PM65722

Modified date:

2012-09-05

Translate my page

Machine Translation

Content navigation