Memory Sharing between Databases

[Selma_g]

Hi,

I am wondering if the bufferpools and agent memory canbe shared within
a EE UDB instance (running 7.2 AIX 5 now but going to V8.1). Our
business model requires us to add about 1-2 db's per month (one db per
customer), and this consume the memory.
Some of these dbs are not highly active but once a db is activated, at
least the bufferpools are allocated along with the init_pool of
agents. Is there a way for bufferpools and agents be shared between
databases, like other RDBMS do ?

Thank you and look forward to your answers.
Selma

[Blair Adamache]

You could do maximum CPU and memory sharing by putting all the data in
one databases, and using a different schema for each customer. And, put
their data in distinct table spaces. Bufferpools are allocated by
database, so cannot be shared by different databases. However, if you
don't like the schema approach above, in v8 bufferpools for inactive
databases can be sized quite small.

Selma_g wrote:

Hi,

I am wondering if the bufferpools and agent memory canbe shared within
a EE UDB instance (running 7.2 AIX 5 now but going to V8.1). Our
business model requires us to add about 1-2 db's per month (one db per
customer), and this consume the memory.
Some of these dbs are not highly active but once a db is activated, at
least the bufferpools are allocated along with the init_pool of
agents. Is there a way for bufferpools and agents be shared between
databases, like other RDBMS do ?

Thank you and look forward to your answers.
Selma

[Selma_g]

Thanks for your suggestion, Blair. However, our business contract
forbids mixing data between customers due to the sensitive nature of
the data. I am stuck creating 1-2 dbs a month. As I recall when I
worked in Sybase years ago, the memory space is shared globally. In
fact sybase claimed they can support something like 32K databases!
Does anyone have a solution for UDB? I can't size the less active dbs
small bufferpools because the customers may use it throughout the day.
Is this a weakness in UDB/DB2 in what otherwise an excellent db engine
?

Look forward to your answers.
Selma

Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<c1**********@hanover.torolab.ibm.com>...

You could do maximum CPU and memory sharing by putting all the data in
one databases, and using a different schema for each customer. And, put
their data in distinct table spaces. Bufferpools are allocated by
database, so cannot be shared by different databases. However, if you
don't like the schema approach above, in v8 bufferpools for inactive
databases can be sized quite small.

Selma_g wrote:

Hi,

I am wondering if the bufferpools and agent memory canbe shared within
a EE UDB instance (running 7.2 AIX 5 now but going to V8.1). Our
business model requires us to add about 1-2 db's per month (one db per
customer), and this consume the memory.
Some of these dbs are not highly active but once a db is activated, at
least the bufferpools are allocated along with the init_pool of
agents. Is there a way for bufferpools and agents be shared between
databases, like other RDBMS do ?

Thank you and look forward to your answers.
Selma

[Mark A]

"Selma_g" <se*********@yahoo.com> wrote in message
news:a3**************************@posting.google.c om...

Thanks for your suggestion, Blair. However, our business contract
forbids mixing data between customers due to the sensitive nature of
the data. I am stuck creating 1-2 dbs a month. As I recall when I
worked in Sybase years ago, the memory space is shared globally. In
fact sybase claimed they can support something like 32K databases!
Does anyone have a solution for UDB? I can't size the less active dbs
small bufferpools because the customers may use it throughout the day.
Is this a weakness in UDB/DB2 in what otherwise an excellent db engine
?

Look forward to your answers.
Selma

Sounds to me like a definition issue. If Sybase databases can share
bufferpools, then maybe their databases are not as separate as DB2 LUW
databases. DB2 for OS/390 database share bufferpools (apparently like
Sybase) and you can have up to 65217 databases, but DB2 for UNIX, Linux, and
Windows databases do not share bufferpools (as you know).

Maybe you are fixated on the term "database" (which has different meanings
for different DBMS products) and you should be able to provide the required
security with multiple users having their own schema with a single database.

[Serge Rielau]

Selma,

You may want to aks for a clarification of the contract.
After all if you share teh buffer pool, don't you share the data?
It's sitting there darn close together.
As Mark points out we are talking definitions here.
Commonly a database in Sybase and MS SQL Server is best mapped to a
schema in DB2.

