IBM Support

JR30422: "ALTER TABLE ALTER COLUMN" STATEMENT DOES NOT ALTER THE CODEPAGE COLUMN IN THE SYSCAT.COLUMNS VIEW

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • When you create a table with a CHAR column and a second column
    that is CHAR  FOR BIT DATA, alter the first column to be CHAR
    FOR BIT DATA, then look
    at the columns in SYSCAT.COLUMNS they do not appear the same.  I
    would expect both columns to have a CODEPAGE of 0. But the one
    that we altered always shows the old codepage, the CODEPAGE
    column does not get updated in the SYSCAT.COLUMNS table.
    
    SYSIBM.SYSCOLUMNS, SYSIBM.COLUMNS, and the stored procedure
    SQLCOLUMNS show the correct output.
    
    Test case:
    $ db2level
    DB21085I  Instance "TEST" uses "64" bits and DB2 code release
    "SQL09015"
    with level identifier "01060107".
    Informational tokens are "DB2 v9.1.0.5", "s080512", "U815922",
    and Fix Pack "5".
    Product is installed at "/home/TEST/sqllib".
    
    
    $ db2 "Create Table TEST ( int1       Integer, AlwaysFBD
    Char(5) for bit data, BecomesFBD Char(5), AlwaysChar Char(5) )"
    DB20000I  The SQL command completed successfully.
    
    $ db2 "Select Substr(TABSCHEMA,1,8) as TABSCHEMA ,
    Substr(TABNAME,1,20)  as TABNAME , Substr(COLNAME,1,10)  as
    COLNAME , Substr(TYPENAME,1,10) as TYPENAME , CODEPAGE From
    SYSCAT.COLUMNS Where TABNAME = 'TEST' Order By TABSCHEMA,
    TABNAME, COLNO"
    
    TABSCHEMA TABNAME              COLNAME    TYPENAME   CODEPAGE
    --------- -------------------- ---------- ---------- --------
    MURALIV   TEST              INT1       INTEGER
    0
    MURALIV   TEST              ALWAYSFBD  CHARACTER         0
    MURALIV   TEST              BECOMESFBD CHARACTER       819
    MURALIV   TEST              ALWAYSCHAR CHARACTER       819
    
      4 record(s) selected.
    
    $ Call SQLCOLUMNS('','%','TEST','%','%')
    
    
      Result set 1
      --------------
    
      TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME
    SQL_DATA_TYPE
      ----------- ---------- ----------- ---------
    -------------------- -------------
      MURALIV     TEST    INT1                      4 INTEGER
    4
      MURALIV     TEST    ALWAYSFBD            -2 CHAR () FOR BIT
    DATA            -2
      MURALIV     TEST    BECOMESFBD          1 CHAR
    1
      MURALIV     TEST    ALWAYSCHAR          1 CHAR
    1
    
      4 record(s) selected.
    
    $ db2 "Alter Table TEST Alter BecomesFBD Set Data Type Char(5)
    For Bit Data"
    DB20000I  The SQL command completed successfully.
    
    $ db2 "Select Substr(TABSCHEMA,1,8) as TABSCHEMA ,
    Substr(TABNAME,1,20)  as TABNAME , Substr(COLNAME,1,10)  as
    COLNAME , Substr(TYPENAME,1,10) as TYPENAME , CODEPAGE From
    SYSCAT.COLUMNS Where TABNAME = 'TEST' Order By TABSCHEMA,
    TABNAME, COLNO"
    
    TABSCHEMA TABNAME              COLNAME    TYPENAME   CODEPAGE
    --------- -------------------- ---------- ---------- --------
    MURALIV   TEST              INT1       INTEGER
    0
    MURALIV   TEST              ALWAYSFBD  CHARACTER         0
    MURALIV   TEST              BECOMESFBD CHARACTER       819 ==>
    EXPECTING 0 here <==
    MURALIV   TEST              ALWAYSCHAR CHARACTER       819
    
      4 record(s) selected.
    
    $ Call SQLCOLUMNS('','%','TEST','%','%')
    
    
      Result set 1
      --------------
    
      TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME
    SQL_DATA_TYPE
      ----------- ---------- --------------------- ---------
    ---------- -------------
      MURALIV     TEST    INT1                4 INTEGER
    4
      MURALIV     TEST    ALWAYSFBD          -2 CHAR () FOR BIT DATA
    -2
      MURALIV     TEST    BECOMESFBD         -2 CHAR () FOR BIT DATA
    -2
      MURALIV     TEST    ALWAYSCHAR          1 CHAR
    1
    
      4 record(s) selected.
    
      Return Status = 0
    

Local fix

  • You can directly select from CODEPAGE column in
    SYSIBM.SYSCOLUMNS instead of from the SYSCAT.COLUMNS catalog
    view.
    

Problem summary

  • "ALTER TABLE ALTER COLUMN" STATEMENT DOES NOT
    LTER THE CODEPAGE COLUMN IN THE SYSCAT.COLUMNS VIEW
    

