db2bpf - Buffer pool definition management command
Displays, validates, and updates the details of the SQLBP.1 and SQLBP.2 buffer
pool definition files. These files contain the buffer pool definitions for one
database.
You can run the db2bpf command to modify buffer pool sizes
on HADR standby servers when the HADR primary server is using the self-tuning memory
manager (STMM).
Authorization
None
Required Connection
None
Command syntax
Command parameters
You specify an information source in one of the following ways:
- -filename
- filename is SQLBP.1, SQLBP.2, or a copy of one of those files.
- -d database-name
- database-name is the name of the database to be used, instead of a file.
You can specify a display-level option to filter the displayed information. If no display-level
options are specified, the header and all buffer pool entries are displayed:
- -1
- Displays only the header
- -2
- Displays the information for the buffer pool entry that is specified by the -b option. If a single entry is not specified, then all buffer pool entries are displayed.
You can set the following options:
- -m member-ID
- Specifies the member ID
- -b bufferpool-ID
- Specifies the buffer pool entry. The value must be greater than or equal to 1.
- -s number-of-slots
- Alters the number of pages or slots for the specified buffer pool.
- -v
- Verifies the file's header for consistency with the buffer pool entries and validates the header, if necessary. Use of this option is mandatory after modifying the page size or the number of slots of a buffer pool.
- -h, --h, --help
- Displays the help screen.
Examples
- Example 1: Display the file header.
- Using the file name option:
Using the database name option:$ db2bpf SQLBP.1 -1 ... Buffer pool File: SQLBP.1 ============================================================================== = Header Information ========================================================= Database name: SAMPLE Checksum: 0x504d01c7 Changed by dart flag: 0x0 Version: 2 (0x00000002) Flavour: 4 (0x00000004) Number of buffer pools not using BUFFPAGE default: 2 Number of buffer pools using BUFFPAGE default: 0 Number of pages/slots used by buffer pools not using BUFFPAGE default: 1500 (0x00000000000005dc) Requires Check: 0 ==============================================================================$ db2bpf -d sample -1 ... ============================================================================== Buffer pool File: /home/XXXX/XXXX/NODE0000/SQL00001/MEMBER0000/SQLBP.1 ============================================================================== = Header Information ========================================================= Database name: SAMPLE Checksum: 0x504d01c7 Changed by dart flag: 0x0 Version: 2 (0x00000002) Flavour: 4 (0x00000004) Number of buffer pools not using BUFFPAGE default: 2 Number of buffer pools using BUFFPAGE default: 0 Number of pages/slots used by buffer pools not using BUFFPAGE default: 1500 (0x00000000000005dc) Requires Check: 0 ==============================================================================
- Example 2: Display all buffer pool entries.
- Using the file name option:
Using the database name option:$ db2bpf SQLBP.1 -2 ... ============================================================================== Buffer pool File: SQLBP.1 ============================================================================== = Buffer pool Entry #01 ======================================================= Bufferpool name: IBMDEFAULTBP Bufferpool ID: 1 Number of slots/pages: 1000 (0x000003e8) Page size: 8192 Number of block pages: 0 Block size: 0 Life LSN: 0000000000000000 Update LSN: 0000000000000000 Checksum: 0x414e7041 Version: 1 (0x00000001) Flavour: 3 (0x00000003) Automatic: off ============================================================================== = Buffer pool Entry #02 ======================================================= Bufferpool name: SAMPLEBP Bufferpool ID: 2 Number of slots/pages: 500 (0x000001f4) Page size: 4096 Number of block pages: 0 Block size: 0 Life LSN: 000000000004506D Update LSN: 000000000004506D Checksum: 0x000f15e9 Version: 1 (0x00000001) Flavour: 3 (0x00000003) Automatic: off$ db2bpf -d sample -2 ... ============================================================================== Buffer pool File: /home/XXXX/XXXX/NODE0000/SQL00001/MEMBER0000/SQLBP.1 ============================================================================== = Buffer pool Entry #01 ======================================================= Bufferpool name: IBMDEFAULTBP Bufferpool ID: 1 Number of slots/pages: 1000 (0x000003e8) Page size: 8192 Number of block pages: 0 Block size: 0 Life LSN: 0000000000000000 Update LSN: 0000000000000000 Checksum: 0x414e7041 Version: 1 (0x00000001) Flavour: 3 (0x00000003) Automatic: off ============================================================================== = Buffer pool Entry #02 ======================================================= Bufferpool name: SAMPLEBP Bufferpool ID: 2 Number of slots/pages: 500 (0x000001f4) Page size: 4096 Number of block pages: 0 Block size: 0 Life LSN: 000000000004506D Update LSN: 000000000004506D Checksum: 0x000f15e9 Version: 1 (0x00000001) Flavour: 3 (0x00000003) Automatic: off ==============================================================================
- Example 3: Display a specific buffer pool entry.
- Using the file name option:
Using the database name option:$ db2bpf SQLBP.1 -2 -b 2 ... ============================================================================== Buffer pool File: SQLBP.1 ============================================================================== = Buffer pool Entry #02 ======================================================= Bufferpool name: SAMPLEBP Bufferpool ID: 2 Number of slots/pages: 500 (0x000001f4) Page size: 4096 Number of block pages: 0 Block size: 0 Life LSN: 000000000004506D Update LSN: 000000000004506D Checksum: 0x000f15e9 Version: 1 (0x00000001) Flavour: 3 (0x00000003) Automatic: off ==============================================================================$ db2bpf -d sample -2 -b 2 ... ============================================================================== Buffer pool File: /home/XXXX/XXXX/NODE0000/SQL00001/MEMBER0000/SQLBP.1 ============================================================================== = Buffer pool Entry #02 ======================================================= Bufferpool name: SAMPLEBP Bufferpool ID: 2 Number of slots/pages: 500 (0x000001f4) Page size: 4096 Number of block pages: 0 Block size: 0 Life LSN: 000000000004506D Update LSN: 000000000004506D Checksum: 0x000f15e9 Version: 1 (0x00000001) Flavour: 3 (0x00000003) Automatic: off ==============================================================================
- Example 4: Verify the consistency of the header.
- Using the file name option:
Using the database name option:$ db2bpf SQLBP.1 -v ... ============================================================================== Buffer pool File: SQLBP.1 ============================================================================== The file header has been successfully verified and validated.$ db2bpf -d sample -v ... ============================================================================== Buffer pool File: /home/XXXX/XXXX/NODE0000/SQL00001/MEMBER0000/SQLBP.1 ============================================================================== The file header has been successfully verified and validated.
- Example 5: Change the number of slots for a buffer pool entry.
- Using the file name option:
Using the database name option:$ db2bpf SQLBP.1 -b 2 -s 600 ... ============================================================================== Buffer pool File: SQLBP.1 ============================================================================== The header was validated successfully. The buffer pool entry has been updated successfully.$ db2bpf -d sample -b 2 -s 600 ... ============================================================================== Buffer pool File: /home/XXXX/XXXX/NODE0000/SQL00001/MEMBER0000/SQLBP.1 ============================================================================== The header was validated successfully. The buffer pool entry has been updated successfully. The header was validated successfully. The buffer pool entry has been updated successfully.
- Example 6: Change the number of slots for a buffer pool entry on a specific member (pureScale and DPF only).
- On pureScale:
On DPF:$ db2bpf -d sample -m 1 -b 2 -s 600 ... ============================================================================== Buffer pool File: /home/XXXX/XXXX/NODE0000/SQL00001/MEMBER0001/SQLBP.1 ============================================================================== The header was validated successfully. The buffer pool entry has been updated successfully. The header was validated successfully. The buffer pool entry has been updated successfully.$ db2bpf -d sample -m 1 -b 2 -s 600 ... ============================================================================== Buffer pool File: /home/XXXX/XXXX/NODE0001/SQL00001/MEMBER0001/SQLBP.1 ============================================================================== The header was validated successfully. The buffer pool entry has been updated successfully. The header was validated successfully. The buffer pool entry has been updated successfully.
Usage notes
- When updating the number of slots of a buffer pool, run the tool with the
-voption to ensure the appropriate header fields are updated. - When specifying a database, db2bpf uses the member specified by the user with
the -m option. If no member is specified by the user, then the DB2NODE
environment variable is checked by db2bpf. If DB2NODE is not set,
db2bpf attempts to use the default node for your environment. The value of the
logical port for the default node depends on the situation:
- For MPP environments, the default node is the first node listed in the db2nodes.cfg file that has the same hostname as the database. The logical port value is 0.
- For non-MPP environments, the default node is the first node listed in the db2nodes.cfg file.
- If there is no db2nodes.cfg file, the default value is 0.
- If an error occurs getting the node number, default to 0.
- When
db2 drop bufferpool ...is invoked, the following fields are changed for that buffer pool entry:- The buffer pool name is set to NULL
- The buffer pool ID is set to 65535 (0xFFFF).
- The buffer pool pages or slots value is set to 0 (0x00000000).
- The page size defaults to 4K.
- The database must be deactivated to run the db2bpf command.
- If no member is specified by using the
-moption, the db2bpf command selects one from theDB2NODEenvironment variable.