PM93879: SYSIBM.SYSCOLUMNS LENGTH2 COLUMN IS NOT CORRECT AFTER AN ALTER TABLE COLUMN CHANGE

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • An ALTER TABLE of a LOB column to change its length results
    in the LENGTH column changing correctly, but the LENGTH2
    column for the AUX table is not updated properly when the
    tablespace is segmented.
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 10 for z/OS and DB2 11 for z/OS      *
    *                 users of ALTER LOB-COLUMN.                   *
    ****************************************************************
    * PROBLEM DESCRIPTION: An incorrect output of catalog table    *
    *                      SYSIBM.SYSCOLUMNS column LENGTH2 value  *
    *                      may occur when altering a LOB column's  *
    *                      maximum length for a table in a non-    *
    *                      partitioned segmented tablespace.       *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    An incorrect output of catalog table SYSIBM.SYSCOLUMNS column
    LENGTH2 may occur when altering a LOB column maximum length for
    a table in a non-partitioned segmented tablespace. It is because
    when altering a LOB column maximum length for a table in a non-
    partitioned segmented tablespace, DB2 does not properly update
    the corresponding LOB auxiliary table's column length in the
    catalog SYSIBM.SYSCOLUMNS field LENGTH2.
    
    Following is an example of such failing scenario:
    SET CURRENT RULES = 'STD';
    DROP DATABASE MYDB;
    RETCODE = SQLCHECK(0,-551);
    COMMIT;
    
    CREATE DATABASE MYDB CCSID UNICODE;
    CREATE TABLESPACE MYTS SEGSIZE 4  IN MYDB CCSID UNICODE;
    
    CREATE TABLE MYTB(ROW_ID ROWID NOT NULL GENERATED ALWAYS,
                      COLLOB CLOB(10)
                     ) IN MYDB.MYTS;
    
    SELECT TBNAME, LENGTH, LENGTH2
      FROM SYSIBM.SYSCOLUMNS
     WHERE TBNAME LIKE 'MYTB%';
    
    ALTER TABLE MYTB ALTER COLLOB SET DATA TYPE CLOB(2000);
    
    SELECT TBNAME, LENGTH, LENGTH2
      FROM SYSIBM.SYSCOLUMNS
     WHERE TBNAME LIKE 'MYTB%';
    

Problem conclusion

  • DB2 is modified to properly update the catalog table SYSIBM.
    SYSCOLUMNS column LENGTH2 field when altering a LOB column's
    maximum length.
    
    One way to find out if the LOB column's maximum length in both
    base table and the corresponding auxiliary table are the same or
    not is running a REPAIR DBD job with DIAGNOSE option. For
    example:
      REPAIR DBD DIAGNOSE DATABASE   MYDB
    
    If the maximum length of a LOB column in base table and the
    auxiliary table are different, the REPAIR job will report an
    error. Following is an example of the error message:
    
    DSNU920I    DSNUEDRT -   OBDS DIFFER AT OFFSET X'000000AA'
                             OBDRAFLL(3)
    DSNU904I    DSNUEDRT -     EXISTING VALUE  X'000007D0'
    DSNU905I    DSNUEDRT -     REBUILT  VALUE  X'0000000A'
    DSNU913I    DSNUEDDR - OBD X'0008' COMPARISON COMPLETE --
                           1 ERRORS WERE FOUND
    
    
    Additional Keywords: SQLALTER SQLLOB SQLINCORR INCORROUT
                         SQLINCORROUT
                         NON-UTS SEGMENT TABLESPACE
    

Temporary fix

  • *********
    * HIPER *
    *********
    

Comments

APAR Information

  • APAR number

    PM93879

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-07-25

  • Closed date

    2013-10-18

  • Last modified date

    2013-12-02

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

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

    UK98635 UK98636

Modules/Macros

  • DSNXIALC
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK98635

       UP13/11/05 P F311 ½

  • RB10 PSY UK98636

       UP13/11/05 P F311 ½

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:

A10

Reference #:

PM93879

Modified date:

2013-12-02

Translate my page

Machine Translation

Content navigation