Business Intelligence Tutorial

Lesson 12. Maintaining the data warehouse

In this lesson, you will learn to define indexes, use the RUNSTATS and REORG utilities, and monitor the warehouse database.

The amount of maintenance that is required for a database is directly related to the amount of database activity or workload. Because this tutorial does not generate significant activity, this lesson is primarily a guide to the DB2 tools and utilities that you can use when you are using an actual warehouse database.

Creating an index

You can create an index to optimize queries for end users of the warehouse. An index is a set of keys, each pointing to a set of rows in a table. The index is a separate object from the table data. The database manager builds the index structure and maintains it automatically. An index gives more efficient access to rows in a table by creating a direct path to the data through the pointers that it creates.

An index is created when you define a primary key or a foreign key. For example, an index was created on the LOOKUP_MARKET table when you defined CITY_ID as its primary key in Lesson 11, Defining keys on target tables.

To create additional indexes:

1. From the DB2 Control Center, expand the objects within the TUTWHS database until you see the Indexes folder.
2. Right-click the Indexes folder and click Create --> Index Using Wizard.

   The Index wizard opens.

3. Follow the detailed instructions in the wizard to build a new index.

Collecting table statistics

Table statistics provide information about the physical and logical characteristics of a table and its indexes. You must periodically collect these statistics, so that DB2 Universal Database can determine the best way to access your data. If extensive changes are made to the data in a table, and the last collection of statistics no longer reflects the actual table data, then data access performance can deteriorate. In general, you should update statistics if there are major changes to the data in your table.

To collect statistics on the LOOKUP_MARKET table:

1. From the DB2 Control Center, right-click on the LOOKUP_MARKET table and click Run Statistics.

   The Run Statistics window opens.

2. Use the online help to determine the level of statistics that you want to gather for the table and its indexes.
3. Use Share level to specify the type of access that users have while statistics are being gathered.
   
   
4. Click OK to begin collecting the table statistics.

Reorganizing a table

Reorganizing a table rearranges it in physical storage, eliminating fragmentation and making sure that the table is stored efficiently in the database. You can also use reorganization to control the order in which the rows of a table are stored, usually according to an index.

To reorganize the LOOKUP_MARKET table:

1. From the DB2 Control Center, right-click on the LOOKUP_MARKET table and click Reorganize.

   The Reorganize Table window opens.

2. In the Using system temporary table space field, specify the name of the table space where the table being reorganized can be temporarily stored. If you do not specify a table space, the temporary copy of the table is stored in the same table space where the table currently resides.
3. In the Using index field, specify the index to use to reorganize the table rows. If you do not specify an index, the table rows are reorganized without regard to order.
4. Click Reorganize Now to reorganize the table immediately. Click Schedule to schedule the reorganization for a specific date and time.

   
   
   

Monitoring a database

The performance monitor provides information about the state of DB2 Universal Database and the data that it controls, and calls attention to unusual situations. The information is provided in a series of snapshots, each of which represents the state of the system and its databases at a point in time. You can control the frequency of the snapshots and the amount of information collected by each.

The information that is collected by a performance monitor is returned in performance variables. These performance variables are a defined set of elements that can:

• Count the number of times something has occurred (counters), for example, the number of rows read.
• Return the current value (gauge), for example, the number of applications currently connected.
• Represent the time when something occurred (time stamp), for example, the time when the last application completed.
• Record the highest value reached (water mark), for example, the maximum size of the database heap.

To monitor the LOOKUP_MARKET table:

1. From the DB2 Control Center, right-click the LOOKUP_MARKET table, and click Performance monitoring --> Start monitor.

   The Start Monitor window opens.

2. The Start Monitor window lists the performance monitors that can take snapshots at the table level. Select the Default_for_table_level monitor, and click OK to start it.

   The Default_for_table_level is an IBM-supplied monitor that is designed to collect key performance related information at the table level.

3. Right-click on the LOOKUP_MARKET table and select Performance monitoring --> Show monitor activity.

   The Show Monitor notebook opens.

   The Details page lists the performance variables that are being collected by the monitor and the values that are returned by the snapshots. The Default_for_table_level monitor takes snapshots (Regular sample box) every 30 seconds, and does not graph its collected data. You can tailor this monitor to your own preferences (for example, graph performance variables or change snapshot interval). See the online help for details.

4. If there is no activity on the LOOKUP_MARKET table, then the values on the Details page will remain blank. To generate table activity:
   1. Open the Command Center.
   2. On the Script page, do the following:
      1. Issue connect to TUTWHS user userid using password, where:
         • userid is the user ID that you used to create the database in Lesson 2, Creating a warehouse database.
         • password is the password for the user ID.
      2. Issue a number of SELECT * FROM userid.LOOKUP_MARKET statements, where userid is the user ID that you used to create the database in Lesson 2, Creating a warehouse database.
   3. Return to the Show Monitor notebook. As snapshots are taken, the values for the Rows Read per Second performance variable will reflect your SQL statement activity.
5. Close the Show Monitor notebook. Right-click on the LOOKUP_MARKET table and click Performance monitoring --> Stop monitor. The Default_for_table_level monitor stops.

What you just did

In this lesson, you created an index. You collected statistics on the LOOKUP_MARKET table, reorganized it, and monitored it. In the next lesson, you will authorize the users of the warehouse database to access the table.