IBM Support

IC68779: Unexpected trailing blanks in results of DECODE function or CASE expression in VARCHAR2 compatibility mode

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • With VARCHAR2 compatibility mode, the result of a DECODE
    function or a CASE expression may have unexpected trailing
    blanks.  When the length of two result values are different and
    the shorter value is returned, it will be padded with trailing
    blanks up to the length of the longer value.
    
    Here is an example:
    
    db2set DB2_COMPATIBILITY_VECTOR=20
    db2stop
    db2start
    db2 create db sample
    
    db2 connect to sample
    db2 create table t1 (c1 char(13))
    db2 insert into t1 values('ABCDEFG')
    
    db2 "SELECT '---'||DECODE('1','1','ABC','ABCDEFG')||'---'  FROM
    t1"
       output:    ---ABC    ---
    
    db2 "SELECT '---'|| Case When '1'='1' Then 'ABC' Else 'ABCDEFG'
    End ||'---' FROM t1"
       output:    ---ABC    ---
    
    Oracle gives output: ---ABC--- in both cases.
    
    db2 "SELECT '---'||DECODE('1','2','ABCDEFG','EFG')||'---' FROM
    t1"
       output:     ---EFG    ---
    
    In all cases, the longer length of the decode/case values is
    picked up.
    

Local fix

  • Cast any of the result expression to VARCHAR.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * With VARCHAR2 compatibility mode, the result of a DECODE     *
    * function or a CASE expression may have unexpected trailing   *
    * blanks.  When the length of two result values are different  *
    * and the shorter value is returned, it will be padded with    *
    * trailing blanks up to the length of the longer value.        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to version 9.7 FixPack4                              *
    ****************************************************************
    

Problem conclusion

  • This was first fixed by V97 FP4
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC68779

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-05-21

  • Closed date

    2011-05-09

  • Last modified date

    2011-05-09

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

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

    IC69431

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU029","label":"Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7"}]

Document Information

Modified date:
10 September 2020