Statistical Reports

Settings During Extract

Indicates whether file system compression is used on the directory for the archive file.

Table and Selection Strategy Information

The statistical information for a step includes:

DBMS

Name of the DBMS for the processed table.

Version

DBMS version number.

Columns

Number of columns in the table.

Cycle

Yes/No indicator that the table is in a traversal cycle.

LOBs

Yes/No indicator that the table contains LOB columns.

Rows

An estimate, based on DBMS statistics, of the number of rows in the table. (Value is N/A if DBMS statistics are not available.)

Row Length

Number of bytes per row.

DB Connections

Number of database connections used to process rows in the table.

Fetch Buffer Size per Connection

The buffer size allocated for each database connection.

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.

PK W/Index

N/A for the start table or the number of indexed primary key values for the start table that are looked up during processing.

PK WO/Index

N/A for the start table or the number of unindexed primary key values looked up during processing.

FK W/Index

Number of indexed foreign key values looked up during processing.

FK WO/Index

Number of unindexed foreign key values looked up during processing.

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:

No Keys

Any needed key values were looked up in a previous step.

Only One Key

The process defaulted to the key lookup method because there is only one value for selecting rows.

Key Lookup

The process defaulted to the key lookup method because all keys have DBMS indexes.

Scan - No Index

The process defaulted to the scan method because all keys do not have DBMS indexes.

Scanning due to large number of keys

The process defaulted to the scan method because a significant portion of the table must be accessed.

User Forced Scan

The process used a forced scan.

User Forced Key Lookup

The process used a forced key lookup method.

Where Clause

Any user-specified condition.

A process generally uses 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, the process defaults to a scan, even if an index is available. A scan is also used with generalized selection criteria (for example, ORDER_ID < '100'). If the default method is undesirable from the standpoint of your needs and database configuration, you can override it. To override, use the Relationships tab on the Access Definition Editor. You can also force the process to default to a scan by using the Primary Key or Relationship Index Analysis feature to establish needed indexes.

Key Information

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:

Lookup Keys

Number of key values used to select rows.

Direction

Direction of traversal as:

dependent

From parent to child

parent

From child to parent

Indexed

Yes/No indicator for DBMS index.

Keys Per Cursor

User-specified number of key values included in the WHERE clause for each key lookup.

DB2® Lookup Cost

Estimated cost, in floating point format, of the key lookup method for the traversal, based on existing RUNSTATS for the table. Only available for DB2 for Linux®, UNIX, and Windows database management systems.

DB2 Scan Cost

Estimated cost, in floating point format, of scan method for the traversal, based on existing RUNSTATS for the table. Only available for DB2 for Linux, UNIX, and Windows database management systems.

Key Length

Number of bytes per key.

Access

The access method used to select rows in the table as:

Not Forcing

Default key lookup or scan method was used.

User Forced Scan

Scan method selected in access definition.

User Forced Key Lookup

Lookup method selected in access definition.

Lookup SQL

The key lookup portion of the WHERE clause used to select rows in the table, in the form columnname = hostvariable.

If indexes are not present, you can use the relationship index analysis to establish them. (See index analysis for a description of the relationship index analysis.)

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, seeKey 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. In the preceding example, the cursor/fetchloop is opened 20 times.

DBMS Access Information

Information regarding database access is provided to the DBMS for each SELECT request that is executed during the processing step. For example, you might use generalized criteria such as ORDER_ID < '100' and Point and Shoot to select start table rows for an extract process. In this case, the statistical report for the processing step includes DBMS access entries for a key lookup to select the Point-and-Shoot rows and a scan to select rows that match the generalized criteria.

DBMS Access Information includes:

Access Type

Method used to select rows:

Cursor Scan

A single cursor was used to read all rows in the table. Any user-specified selection criteria was included in the WHERE clause for the SELECT statement.

PK Lookup

Traversal was from child to parent and the key lookup method was used.

FK Lookup

Traversal was from parent to child and the key lookup method was used.

Keys Per Cursor

Number of key values included in the WHERE clause for each key lookup.

Open Cursor

Number of times a cursor was opened for the SELECT statement.

Rows Fetched

Number of rows fetched for the SELECT statement.

Rows Written

Number of rows written to the archive or extract file.

Process Time

Time elapsed from the first OPEN CURSOR to the last CLOSE CURSOR in the step.

Time in DBMS

Percentage of process time for the step that is spent in DBMS calls.

Time in DBMS Actions

Percentage of process time for the step that is spent processing Actions defined for archive process.

Rows Per Sec

Number of rows extracted per second of process time.

Delete Impact Analysis

Status

Status of indexes for each relationship:

Full

An index exists with the complete set of required columns.

None

No index exists with the required columns.

Partial

One or more indexes exist with only a partial set of the required columns.

Estimated Storage Savings

Storage saved as a result of deleting the table or index. Savings are calculated by multiplying the average row length for the table by the number of rows deleted. For a deleted index, Optim computes the sum of columns in the index and multiplies that value by the total number of rows deleted.

Compression Statistics

When compression options are used in an archive process, those statistics are included in the statistical information. Archive file and individual table compression values are shown as described in the following.