Guys,

Our environment is db2 v8.1.2 on win2k server. We have a single db2 instance running both Data Warehouse (OLAP) and OLTP application databases.

We have been geting buffer pool error for most of the tables. Please refer snapshot of db2diag.log file.

"ADM6073W The table space "TS_PRICE_FCST" (ID "24") is configured to use buffer pool ID "2", but this buffer pool is not active at this time. In the interim the table space will use buffer pool ID "4096". The inactive buffer pool should become available at next database startup provided that the required memory is available."

Please be informed that we have around 8gb of memory on this server and we have allocated around 1 - 1.5 GB for OLAP database alone. The OLTP may have around another 1 GB of memory allocated. Inspite of this memory settings, we are getting the above mentioned error.

My question is: 1] why is this happening and possible remedy for the same. 2] Is there any restriction on instance level memory allocation i.e One instance can be allocated max of 2GB of memory!?

Any thoughts on why this error is encountered will be of immense help.

Thanks in advance.

DB2 does have memory limitations for buffer pools on Windows 2000, but I am not sure if you are hitting the limit. For your configuration, you might want to use extended storage for buffer pools.

Also, check out the Administration Guide: Performance, page 266 where it discusses "Secondary buffer pools in extended memory on 32-bit platforms."

You might also look at APARs in fixpaks 3 and 4.

Using Windows 2003 Server with 64 bit DB2 will allow direct addressing of more memory.

Here is some info from APAR IY49691. Sounds like there is a 3GB limit on DB2 shared memory in Windows 2000 Server:

LOCAL FIX:
change the configuration in accordance with 32-bit shared memory
limits using the following guidelines:

bufferpools + util_heap_sz + pckcachesz + locklist + dbheap
+ sheapthres if intra_parallel is on - sheapthres_shr on v8
+ 100 bytes per page of extended storage if enabled
* 1.1 for additional overhead

Approximate limits for 32-bit (depends on configurations):
AIX 2GB Solaris 3.5GB HP 1GB NUMA-Q 2GB Linux 1.75GB
Windows 2GB or 3GB if Windows version supports 3GB user space

Marcus -

Great response indeed.

Earlier, we had tried the extended space route but it did not solve the problem.

I think as we assessed (and confirmed by your "local fix" section) there is a ceiling on the memory allocation per instance and therefore reducing the bufferpool size and utl_heap_sz well within 2gb did the trick.

Anyway, many thanks for your response. I really appreciate it.

Regards,
Drs