Extract Processing

• The Access Definition is evaluated. If the Access Definition has an invalid entry, an appropriate error or warning is displayed. (Details are discussed later in this section.)
• The Extract File is located and its attributes evaluated to ensure that the named file is suitable as an Extract File. If it is not suitable, an error message is displayed and you are prompted for the name of a valid Extract File. If the Extract File does not exist, you are prompted for allocation information and Move allocates the file. (For a description of the allocation prompts, see the Optim™ Common Elements reference guide.)
• While the Extract Process is executed online, a status report is displayed and updated periodically.
• An Extract Process Report is generated and displayed for browsing.

• The Access Definition is evaluated. If the Access Definition contains an invalid entry, an appropriate error or warning is displayed. (Details are discussed later in this section.)
• The Extract File is located and its attributes evaluated to ensure that the named file is suitable as an Extract File. If it is not suitable, a message is displayed and you are prompted for the name of a valid Extract File.

  If the Extract File does not exist, you are prompted for allocation information and Move allocates the file. (For a description of the allocation prompts, see the Optim Common Elements reference guide.)

• The JCL and Batch Utility control statements for the batch process are built. If you have entered Review at the If Batch, Review or Save JCL prompt, the JCL and control statements are displayed. (You can modify the JCL and control statements or save them and execute the job later. Details are discussed later in this section.)

  If you have entered Save at the If Batch, Review or Save JCL prompt, you are prompted for the name of the file in which to save the JCL and control statements and to indicate whether the job should be submitted after saving. (Details are discussed later in this section.)

• The Extract Process is executed as a batch job. Note the Extract File must be located again when the process is executed.
• An Extract Process Report is generated and stored in the default output file specified in the JCL.

Certain error conditions in the Access Definition prevent the Extract Process from proceeding. Other conditions trigger warning messages.

Error Conditions

Database changes can be detected only when the Access Definition is edited or used. Thus, error conditions are most common when an Access Definition is used without prior review. The following error conditions also prevent you from saving an Access Definition after editing it. The error conditions include:

• Duplicate references in the Table List. A table can be referenced only once. You cannot list a table with one or more views, synonyms or aliases of that table, or list more than one view, synonym or alias for a table. To proceed with the Extract Process, remove any duplicate references.

• Invalid Tables in the Table List. The Table List in the Access Definition does not reference any valid tables. This condition can occur when tables are dropped from the database. Also, any table names that are not fully qualified may not reference valid tables when the default Creator ID is changed. To proceed with the Extract Process, correct the table names in the Access Definition.
• Invalid WHERE clause. Selection criteria for one or more tables are invalid. This condition can occur if tables have been changed since the Access Definition was created. (For example, a column referenced in an SQL WHERE Clause has been dropped.) This condition can also occur if the default value you specify for a substitution variable is the incorrect data type or size for the column, or does not conform to SQL syntax. To perform the extract, change the selection criteria or correct the default value for the substitution variable.
• Invalid Start Table. This condition can occur if the table is dropped from the database or the combination of default Creator ID and the unqualified Start Table name do not reference an actual table. (The status for the table is shown as UNKNOWN and it cannot be used as a Start Table.)
• Missing Primary Key. The primary key for a table that is visited more than once in the process is missing. The primary key ensures that multiple copies of a row are not extracted.
• Unauthorized User. The user initiating the Extract Process is not authorized to select data from a table included in the process. (A site and user option allows unauthorized users to create JCL for the Extract Process. For more information, see the the Optim Common Elements reference guide.)

Warnings

A warning indicates a condition that may require your attention, but does not prevent the Extract Process from proceeding. A warning gives you the option of proceeding or aborting the Extract Process. Warnings are issued for the following conditions:

• Relationship in NEW status. A relationship has been added and you have not explicitly selected or unselected it for processing. You can view the relationship list on the Specify Relationship Usage panel and select or unselect individual relationships. The default for NEW relationships is selected or unselected, depending on the Use NEW Relationships setting on the Access Definition Parameters panel. In general, it is advisable to review NEW relationships before using them.
• Relationship in UNKNOWN status. The Default Creator ID for tables referenced in the Access Definition has been modified, causing a relationship to no longer apply.
• Table in UNKNOWN status. A table has been dropped from the database or the Default Creator ID for tables referenced in the Access Definition has been modified, resulting in a reference to a table that does not exist.
• A table in the Access Definition is not traversed. This condition indicates that a relationship does not exist or is not selected to provide a path to this table.
• A relationship in the Access Definition is not traversed. This condition indicates that a relationship is unused in the process.
• RUNSTATS have not been run for a table in the Access Definition. If the table is large, this condition can affect performance when the process is executed.
• A view, synonym, or alias is to be extracted, but object definitions for objects other than primary keys and relationships have been requested. These other object definitions will not be extracted for views, synonyms, or aliases.

Error and Warning Reporting

If one or more error or warning conditions are encountered, the EXTRACT Errors & Warnings panel is displayed. Any error prevents the Extract Process from proceeding. The Extract Process can continue despite warnings.

You may use the SHOW STEPS command to display additional information about how the Extract Process will proceed.

In the following figure, the EXTRACT Errors & Warnings panel is displayed. Three warnings are documented.

 +------------------- EXTRACT Errors & Warnings ------------------+
 |                                                                |
 | EXTRACT Process Can Proceed Despite the Following Warnings:    |
 |   1 Table(s) in UNKNOWN Status                                 |
 |   2 Table(s) will not be Traversed (See SHOW STEPS)            |
 |   1 Relationship(s) will not be Traversed (See SHOW STEPS)     |
 |                                                                |
 | Press ENTER Key to Proceed Despite Warnings                    |
 | Enter END Command to Return to EXTRACT Menu to Correct Problem |
 |                                                                |
 +----------------------------------------------------------------+

Point-and-Shoot Validation

If specified, the data set containing the primary key values for selected rows using Point-and-Shoot is checked as part of the validation of the Access Definition.

A problem is encountered when:

• The Point-and-Shoot file cannot be found.
• The contents of the file specify primary key values for rows that cannot be found.

If a problem is encountered, you are prompted to specify how to proceed. You can continue processing without using the Point-and-Shoot values or respecify the Point-and-Shoot file name.

For batch execution, Move builds the necessary JCL and Batch Utility control statements. The JOB card information is taken from the JCL specified on the Job Card and Print Options panel.

If you entered YES at the prompt, Prompt for Changes Before Job Submission on the Job Card and Print Options panel, the default job card as indicated on that panel is displayed prior to job submission. You may edit the job card and specify whether changes are to apply to the current job only or are to be applied permanently. (For details, see the Optim Common Elements reference guide.)

The information on the Job Card and Print Options panel is used, along with the extract parameters, to build the JCL and control statements required to perform the Extract Process. If you enter Review at the prompt If Batch, Review or Save JCL on the Specify EXTRACT Parameters and Execute panel, the complete JCL and control statements are displayed in the ISPF editor. You can edit and save the JCL and control statements. (See the Batch Utilities Guide for the EXTRACT statement keywords and values.)

When you have completed reviewing the JCL and control statements, you can submit the job. If you have set the option so that jobs are automatically submitted when END is used, the job is submitted. Otherwise, you will have to explicitly SUBMIT the job from the ISPF editor. (For information on establishing whether jobs are automatically submitted when END is used, see the Optim Common Elements reference guide.)

If you do not want to submit the job, use CANCEL to return to the Specify EXTRACT Parameters and Execute panel. You can modify the specifications or cancel the Extract Process from this panel.

If an error in the job card is encountered, a message is displayed. You can review the job card and correct the error or terminate the Extract Process.

If you save the generated batch job to a data set, you can submit the job directly from the ISPF editor instead of from within an online session. When you do so, you can override the default Creator ID, selection criteria, and SQL WHERE clause defined in the Access Definition used for the Extract Process. This is especially useful when several extracts are to be performed for a set of tables that vary only by Creator ID or by some set of selection criteria.

You can override these values for batch execution by using the PSDFOVRD DD statement to point to the desired overrides. (Examples follow the discussion of the overrides.)

UNL_IMAGECOPY_DSN

To override the Image Copy DSN parameter for an unload program, specify:

UNL_IMAGECOPY_DSN     image.file.dsn

UNL_IMAGECOPY_DATE

To override the Image Copy Date parameter for an unload program, specify:

UNL_IMAGECOPY_DATE     yyyy-mm-dd

UNL_IMAGECOPY_TIME

To override the Image Copy Time parameter for an unload program, specify:

UNL_IMAGECOPY_TIME     hh.mm.ss

UNL_IMAGECOPY_SELECT

