How large of a bufferpool can you create?

Hemant Shah

Folks,

I am using DB2 UDB 8.2 on AIX 5.1. How large of a bufferpool can you create?

I tried to create a 4GB bufferpool db2 complained that is cannot
allocate enogth memory. I have 16GB on this system.
# db2 create bufferpool cfgbuffpool immediate size 1048576 pagesize 4096
SQL20189W The buffer pool operation (CREATE/ALTER) will not take effect until
the next database startup due to insufficient memory. SQLSTATE=01657

when I re-start database I get following error:

# db2start
06/28/2005 16:50:18 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[[dbap3@lidp4]]/g6/dbap3>
# db2 connect to mydb

Database Connection Information

Database server = DB2/6000 8.2.1
SQL authorization ID = DBAP3
Local database alias = MYDB

SQL1478W The defined buffer pools could not be started. Instead, one small
buffer pool for each page size supported by DB2 has been started.
SQLSTATE=01626

I tried to to create 2GB bufferpool and that failed too.

# db2 create bufferpool cfgbuffpool immediate size 1048576 pagesize 4096

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

Nov 12 '05 #1

Serge Rielau

Hemant Shah wrote:

Folks,

I am using DB2 UDB 8.2 on AIX 5.1. How large of a bufferpool can you create?

I tried to create a 4GB bufferpool db2 complained that is cannot
allocate enogth memory. I have 16GB on this system.
# db2 create bufferpool cfgbuffpool immediate size 1048576 pagesize 4096
SQL20189W The buffer pool operation (CREATE/ALTER) will not take effect until
the next database startup due to insufficient memory. SQLSTATE=01657

when I re-start database I get following error:

# db2start
06/28/2005 16:50:18 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[[dbap3@lidp4]]/g6/dbap3>
# db2 connect to mydb

Database Connection Information

Database server = DB2/6000 8.2.1
SQL authorization ID = DBAP3
Local database alias = MYDB

SQL1478W The defined buffer pools could not be started. Instead, one small
buffer pool for each page size supported by DB2 has been started.
SQLSTATE=01626

I tried to to create 2GB bufferpool and that failed too.

# db2 create bufferpool cfgbuffpool immediate size 1048576 pagesize 4096

Is this a 32bit or 64bit version of DB2?

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #2

Mark A

"Hemant Shah" <sh**@typhoon.xnet.com> wrote in message
news:d9**********@new7.xnet.com...

Folks,

I am using DB2 UDB 8.2 on AIX 5.1. How large of a bufferpool can you
create?

I tried to create a 4GB bufferpool db2 complained that is cannot
allocate enogth memory. I have 16GB on this system.
# db2 create bufferpool cfgbuffpool immediate size 1048576 pagesize 4096
SQL20189W The buffer pool operation (CREATE/ALTER) will not take effect
until
the next database startup due to insufficient memory. SQLSTATE=01657

when I re-start database I get following error:

# db2start
06/28/2005 16:50:18 0 0 SQL1063N DB2START processing was
successful.
SQL1063N DB2START processing was successful.
[[dbap3@lidp4]]/g6/dbap3>
# db2 connect to mydb

Database Connection Information

Database server = DB2/6000 8.2.1
SQL authorization ID = DBAP3
Local database alias = MYDB

SQL1478W The defined buffer pools could not be started. Instead, one
small
buffer pool for each page size supported by DB2 has been started.
SQLSTATE=01626

I tried to to create 2GB bufferpool and that failed too.

# db2 create bufferpool cfgbuffpool immediate size 1048576 pagesize 4096
Hemant Shah /"\ ASCII ribbon campaign

If you created a 32 bit instance, then the total DB2 memory allocation for
the instance can be no more than about 1.75GB under AIX (for all DB2 memory
requirements including the bufferpools). So the amount available for
bufferpools is probably closer to 1.25 GB, depending on the parms you
configured in the db and dbm configuration parms. This amount varies by OS
(more for Solaris and Windows, less for HP/UX and Linux).

If you have a 64 bit instance, the maximum amount of DB2 memory allocation
for the instance is far beyond the amount of memory in any computer that I
know about. However, make sure you have enough real memory on the server for
the total amount you specify in the bufferpools, also allowing for other DB2
memory requirements.

In order to create a 64-bit DB2 instance (or convert a 32 bit instance to 64
bit) you must have a 64 bit OS running on 64 bit hardware. Check with your
AIX system administrator about 64 bit AIX.

