Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to solve the bufferpool problem of Db2. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

The memory applied to the database from the operating system can be divided into two parts: 1. Cache pool memory (data pages and free pages) 2. Non-cache pool memory (thread / DLL/ connection server, etc.). In the database, how to set and adjust bufferpool is an important skill, too large value may make the database unable to start, too small will make the application error, can not give full play to the performance of the database.

For example:

When you connect to the DB2 database and run a SQL statement, you will be prompted:

SQL1218N There are no pages currently available in bufferpool "4096". SQLSTATE=57011 .

General solution:

Resize buffpool

Solution:

1 > start the database

Db2inst1@tysq: db2start

Start the db2 command line

Db2inst1@tysq:db2

2 > connect to the database

Db2 = > connect to test

3 > modify buffer pool

Check the existing buffer pool in the current database

Db2= > select * from syscat.bufferpools

The results show that the system has a default bufferpool IBMDEFAULTBP with a size of 4K (1x 4096), which is obviously too small.

Directly modify the size of the IBMDEFAULTBP:

Db2 = > alter bufferpool IBMDEFAULTBP immediate size 50000

Now the size of bufferpool is 200m (50000 to 4096), which can meet the needs of current applications.

4 > restart the database manager

Db2inst1@tysq: db2stop force

Db2inst1@tysq: db2star

There is another way to observe.

Db2inst1@tysq:~ > db2pd-db zssqdb01-buff

Database Partition 0-- Database ZSSQDB01-- Active-- Up 23 days 08:43:02-- Date 2014-01-11-17.58.28.554662

Bufferpools:

First Active Pool ID 1

Max Bufferpool ID 2

Max Bufferpool ID on Disk 2

Num Bufferpools 6

Address Id Name PageSz PA-NumPgs BA-NumPgs BlkSize NumTbsp PgsToRemov CurrentSz PostAlter SuspndTSCt Automatic

0x00002AB4A91D91C0 1 IBMDEFAULTBP 4096 86884 00 5 0 86884 86884 0 True

0x00002AB4A91DA3C0 2 BP32 32768 45000 002 0 45000 45000 0 False

0x00002AB419AE4260 4096 IBMSYSTEMBP4K 4096 16 0000 16 16 0 False

0x00002AB419AE5460 4097 IBMSYSTEMBP8K 8192 16 0000 16 16 0 False

0x00002AB419AE66A0 4098 IBMSYSTEMBP16K 16384 16 0000 16 16 0 False

0x00002AB419AE78E0 4099 IBMSYSTEMBP32K 32768 16 0000 16 16 0 False

You can see the size of each buffer here. If you check the SQL statement, you can find that the corresponding table space for reporting insufficient buffer information is in which table space and what BufferpoolID is used for that table space, so that the corresponding buffer space can be expanded. Not all of these errors are related to IBMDEFAULTBP.

Similarly, sometimes the buffer is set too large, the current machine physical memory is not enough to allocate this large buffer pool, but will make the database unreachable, when trying to connect will prompt such an error: SQL1042C An unexpected system error occurred. SQLSTATE=58004

. At this time, the key is not to expand, but to shrink and solve the problem by shrinking the Bufferpool.

You can try to solve this problem with the following steps:

1. Log in to the system with the user of the DB2 instance Owner

two。 Execute the following command to set the environment variable DB2_OVERRIDE_BPF

Db2set DB2_OVERRIDE_BPF=number-of-pages

Where number-of-pages is the number of pages, which should be a small value that can be assigned by the operating system, such as 5000; the next time the DB2 database connects to activate the database, the DB2 system will try to allocate BUFFERPOOL according to the size specified by this parameter

3. Execute db2 terminate

Run the command db2 connect to db to establish a database connection; since we have set the DB2_OVERRIDE_BPF environment variable, this connection should be successful

4. After the connection is established, do not do anything else, modify the bufferpool immediately

Db2 alter bufferpool bufferpool-name size number-of-pages

5. In order for the size of the new buffer pool we modified to take effect, we need to first run the command db2 connect reset to disconnect the current connection

6. Run the command db2set DB2_OVERRIDE_BPF= to remove the environment variable so that the size of the new buffer pool we configured takes effect

7. Reconnect to the database

Note: when operating on a 32-bit system, the total memory of db2 in this system is only about 1.75G, while that of bufferpool is 1.2G.

To make full use of host memory, change the operating system to 64-bit and your db2 to 64-bit. In addition, db2 should not use more than half of the host memory.

Add one point: (from the Internet)

To accurately reflect the bufferpool settings that are now in effect, you should

1. Db2 get snapshot for bufferpools on dbname | lessDB2 V7, 8, 8, 9, universal. Partition is universal, plus db2_all or rah

2. Db2mtrk-d-vDB2 V8 and 9 are universal. Partition is universal, plus db2_all or rah

3. Db2 "select * from sysibmadm.snapbp" DB2 V9 is universal. Partition should be sysibmadm.snapbp_part.

4. Db2 "select * from sysibmadm.bp_hitratio" DB2 V9 is universal. Partition is common, plus db2_all or rah note: 1 is only valid for a single activated database; 3. 4 is only valid for the current connect database.

After reading the above, do you have any further understanding of how to solve the bufferpool problem of Db2? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.