asked on

DB2 V9.5 database size problem!

Hi.

It is DB2 V9.5. Windows Server 2003. I issued the "create database mydb" statement in the DB2 command window and it was allocated 2Gb on disk!
I tested this case on 2 different systems (actually both are VMs) - result is the same it ia always allocating 2Gb for any new database.

Why empty database takes to much space?
Are there any options to decrease allocated space?

I need a lot of empty databases - about 60-100 databases on same computer. It is a build procedure environment - it creating database for every customer project specific build process, initializing tables, objects and so on, but all tables are empty because the only structure of database required during building process, it is not filled with data at all.

Another thing - we have a number of VM (virtual machines) used as test environments and it seems like we have to essentially increase disk sizes for new VM with DB2 V9.5... :-\ which is bad. So, are there any options to decrease disk allocation size for newly created db?...

It was perfectly works on DB2 V7.x and V8.x - empty databases took only 4-5 Mb on disk, but seems with DB2 V9.x it was essentially changed and it is the cause of problems. :-\

Regards,
Dmitry.

The code using to create new database
 
  DB2 CREATE DATABASE %Database% AUTOMATIC STORAGE NO USING CODESET UTF-8 TERRITORY US 
  DB2 CREATE BUFFERPOOL %BufferPool% ALL NODES SIZE 200 PAGESIZE 8192 
  DB2 CREATE REGULAR TABLESPACE %TableSpace% PAGESIZE 8192 MANAGED BY SYSTEM USING ('%db2container%') EXTENTSIZE 256 PREFETCHSIZE 32 BUFFERPOOL %BufferPool%
 
Is it looks ok?
Can you recomment anything other? Any hints on this?
 
Note: in particular 2Gb disk space allocated by 1st command here, by this:
  DB2 CREATE DATABASE %Database% AUTOMATIC STORAGE NO USING CODESET UTF-8 TERRITORY US 
That is why I'm wondering about such strange behaviour...

Open in new window

momi_sabag

by default when you create a database, db2 will create three tablespaces - user, catalog and temp
if you wish to fully control the size of the database, you will need to explictly specify the creation parameters for these tablespaces when you create the database
you can see the full description here
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.sql.ref.doc/doc/r0000888.html

Dmitry_Bond

ASKER

Maybe better I should clarify my question a bit - why DB2 9.x behave differently and how to make it behaving like DB2 V8 from the creating database point of view?

Now about your recommendation.
Did you mean something like this:

DB2 CREATE DATABASE %Database% AUTOMATIC STORAGE NO USING CODESET UTF-8 TERRITORY US PAGESIZE 8192 CATALOG TABLESPACE MANAGED BY SYSTEM USING 'c:\db2\prod1' EXTENTSIZE 256 PREFETCHSIZE 32 USER TABLESPACE MANAGED BY SYSTEM USING '...' EXTENTSIZE 256 PREFETCHSIZE 32 TEMP TABLESPACE MANAGED BY SYSTEM USING '...' EXTENTSIZE 256 PREFETCHSIZE 32

If yes, sorry but it is somplete _bullshit_!
I do not have to worry about such "technical" parameters at all!
DB2 must provide clear and simple way to maintain default parameters so such statements like "crerate database mydb" will not eat 2Gb disk space.

As for me - the grade "BAD" to the IBM DB2 team if they creating extra complexity instead of controling it and keeping product user friendly!

ASKER CERTIFIED SOLUTION

Dmitry_Bond

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial