By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,672 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

Is there a way to drop DB2 database buffer pool without connecting to database?

P: n/a
Greetings,
We are having a situation here whereby one of our staff created a
very huge 32K buffer pool in a production database and caused the
database to go down. When we try to reconnect to the database using
"db2 connect to <dbname>", its giving the following error:

SQL1224N A database agent could not be started to service a
request, or was terminated as a result of a database
system shutdown or a force command.
SQLSTATE:55032

Now we are not able to connect to the database eventhough after we
restart the server and instance. What we are thinking now is whether
it is possible to drop the 32k buffer pool without connecting to the
database. Or is there any other workaround that would help us to
resolve this problem. Your feedback is very much appreciated.
Thank you.

Feb 15 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
One possible way to solve the current problem is
.. Create an registry variable
db2 set DB2_OVERRIDE_BPF=<no of pages--Set it to a optimum level
based on the memory limits u have!
.. Restart the instance

On Feb 15, 2:17 pm, xeqis...@gmail.com wrote:
Greetings,
We are having a situation here whereby one of our staff created a
very huge 32K buffer pool in a production database and caused the
database to go down. When we try to reconnect to the database using
"db2 connect to <dbname>", its giving the following error:

SQL1224N A database agent could not be started to service a
request, or was terminated as a result of a database
system shutdown or a force command.
SQLSTATE:55032

Now we are not able to connect to the database eventhough after we
restart the server and instance. What we are thinking now is whether
it is possible to drop the 32k buffer pool without connecting to the
database. Or is there any other workaround that would help us to
resolve this problem. Your feedback is very much appreciated.
Thank you.

Feb 15 '07 #2

P: n/a
On Feb 15, 2:46 pm, "Peri" <peri.n...@gmail.comwrote:
One possible way to solve the current problem is

. Create an registry variable
db2 set DB2_OVERRIDE_BPF=<no of pages--Set it to a optimum level
based on the memory limits u have!
. Restart the instance

On Feb 15, 2:17 pm, xeqis...@gmail.com wrote:
Greetings,
We are having a situation here whereby one of our staff created a
very huge 32K buffer pool in a production database and caused the
database to go down. When we try to reconnect to the database using
"db2 connect to <dbname>", its giving the following error:
SQL1224N A database agent could not be started to service a
request, or was terminated as a result of a database
system shutdown or a force command.
SQLSTATE:55032
Now we are not able to connect to the database eventhough after we
restart the server and instance. What we are thinking now is whether
it is possible to drop the 32k buffer pool without connecting to the
database. Or is there any other workaround that would help us to
resolve this problem. Your feedback is very much appreciated.
Thank you.- Hide quoted text -

- Show quoted text -
Hi Xeqis..

Whether the solution given by Peri fixed the issue, kindly let the
group to know abt the solution..if you did something else to recover
from this scenario...

Thanks,
vj_dba

Feb 15 '07 #3

P: n/a
On Feb 15, 4:16 am, vijay...@gmail.com wrote:
On Feb 15, 2:46 pm, "Peri" <peri.n...@gmail.comwrote:
One possible way to solve the current problem is
. Create an registry variable
db2 set DB2_OVERRIDE_BPF=<no of pages--Set it to a optimum level
based on the memory limits u have!
. Restart the instance
On Feb 15, 2:17 pm, xeqis...@gmail.com wrote:
Greetings,
We are having a situation here whereby one of our staff created a
very huge 32K buffer pool in a production database and caused the
database to go down. When we try to reconnect to the database using
"db2 connect to <dbname>", its giving the following error:
SQL1224N A database agent could not be started to service a
request, or was terminated as a result of a database
system shutdown or a force command.
SQLSTATE:55032
Now we are not able to connect to the database eventhough after we
restart the server and instance. What we are thinking now is whether
it is possible to drop the 32k buffer pool without connecting to the
database. Or is there any other workaround that would help us to
resolve this problem. Your feedback is very much appreciated.
Thank you.- Hide quoted text -
- Show quoted text -

Hi Xeqis..

Whether the solution given by Peri fixed the issue, kindly let the
group to know abt the solution..if you did something else to recover
from this scenario...
w
Thanks,
vj_dba

RE.

btw, this situation is always available on HA cluster environments.
try the "buffpage" database configuration parameter.

Feb 15 '07 #4

