IBM Support

IC83801: VARCHAR RESULT DATA TYPE INSTEAD OF CHAR WITH VARCHAR2 ENABLED DATABASE

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as suggestion for future release.

Error description

  • When doing a describe command on a select query, the result data
    type in a VARCHAR2 enabled database may be VARCHAR instead of
    the expected CHAR.
    
    Example:
    ----------------------------------------------------------------
    
    C:\>db2 create table test(c1 int)
    DB20000I  The SQL command completed successfully.
    
    >> A) Output from the describe select command with the issue:
    C:\>db2 "describe select 'Y' as flag1, case when 1 = 1 then 'Y'
    else 'N' end as flag2 from test"
    
     Column Information
    
     Number of columns: 2
    
     SQL type              Type length  Column name         Name
    length
     --------------------  -----------
    ------------------------------  -----------
     452   CHARACTER                 1  FLAG1                5
     448   VARCHAR                   1  FLAG2                   5
    
    
    >> B) Expected output :
    C:\>db2 "describe select 'Y' as flag1, case when 1 = 1 then 'Y'
    else 'N' end as flag2 from test"
    
     Column Information
    
     Number of columns: 2
    
     SQL type              Type length  Column name
    Name length
     --------------------  -----------
    ------------------------------  -----------
     452   CHARACTER                 1  FLAG1                   5
     452   CHARACTER                 1  FLAG2                   5
    
    The effects of setting the varchar2_compat database
    configuration parameter to ON are as follows.
    
    Rules for result data types involving character strings are
    modified as follows:
    
    
    If one operand is...  And the other operand is...  The data type
    of the result is...
    CHAR(x)  CHAR(x)  CHAR(x)
    CHAR(x)  CHAR(y)  VARCHAR(z) where z = max(x,y)
    
    
    
    Rules for result data types involving graphic strings are
    modified as follows:
    
    If one operand is...  And the other operand is...  The data type
    of the result is...
    GRAPHIC(x)  GRAPHIC(x)  GRAPHIC(x)
    GRAPHIC(x)  GRAPHIC(y)  VARGRAPHIC(z) where z = max(x,y)
    
    
    
    Rules for result data types involving character and graphic
    strings are modified as follows:
    
    If one operand is...  And the other operand is...  The data type
    of the result is...
    GRAPHIC(x)  CHAR(y)  VARGRAPHIC(z) where z = max(x,y)
    
    
    
    The functions that return character string arguments, or that
    are based on parameters with character string data types, also
    treat empty string CHAR, NCHAR, VARCHAR, or NVARCHAR values as
    null values. Special considerations apply for some functions
    when the varchar2_compat database configuration parameter is set
    to ON, and these are listed here.
    
        DECODE: If the first result expression is an untyped null it
    is assumed to be VARCHAR(0).  If the first result expression is
    CHAR or GRAPHIC, it is promoted to VARCHAR or VARGRAPHIC.
        GREATEST: If the first expression is CHAR or GRAPHIC, it is
    promoted to VARCHAR or VARGRAPHIC.
        LEAST: If the first expression is CHAR or GRAPHIC, it is
    promoted to VARCHAR or VARGRAPHIC.
        NVL: If the first expression is CHAR or GRAPHIC, it is
    promoted to VARCHAR or VARGRAPHIC.
        NVL2: If the result expression is an untyped null it is
    assumed to be VARCHAR(0).  If the result expression is CHAR or
    GRAPHIC, it is promoted to VARCHAR or VARGRAPHIC.
    

Local fix

  • Cast the final result to CHAR(N)
    

Problem summary

Problem conclusion

Temporary fix

Comments

  • When doing a describe command on a select query, the result data
    type in a VARCHAR2 enabled database may be VARCHAR instead of
    the expected CHAR. This happens when all the data types are
    CHAR(N) where N is the same for all values.
    

APAR Information

  • APAR number

    IC83801

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED SUG

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-05-28

  • Closed date

    2012-11-06

  • Last modified date

    2013-06-17

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

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

    IC91701 IC91702

Fix information

Applicable component levels



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC83801

Modified date: 17 June 2013