Using the Product in a DB2 Environment › Background DB2 Catalog Extraction › Synchronous Data Gatherer › Use Summarization for Harvested Data

Use Summarization for Harvested Data

The Synchronous Data Gatherer harvests SQL statements as they pass through the DB2 system. When a statement appears to be the same statement as the previous call, the information is summarized until a new, un-matching, statement is encountered. This process works efficiently as long as like statements appear one after another, such as repeated FETCHs. When an application produces calls that repeat in a larger cycle, such as FETCH followed by UPDATE, the amount of data collected can be extensive.

To assist in reducing the impact of repetitive SQL statements, the Synchronous Data Gatherer provides a table summarization function for the harvested data. The size of table is determined by the value supplied by the DB2HVSUM command in the TUNSSP00 member.

DB2HVSUM

Allow for the construction of an internal table that will be used to summarize repetitive harvested DB2 data.

The numeric value specified represents the number of data rows that will be stored internally. Each time the Harvester is activated, this table will be searched sequentially to determine if there is a like row present. If the key values of the row are equal, then the data is added to the existing row. If no matching row is found, then a new row is added to the table. When the table is full, the row at the bottom of the table is sent to the monitor file and the new row is placed into the table at that location.

The number of slots is an arbitrary value that should be derived by an estimation of the looping process within a DB2 application and the number of times that application will execute during the monitor cycle. If the largest program executes 25 calls to DB2 before looping back to execute the 25 calls again and is executed many times in a row, then a value of DB2HVSUM=25 would insure that the calls for the application that were made during the monitor session would be summarized before being written.

Part of the summarization key is the SQL statement itself, so that uniquely dynamic calls will not benefit from summarization.

When SQL records are requested along with summarization, the SQL is only written once to reduce the amount of data written.

The value range that can be entered for DB2HVSUM is 0 through 9999.

The default is DB2HVSUM=0.