Dears
I am working on a production transactional database DB2 v(9.5.3)..suddenly when i run a normal select query the DB2 returns:
SQL20169W The buffer pool is not started. SQLSTATE=01654
when i search for this on IBM info center it indicates that to start the bufferpools i should drop and recreate the bufferpools!!!!!

Is there any other way to start the bufferpools without dropping and recreating the Bufferpools ???

please Help as this issue was repeated many times...

Most likely the bufferpools you have defined are too large for the amont of memory available on the server. You probably need to alter some of them to reduce the size.

It might help if you could provide the following information:

• db2 "select * from syscat.bufferpools" (for every database on the server)
• the total amount of memory on the server

Dear Marcus
Thanks for your quick reply..
The SYSCAT.BUFFERPOOLS Data is:
BPNAME BUFFERPOOLID DBPGNAME NPAGES PAGESIZE
IBMDEFAULTBP 1 -2 4096
BP_CONFIG 2 300 8192
BP_LOOKUP 3 700 8192
BP_BILLING 4 99300 8192
BP_AUDIT 5 22400 8192
BP_AUDIT_LOB 6 2800 32768
BP_PAYMENT 7 9740 8192
BP_MSGLOG 8 46700 8192
BP_MSGLOG_LOB 9 14600 32768
BP_ERROR 10 7500 8192

The installed memory at the machine is 4608 MB...

Thanks in Advance

I am not sure all the tables that are associated with IBMDEFAULTBP in your database, but I would change the -2 to some fixed number. If only the catalog tablespace is using IBMDEFAULTBP, then set it to 3000 pages. If you have temp tablespaces using it, then assign them to different tablespaces.

If the only tablespaces using BP_MSGLOG_LOB and BP_AUDIT_LOB bufferpools are tablespaces that only have LOB columns, reduce them to 250 pages. LOB columns do not use bufferpools, even though you must assign a bufferpool to every tablespace.

Slightly off topic, but IMO you have too many bufferpools. For most databases, "something like" this usually works better:

• IBMDEFAULTBP - catalog tablespace only - 3000 pages
• SMALL_8K_BP - small reference tables, and indexes on small and medium size tables (30% of bufferpool memory)
• MEDIUM_8K_BP - medium size tables, and indexes for large tables (30% of bufferpool memory)
• LARGE_8K_BP - Large tables, and system/user temp tables (30% of bufferpool memory)
• 32K_BP - all 32K tablespaces - size depends on whether you have any non-LOB 32 Tablespaces.

Dear Marcus
No problem with all what u said..but the bufferpools were up and started yesterday and suddenly they are all down !!!!
the size of the physical memory is enough for all these buffer pools to be allocated... i hope u have an explanation for this

another question is Is there any other way to get these bufferpools up without dropping and recreating them??

Thanks for your reply and patience http://1.1.1.5/bmi/www.dbforums.com/...s/rolleyes.gif

The fact that the bufferpools were up yesterday, does not mean much. If a bufferpool is set to -2 the size various continously, which can be a problem. Also, you may other things on that server using memory.

Suddenly the bufferpool is down? Is the database activated? Is the instance stopped and started in the mean time? Sounds fuzzy to me.
What might help in your case is the registry variabele "db2_override_bpf". This will make sure that the all the bufferpools are started with a fixed size (specified by that variabele). Once the database is up-and-running you can alter the bufferpools to set the required sizes or let STMM pick it up from there. Not ideal, but everything is better than a database start with hidden bufferpools.