Just create a schema per customer, don't allow any of the users of one
schema to access any objects in the other. If you want to be paranoid
you can even withdraw select privileges from the catalogs (which in
Sybase would have been shared as well, btw).

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Paul Reddin]

Hi,

One thing springs to mind to me, is handling backups and restores for
different customers if they are only in different schemas and not
different DBs.

i.e if you have to restore customer A db, you probably don't want to affect
everybody else.

So, maybe you need to have seperate tablespace backups etc.....

just a thought.

Paul.

[Selma_g]

pa**@abacus.co.uk (Paul Reddin) wrote in message news:<1f**************************@posting.google. com>...

Hi,

One thing springs to mind to me, is handling backups and restores for
different customers if they are only in different schemas and not
different DBs.

i.e if you have to restore customer A db, you probably don't want to affect
everybody else.

So, maybe you need to have seperate tablespace backups etc.....

just a thought.

Paul.

Yes, tablespace backups would be needed customer to customer. I am not
sure I can sell this to management. Maybe I can compare this to the
cost of adding new AIX boxes ! Still this is living dangerously
because one schema name mistake we could load $$$ into bank A when
it's intended for bank B. Or subtract $$$ when we shouldn't have, and
incur customer's retribution for lost interests$, use of fund. I think
that is a hard sell !

Would partition db be an answer ?
Selma

[Blair Adamache]

Mark A is quite right - "database" means something different to every
RDBMS vendor - it even has a different meaning on DB2 zOS versus DB2 on
Linux/UNIX/Windows (where we have instance support).

FWIW (all at the instance level):

The following parts of DB2 use shared memory:

- bufferpools
- lock list
- package cache
- shared sorts
- database heap
- log buffer
- catalog cache
- utility heap (util_heap_sz)

The following parts of DB2 are in an agent's private memory:

- private sorts
- application heap
- agent stack
- query heap
- statement heap

Selma_g wrote:

Thanks for your suggestion, Blair. However, our business contract
forbids mixing data between customers due to the sensitive nature of
the data. I am stuck creating 1-2 dbs a month. As I recall when I
worked in Sybase years ago, the memory space is shared globally. In
fact sybase claimed they can support something like 32K databases!
Does anyone have a solution for UDB? I can't size the less active dbs
small bufferpools because the customers may use it throughout the day.
Is this a weakness in UDB/DB2 in what otherwise an excellent db engine
?

Look forward to your answers.
Selma

Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<c1**********@hanover.torolab.ibm.com>...

You could do maximum CPU and memory sharing by putting all the data in
one databases, and using a different schema for each customer. And, put
their data in distinct table spaces. Bufferpools are allocated by
database, so cannot be shared by different databases. However, if you
don't like the schema approach above, in v8 bufferpools for inactive
databases can be sized quite small.

Selma_g wrote:

Hi,

I am wondering if the bufferpools and agent memory canbe shared within
a EE UDB instance (running 7.2 AIX 5 now but going to V8.1). Our
business model requires us to add about 1-2 db's per month (one db per
customer), and this consume the memory.
Some of these dbs are not highly active but once a db is activated, at
least the bufferpools are allocated along with the init_pool of
agents. Is there a way for bufferpools and agents be shared between
databases, like other RDBMS do ?

Thank you and look forward to your answers.
Selma

[Mark A]

> Yes, tablespace backups would be needed customer to customer. I am not

sure I can sell this to management. Maybe I can compare this to the
cost of adding new AIX boxes ! Still this is living dangerously
because one schema name mistake we could load $$$ into bank A when
it's intended for bank B. Or subtract $$$ when we shouldn't have, and
incur customer's retribution for lost interests$, use of fund. I think
that is a hard sell !

Would partition db be an answer ?
Selma

No, a partitioned database just mean that a given table is partitioned
across multiple nodes so that SQL statements can operated in parallel on the
data. The partitioning of a table is done on a pseudo-random fashion based
on a hashing algorithm. This much worse than have separate tables in
separated schemas for your situation.

I am not sure what the difference is between accidentally referencing the
wrong database vs. referencing the wrong the schema (which is the high level
qualifier for the table).

[Blair Adamache]

Somebody pinged me yesterday about your question and suggested that if
the database is seldom active, but you could predict when access could
take place, you could get a table for the customer into the bufferpool
by doing some SQL that touches all rows (i.e. forces a table scan).

