473,322 Members | 1,501 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,322 software developers and data experts.

V9.5 and SQL1084C


First some background, we are currently using V8.2 but is planning to
move to 9.5 for the next next release of our application (within a
year or so). In our development environment we have a lot of
databases. This is due to the fact that each developer can create a
copy of the current master database, and develop against that. This
have worked very well for V8.2, but in our 9.5 environment we run into
SQL1084C as soon as we get a handful databases active. Of course we
will never get any performance out of a setup like that, but that is
of no importance in this environment.

Now, how do I pinpoint what limit I'm hitting? My first thought was
that kernel.shmmax was to low, so we have doubled it to 536870912

[db2inst1@wbv7d ~]$ cat /etc/sysctl.conf
[...]
kernel.shmmax = 536870912
kernel.msgmni = 1024
kernel.sem = 250 32000 32 1024

but that did not help. Next I tried running db2pd with various flags
(first time I've used it, and I'm having some difficulties
understanding the output), and AFAIK each database is not allocating
so much memory that it should be a problem.

I have tried to enable self_tuning_mem on each database, and set
bufferpools to size automatic, but that doesn't do any good:

for db in `db2 list db directory | grep "Database name" | cut -f2 -
d=`; do db2 "update db cfg for $db using SELF_TUNING_MEM ON"; done
for db in `db2 list db directory | grep "Database name" | cut -f2 -
d=`; do db2 connect to $db; db2 alter bufferpool IBMDEFAULTBP size
automatic; db2 alter bufferpool BPTMP2 size automatic; done

Any hints or help on how to determine what limit I'm hitting, and how
to resolve that is greatly appreciated.
/Lennart

Jun 27 '08 #1
6 5521
Lennart wrote:
First some background, we are currently using V8.2 but is planning to
move to 9.5 for the next next release of our application (within a
year or so). In our development environment we have a lot of
databases. This is due to the fact that each developer can create a
copy of the current master database, and develop against that. This
have worked very well for V8.2, but in our 9.5 environment we run into
SQL1084C as soon as we get a handful databases active. Of course we
will never get any performance out of a setup like that, but that is
of no importance in this environment.

Now, how do I pinpoint what limit I'm hitting? My first thought was
that kernel.shmmax was to low, so we have doubled it to 536870912

[db2inst1@wbv7d ~]$ cat /etc/sysctl.conf
[...]
kernel.shmmax = 536870912
kernel.msgmni = 1024
kernel.sem = 250 32000 32 1024

but that did not help. Next I tried running db2pd with various flags
(first time I've used it, and I'm having some difficulties
understanding the output), and AFAIK each database is not allocating
so much memory that it should be a problem.

I have tried to enable self_tuning_mem on each database, and set
bufferpools to size automatic, but that doesn't do any good:

for db in `db2 list db directory | grep "Database name" | cut -f2 -
d=`; do db2 "update db cfg for $db using SELF_TUNING_MEM ON"; done
for db in `db2 list db directory | grep "Database name" | cut -f2 -
d=`; do db2 connect to $db; db2 alter bufferpool IBMDEFAULTBP size
automatic; db2 alter bufferpool BPTMP2 size automatic; done

Any hints or help on how to determine what limit I'm hitting, and how
to resolve that is greatly appreciated.
/Lennart
Hi Lennart,

What's your OS environment (Linux/AIX/Solaris/...) and how much memory does
it have?
I don't have any hands-on experience yet with 9.5, but from what I've heard
at IDUG 2007 in Athens, it is likely to need more memory due to the new
threaded model.
You said you've doubled shmmax, but it nevertheless is only a little over
half a Gig, which still seems not very much, especially in a multi-db
environment like yours. Depending on the amount of memory, I would advice
raising shmmax again.

HTH

--
Jeroen
Jun 27 '08 #2
The Boss wrote:
Lennart wrote:
>First some background, we are currently using V8.2 but is planning to
move to 9.5 for the next next release of our application (within a
year or so). In our development environment we have a lot of
databases. This is due to the fact that each developer can create a
copy of the current master database, and develop against that. This
have worked very well for V8.2, but in our 9.5 environment we run
into SQL1084C as soon as we get a handful databases active. Of
course we will never get any performance out of a setup like that,
but that is of no importance in this environment.

Now, how do I pinpoint what limit I'm hitting? My first thought was
that kernel.shmmax was to low, so we have doubled it to 536870912