Problem conclusion

  • When you create a table with a CHAR column and a second column
    that is CHAR  FOR BIT DATA, alter the first column to be CHAR
    FOR BIT DATA, then look
    at the columns in SYSCAT.COLUMNS they do not appear the same.  I
    would expect both columns to have a CODEPAGE of 0. But the one
    that we altered always shows the old codepage, the CODEPAGE
    column does not get updated in the SYSCAT.COLUMNS table.
    
    SYSIBM.SYSCOLUMNS, SYSIBM.COLUMNS, and the stored procedure
    SQLCOLUMNS show the correct output.
    
    Test case:
    $ db2level
    DB21085I  Instance "TEST" uses "64" bits and DB2 code release
    "SQL09015"
    with level identifier "01060107".
    Informational tokens are "DB2 v9.1.0.5", "s080512", "U815922",
    and Fix Pack "5".
    Product is installed at "/home/TEST/sqllib".
    
    
    $ db2 "Create Table TEST ( int1       Integer, AlwaysFBD
    Char(5) for bit data, BecomesFBD Char(5), AlwaysChar Char(5) )"
    DB20000I  The SQL command completed successfully.
    
    $ db2 "Select Substr(TABSCHEMA,1,8) as TABSCHEMA ,
    Substr(TABNAME,1,20)  as TABNAME , Substr(COLNAME,1,10)  as
    COLNAME , Substr(TYPENAME,1,10) as TYPENAME , CODEPAGE From
    SYSCAT.COLUMNS Where TABNAME = 'TEST' Order By TABSCHEMA,
    TABNAME, COLNO"
    
    TABSCHEMA TABNAME              COLNAME    TYPENAME   CODEPAGE
    --------- -------------------- ---------- ---------- --------
    MURALIV   TEST              INT1       INTEGER
    0
    MURALIV   TEST              ALWAYSFBD  CHARACTER         0
    MURALIV   TEST              BECOMESFBD CHARACTER       819
    MURALIV   TEST              ALWAYSCHAR CHARACTER       819
    
      4 record(s) selected.
    
    $ Call SQLCOLUMNS('','%','TEST','%','%')
    
    
      Result set 1
      --------------
    
      TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME
    SQL_DATA_TYPE
      ----------- ---------- ----------- ---------
    -------------------- -------------
      MURALIV     TEST    INT1                      4 INTEGER
    4
      MURALIV     TEST    ALWAYSFBD            -2 CHAR () FOR BIT
    DATA            -2
      MURALIV     TEST    BECOMESFBD          1 CHAR
    1
      MURALIV     TEST    ALWAYSCHAR          1 CHAR
    1
    
      4 record(s) selected.
    
    $ db2 "Alter Table TEST Alter BecomesFBD Set Data Type Char(5)
    For Bit Data"
    DB20000I  The SQL command completed successfully.
    
    $ db2 "Select Substr(TABSCHEMA,1,8) as TABSCHEMA ,
    Substr(TABNAME,1,20)  as TABNAME , Substr(COLNAME,1,10)  as
    COLNAME , Substr(TYPENAME,1,10) as TYPENAME , CODEPAGE From
    SYSCAT.COLUMNS Where TABNAME = 'TEST' Order By TABSCHEMA,
    TABNAME, COLNO"
    
    TABSCHEMA TABNAME              COLNAME    TYPENAME   CODEPAGE
    --------- -------------------- ---------- ---------- --------
    MURALIV   TEST              INT1       INTEGER
    0
    MURALIV   TEST              ALWAYSFBD  CHARACTER         0
    MURALIV   TEST              BECOMESFBD CHARACTER       819 ==>
    EXPECTING 0 here <==
    MURALIV   TEST              ALWAYSCHAR CHARACTER       819
    
      4 record(s) selected.
    
    $ Call SQLCOLUMNS('','%','TEST','%','%')
    
    
      Result set 1
      --------------
    
      TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME
    SQL_DATA_TYPE
      ----------- ---------- --------------------- ---------
    ---------- -------------
      MURALIV     TEST    INT1                4 INTEGER
    4
      MURALIV     TEST    ALWAYSFBD          -2 CHAR () FOR BIT DATA
    -2
      MURALIV     TEST    BECOMESFBD         -2 CHAR () FOR BIT DATA
    -2
      MURALIV     TEST    ALWAYSCHAR          1 CHAR
    1
    
      4 record(s) selected.
    
      Return Status = 0
    
    LOCAL FIX:
    You can directly select from CODEPAGE column in
    SYSIBM.SYSCOLUMNS instead of from the SYSCAT.COLUMNS catalog
    view.
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR30422

  • Reported component name

    DB2 UDB ESE WIN

  • Reported component ID

    5765F4101

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-09-05

  • Closed date

    2009-05-28

  • Last modified date

    2009-05-28

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

    JR30420

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

Fix information

  • Fixed component name

    DB2 UDB ESE WIN

  • Fixed component ID

    5765F4101

Applicable component levels

  • R950 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
28 May 2009