Creating Tablespaces : Best Practices

[rAinDeEr]

Hi,

I am trying to create around 70 tablespaces for around 100 tables..
Am using DB2 UDB 8.2 in Linux environment...

This is one i generated through Control centre....

CREATE REGULAR TABLESPACE SNCI001 IN DATABASE PARTITION GROUP
IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY SYSTEM
USING ('/db2home/db2inst1/dnci1d/user_tblspace')
EXTENTSIZE 8 PREFETCHSIZE 16 BUFFERPOOL GBP01 OVERHEAD 12.670000
TRANSFERRATE 0.180000;

Now, to create the remaining using command prompt how should i proceed
in specyfying the directories ...USING
('/db2home/db2inst1/dnci1d/user_tblspace') ??

1.) Do I have to create around 70 directories for each container using
MKDIR first and then proceed with the command ...??

2.) Can I nest the 70 containers under one directory ? Am getting
SQL0298N Bad container path. SQLSTATE=428B2

What is the best practice ??

3.) I have a table with 3 fields having Varchar(4000) and am getting
sqlcode -286
A default tablespace could not be found with a page size atleast 16384.
How should I size the Tablespace and bufferpool for this purticular
table ??

[Hemant Shah]

While stranded on information super highway rAinDeEr wrote:

Hi,

I am trying to create around 70 tablespaces for around 100 tables..
Am using DB2 UDB 8.2 in Linux environment...

This is one i generated through Control centre....

CREATE REGULAR TABLESPACE SNCI001 IN DATABASE PARTITION GROUP
IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY SYSTEM
USING ('/db2home/db2inst1/dnci1d/user_tblspace')
EXTENTSIZE 8 PREFETCHSIZE 16 BUFFERPOOL GBP01 OVERHEAD 12.670000
TRANSFERRATE 0.180000;

Now, to create the remaining using command prompt how should i proceed
in specyfying the directories ...USING
('/db2home/db2inst1/dnci1d/user_tblspace') ??

1.) Do I have to create around 70 directories for each container using
MKDIR first and then proceed with the command ...??

Yes.

>
2.) Can I nest the 70 containers under one directory ? Am getting
SQL0298N Bad container path. SQLSTATE=428B2

I don't think so.

>
What is the best practice ??

It depends on how you are using the database. In high volume environment
the best practice would be to use DMS table spave and have one table
per disk (unless you are using SAN) and have one ioserver per disk in
use. In low volume you can probably get by using SMS table space.

>
3.) I have a table with 3 fields having Varchar(4000) and am getting
sqlcode -286
A default tablespace could not be found with a page size atleast 16384.
How should I size the Tablespace and bufferpool for this purticular
table ??

The table have 3 columns of VARCHAR 4000 which is greater than 4K (page
size of the table space), one size up for page size is 16K, you will have
to create table space with page size of 16K, which will also require to
create a buffer pool with page size 16K.

>

--
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.

[rAinDeEr]

Hi..

How do I start creating Tablespaces in Linux in the command prompt... ?
Control centre is really easy.....

Do i have to use MKDIR then change the ownership ?

[Serge Rielau]

rAinDeEr wrote:

Hi..

How do I start creating Tablespaces in Linux in the command prompt... ?
Control centre is really easy.....

Do i have to use MKDIR then change the ownership ?

Have you considered using automatic storage? It would make your life so
much easier.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Knut Stolze]

rAinDeEr wrote:

1.) Do I have to create around 70 directories for each container using
MKDIR first and then proceed with the command ...??

Correct. Best might be to create the directories as DB2 instance owner.
Then you don't have to worry about the access.

2.) Can I nest the 70 containers under one directory ? Am getting
SQL0298N Bad container path. SQLSTATE=428B2

No. SMS containers require a directory. Thus, you can't place different
containers in the same directory. But you can use common parent
directories:

/containers/container1/
/containers/container2/

DMS containers are different. Since those are files, you can place those
files in the same directory.

3.) I have a table with 3 fields having Varchar(4000) and am getting
sqlcode -286
A default tablespace could not be found with a page size atleast 16384.
How should I size the Tablespace and bufferpool for this purticular
table ??

3 * 4000 = 12000. So you need a tablespace with 16K or 32K page size... and
a buffer pool that goes along with it. As for the size of the
tablespace/bufferpool, it depends on the amount of data. The usual TS/BP
tuning comes into play.

p.s: Is there a particular reason why you want to place all the tables in
dedicated tablespaces? You could just dump them into a few TS and let DB2
take care of the rest. Multiple TS and containers are usually only helpful
if you want to physically separate the data and/or if you have multiple
discs and want to take advantage of parallel I/O. (Or if you need
different page sizes...)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[rAinDeEr]

Am trying to partition the tables in such a way that the tables with
huge data belong to separate tablespaces and bufferpools and tables
with just a few records I have clubbed into a single tablespace for
performance.

Am i proceeding in the right way ?

Also, I have to remotely connect to the Database. I dont have a client
installed nor have i cataloged any where..Have to TELNET and do the
operations.

I am working in a m/c with no partitions..So how do I go about
Automatic Storage ?

Also deciding on the bUfferpool size is another issue..

Around 100 tables...40 of them with very high records...remaining just
a few 100 records..

[Knut Stolze]

rAinDeEr wrote:

Am trying to partition the tables in such a way that the tables with
huge data belong to separate tablespaces and bufferpools and tables
with just a few records I have clubbed into a single tablespace for
performance.

That makes sense. ;-)

Also, I have to remotely connect to the Database. I dont have a client
installed nor have i cataloged any where..Have to TELNET and do the
operations.

You may want to consider using a DB2 client on your own machine. Then you
can handle things a bit easier if you are not very familiar with the CLP
yet.

Also deciding on the bUfferpool size is another issue..

There I would recommend to run the Configuration Advisor (from the control
center) since it makes some suggestions regarding buffer pool sizes.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Mark A]

"rAinDeEr" <ta**********@gmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...

Hi,

I am trying to create around 70 tablespaces for around 100 tables..
Am using DB2 UDB 8.2 in Linux environment...

While it is best practice in DB2 for z/OS to have a single tablespace for
each table (with some exceptions), it is bad practice to that in DB2 for
(LUW) Linux, UNIX, and Windows.

With DB2 for LUW, you want one tablespace for each page size that is needed.
If you want multiple bufferpools, then you may need multiple tablespaces
since tablespaces are assigned to bufferpools (not tables).

You might want additional tablespaces if you have some extremely large
tables (in addition to smaller tables) that will be have a lot of full table
scans (queries that read every single row of the table), and you want to use
a different prefetch or extent size than the other tablespaces.

There is no way you should have 70 tablespaces in DB2 for LUW.

[Ian]

rAinDeEr wrote:

Hi,

I am trying to create around 70 tablespaces for around 100 tables..
Am using DB2 UDB 8.2 in Linux environment...

This is one i generated through Control centre....

CREATE REGULAR TABLESPACE SNCI001 IN DATABASE PARTITION GROUP
IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY SYSTEM
USING ('/db2home/db2inst1/dnci1d/user_tblspace')
EXTENTSIZE 8 PREFETCHSIZE 16 BUFFERPOOL GBP01 OVERHEAD 12.670000
TRANSFERRATE 0.180000;

Now, to create the remaining using command prompt how should i proceed
in specyfying the directories ...USING
('/db2home/db2inst1/dnci1d/user_tblspace') ??

1.) Do I have to create around 70 directories for each container using
MKDIR first and then proceed with the command ...??

No. I'm not sure why other people think that the directory has to exist
first. DB2 will create the directory if it is able to. This applies to
both SMS and DMS (i.e. DB2 does the equivalent of 'mkdir -p').

In fact, if DB2 does create the directory, it creates a file in the
directory named .SQLCRT.FLG that signifies that DB2 created the
directory. When you drop your tablespace, DB2 will remove the
directory if the .SQLCRT.FLG exists.

2.) Can I nest the 70 containers under one directory ? Am getting
SQL0298N Bad container path. SQLSTATE=428B2

The containers can all exist in a single path. Example:

/db2home/db2inst1/dnci1d/user_tblspace/snci001 (for tbspace SNCI001)
/db2home/db2inst1/dnci1d/user_tblspace/snci002 (for tbspaceSNCI002)
Note, DB2 does not append the name of the tablespace to the path you
specify for SMS (which it looks like you were assuming that it did,
based on your container path).

What is the best practice ??

This depends completely on your server and the physical disks you've got
installed in the server.

[Ian]

Mark A wrote:

While it is best practice in DB2 for z/OS to have a single tablespace for
each table (with some exceptions), it is bad practice to that in DB2 for
(LUW) Linux, UNIX, and Windows.

I don't think it's necessarily a bad practice on LUW. But it certainly
shouldn't be done by default.

It's just that the reasons why people do it on z/OS don't apply on
LUW, right? (I'm not a z/OS guy).

[Mark A]

"Ian" <ia*****@mobileaudio.comwrote in message
news:713Kg.4377$y61.1813@fed1read05...

I don't think it's necessarily a bad practice on LUW. But it certainly
shouldn't be done by default.

It's just that the reasons why people do it on z/OS don't apply on
LUW, right? (I'm not a z/OS guy).

It's bad practice for 2 reasons.

First, it takes a lot more admin time. The cost of ownership of the database
is increased, sometimes significantly.

Second, it either wastes huge amount of disk space, or it increases the
chance of an database outage due to a tablespace filling up (assuming we are
talking about DMS). For a given amount of disk space, having just a few
large tablespaces provides a much larger error of margin for a single table
that grows unexpectedly, compared to creating a separate tablespace for each
table.

DB2 for z/OS is different for several reasons, only some of which I will
mention here.

On DB2 z/OS with a simple tablespace, there are no table scans, there are
only tablespace scans. So if a tablespace scan occurs, it will scan all the
tables in the tablespace. This is not true for segmented tablespaces, but
there are some disadvantages to segmented tablespaces that I don't want to
get into here.

On DB2 for z/OS, tablespaces contain the specification for Percent Free and
other parameters that are at the table level in LUW.

On DB2 for z/OS, tablespaces use VSAM Linear datasets, which have a primary
and secondary quantity (size) that is set by the DBA. Once the primary space
is filled up, the tablespace will automatically use up to 127 (or something
like that) secondary extents (of the size you specify) as the table grows.
So it is much less likely to fill up a tablespace, and you don't have to
pre-allocate huge amounts of space upfront. Admittedly, automatic storage in
LUW alleviates "some" of the concerns addressed by this last point, but it
still is not the same.