DB2 FOR LUW / DB2FORLUW / 970 - DB2

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;

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

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
 

Fixed in DB2 V9.7 fp1

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

22.10.2009
21.02.2010
21.02.2010

9.7.FP1