home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

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
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 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