IZ53624: DB2LOOK -L FOR 4300 TABLESPACES IS SLOW

DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

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

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * N/A                                                          *
  ****************************************************************
  * 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.sqlecho "    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                                                     *
  * --------------------------------------------------------------
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 version 9.5 Fix Pack 5                        *
  ****************************************************************
  

Problem conclusion

• First fixed in DB2 version 9.5 Fix Pack 5
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  IC64055 IZ75368

Fix information

• Fixed component name

  DB2 UDB ESE SOL

• Fixed component ID

  5765F4102

Applicable component levels

• R950 PSN

     UP

• R950 PSY

     UP