[db2inst1@wbv7d ~]$ cat /etc/sysctl.conf
[...]
kernel.shmmax = 536870912
kernel.msgmni = 1024
kernel.sem = 250 32000 32 1024

but that did not help. Next I tried running db2pd with various flags
(first time I've used it, and I'm having some difficulties
understanding the output), and AFAIK each database is not allocating
so much memory that it should be a problem.

I have tried to enable self_tuning_mem on each database, and set
bufferpools to size automatic, but that doesn't do any good:

for db in `db2 list db directory | grep "Database name" | cut -f2 -
d=`; do db2 "update db cfg for $db using SELF_TUNING_MEM ON"; done
for db in `db2 list db directory | grep "Database name" | cut -f2 -
d=`; do db2 connect to $db; db2 alter bufferpool IBMDEFAULTBP size
automatic; db2 alter bufferpool BPTMP2 size automatic; done

Any hints or help on how to determine what limit I'm hitting, and how
to resolve that is greatly appreciated.
/Lennart

Hi Lennart,

What's your OS environment (Linux/AIX/Solaris/...) and how much
memory does it have?
I don't have any hands-on experience yet with 9.5, but from what I've
heard at IDUG 2007 in Athens, it is likely to need more memory due to
the new threaded model.
You said you've doubled shmmax, but it nevertheless is only a little
over half a Gig, which still seems not very much, especially in a
multi-db environment like yours. Depending on the amount of memory, I
would advice raising shmmax again.

HTH
An additional thought: check your swap space.
When allocating a shared memory segment, the OS (Linux at least, not sure
about other OS's) needs enough swap space to be able to swap out the
segment.
As you have doubled shmmax, you therefor also may need to increase swap
space.

Cheers.

--
Jeroen
Jun 27 '08 #3
The Boss wrote:
The Boss wrote:
>Lennart wrote:
>>First some background, we are currently using V8.2 but is planning
to move to 9.5 for the next next release of our application (within
a year or so). In our development environment we have a lot of
databases. This is due to the fact that each developer can create a
copy of the current master database, and develop against that. This
have worked very well for V8.2, but in our 9.5 environment we run
into SQL1084C as soon as we get a handful databases active. Of
course we will never get any performance out of a setup like that,
but that is of no importance in this environment.

Now, how do I pinpoint what limit I'm hitting? My first thought was
that kernel.shmmax was to low, so we have doubled it to 536870912

[db2inst1@wbv7d ~]$ cat /etc/sysctl.conf
[...]
kernel.shmmax = 536870912
kernel.msgmni = 1024
kernel.sem = 250 32000 32 1024

but that did not help. Next I tried running db2pd with various flags
(first time I've used it, and I'm having some difficulties
understanding the output), and AFAIK each database is not allocating
so much memory that it should be a problem.

I have tried to enable self_tuning_mem on each database, and set
bufferpools to size automatic, but that doesn't do any good:

for db in `db2 list db directory | grep "Database name" | cut -f2 -
d=`; do db2 "update db cfg for $db using SELF_TUNING_MEM ON"; done
for db in `db2 list db directory | grep "Database name" | cut -f2 -
d=`; do db2 connect to $db; db2 alter bufferpool IBMDEFAULTBP size
automatic; db2 alter bufferpool BPTMP2 size automatic; done

Any hints or help on how to determine what limit I'm hitting, and
how to resolve that is greatly appreciated.
/Lennart

Hi Lennart,

What's your OS environment (Linux/AIX/Solaris/...) and how much
memory does it have?
I don't have any hands-on experience yet with 9.5, but from what I've
heard at IDUG 2007 in Athens, it is likely to need more memory due to
the new threaded model.
You said you've doubled shmmax, but it nevertheless is only a little
over half a Gig, which still seems not very much, especially in a
multi-db environment like yours. Depending on the amount of memory, I
would advice raising shmmax again.

HTH

An additional thought: check your swap space.
When allocating a shared memory segment, the OS (Linux at least, not
sure about other OS's) needs enough swap space to be able to swap out
the segment.
As you have doubled shmmax, you therefor also may need to increase
swap space.

Cheers.
I've done some googling and found a couple of links that might be of
interest:

http://www-1.ibm.com/support/docview...id=swg1IZ08069
http://www.ibm.com/developerworks/fo...96706&tstart=0

Also found a blogpost but couldn't reach the blog-site directly, so here is
the interesting part I was able to grab via Google's cache:

<quote>
Tuesday, January 15, 2008
DB2 9.5 Migration SQL1084C, Registry Variables

Was doing another 9.5 migration on Linux last night, and ran into two
issues.

1) db2 migrate db pepto
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019

It turns out the new threaded model is much more picky about the maximum
amount of shared memory it has available. To fix this issue modify
/etc/sysctl.conf and modify kernel.shmmax to something large like:

kernel.shmmax=1610612736 (1.5GB, should be the amount of memory in your
system)

Run sysctl -p (as root)

You should be able to migrate to 9.5 now.

2) Registry variables disappeared. Nothing seems to be in the doc about it
(unless I am blind). But make sure you check them after your migration.