There are good features in v8, such as dynamically changing the sizes of
bufferpools, that would help with the problem you face.

Selma_g wrote:

Hi,

I am wondering if the bufferpools and agent memory canbe shared within
a EE UDB instance (running 7.2 AIX 5 now but going to V8.1). Our
business model requires us to add about 1-2 db's per month (one db per
customer), and this consume the memory.
Some of these dbs are not highly active but once a db is activated, at
least the bufferpools are allocated along with the init_pool of
agents. Is there a way for bufferpools and agents be shared between
databases, like other RDBMS do ?

Thank you and look forward to your answers.
Selma

[Selma_g]

Blair and All,

Dynamically changing bufferpool sizes in V8 - that has possibility. V8
allows me to do that without forcing off all apps in a particular db ?
If that is the case i can build some intellegence into monitoring db
activities and dynamically adjust BP according to load. Has anyone
done that ?

Selma
Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<c1**********@hanover.torolab.ibm.com>...

Somebody pinged me yesterday about your question and suggested that if
the database is seldom active, but you could predict when access could
take place, you could get a table for the customer into the bufferpool
by doing some SQL that touches all rows (i.e. forces a table scan).

There are good features in v8, such as dynamically changing the sizes of
bufferpools, that would help with the problem you face.

Selma_g wrote:

Hi,

I am wondering if the bufferpools and agent memory canbe shared within
a EE UDB instance (running 7.2 AIX 5 now but going to V8.1). Our
business model requires us to add about 1-2 db's per month (one db per
customer), and this consume the memory.
Some of these dbs are not highly active but once a db is activated, at
least the bufferpools are allocated along with the init_pool of
agents. Is there a way for bufferpools and agents be shared between
databases, like other RDBMS do ?

Thank you and look forward to your answers.
Selma

[Blair Adamache]

You can read about ALTER BUFFERPOOL here:

http://publib.boulder.ibm.com/infoce...help/index.jsp

Search on "alter bufferpool statement".

Selma_g wrote:

Blair and All,

Dynamically changing bufferpool sizes in V8 - that has possibility. V8
allows me to do that without forcing off all apps in a particular db ?
If that is the case i can build some intellegence into monitoring db
activities and dynamically adjust BP according to load. Has anyone
done that ?

Selma
Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<c1**********@hanover.torolab.ibm.com>...

Somebody pinged me yesterday about your question and suggested that if
the database is seldom active, but you could predict when access could
take place, you could get a table for the customer into the bufferpool
by doing some SQL that touches all rows (i.e. forces a table scan).

There are good features in v8, such as dynamically changing the sizes of
bufferpools, that would help with the problem you face.

Selma_g wrote:

Hi,

I am wondering if the bufferpools and agent memory canbe shared within
a EE UDB instance (running 7.2 AIX 5 now but going to V8.1). Our
business model requires us to add about 1-2 db's per month (one db per
customer), and this consume the memory.
Some of these dbs are not highly active but once a db is activated, at
least the bufferpools are allocated along with the init_pool of
agents. Is there a way for bufferpools and agents be shared between
databases, like other RDBMS do ?

Thank you and look forward to your answers.
Selma

[Mark A]

"Selma_g" <se*********@yahoo.com> wrote in message
news:a3**************************@posting.google.c om...

Blair and All,

Dynamically changing bufferpool sizes in V8 - that has possibility. V8
allows me to do that without forcing off all apps in a particular db ?
If that is the case i can build some intellegence into monitoring db
activities and dynamically adjust BP according to load. Has anyone
done that ?

Selma

It will be a lot easier, and the system will perform better, if you just
tell you boss that a Sybase database is the same as a DB2 schema (in one
database).

[Knut Stolze]

Selma_g <se*********@yahoo.com> wrote:

Still this is living dangerously
because one schema name mistake we could load $$$ into bank A when
it's intended for bank B. Or subtract $$$ when we shouldn't have, and
incur customer's retribution for lost interests$, use of fund. I think
that is a hard sell !

You can probably handle such things quite easily if you set up the proper
privileges on the table. I.e. only the user "BANKA" can modify the data in
schema BANKA and so one. Also, the same issue exists with different
databases - you connect to the wrong database and the money goes to the
wrong bank.

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