db2 udb 7.1 on windows question

dale washington

db2 udb 7.1 on windows question
Hi guys :

I wonder whether when you create a SMS tablespace on db2 udb 7.1 if you
just specify the drives as in the below example and supposing that each
drive has 8 gig of space available. All these space available will be
allocate by the tablespace filesystem it means 24 gig. It is true ?


CREATE TABLESPACE ACCOUNTING
MANAGED BY SYSTEM
USING ('d:\acc_tbsp', 'e:\acc_tbsp', 'f:\acc_tbsp')
EXTENTSIZE 64
PREFETCHSIZE 32

T.I.A

Dale

_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963



Fabrizio Napolitano

Re: db2 udb 7.1 on windows question
(in response to dale washington)
Hi Dale ,

I should say it is not complitely as you said, what I mean is that 24 GB
is the maximum the tablespace could USE at the moment when you create it.
Anyway SMS tablespaces allocate space only when they need it, at creation
time (if I remember well) SMS allocate 2 extent + 2 extent for any new
tables created in it (be cautios with what I am saying because I am going
by memory ) so after you run the create command only 128*4KB will be
allocate.

When you start loading data in your tables the size of your tablespace
will start growing. But if in the meantime the space available on the
three drive was used also by other software or tablespaces then you will
have less then 24GB for you tablespace; let looks at an example:

you create your tablespace when on yoru drive there are 24GB free in
total, but before you load data in the tables of this tbs the following
things happens
a software b used 3GB on drive d: -> space left free 5GB
another tablespace used 1GB on drive e: -> space left free 7GB
and softwre c used 5 GB on drive f: -> space left free 3GB
then when you finally start loading data you will have actually 9GB free
(unless somithing else is used or freed during your load) , in fact SMS
tablespace goes full when one of the containers is full it makes no
differences if in the other containers there is still a lot of space!
SMS are really dynamic in this sense so if you are not sure if someone
else is going to you those drives and you already know that you need 24GB
it is maybe better to go for DMS tablespaces.

Remeber also that you can not allocate more containers to a SMS
tablespace, the only way to enlarge it is freeing space on the drives or
redirect restore
(maybe relocate db utility? see a post of kelly schlamb on the db2eug
mailin list:
snip
If you are on a relatively recent level of V7 (or on V8) then you can use
the db2relocatedb tool to help you move containers (I can't remember
exactly which fixpak it started shipping in -- but any fixpak released in
2002 should contain it).

I believe it is discussed in the release notes but if you can't figure out
how to use it, let me know and I'll jot a few instructions down for you.

Basically, you need to shut the database down, manually move the
container(s) to the new location, and then run the db2relocatedb tool. You
provide a configuration file to the tool (telling it about the database
and
what changes you want) and it will go out and change things to point to
the
new container location(s).
end snip
but I don't know this utility very well)

hope this help
Fabrizio






dale washington <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
09/01/2003 08:37
Please respond to DB2 Data Base Discussion List


To: [login to unmask email]
cc:
Subject: db2 udb 7.1 on windows question


Hi guys :

I wonder whether when you create a SMS tablespace on db2 udb 7.1 if
you
just specify the drives as in the below example and supposing that each
drive has 8 gig of space available. All these space available will be
allocate by the tablespace filesystem it means 24 gig. It is true ?


CREATE TABLESPACE ACCOUNTING
MANAGED BY SYSTEM
USING ('d:\acc_tbsp', 'e:\acc_tbsp', 'f:\acc_tbsp')
EXTENTSIZE 64
PREFETCHSIZE 32

T.I.A

Dale

_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963



the reached at
[login to unmask email]