|
Problem IC62249
|
Status: Closed |
A new index has been added to SYSIBM.SYSROUTINES to improve the performance of the DROP PACKAGE BODY PL/SQL statement |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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. |
| Problem Summary: |
****************************************************************
* 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 *
**************************************************************** |
| Local Fix: |
|
| 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 |
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. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 28.07.2009
17.02.2010
17.02.2010 |
| 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
|
 |
