473,324 Members | 2,214 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

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
6 6582
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: eric | last post by:
Hi there, BP hit ratio = 1 - (BP physical reads / BP logical reads). If all the BP physical reads are asynchronous, it should mean that the pages are brought up to the bufferpool before the...
36
by: xixi | last post by:
hi, we are using db2 udb v8.1 on windows, i try to use the configuration advisor to get recommendation on the parameter setting, at first, i give the target memory for DB2 is 80% of the physical...
6
by: Mark | last post by:
I understand the concept of catalog cache (memory allocated from the dbheap to allow catalog lookups without the need to access disk each time). But the DB2 catalog tablespace (SYSCATSPACE) is...
3
by: Mark | last post by:
In a DB2 V8.1 performance tuning document from a 3rd party vendor, I found this statement. Can anyone verify this? "DB2 requires 100 bytes of memory for every buffer pool and extended storage...
0
by: jorge_martelanz | last post by:
I would like to receive experiences/ideas/recomendations/etc. from the community about commercial tools available to tune bufferpools in DB2 for z/OS I am aware of: - BPA4DB2 by ESAI - Buffer...
0
by: hussainiyad | last post by:
hi to all this is hussain i need ur help. in a datagrid there r two textboxes and a button and a label, if i type any value in that textboxes and click the button if the values r equal then the msg...
3
by: Suresh P | last post by:
Hi All, I tried to access the mysql database in ODBC using ip address and username/password. It returns, "cannot connect to MySQL server on IP ADDRESS(10060)". This could be related to Firewall...
3
by: Sebouh | last post by:
Hi guys. I'm completely new to databases, so i wanna ask you if this is achievable. I've been learning how to connect to an MS SQL database file with java. I've finally learned the basics,...
4
by: Patrick Finnegan | last post by:
Is there a DB2 setting that will force a table to be cached in the buffer pool? We have four tables that we want to cache completely in the buffer pool to ensure that all the data is read from...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.