The Extract Process generates an Extract Process Report
that provides general information and statistics about the Extract
Process.
The Extract Process Report displays the following information:
- Name of the Extract Request (or “Untitled” if you
did not save the request).
- Name of the Optim™ Server,
or “(Local)” for a client workstation.
- Name of the generated Extract File or Extract File
segments.
- Name of the Access Definition for the Extract Request
or “LOCAL”.
- Indicator (“Skipped” or “Processed”) for processing
file attachments.
- User IDs of the user requesting the Extract Process.
- Date and time the Extract Process started.
- Date and time the Extract Process completed.
- The elapsed time.
Extract Process Warnings
A list of any warnings or errors that occur during processing
is provided.
Process Summary
Statistics
for the data extracted are provided:
- Total number of Tables Processed.
- Total number of Rows Extracted.
- Total number of Rows with Errors.
- Total number of First Pass Table Rows.
Object Details
The
number of objects copied to the Extract File or “Not Selected” if
the object was not selected in the Extract Request.
Row Details
Statistics
for each table are provided:
- Total number of rows extracted from each table.
- Total number of failed rows for each table.
- Names of tables used in the Extract. The tables
are listed in the same order as in the Access Definition.
Statistical Information
If
you selected the Generate Statistical Report check
box on the Extract Request Editor, detailed performance
information for each step in the traversal path is displayed at the
end of the report. (The steps correspond to those displayed using
the Show Steps command, available from the Tools menu
in the Access Definition Editor.)
Each step consists of a Table entry, and may include
one or more Relationship entries, Primary Key entries, or DBMS Access
entries.
Note: Statistical information may indicate whether you
can improve performance by overriding the default method (scan or
key lookup) of accessing a table. For details, see the Common Elements Manual.
Settings During Extract
Indicates if file system compression is used on the
directory containing the Extract File.
Table
A Table
entry provides general table information and the strategy used to
process key values for extracting related rows. The title line for
a Table entry displays the table name and step number. (If the table
is a reference table, "Ref Table" is shown instead of the step number.)
- DBMS
- Type of DBMS associated with the table.
- Version
- Version number of the DBMS.
- Columns
- Number of columns in the table.
- Cycle
- Indicator (Yes or No) that the table is part of
a traversal cycle.
- Lobs
- Indicator (Yes or No) that the table contains large
objects.
- Est. Rows
- Estimated number of rows in the table. (N/A indicates
that DBMS statistics are not available.)
- Row Length
- Size of the row in bytes.
- DB Connections
- Number of concurrent database connections used to
extract data from the table.
- Select w/ UR
- Indicator (Yes or No) that uncommitted rows were
extracted from the table.
- PK W/Index
- Number of primary key values to process; the primary
key has a supporting index.
- PK WO/Index
- Number of primary key values to process; the primary
key does not have a supporting index.
- FK W/Index
- Number of foreign key values to process; the foreign
key has a supporting index.
- FK WO/Index
- Number of foreign key values to process; the foreign
key does not have a supporting index.
- Parent Strategy
- Identifies the strategy (scan or key lookup) used
to process primary key values for extracting related rows.
Note: A
scan reads all rows in a table at one time; whereas, a key lookup
locates rows using a WHERE clause to search for key values.
- No Keys
- No key values to process.
- Only one Key
- Use a key lookup, since there is only one key value
to process.
- KeyLookup - All Keys indexed
- Use a key lookup, since all keys have supporting
indexes.
- Scan - No Index
- Use a scan, since one or more keys do not have supporting
indexes.
- Scanning due to large number of keys
- Use a scan, since the number of key values to process
is a significant portion of the table.
- User Forced Scan
- Use a scan, as specified by the user on the Relationships tab
in the Access Definition Editor.
- User Forced Key Lookup
- Use a key lookup, as specified by the user on the Relationships tab
in the Access Definition Editor.
- Dependent Strategy
- Identifies the strategy (scan or key lookup) used
to process foreign key values for extracting related rows. (The same
values shown above for Parent Strategy are
valid for Dependent Strategy.)
- Where Clause
- Identifies user-specified criteria, if any.
Relationship or Primary Key
A Table entry may include one or more Relationship entries,
if the table is a child table in a relationship with a previously
processed table, or one or more Primary Key entries, if the table
is the parent table in a relationship with a previously processed
table.
The title line for a Relationship entry
displays the name of the relationship used to traverse from parent
to child during the Extract Process, and the entry indicates the number
of foreign key values used to extract related child rows. A Primary
Key entry indicates the number of primary key values used to extract
related parent rows.
- Lookup Keys
- Number of key values from related rows already extracted.
- Direction
- Indicates the direction of the traversal path:
- dependent
- Traverse from parent to child for a Relationship
entry.
- parent
- Traverse from child to parent for a Primary Key
entry.
- Indexed
- Indicator (Yes or No) that a supporting index is
available.
- Keys Per Cursor
- Number of key values to include in the WHERE clause
when using a key lookup. For example, if the key has a single column
(COL1) and Keys Per Cursor is 5, then the WHERE
clause would include “COL1 IN (?,?,?,?,?)” and 5 key values would
be processed in a single request to the DBMS.
Note: By default, Move
processes 1 key value at a time. You can increase this value on the Relationships tab
of the Access Definition Editor. (For details,
see the Common Elements Manual.)
- DB2® Lookup
Cost
- The DBMS estimated cost to process a single key
value, if the DBMS is DB2 or
UDB and RUNSTATS have been run for the table.
- DB2 Scan Cost
- The DBMS estimated cost to scan the entire table,
if the DBMS is DB2 or UDB and
RUNSTATS have been run for the table.
- Key Length
- The length of the key value.
- Access
- Indicates whether the method of accessing the table
was forced.
Note: By default, Move determines whether to use a scan
or a key lookup. You can specify a forced scan or key lookup on the Relationships tab
of the Access Definition Editor. For details,
see the Common Elements Manual.
- Not Forcing
- User did not force a scan or a key lookup.
- User Forced Scan
- User forced a scan.
- User Forced Key Lookup
- User forced a key lookup.
- Lookup SQL
- The SQL condition, including the key column names
and host variables, used to process a single key value.
DBMS Access
A
Table entry may also include one or more DBMS Access entries, which
provide information about the database access to the table. A DBMS
Access entry is provided for each SELECT statement used to process
related rows in the table.
- Access Type
- Indicates the method used to access the table:
- Cursor Scan
- Use a single cursor to read all rows in the table.
Selection criteria, if specified, is included in the WHERE clause
of the SELECT statement.
- PK Lookup
- Use a key lookup to extract rows for one or more
primary key values.
- FK Lookup
- Use a key lookup to extract rows for one or more
foreign key values.
- Keys Per Cursor
- If Access Type is PK Lookup
or FK Lookup, the number of key values processed
using a single SELECT statement. (Note that if Access Type is Cursor
Scan, this value is zero (0).)
- Open Cursor
- Number of times a cursor is opened for the SELECT
statement.
- Rows Fetched
- Number of rows fetched for the SELECT statement.
- Rows Written
- Number of rows written to the Extract File.
- Process Time
- The elapsed time from when the cursor was first
opened to when the cursor was last closed for the SELECT statement.
- Time in DBMS
- The elapsed time spent in calls to the DBMS.
- Rows Per Sec
- Number of rows extracted per second of elapsed time.
Totals
- Elapsed Time
- The total amount of elapsed time for all steps.
- Time in DBMS
- The
total amount of time spent in calls to the DBMS for all steps.
To save the report to a file, click to
open the Windows Save dialog.
To
print the report, click to open the Print dialog.
To
refer to the report after you close it, click to redisplay the report from the last Extract Process
run, or All to display a list of retained reports.
For details about retaining process reports, see the Common Elements Manual.