|
Problem IC64055
|
Status: Closed |
DB2LOOK -L FOR 4300 TABLESPACES IS SLOW |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Description :
db2look -l for 4300 tablespaces is slow
Problem reproducible steps:
$ db2start
$ db2 create db sample
$ ./prep.sh <-- see below
$ db2 connect to sample
$ db2 connect to sample
$ db2look -d SAMPLE -l -o TBSP.ddl
----------------------------------------------------------------
prep.sh
----------------------------------------------------------------
#
db2 connect to sample
db2 "CREATE TABLESPACE USER PAGESIZE 4096 MANAGED BY DATABASE
USING (file 'CON1' 61440, file 'CON2' 61440)"
CNT=1
MAX=4300
#MAX=2
while [ "$CNT" -le "$MAX" ]
do
echo "CREATE LARGE TABLESPACE \"T$CNT\" IN DATABASE PARTITION
GROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY DATABASE" > c.sql
echo " USING (FILE 'T$CNT.DBF001' 448, " >> c.sql
echo " FILE 'T$CNT.DBF002' 448, " >> c.sql
echo " FILE 'T$CNT.DBF003' 448, " >> c.sql
echo " FILE 'T$CNT.DBF004' 448) " >> c.sql
echo ' EXTENTSIZE 48 ' >> c.sql
echo ' PREFETCHSIZE AUTOMATIC ' >> c.sql
echo ' BUFFERPOOL IBMDEFAULTBP ' >> c.sql
echo ' OVERHEAD 7.500000 ' >> c.sql
echo ' TRANSFERRATE 0.060000 ' >> c.sql
echo ' NO FILE SYSTEM CACHING;' >> c.sql
#echo ' DROPPED TABLE RECOVERY ON;' >> c.sql
echo ' ' >> c.sql
echo "CREATE TABLE TAB$CNT (c1 int, c2 clob) in USER long in
T$CNT;" >> c.sql
echo "INSERT INTO TAB$CNT values ( 1, '10');" >> c.sql
db2 -tvf c.sql
CNT=`expr $CNT + 1`
done
db2 terminate
rm c.sql
---------------------------------------------------------------- |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* software *
****************************************************************
* PROBLEM DESCRIPTION: *
* db2look was taking more than 10 hrs to complete db2look -l *
* option which is used to generate tablespace DDLs *
****************************************************************
* RECOMMENDATION: *
* please move to v97 fp1 where the performance with db2look -l *
* option has been improved *
**************************************************************** |
| Local Fix: |
N/A |
| 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 |
db2look performance was improved after providing the fix and now
it taken 1 hrs to generate more than 4300 tablespace DDLs |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 22.10.2009
24.02.2010
24.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
|
 |
