DB2 FOR LUW / DB2FORLUW / 980 - DB2

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;

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;

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.

DB2 Version 9.8 Fix Pack 4 for AIX and Linux
 DB2 Version 9.8 Fix Pack 5 for AIX and Linux
 

This APAR is fixed on db2 v9.8Fp4.

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

06.10.2010
29.07.2011
29.07.2011

9.8.FP4