DB2 FOR LUW / DB2FORLUW / 970 - DB2

A new index has been added on the SYSIBM.SYSROUTINES catalog 
table to improve the performance of the DROP PACKAGE and DROP 
PACKAGE BODY PL/SQL statements.

**************************************************************** 
* USERS AFFECTED:                                              * 
* It might affect users who are interested in migrate from     * 
* Oracle to DB2. Particularly, those customers whose           * 
* application contain thousands of modules with many routines  * 
* in them. They might hit performance issue when they try to   * 
* drop module or drop module body.                             * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* We have a performance problem in GA in the DROP MODULE and   * 
* DROP MODULE BODY statements.                                 * 
* Usually we don't get too concerned about DDL performance.    * 
* The difference in this case is that internally we use DROP   * 
* MODULE BODY as part of CREATE OR REPLACE PACKAGE BODY, and   * 
* when there are thousands of modules with many routines in    * 
* them, the performance hit is significant.                    * 
*                                                              * 
* The root of the problem is that the available index on       * 
* SYSROUTINES is on columns SCHEMA+SPECIFICNAME+MODULEID (when * 
* ideally it should've been defined over                       * 
* SCHEMA+MODULEID+SPECIFICNAME)                                * 
*                                                              * 
* So, to figure out the routines in a given module, we can     * 
* only use the first column of the index, and then loop over   * 
* all the routines in the schema to find the ones that belong  * 
* to the module in question.                                   * 
* When there are several thousand pl/sql packages, each with   * 
* many routines, this could be a long scan. It was Patrick who * 
* brough this up initially, and last week we also heard        * 
* complaints from Vikram.                                      * 
* A CREATE OR REPLACE PACKAGE BODY statement can take several  * 
* minutes.                                                     * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Update the product to V97 fixpack 1 or any further fixpack   * 
* or releases                                                  * 
****************************************************************

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
 

The problem has been addressed in V97 FP1 and later fixpacks as 
well as any future releases. With the fix, customers should not 
observe the performance degradation when they drop module or 
drop module body.

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

28.07.2009
17.02.2010
17.02.2010

9.7.FP1