A Statistical Report provides detailed performance information about an Archive, Extract, or Delete Process.
The Statistical Report for an Archive or Extract Process is organized according to the steps in the process, as it proceeds from table to table. These steps coincide with the Show Steps report obtained from the Access Definition Editor. You cannot obtain a Show Steps report for a Delete Process and the Statistical Report for a Delete Process is organized by table.
For each Archive or Extract step, the Statistical Report provides basic information about the table that is processed and the number of key values used to retrieve rows in the table. The report also includes information regarding Relationships with previously processed parent tables and Primary Keys for the Start Table or previously processed child tables. Finally, DBMS Access statistics provide information about the actual database access to the table.
The Table and Selection Criteria, Key, and DBMS Access information in the Statistical Report, together with DBMS tools and statistics, allow you to tune your Archive or Extract Processes to more nearly meet your performance or the requirements within the context of your processing strategy. You may need to run a sample process several times, adjusting the selection strategy, analyzing and adding DBMS indexes, and experimenting with the number of keys per cursor to find the optimal combination for your database and performance needs.
For each table in a Delete Process, the Statistical Report provides detailed processing information as well as basic information about the table that is processed.
A bold header indicates the Step number and the Table name. A separate step is shown for each instance in which a table is accessed in a processing cycle. (Ref Table), instead of the step number, is shown for a Reference Table.
Indicates if file system compression is used on the directory containing the Archive File.
The statistical information for a Step includes:
For each table, one of the following statistics is provided, according to the relationship with the table processed in a previous step. For example, if the step processes the Start Table or the traversal is from child to parent table, a PK value is shown. If the traversal is from parent table to child table, an FK value is shown.
Finally, the strategy and any user-provided criteria used to select rows is identified. For the Start Table or a traversal from child table to parent table, a Parent Strategy is shown. If traversal is from parent table to child table, a Dependant Strategy is shown. In either case, the strategy used for selecting rows by key value is one of the following:
When evaluating this information, note that the process will generally use a key lookup if a DBMS index is available and a scan, if one is not. If a significant portion of the table must be accessed or generalized selection criteria is used (for example, ORDER_ID < '100'), the process defaults to a scan, even if an index is available. If the default method is undesirable from the standpoint of your database needs and configuration, you can override it from the Relationships tab on the Access Definition Editor, or force the process to default to a scan by using the Primary Key or Relationship Index Analysis feature to establish needed indexes.
Information about keys used to select rows in the table is also provided. This information indicates the number of key values for which the table was searched. For the Start Table or a traversal from child table to parent table, Primary Key information is shown. If traversal is from parent table to child table, Relationship information, with the name of the relationship, is shown. The key information includes:
If indexes are not present, you may want to use the Relationship Index Analysis feature to establish them. (See Index Analysis for a description of the Relationship Index Analysis function.)
You might also want to modify the Keys Per Cursor value. The Key Lookup method uses an SQL WHERE clause to select rows. For example, if 100 rows with unique customer IDs are processed, the WHERE clause in the SELECT statement includes CUST_ID = hostvar. By default, a cursor/fetchloop is opened once per key or 100 times, in the example, with a single key value as the host variable for each. However, you can change the Key Lookup Limit to override the default Keys Per Cursor setting. (For more information, see Key Lookup Limit). If this setting is 5, the host variable includes five key values for each lookup and a cursor/fetchloop is opened for every five keys. For example, in the above example, the cursor/fetchloop is opened 20 times.
Information regarding database access is provided for each SELECT request to the DBMS that is executed during the processing step. For example, if you use both generalized criteria such as ORDER_ID < '100' and Point and Shoot to select Start Table rows for an Extract Process, the Statistical Report for the processing step will include two DBMS Access entries, one for the key lookup to select the Point-and-Shoot rows and one for the scan needed to select rows that match the generalized criteria.
DBMS Access Information includes:
Statistical information for a Delete Process can be included in the Delete Process Report or in the Archive Process Report, if specified.
For each table in a Delete Process, the Statistical Report provides detailed processing information as well as basic information about the table that is processed. The Delete Impact Analysis section shows the estimate amount of storage saved as a result of the deletion. This estimate does not account for any additional DBMS storage being used to manage the row data.
A bold header indicates the table from which rows are deleted. At the end of the report the totals for the process are displayed. This statistical information can indicate whether you can improve performance by overriding the default method (scan or key lookup) of accessing a table. (For details on overriding the default method, see Table Access Strategy.)
When compression options are used in an Archive process, those statistics are included the Statistical Information. Archive File and individual table compression values are shown as described below.