Nov 12 '05 #3

Matt Emmerton

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3i************@individual.net...

Hemant Shah wrote:

Folks,

I am using DB2 UDB 8.2 on AIX 5.1. How large of a bufferpool can you create?
I tried to create a 4GB bufferpool db2 complained that is cannot
allocate enogth memory. I have 16GB on this system.

# db2 create bufferpool cfgbuffpool immediate size 1048576 pagesize 4096
SQL20189W The buffer pool operation (CREATE/ALTER) will not take effect until the next database startup due to insufficient memory. SQLSTATE=01657
This warning occurs only when the the difference between the old/new
bufferpool sizes is too large, and there is not enough continguous memory in
the system to expand some control structures. A restart is required in this
case.
when I re-start database I get following error:

# db2start
06/28/2005 16:50:18 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful.
[[dbap3@lidp4]]/g6/dbap3>
# db2 connect to mydb

Database Connection Information

Database server = DB2/6000 8.2.1
SQL authorization ID = DBAP3
Local database alias = MYDB

SQL1478W The defined buffer pools could not be started. Instead, one small buffer pool for each page size supported by DB2 has been started.
SQLSTATE=01626

I tried to to create 2GB bufferpool and that failed too.

# db2 create bufferpool cfgbuffpool immediate size 1048576 pagesize 4096

Is this a 32bit or 64bit version of DB2?

The 32-bit version of DB2 can only access a maximum of 2GB of memory. This
limits your bufferpools to around 1.7GB due to the overhead of other memory
sets and limitations in the shared memory infrastructure on 32-bit AIX.

The 64-bit version of DB2 can access large amounts of memory without
problems -- IBM has published benchmarks in the past year that used close to
1TB of memory.

--
Matt Emmerton

Nov 12 '05 #4

Buck Nuggets

Mark A wrote:

In order to create a 64-bit DB2 instance (or convert a 32 bit instance to 64
bit) you must have a 64 bit OS running on 64 bit hardware. Check with your
AIX system administrator about 64 bit AIX.

Minor nit: you can create a 64-bit instance on a 32-bit aix kernel as
long as the hardware supports 64-bit applications. Ideally, you'd have
the 64-bit kernal installed, but the performance impact is supposed to
be small.
buck

Nov 12 '05 #5

Hemant Shah

While stranded on information super highway Buck Nuggets wrote:

Mark A wrote:

In order to create a 64-bit DB2 instance (or convert a 32 bit instance to 64
bit) you must have a 64 bit OS running on 64 bit hardware. Check with your
AIX system administrator about 64 bit AIX.
Minor nit: you can create a 64-bit instance on a 32-bit aix kernel as
long as the hardware supports 64-bit applications. Ideally, you'd have
the 64-bit kernal installed, but the performance impact is supposed to
be small.

It is a 64-bit system it a P-630. How do I find out if the kernel,
and db2 install is 64-bit.


buck

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

Nov 12 '05 #6

Hemant Shah

While stranded on information super highway Serge Rielau wrote:

Hemant Shah wrote:

Folks,

I am using DB2 UDB 8.2 on AIX 5.1. How large of a bufferpool can you create?

I tried to create a 4GB bufferpool db2 complained that is cannot
allocate enogth memory. I have 16GB on this system.
# db2 create bufferpool cfgbuffpool immediate size 1048576 pagesize 4096
SQL20189W The buffer pool operation (CREATE/ALTER) will not take effect until
the next database startup due to insufficient memory. SQLSTATE=01657

when I re-start database I get following error:

# db2start
06/28/2005 16:50:18 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[[dbap3@lidp4]]/g6/dbap3>
# db2 connect to mydb

Database Connection Information

Database server = DB2/6000 8.2.1
SQL authorization ID = DBAP3
Local database alias = MYDB

SQL1478W The defined buffer pools could not be started. Instead, one small
buffer pool for each page size supported by DB2 has been started.
SQLSTATE=01626

I tried to to create 2GB bufferpool and that failed too.

# db2 create bufferpool cfgbuffpool immediate size 1048576 pagesize 4096
Is this a 32bit or 64bit version of DB2?

The AIX is 64-bit

