DB2 - Problembeschreibung
Problem IC71652 | Status: Geschlossen |
CREATE OR REPLACE ALIAS makes system catalog tables inconsistent when acting on table or view | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
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-Zusammenfassung: | |
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. | |
verfügbare FixPacks: | |
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows | |
Lösung | |
This APAR is first fixed in the DB2 v9.7fp3a | |
Workaround | |
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. | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC71669 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 05.10.2010 28.10.2010 22.11.2010 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP3A | |
Problem behoben lt. FixList in der Version |