|
Problem IC63969
|
Status: Closed |
IMPORT PERFORMANCE SUFFERS OR DEADLOCKS / LOCK TIMEOUTS ON V95 DUE TO INTERNAL UDF |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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 Summary: |
****************************************************************
* 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: |
|
| 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 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
|
| Solution |
Fixed in DB2 V9.7 fp1 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 22.10.2009
21.02.2010
21.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
|
 |