Regards,
Rob Williams
</quote>
[Blog-link:
http://www.mhubel.com/blog/rob/2008/...registry.html]

--
Jeroen
Jun 27 '08 #4
On May 22, 6:56 pm, Liam Finnie <lfin...@ca.ibm.comwrote:
[...]
Hello,

You should take a look at the INSTANCE_MEMORY configuration parameter
- that limits the total amount of memory that can be allocated by the
DB2 instance. Once you get close to that limit, you likely won't be
able to activate any more databases. By default, the limit is
AUTOMATIC, and defaults to somewhere between 75% and 95% of RAM, and
you are not allowed to set it larger than the RAM on your box. In
previous releases, DB2's memory configuration was not limited to RAM
on the box, which means similar memory configurations may have worked,
but would have likely resulted in heavy pageing (and so, very poor
performance).

BTW - DB2 9.5 should not need more memory due to the threaded model,
in fact, overall memory requirements should have decreased slightly,
since threads are much more lightweight than entire processes.

If you want to monitor overall memory usage by DB2, you can use the
admin_get_dbp_mem_usage table function:
SELECT * FROM TABLE (SYSPROC.ADMIN_GET_DBP_MEM_USAGE()) AS T

db2pd also has a new option that outputs memory consumption with
better granularity:
db2pd -dbptnmem
Search for dbptnmem in the docs for a description of the info returned
by this command.

As for why self-tuning may not have helped (at least a bit), the
reason could be that you are not on a high enough Linux kernel level.
You need to be on at least RHEL5 or SLES10 SP1 (or equivalent) to
enable database memory tuning by STMM if both INSTANCE_MEMORY and
DATABASE_MEMORY are set to AUTOMATIC. If you set INSTANCE_MEMORY to a
specific value, that will allow STMM to start tuning database memory
consumption.

Cheers,
Liam.
Thanx for your valuable input Liam. Exactly the kind of info I hoped
someone would provide. Meanwhile I did some research on my own. First
I tried to activate as many databases as possible before hitting
SQL1084C. Then ran:

[db2inst1@wbv7d ~]$ db2mtrk -i -d -v | grep "Total" | awk '{ print
$2 }'
16121856
32440320
35192832
32899072
31981568
31850496
31981568
31850496
21495808

But that only sums up to ~250Mb, so I figure there are more mem being
used. Next I checked the dbm cfg for INSTANCE_MEMORY

[db2inst1@wbv7d ~]$ db2 get dbm cfg show detail | grep INSTANCE_MEMORY
Size of instance shared memory (4KB) (INSTANCE_MEMORY) =
AUTOMATIC(193963) AUTOMATIC(193963)

which is indeed close to the amount of memory on the machine. The I
looked at one of the activated databases for DATABASE_MEMORY:

[db2inst1@wbv7d ~]$ db2 get db cfg for nyav7 show detail | grep
DATABASE_MEMORY
Size of database shared memory (4KB) (DATABASE_MEMORY) =
COMPUTED(23424) COMPUTED(23424)

So we have roughly 100Mb per database according to this.
The function you mention turns out very useful. By activating db by db
and looking at the result I can actually determine how much memory
that is consumed step by step:

deactivate all db
[db2inst1@wbv7d ~]$ for db in `db2 list db directory | grep -B5
"Directory entry type = Indirect" | grep "Database
alias" | cut -f2 -d=`; do db2 activate db $db; db2 connect to $db; db2
"SELECT * FROM TABLE (SYSPROC.ADMIN_GET_DBP_MEM_USAGE()) AS T"; db2
connect reset; done
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 356188160
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 493092864
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 629997568
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 767033344
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 783024128
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 785973248
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 794230784
794427392
[...]
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
[...]
>>493092864-356188160
136904704
>>629997568-493092864
136904704
>>767033344-629997568
137035776
>>783024128-767033344
15990784
>>794230784-785973248
8257536