To override the Image Copy Criteria parameter for an unload program, specify:

UNL_IMAGECOPY_SELECT     { A | B | L | S }

UNL_OBID

To process an Image Copy data set created on a different subsystem, specify:

UNL_OBID    [cid.]tblname  obid

cid.tblname

The table name must be specified. If you do not specify the Creator ID (cid), the default Creator ID defined in the Access Definition is assumed.

obid

The Db2® Object Identifier must be specified and is used to generate the OBID parameter or the ORIGINOBID parameter on the UNLOAD statement. See the appropriate BMC or IBM® reference manual for more information.

COMMIT_COUNT

To override the commit count that was specified when the job was created, specify:

COMMIT_COUNT   value

The value can range from zero to the site limit.

COMMIT_MINUTES

To change commit processing from number of updates to elapsed time, specify:

COMMIT_MINUTES   value

The value is specified in minutes and will override the commit count. The value can range from 1 to 1440. The process report will be changed to reflect the change from the number of updates to elapsed time.

DEFCID

To override the default Creator ID in the Access Definition, specify:

DEFCID     cid 

cid

The default Creator ID to be used. This applies only to tables that are not explicitly qualified in the Access Definition.

Only one DEFCID parameter may be specified for an Extract Process. This override also affects the names of the tables in the relationships on the Relationship Usage list. If a relationship is not found for the updated table name, an error occurs when the extract is performed.

SEL

To override the selection criteria in the Access Definition for a table or to specify selection criteria for a table that does not have selection criteria in the Access Definition, specify:

SEL     [cid.]table  column  [ selcriteria ]           

cid.table

The table name is required. If you do not specify the Creator ID (cid.), the default Creator ID defined in the Access Definition is assumed.

column

The column name is required.

selcriteria

The selection criteria. This begins with an SQL operator. This specification is limited to 53 characters.

If you do not specify selection criteria, any selection criteria in the Access Definition is ignored for the current Extract Process.

Selection criteria can be specified for one or more columns in the table, but each criteria must apply to a different column.

You can specify only one SEL parameter for each column in a table. You can specify selection criteria overrides for as many columns in as many tables as you want as long as a separate SEL parameter is provided for each column.

SQL

To override the SQL WHERE Clause in the Access Definition for a table or to specify an SQL WHERE Clause for a table that does not have one in the Access Definition, specify:

SQL    [cid.]table  [ /correlation/ ]  [ where ]

cid.table

The table name must be specified. If you do not specify the Creator ID (cid.), the default Creator ID defined in the Access Definition is assumed.

/correlation/

Add or change a correlation name. If you specify a correlation name, it must immediately follow table and be enclosed in slashes.

where

The SQL WHERE Clause. This must conform to the requirements specified for the Specify SQL WHERE Clause panel. However, the keyword WHERE is not required and the specification here is limited to a maximum of 425 lines.

If you do not specify an SQL WHERE Clause, any SQL WHERE Clause specified in the Access Definition is ignored for the current Extract Process.

You can specify the WHERE Clause override for more than one table as long as a separate SQL parameter is provided for each. You can specify only one SQL parameter for a table.

EVERY_NTH_ROW

To override the numeric value in the Access Definition used as a factor for selecting rows from a table, specify:

EVERY_NTH_ROW    [cid.]tblname   value    

cid.tblname

Table name is required. If you do not specify the Creator ID (cid), the default Creator ID defined in the Access Definition is assumed.

value

A numeric value to specify a sampling factor for a table (tblname). Valid values are 1 through 65,535.

ROW_LIMIT

To override the numeric value in the Access Definition used to limit the number of rows selected from a table, specify:

ROW_LIMIT    [cid.]tblname   value    

cid.tblname

Table name is required. If you do not specify the Creator ID (cid), the default Creator ID defined in the Access Definition is assumed.

value

A numeric value to limit the number of rows selected from a table (tblname). Valid values are 1 through 4,294,967,295.

VAR

To override the default value of a substitution variable assigned in the Access Definition, specify:

VAR    varname   value        

varname

The name of the substitution variable assigned in the Access Definition. A colon (:) in front of VarName is optional.

value

The value for the substitution variable. You must enclose the value in single quotes if the variable is for a CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, BINARY, VARBINARY, DATE, TIME, or TIMESTAMP column.

Note: If you specify a column name for the default value, do not enclose the value in quotes.

GROUP

To override the Group Selection Processing in the Access Definition for the Start Table or to specify Group Selection Processing when it has not been defined, specify:

GROUP    [column  VALUES=values  ROWS=rows]  

column

The name of the column in the Start Table for which Group Selection Processing is requested. The column must exist in the Start Table.

values

The number of distinct values to select for the specified columns.

The value must be in the range 1 and 4,294,967,295. Specify an asterisk to obtain a specific number of rows from all distinct values of the column. (For additional information on Group Selection Processing, see the Optim Common Elements reference guide.)

rows

The number of rows to select for each value of the specified column.

The value must be in the range 1 and 4,294,967,295. Specify an asterisk to obtain all rows for each distinct value of the selected column.

Only one asterisk may be specified, therefore an asterisk cannot be specified for both values and rows.

This specification can be used with most selection criteria. However, the Every Nth specification is ignored.

Specify GROUP with no operands to ignore the Group Selection specifications in the Access Definition for the current Extract Process.

UNKNOWN

To override the default treatment for objects referred to in the Access Definition that no longer exist, specify:

UNKNOWN    { FAIL | ALLOW }

FAIL

Terminate the Extract Process if any tables or relationships named in the Access Definition have become unknown since the batch process was initiated. The report contains a message noting the first unknown object. FAIL is the default.

ALLOW

Bypass the unknown tables and relationships and continue with the Extract Process. The report contains a message listing the unknown objects.

Since the Extract Process uses existing tables and the existing relationships between those tables to traverse the database, be aware that if an unknown table or relationship is bypassed, “related” tables in the Access Definition may not be included even when these tables exist.

UNKNOWN ALLOW is typically used when you override the default Creator ID with DEFCID. Changing the default Creator ID may result in naming tables that do not exist. The UNKNOWN ALLOW parameter lets you direct the Extract Process to skip these “unknown” tables.

WITH_UR

To override the extracting of uncommitted data from the database during the Extract Process, specify:

WITH_UR    { Y | N }

Use the Y operand to extract uncommitted data from the database. Use N to only archive committed data.

Rules for Parameters

The following rules apply when specifying these parameters:

• One or more parameters may be specified in the JCL.
• Each parameter keyword must begin in the first space of the line.
• The qualifiers for the parameters must be space separated.
• If a parameter spans multiple lines, continue on the next line.
• You can comment the parameter list by specifying an asterisk, *, in the first position of each comment line.
• If multiple selection criteria are specified, they are ANDed or ORed based on your response on the Specify Selection Criteria panel.
• If both an SQL WHERE Clause and selection criteria are specified for an individual table, the clauses are ANDed.
• You can store the parameters in a sequential file or a partitioned data set. The record length should be 80. If it exceeds 80, only the first 80 characters are processed. Sequence numbers are not allowed.

The parameters should conform to the same syntax required when specified directly in the jobstream.

To override the default Creator ID and specify selection criteria for two of the tables in the extract, insert in the JCL:

//PSDFOVRD DD *
DEFCID PSTDEMO2
* LIMIT SELECTION TO CUSTOMERS IN NEW JERSEY
* WHO HAVE ORDERS FOR WHICH THE
* FREIGHT CHARGES EXCEEDED $50.00
SEL CUSTOMERS STATE ='NJ'
SEL ORDERS FREIGHT_CHARGES >50.00

SEL requires at least one space between the column name and the selection criteria.

1. To override the SQL WHERE Clause for one table in the extract, insert in the JCL:

   //PSDFOVRD DD *
   * LIMIT SELECTION TO CUSTOMERS IN PRINCETON,
   * NEW JERSEY
   SQL PSTDEMO2.CUSTOMERS WHERE CITY=
    'PRINCETON' AND STATE='NJ'

2. To specify a sequential file named PST.SAMPLE.PARMS as the source of the parameter list, insert in the JCL:

   
   //PSDFOVRD DD DSN=PST.SAMPLE.PARMS,DISP=SHR

3. To use an Image Copy file named PST.IMAGE.COPY in an Extract Process utilizing an unload program, insert in the JCL:

   
   //PSDFOVRD DD *
   * CHANGE IMAGE COPY SPECIFICATION TO
   * A PARTICULAR DATASET
   UNL_IMAGECOPY_SELECT S
   UNL_IMAGECOPY_DSN PST.IMAGE.COPY

