OLAP Setup and User's Guide

Defragmenting (Reorganizing) Your Relational Database

Over time, the tables used by DB2 OLAP Server, especially the fact and key tables, need to be reorganized or defragmented to reclaim unused space. Your database administrator should do this using the appropriate tool for your database manager.

Defragmenting on Windows NT and UNIX

If you are using DB2, use the REORG command, as in the following scenario:

1. Select a row from the CUBECATALOG table and determine the RELCUBEID and FACTTABLECOUNT for the application and database you want to reorganize. The SQL statement to do this might be:

   SELECT RELCUBEID, FACTTABLECOUNT FROM CUBECATALOG WHERE APPNAME='Myapp' 
     AND CUBENAME='MyCube'
   

   In this example, assume that:

   • the RELCUBEID is 6 and FACTTABLECOUNT is 4, then you will have 4 fact tables (CUBE6FACT1, CUBE6FACT2, CUBE6FACT3, and CUBE6FACT4)
   • you have 4 indexes (CUBE6FINDEX1, CUBE6FINDEX2, CUBE6FINDEX3, CUBE6FINDEX4)
   • the key table is either CUBE6KEYA or CUBE6KEYB, depending on what restructures have been performed. The key table index is CUBE6KINDEX.
2. Run REORGCHK on all of the fact tables and the key table. For example:

   reorgchk on table userid.cube6fact1
   reorgchk on table userid.cube6fact2
   reorgchk on table userid.cube6fact3
   reorgchk on table userid.cube6fact4
   reorgchk on table userid.cube6keya
    
   

3. Where REORGCHK indicates that a REORG is required, REORG the table in conjunction with its index. For example:

   reorg table userid.cube6fact3 index userid.cube6findex3