Now the funny part, when I where to compare this with how one of my
8.2 installations behaved I realized that all 8.2 machines actually
have at least 2Gb memory. Anyhow, I can activate a lot more databases
on one of those machine so it looks as if a database needs more
initial memory on 9.5 than on 8.2

[db2inst1@wb-05 ~]$ for db in `db2 list db directory | grep -B5
"Directory entry type
= Indirect" | grep "Database alias" | cut -f2 -d=`; do db2
activate db $db; done
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
SQL1494W Activate database is successful, however, there is already
a
connection to the database.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
[...]

I will probably stuff 4Gb memory in the 9.5 dev machines and hopefully
that will solve this issue for now. I feel much better once I realized
that the machine causing this trouble had half the mem compared to the
8.2

Thanks both to Jeroen and Liam for taking your time with this

/Lennart
Jun 27 '08 #5
On May 22, 4:32 pm, Lennart <Erik.Lennart.Jons...@gmail.comwrote:
On May 22, 6:56 pm, Liam Finnie <lfin...@ca.ibm.comwrote:
[...]
Hello,
You should take a look at the INSTANCE_MEMORY configuration parameter
- that limits the total amount of memory that can be allocated by the
DB2 instance. Once you get close to that limit, you likely won't be
able to activate any more databases. By default, the limit is
AUTOMATIC, and defaults to somewhere between 75% and 95% of RAM, and
you are not allowed to set it larger than the RAM on your box. In
previous releases, DB2's memory configuration was not limited to RAM
on the box, which means similar memory configurations may have worked,
but would have likely resulted in heavy pageing (and so, very poor
performance).
BTW - DB2 9.5 should not need more memory due to the threaded model,
in fact, overall memory requirements should have decreased slightly,
since threads are much more lightweight than entire processes.
If you want to monitor overall memory usage by DB2, you can use the
admin_get_dbp_mem_usage table function:
SELECT * FROM TABLE (SYSPROC.ADMIN_GET_DBP_MEM_USAGE()) AS T
db2pd also has a new option that outputs memory consumption with
better granularity:
db2pd -dbptnmem
Search for dbptnmem in the docs for a description of the info returned
by this command.
As for why self-tuning may not have helped (at least a bit), the
reason could be that you are not on a high enough Linux kernel level.
You need to be on at least RHEL5 or SLES10 SP1 (or equivalent) to
enable database memory tuning by STMM if both INSTANCE_MEMORY and
DATABASE_MEMORY are set to AUTOMATIC. If you set INSTANCE_MEMORY to a
specific value, that will allow STMM to start tuning database memory
consumption.
Cheers,
Liam.

Thanx for your valuable input Liam. Exactly the kind of info I hoped
someone would provide. Meanwhile I did some research on my own. First
I tried to activate as many databases as possible before hitting
SQL1084C. Then ran:

[db2inst1@wbv7d ~]$ db2mtrk -i -d -v | grep "Total" | awk '{ print
$2 }'
16121856
32440320
35192832
32899072
31981568
31850496
31981568
31850496
21495808

But that only sums up to ~250Mb, so I figure there are more mem being
used. Next I checked the dbm cfg for INSTANCE_MEMORY

[db2inst1@wbv7d ~]$ db2 get dbm cfg show detail | grep INSTANCE_MEMORY
Size of instance shared memory (4KB) (INSTANCE_MEMORY) =
AUTOMATIC(193963) AUTOMATIC(193963)

which is indeed close to the amount of memory on the machine. The I
looked at one of the activated databases for DATABASE_MEMORY:

[db2inst1@wbv7d ~]$ db2 get db cfg for nyav7 show detail | grep
DATABASE_MEMORY
Size of database shared memory (4KB) (DATABASE_MEMORY) =
COMPUTED(23424) COMPUTED(23424)

So we have roughly 100Mb per database according to this.

The function you mention turns out very useful. By activating db by db
and looking at the result I can actually determine how much memory
that is consumed step by step:

