How to purge or reduced the Tivoli Warehouse Size

Body

How to reduced the Tivoli Data Warehouse (TDW) Size.

IF the size of the TDW is too big or and if you have to reduced the size of this database you have two option or two solutions.

But before you do anything, I will recommend that you backup your TDW database. The command you use to backup the database will depend on the type

of database that you are running.

A typical DB2 command wiill be  DB2 BACKUP DATABASE <database name>.

There ate two option here to reduced the size of the TDW  database.

1. You can choose to delete the complete TDW database if you do not have any use for the old data.

In this case you use the DROP command as shown below

DROP DATABASE <database name>

Note that dropping a database can have far-reaching effects, because this action deletes all its objects, containers, and associated files.
The dropped database is removed and uncataloged from the database directories.

2. The second option is to purge the database. This will retain all the objects, containers, and associated files and the database is still cataloged.

This simple reduces the size of the tables contained in the database. This option is the preferred option but can take some time to implement depending

on the size of the TDW database.

Purge of the Data

Before you commence on purging the TDW  you will need to considered how to purge the Warehouse tables and the steps below will help you before you start the purge.

1.  Do runstats on all tables.
2.  Record tabname and card information in a file to know what we started with.
3.  Set DB2 Profile registry variables (listed in “Helpful Strategy” section below).
4.  Update dbm cfg parameters (listed in ““Helpful Strategy” section below).
5.  Update db cfg parameters (listed in ““Helpful Strategy” section below).
6.  Alter bufferpools for automatic storage. Increase the bufferpools for database.
7.  Calculate db size before shrinking.

If you are using DB2 as you database engine then the following are helpful Strategy to maintain a sound database.

1. Set DB2 Profile registry variables.

    db2set DB2_SKIPINSERTED=ON
    db2set DB2_USE_ALTERNATE_PAGE_CLEANING=YES
    db2set DB2_EVALUNCOMMITTED=ON
    db2set DB2_SKIPDELETED=ON
    db2set DB2_PARALLEL_IO=*

2. Update dbm cfg parameters.

    update dbm cfg using DFT_MON_BUFPOOL ON ;
    update dbm cfg using DFT_MON_LOCK ON ;
    update dbm cfg using DFT_MON_SORT ON ;
    update dbm cfg using DFT_MON_STMT ON ;
    update dbm cfg using DFT_MON_TABLE ON ;
    update dbm cfg using DFT_MON_UOW ON ;
    update dbm cfg using ASLHEAPSZ 32 ;

3. Update db cfg parameters.

    update db cfg for <DBNAME> using STMT_CONC LITERALS ;
    update db cfg for <DBNAME> using DFT_DEGREE ANY ;
    update db cfg for <DBNAME> using PCKCACHESZ AUTOMATIC ;
    update db cfg for <DBNAME> using CATALOGCACHE_SZ 2000 ;
    update db cfg for <DBNAME> using LOGBUFSZ 2048 ;
    update db cfg for <DBNAME> using UTIL_HEAP_SZ 524288 ;
    update db cfg for <DBNAME> using BUFFPAGE 10000 ;
    update db cfg for <DBNAME> using LOGFILSIZ 16384 ;
    update db cfg for <DBNAME> using CUR_COMMIT ON ;
    update db cfg for <DBNAME> using SECTION_ACTUALS BASE ;

Now the purge itself is a series of reducing the each table size in the database and you can use the delete command
Specifying a date to start and then reduced by say 30 days.

So from the database run the describe  to get the schema of the table.
db2 describe table <table name>

Use a date field from the output describe command say(ordertime)

DELETE from <tablename > where ordertime < (current timestamp - 30 days)

However, there is a much quicker way to purge the ITM Warehouse database. Using this method it is assumed that you
do not want to keep the old data and it are ok to start again.
If this is what you want to do then you can just use the

DROP DATABASE <database name>

Note the dropping a database can have far-reaching effects, because this action deletes all its objects, containers, and associated files.
The dropped database is removed (uncataloged) from the database directories.

Lastly you will need to run the DB2 command to reclaim the disk space

 db2 "alter tablespace tab8k reduce max"

How to reduced the Tivoli Data Warehouse Oracle Database

Subscribe and follow us for all the latest information directly on your social feeds: