?
Solved

DB2 V9.5 database size problem!

Posted on 2009-04-16
3
Medium Priority
?
1,353 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:Dmitry_Bond
  • 2
3 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24157108
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

0
 

Author Comment

by:Dmitry_Bond
ID: 24159942
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!
0
 

Accepted Solution

by:
Dmitry_Bond earned 0 total points
ID: 24166219
Sorry, my apologizes to IBM guys. I was not right about this case with DB2.
I found the problem  there was additional db initialization script started which did not write any info into log file, so it looked like it hang on DB2 CREATE DATABASE %Database% while in real it did hang on DB2 update DB CFG for %Database% using LOGFILSIZ    16000.

So, 2GB is = DbPageSize * LOGFILSIZ * LOGPRIMARY = 4096 * 16000 * 32 =~ 2GB.

So, case closed.

PS.
Hm... seems not too many experts are here if got only 1 recommendation related to absolutely to different area. :-\ Maybe it not costs that money...
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question