Posted by: iainduncani | May 7, 2010

Reducing the database size for MIH databases

The Development Environment Setup Tool (DEST) that comes with Master Information Hub (MIH) and Master Data Management (MDM) Server will create a data base for you with all of the necessary content for developing an MIH application. When it creates this database it sets all of the tablespaces (this is the part of the database that actually stores data on the file system) to use automatic storage which will allocate a certain amount of space on your hard drive to store the data and then resize automatically when full to make space for any new content.

The default settings that DB2 uses to increase the tablespace size is quite generous. This may be fine if you are intending on populating the database with lots of sample data but as a developer on the product I tend to be creating short-lived databases that are not required to store much data. Furthermore I tend to have many databases on my file system all of which are in use to some extent. This all means that when each database takes over 2GB of space it can become problematic in terms of spare capacity on my harddrive.

The following instructions explain how to reduce the size of the database (in the example called “MyDB”) so that it is as small as possible (around 600MB for an MIH database). Note that this will mean that they will be resized more often which could have a performance hit for running transactions but harddrive space was more important to me.

  1. Open up a DB2 command line processor (either in the DB2 control centre or via the start menu on Windows)
  2. Connect to the database by typing:

    CONNECT TO MyDB
  3. You now need to find out all the tablespaces that are being used by this database to do this type the command:

    LIST TABLESPACES

    This should then return a list of all of the tablespaces used by this database as well as what type of tablespace they are such as this:

    Tablespace ID = 0
    Name = SYSCATSPACE
    Type = Database managed space
    Contents = All permanent data. Regular table space.

    State = 0x0000
      Detailed explanation:
        Normal

    You will only be able to reduce the size of the tablespaces of type “Database managed space”, at the time of writing there were 6 generated for me: SYSCATSPACE, USERSPACE1, USERDATA, USERIND, USERLONG and SYSTOOLSPACE.

  4. Now that you have a list of tablespaces being used you need to issue an SQL command that has two parts. The first part will reduce the size and the second will stop them increasing in size by a large amount next time data is added to the database. If you don’t include this second part then the database size will decrease but next time you add some data to it it will then balloon up again (when I did this I found that a single transaction could cause the database size to increase by almost 1GB). The command to achieve all this is (note you could do it as two “ALTER TABLESPACE” commands if you wanted to but I’ve done it as one):

    ALTER TABLESPACE <TABLESPACE> REDUCE INCREASESIZE 10 PERCENT

    Where <TABLESPACE> is the name of the tablespace (such as USERDATA) that you are working with. This will need to be repeated for each of the tablespaces discovered in the previous step. In this example I am telling DB2 to increase the size of the tablespace by 10% when it needs extra capacity, this number can be changed depending on your performance/disk space needs. More information about what are valid inputs for the INCREASESIZE command can be found here.

Combining these steps together should produce a single chunk of SQL that can be run to reduce the size of the database:

ALTER TABLESPACE SYSCATSPACE REDUCE INCREASESIZE 10 PERCENT;
ALTER TABLESPACE USERSPACE1 REDUCE INCREASESIZE 10 PERCENT;
ALTER TABLESPACE USERDATA REDUCE INCREASESIZE 10 PERCENT;
ALTER TABLESPACE USERIND REDUCE INCREASESIZE 10 PERCENT;
ALTER TABLESPACE USERLONG REDUCE INCREASESIZE 10 PERCENT;
ALTER TABLESPACE SYSTOOLSPACE REDUCE INCREASESIZE 10 PERCENT;
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: