Creating and Editing Buffer Pools

Go Up to Creating and Editing Database Dependent Objects

The buffer pool is a memory structure that can cache data blocks in memory, providing faster data access by retaining the object in memory to avoid I/O conflicts or remove data blocks from memory as soon as they are no longer in use, thereby saving cache space. You can choose to use the buffer pool to store indexes and tables for many different database platforms.

The default page size of a buffer pool is the size specified when the database was created unless you explicitly specify another page size. Pages can be ready into a buffer pool only if the table space page size is the same as the buffer pool page size, so ensure the buffer pool page size matches that of the table space.

The following database platforms support buffer pool creation.

• IBM DB2 for LUW 5.x, 6.x, 7.x, 8.x, 9.x, 10.x

Note: The Bufferpool Wizard and Bufferpool Editor share the same options, except for Attachment Bindings options which are present only in the editor.

Create a Buffer Pool

1. In the Data Model Explorer, expand the Logical Main Model, right-click the Buffer Pools node, and then click New Bufferpool.
2. Complete the Bufferpool Wizard and then click Finish to create the buffer pool.

TIP: Once you have created the auxiliary table, you can edit it by right-clicking the auxiliary table you want to change, and then selecting Edit Auxiliary Table.

Name page/tab

• Specify the size of the buffer pool in the number of pages. If you do not specify the size, the default size is -1.
• Click the list to specify the size of the pages for the buffer pool in Kilobytes.
• Extended Storage: Select to use extended storage on unix systems, then if the pages requested are not in the buffer pool, they can be accessed from extended storage, which can be faster than reading the pages from disk. This option is ignored in IBM DB2 for LUW version 9 and later.

Note: Ensure your system has enough real memory for all the buffer pools, in addition to the memory required for the database manager and application.

Nodes page/tab

In version 9, IBM started using DBPARITITOINNUM instead of NODE.

Definition page/tab

Enter or edit a definition for the buffer pool. If the target database supports it, ER/Studio Data Architect adds this definition as a comment when generating SQL code.

DDL page/tab

Displays the CREATE BUFFERPOOL statement needed to build the buffer pool. ER/Studio Data Architect uses the platform-specific parser of the model's selected database platform to generate the buffer pool.

Attachment Bindings tab

Bind an external piece of information, or attachment to the buffer pool. You can also remove an attachment from an object, override an attachment binding's default value, or change the position of a bound attachment. To override the value of the attachment you have moved to the Selected Attachments grid, double-click the Value field of the target attachment. ER/Studio Data Architect opens the Value Override Editor or a list depending on the attachment datatype. Attachments are created in the Attachments folder of the Data Dictionary. For more information, see Attaching External Documents to the Data Model.

See Also

• Developing the Physical Model
• Best Practices