deactivate all db
[db2inst1@wbv7d ~]$ for db in `db2 list db directory | grep -B5
"Directory entry type = Indirect" | grep "Database
alias" | cut -f2 -d=`; do db2 activate db $db; db2 connect to $db; db2
"SELECT * FROM TABLE (SYSPROC.ADMIN_GET_DBP_MEM_USAGE()) AS T"; db2
connect reset; done
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 356188160
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 493092864
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 629997568
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 767033344
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 783024128
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 785973248
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 794230784
794427392
[...]
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
[...]
>493092864-356188160
136904704
>629997568-493092864
136904704
>767033344-629997568
137035776
>783024128-767033344
15990784
>794230784-785973248

8257536

Now the funny part, when I where to compare this with how one of my
8.2 installations behaved I realized that all 8.2 machines actually
have at least 2Gb memory. Anyhow, I can activate a lot more databases
on one of those machine so it looks as if a database needs more
initial memory on 9.5 than on 8.2

[db2inst1@wb-05 ~]$ for db in `db2 list db directory | grep -B5
"Directory entry type
= Indirect" | grep "Database alias" | cut -f2 -d=`; do db2
activate db $db; done
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
SQL1494W Activate database is successful, however, there is already
a
connection to the database.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
[...]

I will probably stuff 4Gb memory in the 9.5 dev machines and hopefully
that will solve this issue for now. I feel much better once I realized
that the machine causing this trouble had half the mem compared to the
8.2

Thanks both to Jeroen and Liam for taking your time with this

/Lennart
Hi Lennart,

Glad that helped. You mentioned that databases in 9.5 need more
initial memory than in 8.2.... this may be because the configuration
advisor runs by default starting in v9, which means that during
database creation, the config advisor will look at the memory
configuration on your box, and update various configuration parameters
automatically (to get improved out-of-the-box performance). If you
want to bypass the configuration advisor in 9.5, you can add
'AUTOCONFIGURE APPLY NONE' - that will give you a very minimal initial
database configuration (similar to the default in 8.2).

Cheers,
Liam.
Jun 27 '08 #6
On May 23, 4:05 pm, Liam Finnie <lfin...@ca.ibm.comwrote:
On May 22, 4:32 pm, Lennart <Erik.Lennart.Jons...@gmail.comwrote:
On May 22, 6:56 pm, Liam Finnie <lfin...@ca.ibm.comwrote:
[...]
Hello,
You should take a look at the INSTANCE_MEMORY configuration parameter
- that limits the total amount of memory that can be allocated by the
DB2 instance. Once you get close to that limit, you likely won't be
able to activate any more databases. By default, the limit is
AUTOMATIC, and defaults to somewhere between 75% and 95% of RAM, and
you are not allowed to set it larger than the RAM on your box. In
previous releases, DB2's memory configuration was not limited to RAM
on the box, which means similar memory configurations may have worked,
but would have likely resulted in heavy pageing (and so, very poor
performance).
BTW - DB2 9.5 should not need more memory due to the threaded model,
in fact, overall memory requirements should have decreased slightly,
since threads are much more lightweight than entire processes.
If you want to monitor overall memory usage by DB2, you can use the
admin_get_dbp_mem_usage table function:
SELECT * FROM TABLE (SYSPROC.ADMIN_GET_DBP_MEM_USAGE()) AS T
db2pd also has a new option that outputs memory consumption with
better granularity:
db2pd -dbptnmem
Search for dbptnmem in the docs for a description of the info returned
by this command.
As for why self-tuning may not have helped (at least a bit), the
reason could be that you are not on a high enough Linux kernel level.
You need to be on at least RHEL5 or SLES10 SP1 (or equivalent) to
enable database memory tuning by STMM if both INSTANCE_MEMORY and
DATABASE_MEMORY are set to AUTOMATIC. If you set INSTANCE_MEMORY to a
specific value, that will allow STMM to start tuning database memory
consumption.
Cheers,
Liam.
Thanx for your valuable input Liam. Exactly the kind of info I hoped
someone would provide. Meanwhile I did some research on my own. First
I tried to activate as many databases as possible before hitting
SQL1084C. Then ran:
[db2inst1@wbv7d ~]$ db2mtrk -i -d -v | grep "Total" | awk '{ print
$2 }'
16121856
32440320
35192832
32899072
31981568
31850496
31981568
31850496
21495808
But that only sums up to ~250Mb, so I figure there are more mem being
used. Next I checked the dbm cfg for INSTANCE_MEMORY
[db2inst1@wbv7d ~]$ db2 get dbm cfg show detail | grep INSTANCE_MEMORY
Size of instance shared memory (4KB) (INSTANCE_MEMORY) =
AUTOMATIC(193963) AUTOMATIC(193963)
which is indeed close to the amount of memory on the machine. The I
looked at one of the activated databases for DATABASE_MEMORY:
[db2inst1@wbv7d ~]$ db2 get db cfg for nyav7 show detail | grep
DATABASE_MEMORY
Size of database shared memory (4KB) (DATABASE_MEMORY) =
COMPUTED(23424) COMPUTED(23424)
So we have roughly 100Mb per database according to this.
The function you mention turns out very useful. By activating db by db
and looking at the result I can actually determine how much memory
that is consumed step by step:
deactivate all db
[db2inst1@wbv7d ~]$ for db in `db2 list db directory | grep -B5
"Directory entry type = Indirect" | grep "Database
alias" | cut -f2 -d=`; do db2 activate db $db; db2 connect to $db; db2
"SELECT * FROM TABLE (SYSPROC.ADMIN_GET_DBP_MEM_USAGE()) AS T"; db2
connect reset; done
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 356188160
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 493092864
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 629997568
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 767033344
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 783024128
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 785973248
794427392
[...]
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM
PEAK_PARTITION_MEM
-------------- -------------------- ---------------------
--------------------
0 794472448 794230784
794427392
[...]
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
[...]
>>493092864-356188160
136904704
>>629997568-493092864
136904704
>>767033344-629997568
137035776
>>783024128-767033344
15990784
>>794230784-785973248
8257536
Now the funny part, when I where to compare this with how one of my
8.2 installations behaved I realized that all 8.2 machines actually
have at least 2Gb memory. Anyhow, I can activate a lot more databases
on one of those machine so it looks as if a database needs more
initial memory on 9.5 than on 8.2
[db2inst1@wb-05 ~]$ for db in `db2 list db directory | grep -B5
"Directory entry type
= Indirect" | grep "Database alias" | cut -f2 -d=`; do db2
activate db $db; done
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
SQL1494W Activate database is successful, however, there is already
a
connection to the database.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
DB20000I The ACTIVATE DATABASE command completed successfully.
SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019
[...]
I will probably stuff 4Gb memory in the 9.5 dev machines and hopefully
that will solve this issue for now. I feel much better once I realized
that the machine causing this trouble had half the mem compared to the
8.2
Thanks both to Jeroen and Liam for taking your time with this
/Lennart