P: n/a
On Feb 15, 12:26 pm, "Hardy" <wyh...@gmail.comwrote:
On Feb 15, 4:16 am, vijay...@gmail.com wrote:
On Feb 15, 2:46 pm, "Peri" <peri.n...@gmail.comwrote:
One possible way to solve the current problem is
. Create an registry variable
db2 set DB2_OVERRIDE_BPF=<no of pages--Set it to a optimum level
based on the memory limits u have!
. Restart the instance
On Feb 15, 2:17 pm, xeqis...@gmail.com wrote:
Greetings,
We are having a situation here whereby one of our staff created a
very huge 32K buffer pool in a production database and caused the
database to go down. When we try to reconnect to the database using
"db2 connect to <dbname>", its giving the following error:
SQL1224N A database agent could not be started to service a
request, or was terminated as a result of a database
system shutdown or a force command.
SQLSTATE:55032
Now we are not able to connect to the database eventhough after we
restart the server and instance. What we are thinking now is whether
it is possible to drop the 32k buffer pool without connecting to the
database. Or is there any other workaround that would help us to
resolve this problem. Your feedback is very much appreciated.
Thank you.- Hide quoted text -
- Show quoted text -
Hi Xeqis..
Whether the solution given by Peri fixed the issue, kindly let the
group to know abt the solution..if you did something else to recover
from this scenario...
w
Thanks,
vj_dba

RE.

btw, this situation is always available on HA cluster environments.
try the "buffpage" database configuration parameter.
buffpage is ignored unless you've explicitly set a bufferpool's size
to -1, so it's quite useless in this situation.

The fact that the original poster gets a SQL1224N is strange -- if the
bufferpool can't be allocated in memory, DB2 should dump a "not enough
memory" message and start up with the "hidden" bufferpools (one
bufferpool for each page size with a small number of pages in each
bp). This allows you to connect to the database and alter the sizes
as neccessary.

First, I'd open a PMR with IBM. You should not get a SQL1224N in this
case - EVER.
Second, use Peri's suggestion of doing "db2set DB2_OVERRIDEBPF=<some
small number>" This will force DB2 to ignore the bufferpool sizes in
the catalog, and will use <some small numberof pages for each
bufferpool instead. This will let you alter the bufferpool sizes.

Regards,
Matt Emmerton

Feb 16 '07 #5

P: n/a
On Feb 16, 8:30 am, "memme...@yahoo.com" <m...@gsicomp.on.cawrote:
On Feb 15, 12:26 pm, "Hardy" <wyh...@gmail.comwrote:


On Feb 15, 4:16 am, vijay...@gmail.com wrote:
On Feb 15, 2:46 pm, "Peri" <peri.n...@gmail.comwrote:
One possible way to solve the current problem is
. Create an registry variable
db2 set DB2_OVERRIDE_BPF=<no of pages--Set it to a optimum level
based on the memory limits u have!
. Restart the instance
On Feb 15, 2:17 pm, xeqis...@gmail.com wrote:
Greetings,
We are having a situation here whereby one of our staff created a
very huge 32K buffer pool in a production database and caused the
database to go down. When we try to reconnect to the database using
"db2 connect to <dbname>", its giving the following error:
SQL1224N A database agent could not be started to service a
request, or was terminated as a result of a database
system shutdown or a force command.
SQLSTATE:55032
Now we are not able to connect to the database eventhough after we
restart the server and instance. What we are thinking now is whether
it is possible to drop the 32k buffer pool without connecting to the
database. Or is there any other workaround that would help us to
resolve this problem. Your feedback is very much appreciated.
Thank you.- Hide quoted text -
- Show quoted text -
Hi Xeqis..
Whether the solution given by Peri fixed the issue, kindly let the
group to know abt the solution..if you did something else to recover
from this scenario...
w
Thanks,
vj_dba
RE.
btw, this situation is always available on HA cluster environments.
try the "buffpage" database configuration parameter.

buffpage is ignored unless you've explicitly set a bufferpool's size
to -1, so it's quite useless in this situation.

The fact that the original poster gets a SQL1224N is strange -- if the
bufferpool can't be allocated in memory, DB2 should dump a "not enough
memory" message and start up with the "hidden" bufferpools (one
bufferpool for each page size with a small number of pages in each
bp). This allows you to connect to the database and alter the sizes
as neccessary.

First, I'd open a PMR with IBM. You should not get a SQL1224N in this
case - EVER.
Second, use Peri's suggestion of doing "db2set DB2_OVERRIDEBPF=<some
small number>" This will force DB2 to ignore the bufferpool sizes in
the catalog, and will use <some small numberof pages for each
bufferpool instead. This will let you alter the bufferpool sizes.

