Error while loading data into DB2 (Issue in Bufferpool)

Question

For the past few days we are getting an while loading the data into DB2 Tables.

com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-1218, SQLSTATE=57011, SQLERRMC=4096, DRIVER=4.24.92

I tried to check the actual reason and found it to be an issue in the buffer pool.

However, we tried creating additional buffer pools but the issue still exists. Please help to fix this issue. Thanks in advance.

Answer 1

If you looked up the error message 1218 the solution is right there as it says "increase the bufferpool"

Alter the bufferpool to increase its size as described here.

Answer 2

The bufferpool id (4096) in SQLERRMC means, that there is no pages in a tiny hidden bufferpool IBMSYSTEMBP4K of 16 4K pages. This bufferpool is assigned to a tablespace, when a bufferpool corresponding to this tablespace can't be started during the database activation. This can happen because of a configuration error or memory pressure in the system, for example.
You shouldn't create additional bufferpools, because it can't make DB2 use this bufferpool immediately for this tablespace. Drop these additional bufferpools you created. You can't enlarge this tiny hidden bufferpool as well.

The only thing you can do is to find this bufferpool, set its size accordingly and restart the database. The following query should help you to fine this bufferpool.

SELECT BPNAME, NPAGES
FROM SYSCAT.BUFFERPOOLS S
WHERE NOT EXISTS 
(
SELECT 1
FROM TABLE (MON_GET_BUFFERPOOL(NULL, -2)) M
WHERE M.BP_NAME = S.BPNAME
)

Run the following query for each bufferpool name in the result, for example. It sets the bufferpool size to 5000 pages (with an ability to grow if you have Self Tuning Memory Management enabled).

ALTER BUFFERPOOL bp_name_from_result SIZE 5000 AUTOMATIC

Restart the database afterwards (force all users off the database & run deactivate db mydbname). Check with the 1-st query again, if you still have some bufferpools not started.