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