Analyze the base and automatize maintenance processing.

The different operations which involve deletion, insertion and modification mean that the DB2 database can become disorganized. This has a detrimental effect on response times. It is important to carry out a reorganization procedure.

An efficient DBSPACE reorganization must incorporate a DROP step (the only way to recover all the occupied pages). Thus reorganizations such as UNLOAD DBSPACE/RELOAD DBSPACE PURGE are different because DROP DBSPACE is not included. The disadvantage of DROP DBSPACE is that it also suppresses the whole environment (INDEXES, VIEWS, GRANTS ...).

An efficient reorganization should contain the following stages :

• UNLOAD DBSPACE
• Inventory of the whole environment (long and tedious)
• DROP DBSPACE
• ACQUIRE DBSPACE PCTFREE=xx
• Reload the tables
• Re-create the environment

This procedure must be repeated for each dbspace. It provides high quality performance but it is, nevertheless a very tedious operation.

SQL-REORG performs all the above steps automatically.

It is composed of :

• An ON-LINE editor which prepares and modifies reorganizations from sets of critical dbspaces.
• A BATCH utility which allows different dbspace functions to be operated such as reorganization, unload, reload ...

The ANALYZE function of SQL-REORG

It is never easy to determine which dbspaces should be reorganized.
SQL-REORG performs a full analysis of the base and provides a list of critical dbspaces as well as the reasons for reorganization.
SQL-REORG allows you to string together automatically the reorganization of all the dbspaces included in the list or to modify the list (addition or suppression of dbspaces) before performing their ON-LINE reorganization.
 

The REBIND function of SQL-REORG

After reorganization, the access modules of the reorganized dbspaces are invalidated.
The REBIND function of SQL-REORG, lists and rebinds all the invalidated access modules.
 

The ON-LINE editor allows you to work on the list of chosen dbspaces by using ANALYZE or on a list that you have drawn up yourself.
 
 

A function key displays the list of all the dbspaces

Addition of further dbspaces (simply by ticking)

Display of free dbspaces

Just start the BATCH utility to reorganize the chosen dbspaces.