|
Problem IC71652
|
Status: Closed |
CREATE OR REPLACE ALIAS makes system catalog tables inconsistent when acting on table or view |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem 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; |
| 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; |
| 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. |
| available fix packs: |
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
|
| Solution |
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-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC71669
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 05.10.2010
28.10.2010
22.11.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP3A
|
| Problem solved according to the fixlist(s) of the following version(s) |
