A critical aspect of maintaining a database is getting the most current data possible into it. In data warehouses, this need is often met with extract-transform-load (ETL) technologies. DB2 for LUW addresses the loading aspect with a very sophisticated built-in load utility that makes it easy to load large amounts of data quickly into your database. Despite the load utility’s efficiency, large amounts of data generally translates into large amounts of time spent loading and it becomes valuable to be able to track the progress of such utilities as they execute. In today’s post, we’ll look at how the db2top Utilities screen makes it easy to monitor the progress of your utilities, including loads and backups.
You launch the Utilities screen by pressing the ‘u’ key from db2top. This presents a screen like the following:
The table consists of the following columns:
| Column Name | Definition |
|---|---|
| Hash Value | A hash computed from the utility_description. |
| # of entries | The number of instances of the same utility that this row represents. It is followed by a “+” if there are more than one. |
| Utility Start Time | The date and time when the current utility was originally invoked (utility_start_time). |
| Utility Type |
The class of utility (utility_type). It can have one of the following values:
|
| Uti Pri | Utility priority specifies the amount of relative importance of a throttled utility with respect to its throttled peers. A priority of 0 implies that a utility is executing unthrottled. Non-zero priorities must fall in the range of 1-100, with 100 representing the highest priority and 1 representing the lowest (utility_priority). |
| Utility State |
The state of a utility (utility_state). It can have one of the following values:
Available only for DB2 9.1 or later. |
| Invoker Type |
A description of how a utility was invoked (utility_invoker_type). It can have one of the following values:
Available only for DB2 9.1 or later. |
| Completed Work | The number of work units for the current phase which have been completed (progress_completed_units). If the Work Unit column is “Bytes”, the value of this column is expressed as some multiple of bytes (e.g. K for kilobytes, M for megabytes, etc.) |
| Work Unit |
The metric for interpreting the progress_total_units and progress_completed_units elements (progress_work_metric). It can have one of the following values:
|
| Prog% | The percentage of work units of the phase that have been completed (progress_completed_units / progress_total_units). |
| Phase Start Time | A timestamp representing the start of the phase (progress_start_time). |
| Progress Description | Describes the phase of work (progress_description). It is truncated to 20 characters if necessary. |
| Utility Description | A brief description of the work a utility is performing. For example, a rebalance invocation may contain "Tablespace ID: 2" representing that this rebalancer is working on table space with ID 2. The format of this field is dependent on the class of utility and is subject to change between releases (utility_description). It is truncated to 80 characters if necessary.
Note: this column is not shown when the screen is less than 94 columns wide |
When you hold the Shift key and press the ‘X’ key to turn on extended mode, the second column, # of entries, disappears and is replaced with two new columns:
| Column Name | Definition |
|---|---|
| Utility Id | The unique identifier corresponding to the utility invocation (utility_id). |
| Node Nbr | The number assigned to the node in the db2nodes.cfg file (node_number). |
In this mode, assuming you are taking a global snapshot, instead of seeing an aggregate across all database partitions of what the utility is doing as in the screen output shown above, you see the utility at each of the individual partitions as shown below:
The Utilities screen is not affected by pressing the ‘k’ key to switch between deltas and actuals or pressing the ‘i’ key to switch between showing active objects and showing all objects. As was shown above, the Utilities screen is affected by holding the Shift key and pressing the ‘X’ key to turn on and off extended mode and is affected by holding the Shift key and pressing the ‘G’ key to switch between a global and a local snapshot.