Hi Lennart,

Glad that helped. You mentioned that databases in 9.5 need more
initial memory than in 8.2.... this may be because the configuration
advisor runs by default starting in v9, which means that during
database creation, the config advisor will look at the memory
configuration on your box, and update various configuration parameters
automatically (to get improved out-of-the-box performance). If you
want to bypass the configuration advisor in 9.5, you can add
'AUTOCONFIGURE APPLY NONE' - that will give you a very minimal initial
database configuration (similar to the default in 8.2).
Hi Liam, the databases on 9.5 are restored from a backup of the 8.2
database (and automatically migrated). I haven't checked, but I guess
that this option only applies when you create a db from scratch.
/Lennart

Cheers,
Liam.
Jun 27 '08 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Jim | last post by:
Hello, I have a broken server that we are going to be moving off to a new server with a new version of DB2 but here is what I have right now: RedHat 7.0 (2.2.24smp) DB2 v6.1.0.40 I am...
3
by: Jurgen Haan | last post by:
Hiya everyone. I'm toying with a little db server. It's an AMD64 X2, so dual cpu 64 bit. It's a box with a total of 2GB of memory. We have a database server with comparable specs. an AMD64...
4
by: Roger Eriksson | last post by:
I did set up this thing and it worked well during my test so I invited the users to test it before it would be put into production. I got no complaits and thought it all was working. So I...
0
by: Phil Sherman | last post by:
This occurred in a non-supported UDB environment. I am providing it as "gotcha" information. SQL1084C error was received when attempting the first connect to the (formerly working) SAMPLE...
2
by: richard.crosh | last post by:
Hello, Using DB2 8.2 FP 9 - 32bit on Solaris 5.8 and a tape management tool of Veritas, the archive hangs on occassion. The error is "shmat failed" in the db2diag.log. Then DB2 hangs and self...
3
by: Lennart | last post by:
A while back I raised a discussion ( http://tinyurl.com/4h3qg7 ) regarding SQL1084C Shared memory segments cannot be allocated. We still have this problem, so I thought I should bump the thread. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
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...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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: 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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.