planning, and instance and database configuration. Since databases created 

with default values are suited for computers with relatively small memory and 

disk storage, you may need to modify them to fit your environment. This section 

focuses on a number of DB2 UDB performance tuning tips that may be used for 

initial configuration.

9.5.1  Table spaces

At database creation time, three table spaces are created: 

  SYSCATSPACE

   Catalog table space for storing information about all the objects 

in the database 

  TEMPSPACE1

   System temporary table space for storing internal temporary 

data required during SQL operations such as sorting, reorganizing tables, 

creating indexes, and joining tables

  USERSPACE1

   For storing application data

By default all the three table spaces are created as 

System Managed Spaces 

(SMS)

, which means that the regular operating system functions will be used for 

handling I/O operations.

Reading and writing data from tables will be buffered by the operating system, 

and space will be allocated according to the operating system conventions: files 

with 

DAT

 extension for tables and 

INX

 files for table indexes. When the table is 

initially created only one page is allocated on disk. When records are inserted 

into a table, DB2 UDB will extend the files by one page at a time.

On heavy inserts, extending files by only one page at a time can be very 

expensive. To minimize internal overhead for the table space extension, you can 

enable multi page file allocation. With multi page file allocation enabled for SMS 

table spaces, disk space is allocated one extent at a time (contiguous groups of 

pages defined for the table space).

To check whether the feature is enabled, look at the database configuration and 

search for the 

Multi page 

word.

Example 9 28   Checking for current page allocation status

$db2 get db cfg for sample

Rollforward pending                                     = NO

Restore pending                                         = NO

Multi page file allocation enabled                      = NO

Log retain for recovery status                          = NO

 Chapter 9. Testing and tuning 

297