IBM Support

IC93207: SQL0901N REASON "COLUMN NUMBER OUT OF RANGE" WHEN SELECTING FROM TABLES RELATED VIA FOREIGN KEY, AFTER ALTER DROP COLUMN

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • When two tables have the same Foreign key name to refer to each
    other on different parent - dependent relationships, if a column
    (not related to the foreign key columns) is dropped from one of
    the tables, queries or joins that involve those tables might
    fail with SQL0901N REASON "COLUMN NUMBER OUT OF RANGE".
    
    The following example shows this behaviour:
    
    create table t1 (c1 int not null ,c2 int, c3 int,constraint PKt1
    primary key (c1));
    create table t2 (c1 int not null,c2 int,c3 int,c4 int,constraint
    PKt2 primary key (c1));
    
    alter table t2 add constraint FK2 foreign key(c4) references t1;
    alter table t1 add constraint FK2 foreign key(c3) references t2;
    
    Note that both tables refer to each other with the same Foreign
    Key name : FK2.
    
    
    db2 alter table t2 drop column c3;
    
    db2 "select * from t1,t2 where t1.c1=t1.c3"
    SQL0901N  The SQL statement failed because of a non-severe
    system error.
    Subsequent SQL statements can be processed.  (Reason "column
    number out of
    range".)  SQLSTATE=58004
    

Local fix

  • Make sure that there are no duplicate Foreign key names between
    a pair of tables.
    Drop and recreate the foreign keys with different names in
    between the pair of tables:
    
    alter table t2 drop foreign key FK2;
    alter table t1 drop foreign key FK2;
    alter table t2 add constraint FK21 foreign key(c4) references
    t1;
    alter table t1 add constraint FK12 foreign key(c3) references
    t2;
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 9.7 and Fix Pack 9                    *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in DB2 Version 9.7 and Fix Pack 9
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC93207

  • 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

    2013-06-19

  • Closed date

    2013-12-17

  • Last modified date

    2013-12-17

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

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

    IC96294 IC96330

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC93207

Modified date: 17 December 2013