|
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 |