You can save the JCL and Batch Utility control statements, modify them, and execute the process without re-invoking Move. Specify S to the prompt, If Batch, Review or Save JCL. The following prompts for the information to save the JCL and control statements.

+---------------------------- Save JCL Parameters --------------------------+
|                                                                           |
|  DSN to Save JCL to     ===>                                              |
|  Member (if PDS)        ===>                                              |
|  Replace Existing Data  ===>       Y-Yes, N-NO                            |
|                                                                           |
|  DSN to Hold SYSIN Data ===>                                              |
|  Member (if PDS)        ===>                                              |
|  Replace Existing Data  ===>       Y-Yes, N-NO                            |
|                                                                           |
|  Submit JCL, or Review  ===>       S-Submit, R-Review, N-Neither          |
+---------------------------------------------------------------------------+

This panel includes:

DSN to Save JCL to

Name of the sequential file or partitioned data set to receive the JCL and control statements.

If you specify a partitioned data set, specify the member name in Member.

Member (if PDS)

Name of the member in the partitioned data set specified at DSN to Save JCL to. If you specify a member name for a sequential file, an error message is displayed.

Replace Existing Data

Specify whether the generated JCL and control statements replace existing data in the specified file.

DSN to Hold SYSIN Data

Name of the sequential file or partitioned data set to hold SYSIN data.

If you specify a partitioned data set, specify the member name in Member.

Member (if PDS)

Name of the member in the partitioned data set specified at DSN to Hold SYSIN Data. If a sequential file is specified and you specify a member name, an error message displays.

Replace Existing Data

Specify whether the generated JCL and control statements replace existing data in the specified file.

Submit JCL or Review

Specify whether the JCL and control statements are saved and submitted, displayed for review, or both.

If you select Submit, the JCL and control statements are saved and the job is submitted. If you select Review, use ISPF facilities to save or submit the JCL and control statements. If you select Neither, the JCL and control statements are saved, but not submitted or displayed for review.

If an unload program is used, the job can only be executed in batch. However, disregard the error messages and return codes in the report for the unload program. You should review the Move Extract Process Report to determine whether the job executed successfully.

For example, the Extract Process uses the unload program only to read the data; the data is not written to DDNAME SYSREC. (The Extract Process uses its own facilities to write the data to the Extract File.) For example, UNLOAD PLUS sets a return code of 4 to indicate no records were written to SYSREC although the Extract Process has performed successfully. As another example, UNLOAD PLUS sets a return code of 12 when the Extract Process was successful but terminated prematurely, because of a user limit for the number of rows from an individual table or for the number of rows extracted for the entire process.

The Extract Process Report always contains appropriate messages. Usually these messages are sufficient. However, the following message may be generated when termination is due to processing of the unload program.

Error detected during execution of the Unload Program. See z/OS® Job Log for the error message.

Before invoking an unload program, Move checks for errors to ensure that the selection criteria adhere to restrictions of the unload program. If an error is encountered, Move displays a message. These restrictions are:

1. Expressions before an operator are limited to a single column name.
2. Subselection is not allowed after an operator.
3. Expressions after an operator are limited to a constant or one of the following terms: NULL, CURRENT DATE, or CURRENT TIMESTAMP.
4. The EXISTS predicate is not allowed.

When an Extract Process is performed online, Move provides a status notification pop-up window. The content of the window varies depending on whether data and/or object definitions are being extracted. In the following figure, data is being extracted.

 +-------------- EXTRACT Process Status -------------+
 |                                                   |
 |                       EXTRACT Process in Progress |
 |                                                   |
 |  Total Number of Extracted Rows: 2053             |
 |                                                   |
 | Processing Table:  FOPDEMO.ORDERS                 |
 |       Total Rows:  523                            |
 |                                                   |
 +---------------------------------------------------+

The total number of rows that have been extracted is displayed. Also, the name of the currently processing table and total rows that have been extracted from that table are displayed. This is revised:

• After every 1000 rows are extracted for each table to display the current total number of processed rows.
• When the extract for one table is complete and the extract for the next table begins.

When object definitions are extracted, the status panel includes a list of the object definitions (primary keys and relationships, indexes, etc.), and the status of each as one of the following:

COMPLETED

The object definition has been extracted.

IN PROGRESS

The object definition has been selected and is in the process of being extracted.

SELECTED

The object definition has been selected but has not been extracted yet.

UNSELECTED

The object definitions have not been selected.