IBM Support

IC71652: CREATE OR REPLACE ALIAS makes system catalog tables inconsistent when acting on table or view

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • The CREATE OR REPLACE ALIAS statement is designed to create a
    brand new alias if one doesn't already exist, or replace the
    existing alias.  If the alias name specified in the CREATE OR
    REPLACE ALIAS statement refers to an actual existing table or
    view, the system catalog tables become inconsistent leading to
    various unexpected and undesired behaviour.
    
    Here is an example:
    
    1. create table mytable (c1 int);
    2. create or replace alias mytable for mytable2;
    
    The second statement succeeds, however, makes the catalogs
    inconsistent.  This statement should be blocked with SQL0601,
    causing it to rollback.
    
    If you are affected by this, you may see errors when trying to
    run your queries such as:
    
    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
    
    SQL0901N  The SQL statement failed because of a non-severe
    system error.
    Subsequent SQL statements can be processed.  (Reason "table is
    not found".)
    SQLSTATE=58004
    
    You will also likely see db2diag.log messages such as the
    following:
    
    2010-09-22-14.55.08.804845-240 E94840A971         LEVEL: Info
    (Origin)
    PID     : 2462712              TID  : 69141       PROC : db2sysc
    0
    INSTANCE: sampleins             NODE : 000         DB   :
    SAMPLEDB
    APPHDL  : 0-5371               APPID:
    10.180.46.38.49403.100922184639
    AUTHID  : SAMPLEDB
    EDUID   : 69141                EDUNAME: db2agent (SAMPLEDB) 0
    FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650
    MESSAGE : ZRC=0x803100AF=-2144272209=SQLNN_E_BADNEWS
              "unexpected error but state is OK"
    DATA #1 : String, 428 bytes
    Compiler error stack for rc = -2144272209:
    sqlnn_cmpl[370]
    sqlnr_exe[400]
    sqlnr_rcc[100]
    sqlnr_seq[100]
    sqlnr_comp[110]
    sqlnr_start_action[20]
    sqlnr_rcc[100]
    sqlnr_seq[100]
    sqlnr_comp[110]
    sqlnr_endqtb_action[10]
    sqlnr_rcc[100]
    sqlnr_seq[100]
    sqlnr_comp[110]
    sqlnr_rijel_action[20]
    sqlnr_gbpd_partition[1710]
    sqlnr_gbpd_chk_jointype[200]
    sqlns_unpack_ri[50]
    sqlnq_return_proper_ftb[100]
    sqlnq_cat_look[170]
    sqlnq_cat_look2[100]
    
    Note that even if the table that has been replaced by an alias
    due to this APAR is not being referenced directly in the query,
    if it is somehow related to the query via referential integrity
    constraints, for example, your query will still fail.
    
    Finally, a simple query you may run to check whether you are
    affected by this problem is:
    
    with tables as (select creator, name from sysibm.systables
       where type !='A')
    select tbcreator, tbname from sysibm.syscolumns t1
       where not exists (select creator, name from tables
            where t1.tbcreator=creator and t1.tbname=name)
           group by tbcreator, tbname;
    

Local fix

  • If you have not yet encountered this problem, then adjust any
    scripts and processes to ensure you do not mistakenly run CREATE
    OR REPLACE ALIAS on any other object except for aliases.  If you
    are already impacted by this, contact DB2 Support immediately.
    

Problem summary

  • The CREATE OR REPLACE ALIAS statement is designed to create a
    brand new alias if one doesn't already exist, or replace the
    existing alias.  If the alias name specified in the CREATE OR
    REPLACE ALIAS statement refers to an actual existing table or
    view, the system catalog tables become inconsistent leading to
    various unexpected and undesired behaviour.
    
    Here is an example:
    
    1. create table mytable (c1 int);
    2. create or replace alias mytable for mytable2;
    
    The second statement succeeds, however, makes the catalogs
    inconsistent.  This statement should be blocked with SQL0601,
    causing it to rollback.
    
    If you are affected by this, you may see errors when trying to
    run your queries such as:
    
    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
    
    SQL0901N  The SQL statement failed because of a non-severe
    system error.
    Subsequent SQL statements can be processed.  (Reason "table is
    not found".)
    SQLSTATE=58004
    
    You will also likely see db2diag.log messages such as the
    following:
    
    2010-09-22-14.55.08.804845-240 E94840A971         LEVEL: Info
    (Origin)
    PID     : 2462712              TID  : 69141       PROC : db2sysc
    0
    INSTANCE: sampleins             NODE : 000         DB   :
    SAMPLEDB
    APPHDL  : 0-5371               APPID:
    10.180.46.38.49403.100922184639
    AUTHID  : SAMPLEDB
    EDUID   : 69141                EDUNAME: db2agent (SAMPLEDB) 0
    FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650
    MESSAGE : ZRC=0x803100AF=-2144272209=SQLNN_E_BADNEWS
              "unexpected error but state is OK"
    DATA #1 : String, 428 bytes
    Compiler error stack for rc = -2144272209:
    sqlnn_cmpl[370]
    sqlnr_exe[400]
    sqlnr_rcc[100]
    sqlnr_seq[100]
    sqlnr_comp[110]
    sqlnr_start_action[20]
    sqlnr_rcc[100]
    sqlnr_seq[100]
    sqlnr_comp[110]
    sqlnr_endqtb_action[10]
    sqlnr_rcc[100]
    sqlnr_seq[100]
    sqlnr_comp[110]
    sqlnr_rijel_action[20]
    sqlnr_gbpd_partition[1710]
    sqlnr_gbpd_chk_jointype[200]
    sqlns_unpack_ri[50]
    sqlnq_return_proper_ftb[100]
    sqlnq_cat_look[170]
    sqlnq_cat_look2[100]
    
    Note that even if the table that has been replaced by an alias
    due to this APAR is not being referenced directly in the query,
    if it is somehow related to the query via referential integrity
    constraints, for example, your query will still fail.
    
    Finally, a simple query you may run to check whether you are
    affected by this problem is:
    
    with tables as (select creator, name from sysibm.systables
       where type !='A')
    select tbcreator, tbname from sysibm.syscolumns t1
       where not exists (select creator, name from tables
            where t1.tbcreator=creator and t1.tbname=name)
           group by tbcreator, tbname;
    

Problem conclusion

  • This APAR is first fixed in the DB2 v9.7fp3a
    

Temporary fix

  • If you have not yet encountered this problem, then adjust any
    scripts and processes to ensure you do not mistakenly run CREATE
    OR REPLACE ALIAS on any other object except for aliases.  If you
    are already impacted by this, contact DB2 Support immediately.
    

Comments

APAR Information

  • APAR number

    IC71652

  • 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-10-05

  • Closed date

    2010-10-28

  • Last modified date

    2010-11-22

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

    IZ86171

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

    IC71669

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC71652

Modified date: 22 November 2010