IBM Support

Data warehouse database size is huge and growing fast

Technote (troubleshooting)


Problem(Abstract)

Data warehouse database used by IBM Collaborative Lifecycle (CLM) applications size is huge and growing fast.

Symptom

You see that current data warehouse size is above 200GB and it is growing about 1GB a day or more.


Cause

Due to high usage of CLM applications the historical tables are growing rapidly. It is designed behavior.

Diagnosing the problem

Check if the tables containing historical data are using the most of the space. There are RIDW.H_* tables. You can check the table size using How to check which tables are using the most space article.

Resolving the problem

WORKAROUND:
The workaround is to disable the jobs gathering historical data and truncate the historical data manually. This will reduce the database size but you will not be able to use the reports which use historical data.

Procedure:


  1. Navigate to https://your-server:9443/dcc/web#action=com.ibm.rational.datacollection.manageResources.configureETLJob and uncheck all jobs referring to historical data. This step is needed only in CLM 6.0.1 or below.

  2. Run the script provided below
    Disclaimer

    All source code and/or binaries attached to this document are referred to here as "the Program". IBM is not providing program services of any kind for the Program. IBM is providing the Program on an "AS IS" basis without warranty of any kind. IBM WILL NOT BE LIABLE FOR ANY ACTUAL, DIRECT, SPECIAL, INCIDENTAL, OR INDIRECT DAMAGES OR FOR ANY ECONOMIC CONSEQUENTIAL DAMAGES (INCLUDING LOST PROFITS OR SAVINGS), EVEN IF IBM, OR ITS RESELLER, HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.


    truncate.sqltruncate.sql

Document information

More support for: Jazz Reporting Service
Data Collection Component

Software version: 6.0, 6.0.1, 6.0.2

Operating system(s): Linux, Windows

Reference #: 1986358

Modified date: 09 July 2016