DB2 FOR LUW / DB2FORLUW / 970 - DB2

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.

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

Always issue CREATE PROCEDURE statements from the catalog node.

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
 

First fixed in DB2 Version 9.7 Fix Pack 1 and all subsequent Fix 
Packs.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

23.09.2009
21.12.2009
21.12.2009

9.7.FP1