Session 5: Correcting Space-related Problems

Go Up to Space Analyst for IBM DB2 for Linux, Unix, and Windows Tutorial

Once you have identified space-related bottlenecks in your database with Space Analyst, you can use a number of different tools and facilities within the product to eliminate each storage headache.

Correcting Tablespace 'out of space' Problems

Space Analyst can help you quickly remedy tablespaces that are running out of room by:

• Resizing existing tablespace containers to have more room.
• Adding new containers file to an existing tablespace.
• Moving Objects from a near full tablespace to less utilized tablespace

Resizing and adding of tablespace containers can be managed by performing the following steps:

1. Open Space Analyst and select the Database Overview tab.
2. Select the graph in Tablespace Overview section and change to the grid display by selecting the right mouse menu option toggle display.
3. Select the DMS tablespace that is running low on free space and either select the Open toolbar option or the Open right mouse menu option.

This opens the Tablespace in DBArtisan's tablespace editor. Once you are in the editor, you can remedy the tablespace problem in one of two ways:

• To resize an existing container, select the container from the grid and click the Edit button and extend/ resize the container to a new, larger size. Then close the editor and select the Alter toolbar button to make the change permanent to the tablespace.
• To add a new container to the tablespace, select the Add button in the editor, and specify the new container attributes. Once complete, close the Add Container dialog and select the Alter toolbar button to make the change permanent to the database.

Correcting Space-related Object Problems

Once you identify specific objects that need storage corrections, you can use Space Analyst to help you quickly resolve your found issues. The Space Management wizard inside of Space Analyst can be used to build reorganization and space maintenance jobs that run right the first time. All jobs can either be immediately run or scheduled for later execution.

To create a standard database or object reorganization job, follow these steps:

1. On the toolbar, click the Space Management.
2. Select New Space Management Task.
3. Click Next.
4. Select Create standard reorganization job.
5. Click Next.
6. Select the objects you would like to reorganize from the Explorer tree on the left hand side of the wizard. Note that you can reorganize full tablespaces, selected schemas or selected tables.
7. Click Next.
8. The next few panels let you customize the reorganization. You can choose to either set options at the global level (affects all objects) or specific object level. Using the options, you can reorganize tables and indexes, perform LOB and Long data reorganizations and specify other customizations.
9. The second to last panel builds your reorganization script and allows you to preview the operation.
10. Click Next.
11. The final panel allows you to execute the reorganization script immediately, or schedule it for later execution.

Proactive Space Management

If you have many tablespace/schemas to take care of, then you might want to relieve the burden of performing detailed storage analysis on every tablespace or schema, each of which may or may not require attention. Fortunately, Space Analyst makes it easy to create smart space management jobs that can automate this task for you.

You can create a "reorganization analyst job" that allows you to specifically select various tablespaces/ schemas or objects and then set reorganization thresholds that determine when reorganization will occur. Once constructed, you can schedule these jobs to run as often as you'd like. During execution, these jobs will interrogate your chosen database/objects and determine if a reorganization needs to occur by applying your customized reorganization thresholds. If any objects are found to need reorganization, they are dynamically reorganized using any customized options you specify. Optionally, this job can also automatically update the statistics for the selected objects before and after reorganization and rebind any packages that depend on the reorganized objects.

These "autonomic" space maintenance jobs allow you to truly "set and forget" the task of monitoring and reorganizing the space of your critical databases.