# lslpp -h bos.64bit
Fileset Level Action Status Date Time
----------------------------------------------------------------------------
Path: /usr/lib/objrepos
bos.64bit
5.1.0.35 COMMIT COMPLETE 12/12/02 04:43:14
5.1.0.37 COMMIT COMPLETE 12/12/02 04:47:22
5.1.0.52 COMMIT COMPLETE 02/05/04 17:48:09

Path: /etc/objrepos
bos.64bit
5.1.0.35 COMMIT COMPLETE 12/12/02 04:43:15

How do I find out if db2 is 64-bit? I cannot figure out based on lslpp
output.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

Nov 12 '05 #7

DB2 Guy

im not familiar with aix...check this command to verify if the DB2 is
32 or 64-bit

$db2level

you see a 64-bit in the message...

for the memory...configure the shared memory (SHMMAX) for the AIX
kernel -,look for the the documentation ^_^...check if its greater your
requested memory...

Cheers,
Cheers

Nov 12 '05 #8

Mark A

> im not familiar with aix...check this command to verify if the DB2 is

32 or 64-bit

$db2level

you see a 64-bit in the message...

for the memory...configure the shared memory (SHMMAX) for the AIX
kernel -,look for the the documentation ^_^...check if its greater your
requested memory...

Cheers,

Just to clarify the above, logon as the instance owner, and type db2level.
That will tell you how many bits that instance has. If you have other
instances, repeat for each instance owner.

You don't need to re-install DB2 to create a 64 instance, or to upgrade an
existing 32 bit instance to 64 bit.

Nov 12 '05 #9

Jurgen Haan

Matt Emmerton wrote:

The 32-bit version of DB2 can only access a maximum of 2GB of memory. This
limits your bufferpools to around 1.7GB due to the overhead of other memory
sets and limitations in the shared memory infrastructure on 32-bit AIX.
Really? I thought the max for 32bit was 4GB and due to those same
limitations in AIX you would be able to address about 3.2GB on this
system. Or am I in error here? I'm not really an AIX man, I use X86, but
the difference between those systems isn't 2GB of adressable memory,
is it?
The 64-bit version of DB2 can access large amounts of memory without
problems -- IBM has published benchmarks in the past year that used close to
1TB of memory.
What was it, 24GB per Bufferpool on a 64bit system?
--
Matt Emmerton

-R-

Nov 12 '05 #10

Mark A

"Jurgen Haan" <ju****@fake.dom> wrote in message
news:42***********************@news.xs4all.nl...

Really? I thought the max for 32bit was 4GB and due to those same
limitations in AIX you would be able to address about 3.2GB on this
system. Or am I in error here? I'm not really an AIX man, I use X86, but
the difference between those systems isn't 2GB of adressable memory, is
it?
It is a DB2 limitation, combined with an OS limitation, that limites the
amount of usable DB2 memory per instance to about 1.75 GB. One could always
create multiple instances if you had multiple databases, to get around this
limitation to some degree.
What was it, 24GB per Bufferpool on a 64bit system?

-R-

If DB2 was using close to 1TB of memory total on a 64 bit machine, the
amount of bufferpool memory used would need to be close to 1TB, since
bufferpools will using the vast majority of the memory in DB2 with such a
configuration.

Nov 12 '05 #11

Jurgen Haan

Mark A wrote:

If DB2 was using close to 1TB of memory total on a 64 bit machine, the
amount of bufferpool memory used would need to be close to 1TB, since
bufferpools will using the vast majority of the memory in DB2 with such a
configuration.

Aha, so a single bufferpool can grow up to 1TB on a 64bit system?
During the performance tuning course I took at IBM NL, I raised the
question about bufferpool sizes on 64bit systems, and I was told the
maximum size per bufferpool was 24GB. Was I misinformed?

-R-

Nov 12 '05 #12

Knut Stolze

Jurgen Haan wrote:

Mark A wrote:

If DB2 was using close to 1TB of memory total on a 64 bit machine, the
amount of bufferpool memory used would need to be close to 1TB, since
bufferpools will using the vast majority of the memory in DB2 with such a
configuration.

Aha, so a single bufferpool can grow up to 1TB on a 64bit system?
During the performance tuning course I took at IBM NL, I raised the
question about bufferpool sizes on 64bit systems, and I was told the
maximum size per bufferpool was 24GB. Was I misinformed?

Have a look at the SQL limits in the manual: a buffer pool can have up to
2.14 billion pages in a 64bit instance, which is about 8TB for 4K page
size.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

Nov 12 '05 #13

Ian

Hemant Shah wrote:

The AIX is 64-bit

# lslpp -h bos.64bit

This just tells you if the 64-bit kernel is installed on your server,
but not if it is actually running.

You need to run the command 'bootinfo -K' to determine if the 64-bit
kernel is running (and this command requires root privileges).

Unless you AIX admin specifically enabled the 64-bit kernel, chances are
you are running the 32-bit kernel (i.e. that's the default out of the
box).

Nov 12 '05 #14

Hemant Shah

While stranded on information super highway Mark A wrote:

im not familiar with aix...check this command to verify if the DB2 is
32 or 64-bit

$db2level

you see a 64-bit in the message...

for the memory...configure the shared memory (SHMMAX) for the AIX
kernel -,look for the the documentation ^_^...check if its greater your
requested memory...

Cheers,

Just to clarify the above, logon as the instance owner, and type db2level.
That will tell you how many bits that instance has. If you have other
instances, repeat for each instance owner.

You don't need to re-install DB2 to create a 64 instance, or to upgrade an
existing 32 bit instance to 64 bit.

It is 32-bit install of DB2.
How do I upgrade it to 64-bit?

I have a CD with both versions.
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

Nov 12 '05 #15

Hemant Shah

While stranded on information super highway Ian wrote:

Hemant Shah wrote:

The AIX is 64-bit

# lslpp -h bos.64bit
This just tells you if the 64-bit kernel is installed on your server,
but not if it is actually running.

You need to run the command 'bootinfo -K' to determine if the 64-bit
kernel is running (and this command requires root privileges).

Unless you AIX admin specifically enabled the 64-bit kernel, chances are
you are running the 32-bit kernel (i.e. that's the default out of the
box).

Thanks for the info. It is running in 32-bit mode. I will have to
change it to make it run in 64-bit mode.

If my applications have problems with 64-bit, can I switch it back to 32-bit?

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

Nov 12 '05 #16

DB2 Guy

i haven't tried updating instances from 32-64...but try to read this
site...

http://publib.boulder.ibm.com/infoce...e/r0002060.htm

check for this commands that are helpful for migration - db2icrt,
db2level, db2iupdt

when creating instance using db2icrt u need to specify the -w option
(width in bits e.g. 31, 32, 64)...

as far as i can remember to a create 64bit instance a 64 bit code of
db2 must be installed (please verify this to the mentioned site)...

Since I haven't done this procedure (converting 32-64) i am not sure if
this procedure works...you may try this procedure if agree with it and
make sure to backup your databases and configuration...i strongly
suggest you test it on different aix machine(if you have)...

1. Install 64bit DB2 (if not yet installed)
2. Issue db2ipudt on the instance specifying the -w 64 option to make
it 64 bit

Nov 12 '05 #17

DB2 Guy

stop db2 instances before doing the procedure

1. Install 64bit DB2 (if not yet installed)
2. Issue db2ipudt on the instance specifying the -w 64 option to make
it 64 bit

then start db2 instances

Nov 12 '05 #18

Mark A

"DB2 Guy" <ma******@hotmail.com> wrote in message

as far as i can remember to a create 64bit instance a 64 bit code of
db2 must be installed (please verify this to the mentioned site)...

Some distributions of DB2 have one code base for both 32-bit and 64-bit
installations. This includes DB2 for AIX 5L.

Other versions of DB2 which have one distribution for both 32-bit and 64-bit
include:

- DB2 for HP/UX for 11i PA RISC, HP 9000 Servers
- DB2 for Linux on AMD64 and Intel EM64T
- DB2 for Linux iSeries and pSeries
- DB2 for Solaris

Nov 12 '05 #19

Knut Stolze

DB2 Guy wrote:

stop db2 instances before doing the procedure

1. Install 64bit DB2 (if not yet installed)
2. Issue db2ipudt on the instance specifying the -w 64 option to make
it 64 bit

I just did this with a few of our instances on an AIX 5.2 box and it was
really easy.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

Nov 12 '05 #20

Hemant Shah

While stranded on information super highway Knut Stolze wrote:

DB2 Guy wrote:

stop db2 instances before doing the procedure

1. Install 64bit DB2 (if not yet installed)
2. Issue db2ipudt on the instance specifying the -w 64 option to make
it 64 bit

I just did this with a few of our instances on an AIX 5.2 box and it was
really easy.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

Thanks (to all) for the info, I will give it a try.
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

Nov 12 '05 #21