Reorganizing the database
- DB2®
- The DB2® database has been
set up to maintain itself, so there is little user maintenance to
do. Periodically, DB2® checks
the database by running an internal routine. DB2® determines when this routine must be run
using a default policy. This policy can be modified, if need be, or
can be switched off so that DB2® does
not perform internal automatic maintenance. Using the statistical
information that DB2® discovers
by running this routine, it adjusts its internal processing parameters
to maximize its performance.
This routine has also been made available for you to run manually in the case either where you feel that the performance of DB2® has degraded, or because you have just added a large amount of data , and anticipate performance problems. The routine is imbedded in a tool called dbrunstats, which can be run to improve performance while DB2® is processing data without causing any interruption.
It is also possible to physically and logically reorganize the database using the dbreorg script. This effectively re-creates the tablespace using its internal algorithms to determine the best way to physically and logically organize the tables and indexes on disk. This process is time-consuming, and requires that HCL Workload Automation is down while it is run, but it does provide you with a freshly reorganized database after major changes.
The use of these tools is described in Administrative tasks - DB2.
These tools are implementations of standard DB2 facilities. If you are an expert user of DB2 you can use the standard facilities of DB2® to achieve the same results. For details go to the Information Center for DB2®, version 9.5, at: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5//index.jsp.
- Oracle
- For Oracle databases see the Oracle maintenance documentation.
Oracle 10g by default has an internally scheduled procedure to collect database statistics: if the default schedule is not changed, Oracle 10g will automatically optimize its performance by running this procedure daily. Oracle 9i does not have the same schedule by default, but could be set up to do so.