Browse DevX
Sign up for e-mail newsletters from DevX


Tuning DB2: Where Your Data Is and Why it Matters-3 : Page 3




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Disk Storage and Storage Management
Data is located logically in named areas called tablespaces, which can be System Managed Space (SMS) or Database Managed Space (DMS). In either case, the space comprises one or more containers.In the case of a System Managed Space, a container is a file system directory. Tables and indices are created as numerous files within the directory, and, as the files grow, allocation of disk space is entrusted to the host file system. This means the tablespace is subject to file system fragmentation and overhead during the opening and closing of multiple files.

Database Managed Space requires an up-front allocation for storage, and the database administrator must perform additional allocation when required. A DMS container can be a single file in a host file system or can be created directly on a disk, bypassing the filesystem altogether. Generally DMS gives much better performance. (To find out how DB2 arranges data within the tablespace, regardless of type, see Sidebar, "Does the Data Get Fragmented?")Tablespaces can be nominated as:

  • Regular, for normal persistent data
  • Temporary, for data sets built during processing of queries
  • Long, specifically for storing long data types such as graphics. Long data may be stored in regular tablespaces, though handling and tuning long data separately can result in significant performance gains.

How to create a new tablespace:
You can specify tablespace options in the CREATE DATABASE command, and add more with CREATE TABLESPACE or by using the Control Center. Then when you create tables, you can specify which tablespace to use for the table and/or indices.

Why have more than one tablespace?
In DB2, storage is handled in pages. Depending on the tablespace page size and the size of rows in your tables, a row may occupy many pages, or a page may contain many rows. Storing a row, or many rows, in a single page, allows the entire row or rows to be retrieved as a single unit from disk. (The same economy applies to buffer pools in memory—see next page).Large page sizes are good for storing tables with very long rows or tables used regularly for sequential data access. Conversely, storing small, randomly accessed rows in large pages makes for inefficient use of the buffer pools. To allow different tables to be stored with different page sizes and extent sizes, and to allow separate buffering, it's best to use multiple user tablespaces as you will see on the next page

Every database requires:

  • A system catalog tablespace named SYSCATSPACE to store a description of the database and its structure and contents. By default this is created as SMS.
  • A system temporary tablespace, for holding data while sorting or collating results. By default, an SMS tablespace named TEMPSPACE1 is created for you under the instance's default database path.
  • One or more regular tablespaces for user data. By default, an SMS tablespace named USERSPACE1 is created for you.
But you may also have one or more long tablespaces for long data, and one or more user temporary tablespaces for declared temporary tables (a new feature in DB2 v7.1).Unless you specify otherwise, SMS tablespaces are created under the instance's default database path (parameter DFTDBPATH).

How to create additional containers:
Use the