|
Problem IC63388
|
Status: Closed |
INCONSISTENT SYSTEM CATALOG TABLES MAY OCCUR WHEN CREATING STORED PROCEDURES FROM NON-CATALOG PARTITION |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
When creating SQL stored procedures from non-catalog database
partitions, if the statement is rolled back due to a
failure, it is possible that the DB2 system catalog tables
become
inconsistent.
.
This results in the inability to create a procedure of the same
name and signature of the one that was thought to be rolled
back.
.
For example, you will see the following message when attempting
to create the procedure you thought had been dropped or not
successfully created:
.
DB21034E The command was processed as an SQL statement because
it was not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0454N The signature provided in the definition for routine
"PROC.MYPROC"
matches the signature of some other routine that already exists
in the schema
or module for the type. LINE NUMBER=5. SQLSTATE=42723
.
Additionally, when attempting to drop the stored procedure, the
drop will seem to succeed when issued from a non-catalog
database partition:
.
drop procedure myproc
DB20000I The SQL command completed successfully.
.
If the drop is issued from the catalog database partition, you
will receive the following message:
.
drop procedure myproc
SQL0100W No row was found for FETCH, UPDATE or DELETE; or the
result of a
query is an empty table. SQLSTATE=02000
.
For either of the two drop scenarios, the system catalogs
remain inconsistent, and can only be properly cleaned up by
DB2 Service. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All DB2 Version 9.7 GA servers, with users issuing CREATE *
* PROCEDURE statements from non-catalog database partitions *
****************************************************************
* PROBLEM DESCRIPTION: *
* Staring in DB2 Version 9.7 GA, the restriction of creating *
* SQL stored procedures from non-catalog database partitions *
* has been lifted. This feature exposed an issue with *
* properly rolling back updates made to the system catalog *
* tables, leaving the catalog tables in inconsistent states. *
****************************************************************
* RECOMMENDATION: *
* Issue CREATE PROCEDURE statements from the catalog database *
* partition. Contact DB2 Service in order to appropriately *
* clean the system catalog tables of the inconsistency that *
* may have occurred. *
**************************************************************** |
| Local Fix: |
Always issue CREATE PROCEDURE statements from the catalog node. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
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 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 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 10 for Linux, UNIX, and Windows
|
| Solution |
First fixed in DB2 Version 9.7 Fix Pack 1 and all subsequent Fix
Packs. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 23.09.2009
21.12.2009
21.12.2009 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
