home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IC63969 Status: Geschlossen

IMPORT PERFORMANCE SUFFERS OR DEADLOCKS / LOCK TIMEOUTS ON V95 DUE
TO INTERNAL UDF

Produkt:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problembeschreibung:
IMPORT performance suffers in v9.5 due to 
an internal SQL used by IMPORT that references the internal 
function BASE_TABLE(). 
The change to this internal SQL was introduced as a side 
effect of APAR IY86809 
Running an event monitor or a snapshot would show a high value 
of Rows Read. 
. 
In addition to performance problems, the system 
may encounter deadlocks and/or lock timeouts and/or 
lock waits while IMPORTS are running concurrently with 
other operations, as a result of too many rows in 
SYSTABLES being accessed and locked. 
. 
Environments more vulnerable to poor performance are ones with 
several tables since this results in several rows in 
SYSIBM.SYSTABLES which is a table accessed by the internal 
UDF. 
To recreate the problem, run: 
(1)Have several tables created so that SYSIBM.SYSTABLES is 
   large and performn RUNSTATS on these tables. 
(2)Create an event monitor: 
   db2 "create event monitor myevent for TABLES, TABLESPACES, 
   BUFFERPOOLS, STATEMENTS write to file '<path>' maxfiles 50 
   maxfilesize 1024 replace manualstart"; 
(3)Activate the event monitor 
   db2 "set event monitor myevent state 1" 
(4)Perform a simple import such as: 
   db2 "import from /dev/null of del replace into <sn>.<tn>" 
   where <sn> is the schema name and <tn> is the table name. 
(5)Deactivate the event monitor: 
   db2 "set event monitor myevent state 0" 
(6)Format the event monitor: 
   db2evmon -path <path> > evmon.txt 
(7)Scan the evmon.txt file for the query that shows several 
   "Rows read" which will be in the section labelled 
    Type: Dynamic 
    Operation: Close 
. 
The query would appear as follows: 
SELECT T.TYPE, SUM(CASE WHEN TC.ENFORCED='Y' THEN 1 ELSE 0 END) 
AS CHILDREN, SUM(CASE WHEN TC.ENFORCED='Y' 
AND R.TABNAME=T.TABNAME AND R.TABSCHEMA=T.TABSCHEMA THEN 1 ELSE 
0 END) AS SELFREFS FROM TABLE(SYSPROC.BASE_TABLE('<schema>', 
'<table>')) B, SYSCAT.TABLES T LEFT OUTER JOIN SYSCAT.REFERENCES 
R ON R.REFTABNAME=T.TABNAME AND R.REFTABSCHEMA=T.TABSCHE 
MA LEFT OUTER JOIN SYSCAT.TABCONST TC ON TC.TABNAME=R.TABNAME 
AND TC.TABSCHEMA=R.TABSCHEMA AND TC.CONSTNAME=R.CONSTNAME 
AND TC.TYPE='F' where T.TABNAME=B.BASENAME AND 
T.TABSCHEMA=B.BASESCHEMA GROUP BY T.TYPE;
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* Users of IMPORT                                              * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* IMPORT performance suffers in v9.5 due to                    * 
*                                                              * 
* an internal SQL used by IMPORT that references the internal  * 
*                                                              * 
* function BASE_TABLE().                                       * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 V9.7 fp1                                      * 
****************************************************************
Local-Fix:
verfügbare FixPacks:
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 6 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 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

Lösung
Fixed in DB2 V9.7 fp1
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
22.10.2009
21.02.2010
21.02.2010
Problem behoben ab folgender Versionen (IBM BugInfos)
9.7.FP1
Problem behoben lt. FixList in der Version
9.7.0.1 FixList