Regards,
Matt Emmerton- Hide quoted text -

- Show quoted text -
Hi all,
Thank you very much for all your feedbacks. As what Mr. Peri has
suggested, I set the environment variable "DB2_OVERRIDE_BPF", using:
"db2 set DB2_OVERRIDE_BPF=10000"
then I restart the instance and reconnect to the database, woww....it
was succesful. I then altered the 32k buffer pool and the problem
solved. Actually I was also wondering why DB2 did not automatically
use the default IBMDEFAULTBP when it fails to allocate memory for the
new buffer pool. The database version that we are using is 7.2 on
Linux. Anyway, the problem is solved and thanks again for all your
valuable feedback.

Feb 16 '07 #6

P: n/a
On Feb 15, 6:24 pm, xeqis...@gmail.com wrote:
On Feb 16, 8:30 am, "memme...@yahoo.com" <m...@gsicomp.on.cawrote:


On Feb 15, 12:26 pm, "Hardy" <wyh...@gmail.comwrote:
On Feb 15, 4:16 am, vijay...@gmail.com wrote:
On Feb 15, 2:46 pm, "Peri" <peri.n...@gmail.comwrote:
One possible way to solve the current problem is
. Create an registry variable
db2 set DB2_OVERRIDE_BPF=<no of pages--Set it to a optimum level
based on the memory limits u have!
. Restart the instance
On Feb 15, 2:17 pm, xeqis...@gmail.com wrote:
Greetings,
We are having a situation here whereby one of our staff created a
very huge 32K buffer pool in a production database and caused the
database to go down. When we try to reconnect to the database using
"db2 connect to <dbname>", its giving the following error:
SQL1224N A database agent could not be started to service a
request, or was terminated as a result of a database
system shutdown or a force command.
SQLSTATE:55032
Now we are not able to connect to the database eventhough after we
restart the server and instance. What we are thinking now is whether
it is possible to drop the 32k buffer pool without connecting to the
database. Or is there any other workaround that would help us to
resolve this problem. Your feedback is very much appreciated.
Thank you.- Hide quoted text -
- Show quoted text -
Hi Xeqis..
Whether the solution given by Peri fixed the issue, kindly let the
group to know abt the solution..if you did something else to recover
from this scenario...
w
Thanks,
vj_dba
RE.
btw, this situation is always available on HA cluster environments.
try the "buffpage" database configuration parameter.
buffpage is ignored unless you've explicitly set a bufferpool's size
to -1, so it's quite useless in this situation.
The fact that the original poster gets a SQL1224N is strange -- if the
bufferpool can't be allocated in memory, DB2 should dump a "not enough
memory" message and start up with the "hidden" bufferpools (one
bufferpool for each page size with a small number of pages in each
bp). This allows you to connect to the database and alter the sizes
as neccessary.
First, I'd open a PMR with IBM. You should not get a SQL1224N in this
case - EVER.
Second, use Peri's suggestion of doing "db2set DB2_OVERRIDEBPF=<some
small number>" This will force DB2 to ignore the bufferpool sizes in
the catalog, and will use <some small numberof pages for each
bufferpool instead. This will let you alter the bufferpool sizes.
Regards,
Matt Emmerton- Hide quoted text -
- Show quoted text -

Hi all,
Thank you very much for all your feedbacks. As what Mr. Peri has
suggested, I set the environment variable "DB2_OVERRIDE_BPF", using:
"db2 set DB2_OVERRIDE_BPF=10000"
then I restart the instance and reconnect to the database, woww....it
was succesful. I then altered the 32k buffer pool and the problem
solved. Actually I was also wondering why DB2 did not automatically
use the default IBMDEFAULTBP when it fails to allocate memory for the
new buffer pool. The database version that we are using is 7.2 on
Linux. Anyway, the problem is solved and thanks again for all your
valuable feedback.- Hide quoted text -

- Show quoted text -
DB2 7.2 has been out of service for more than 2 years (no longer
supported). The most current DB2 release is V9.1. In V8.1 or above,
DB2 will start with some "hidden" small bufferpools (one for each page
size) if there is not enough memory to start the database with the
bufferpool sizes defined by the DBA.

DB2 cannot just use IBMDEFAULTBP because it is a 4K bufferpool and you
obviously need a bufferpool with a 32K page size. Also, someone could
have set the number of pages for the IBMDEFAULTBP bufferpool so large
that it could not load into memory either.

Feb 16 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.