Exception while running number of queries concurrently on DB2

Question

We are testing an application specific Design where we need to show progressive counts after firing many queries simultaneously.

When we are firing a single query, we are successfully able to get Results Asynchronously.

But, when we are firing 3-4 queries simultaneously, after a significant amount of time & after getting some records for each query we are getting an exception which states

“com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -1218, SQLSTATE: 57011, SQLERRMC: 4099”

Because of this, all the queries fired do not get executed completely.

There is no problem if these queries are fired one by one. (query2 starts executing after query1 completes & so on)

After looking for this SQLCODE in Control Center we found out the details which are,

---

SQL1218N There are no pages currently available in bufferpool

  "<buffpool-num>".

Explanation:

All of the pages in the bufferpool are currently being used. A request to use another page failed.

The statement may be successful if executed again. If this error occurs frequently, some or all of the following actions may prevent further failures:

1. increase the bufferpool size

2. decrease the maximum number of database agents and/or connections

3. decrease the maximum degree of parallelism

4. decrease the prefetch size for table spaces that are in this

   bufferpool

5. move some table spaces into other bufferpools.

   sqlcode: -1218

   sqlstate: 57011

---

On our DB instance, we already had a buffer pool of 32K page size & even after creating another buffer pool of 32K, the problem persists.

Can anyone kindly help us in solving the problem.

Thanks & Regards,

Ravindra Jain

Answer 1

The most likely cause is your transactions are not issueing "commits" so there is buildup of uncommited data in the buffer pools.

Also 32K seems very small for current hardware ( Since DB2 can handle CLOBs and BLOBs of several megabytes) 32MB or more would be more appropriate for a bufferpool size.

Answer 2

We are committing after each record is inserted/updated. So, redo log is not a problem. (buffer pool will never be over-flooded).

32K is the page size of the buffer pool & buffer pool contains 10000 pages. So, are these configurations appropriate for the buffer pool?

Answer 3

The pagesize of a bufferpool has to match the pagesize of the tablespaces it is associated with. Do all of your tablespaces use 32K pages? You should check whether there is a different bufferpool that is running out of pages. 10,000 pages should be more than enough to avoid the error you are seeing.