To improve performance, database manager define Buffer Pools. Data reading from buffer pools improves performance. Table spaces acts as logical storage. All the database objects stored in Table space.
Table spaces Vs Buffer Pools
If we want to see BufferPools in our project, just query this:
SYSCAT.BUFFERPOOLS
If you want to see Table spaces just query:
SYSCAT.TABLESPACES
Table Spaces:
All data for a database is stored in a number of table spaces. You can think of a table space as a child and a database as its parent, where the table space (child) cannot have more than one database (parent). Because there are different uses for table spaces, they are classified according to their usage and how they will be managed. There are five different table spaces, named according to their usage:
- Catalog table space
- There is only one catalog table space per database, and it is created when the CREATE DATABASE command is issued. Named SYSCATSPACE by DB2, the catalog table space holds the system catalog tables. This table space is always created when the database is created.
- Regular table space
- A regular table space stores all permanent data, including regular tables and indexes. It can also hold large data such as LOBs (Large Objects) unless they are explicitly stored in a large table space. A table and its indexes can be divided into separate regular table spaces, if the table spaces are database-managed space (DMS) for non-partitioned tables or system-managed space (SMS) for partitioned tables. DMS and SMS are described in the section Table space management. Catalog table space is an example of a regular table space. By default, the catalog table space is the only regular table space created during the database creation.
- Large table space
- A large table space stores all permanent data just as a regular table space does, including LOBs. This table space type must be DMS, which is the default type. A table created in a large table space can be larger than a table in a regular table space. A large table can support more than 255 rows per data page improving space utilization on data pages. DB2 creates one large table space named USERSPACE1 when a database is created.
- System temporary table space
- A system temporary table space stores internal temporary data required during SQL operations such as sorting, reorganizing tables, creating indexes, and joining tables. At least one system temporary must exist per database. The default created with the database is named TEMPSPACE1.
- User temporary table space
- A user temporary table space stores declared global temporary tables. No user temporary table spaces exist when a database is created. At least one user temporary table space should be created to allow definition of declared temporary tables. User temporary table spaces are optional. No user temporary table space is created by default.
Buffer Pools:
A buffer pool is associated with a single database and can be used by more than one table space. When considering a buffer pool for one or more table spaces, you must ensure that the table space page size and the buffer pool page size are the same for all table spaces that the buffer pool services. A table space can only use one buffer pool.
- When the database is created, a default buffer pool named IBMDEFAULTBP is created, which is shared by all table spaces. More buffer pools can be added using the CREATE BUFFERPOOL statement.
- The buffer pool size defaults to the size specified by the BUFFPAGE database configuration parameter, but you can override it by specifying the SIZE keyword in the CREATE BUFFERPOOL command.
- Adequate buffer pool size is essential to good database performance, because it will reduce disk I/O, which is the most time consuming operation.
- Large buffer pools also have an effect on query optimization, because more of the work can be done in memory.
Srinimf 
One thought
Comments are closed.