EXTRACT

Edit online

Use an EXTRACT statement to create an Extract File that contains the selected set of related rows from one or more tables and, if requested, the object definitions for those tables. The Extract File is used as input to the Move, Insert, Load, Create, and Convert processes. The Extract File can be used repeatedly and simultaneously by many users.

The EXTRACT statement requires a temporary Access Definition or a permanent Access Definition defined in the Optim™ Directory. You can use keywords to override parameters in the Access Definition and to provide parameters similar to those for the online Extract Process.

Note: If Optim is APF-authorized (a requirement to use an unload utility), to extract IMS data using an EXTRACT statement, you must add the Optim program library (SFOPLLIB) to the IMS Program Libraries list in the IMS environment definition. Refer to the Move User Manual Definitions section.


EXTRACT
  { ACCESS_DEFINITION_DEFINE (parameters) ; |
    ACCESS_DEFINITION group.user.name
      [ DEFAULT_CID cid ]
      [ UNKNOWN { FAIL | ALLOW } ]
      [ SELECT ( [ cid. ]table1,coloperator,column1
          { [ ,critoperator,criteria] | [,DELETE ] }
          [ ,column2{ [ ,critoperator,criteria] | [,DELETE ] } ... ] ) ]
      [ SELECT ( [ cid. ]table2,coloperator,column1
          {critoperator,criteria ] | [,DELETE ] }
          [ ,column2 { [ ,critoperator,criteria  ] | [,DELETE ] } ... ] ) ] ...
      [ SQL ( [ cid. ] table1 [, [ /correlation/ ] whereclause1] ) ]
      [ SQL ( [ cid. ] table2 [, [ /correlation/ ] whereclause2] ) ] ...
      [ VAR ( varname,value ) ]
      [ POINT_SHOOT_DSN explicitfilename ]
      [ POINT_SHOOT_ERROR { STOP | CONTINUE |SUBSET } ]
      [ OBJECT_DEFS { NO | YES } ]
      [ INCLUDE_OBJECT (obj1,obj2,... ) ]
      [ EXCLUDE_OBJECT (obj1,obj2,... ) ]
      [ INCLUDE_DATA { YES | NO } ]
      [ ROW_SELECT_BY { ROWLIST | BOTH } }
  EXTRACT_FILE ( File Allocation Parameters )
  [ UNLOAD_UTILITY { BMC | IBM | CDB | CDBO }
      [ IMAGE_COPY 
         ( MODE { L | A | B | D }  
         [ DATE yyyy-mm-dd ]  
         [ TIME hh.mm.ss ]  
         [ DSNAME dsname ] ) ]
      [ PARTITIONS ( part1 [, part2 [ , ... ] ) ][ OTHER_PARTITIONS_SAME { NO | YES }] ] 
   [ WITH_UR { NO | YES } ]
   [ ROW_LIMIT n ]
   [ DEFAULT_KEY_LIMIT n ]
   [ SKIP_EXT_CATALOG  { YES | NO } ]
   [ REPLACE_EXT_DIR { NO | YES } ]
   [ CONVERT_PERFORM { NO | YES }
     [ CONVERT_SORT { NO | YES } ]
     [ CONVERT_MAX_DISCARDS n ]
     [ CONVERTED_FILE (File Allocation Parameters) ]
     [ CONVERSION_ERROR_RC4 { NO | YES }]
     [ CONTROL_FILE (File Allocation Parameters) ]
     { TABLE_MAP mapid.name | TABLE_MAP_DEFINE (parameters); } 
     [ AGING (parameters) ] ]
   [ REPORT_LEVEL { DETAIL | SUMMARY } ]
 [ EXTRACT_LENGTH_ERROR_RC4 { NO | YES } ]
 [ BYPASS_ROW_SQLCODE_20412 { YES | NO } ]

Access Definition Keywords

Use the following keywords to specify the Access Definition and override or augment various parameters in the Access Definition.

ACCESS_DEFINITION

The name of the Access Definition. ACCESS_DEFINITION must be included in the EXTRACT statement and must precede any keywords that override parameters in the Access Definition (e.g., SELECT, SQL, DEFAULT_CID).

group.user.name: The three-part Access Definition name.

ACCESS_DEFINITION_DEFINE

The Optim online process generates this keyword when it creates an EXTRACT job for batch execution.

When you create an EXTRACT job outside of the Optim online process, the best practice is to use the ACCESS_DEFINITION keyword to refer to a named Access Definition in the Optim Directory.

Place the ACCESS_DEFINITION_DEFINE parameters within parentheses. A semicolon must follow the close parenthesis. (See ACCESS_DEFINITION_DEFINE Parameters for the allowable parameters.)

Note: The ADNAME keyword is generated when you specify a named Access Definition in the online process. This keyword is for documentation purposes only.

DEFAULT_CID

Override for the default Creator ID specified in the Access Definition. When used, this keyword must precede any keyword values that require a default Creator ID (e.g., SELECT).

cid: The default Creator ID.

UNKNOWN

The action taken if the Access Definition references unknown tables or relationships.

FAIL: Terminate processing if any tables or relationships named in the Access Definition are unknown (default).
ALLOW: Bypass unknown tables and relationships and continue processing.

Selection Criteria

SELECT

Selection criteria for rows in a table. Use critoperator and criteria to augment Access Definition selection criteria (according to coloperator) or to override any existing Access Definition selection criteria for the specified column. Use DELETE, instead of critoperator and criteria, to bypass existing Access Definition selection criteria for the specified column.

cid.table

The table name. If you omit the Creator ID, the default Creator ID is used.

coloperator

The operator used to combine selection criteria for multiple columns in a table. You must specify one of the following:

AND: Select data that matches criteria for all columns.
OR: Select data that matches criteria for at least one column.

column

The name of the column. You must specify at least one column operand.

critoperator

Operator for criteria. (See the following explanation for criteria.)

criteria

Up to 250 characters of criteria per column. If the column has a character, graphic, or binary data type (e.g., CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, BINARY, VARBINARY, DATE, TIME, TIMESTAMP, or TIMESTAMP WITH TIME ZONE), the criteria must be delimited with single quotes. If the column has a numeric data type (e.g., INTEGER, SMALLINT, BIGINT or DECIMAL), the criteria must not be in quotes.

Separate criteria and critoperator with a comma.

Note: You must leave a space after a comma that precedes a numeric value if the DB2® setup specifies a comma as the decimal point value.

EQ, criteria
NE, criteria
GT, criteria
LT, criteria
GE, criteria
LE, criteria
IN, (a,b,c,d...)
NOT IN, (a,b,c,d...)
IS NULL
IS NOT NULL
LIKE, pattern
NOT LIKE, pattern
BETWEEN, x AND y
NOT BETWEEN, x AND y

DELETE

Bypass existing selection criteria in the Access Definition for the column. If you use DELETE, you must omit critoperator and criteria for the column.

SQL

An SQL WHERE clause for the specified table. Overrides any SQL WHERE clause specified in the Access Definition. (Omit whereclause to bypass an Access Definition SQL WHERE clause for the specified table.) You can specify multiple SQL statements, but duplicate table names cause an error.

cid.table: The table name. If you omit the Creator ID, the default Creator ID is used.
/correlation/: The optional correlation name must be enclosed in slashes and must immediately precede whereclause. Overrides any correlation name specified in the Access Definition.
whereclause: Lines of criteria as an SQL WHERE clause. Break each line at a blank, start anywhere on the next line, do not use quotes or parentheses, and end the clause with a close parenthesis.
Note: You must leave a space after a comma that precedes a numeric value if the DB2 setup specifies a comma as the decimal point value.

VAR

Override for the default value of a substitution variable assigned in the Access Definition. If a default value was not defined in the Access Definition, you must use the VAR keyword to specify a value, or an error occurs.

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, TIMESTAMP, or TIMESTAMP WITH TIME ZONE column.
Note: If you specify a column name for the default value, do not enclose the value in quotes.

POINT_SHOOT_DSN

The name of a Point-and-Shoot file used to select data to be extracted. Overrides any Point-and-Shoot file specified in the Access Definition.

explicitfilename: The fully qualified name of the Point-and-Shoot file.
Note: You can also specify a Point-and-Shoot file by placing a PSDFPNS DD * data set in the job stream, containing key values to be processed. In this case, omit the POINT_SHOOT_DSN keyword.

POINT_SHOOT_ERROR

Indicate action to be taken when processing the Point-and-Shoot file results in errors.

STOP: Stop the run.
CONTINUE: Continue the run without using the Point-and-Shoot file.
SUBSET: If invalid keys are found, continue the run with the valid keys only. If errors other than invalid keys are found, stop the run.

Object Definitions

OBJECT_DEFS

Indicate whether to extract object definitions (e.g., primary keys, relationships, indexes, views, synonyms, aliases, procedures, triggers, and user-defined types and functions).

NO: Do not extract object definitions (default).
YES: Extract object definitions of all types.

INCLUDE_OBJECT

Include specific object types by name if the OBJECT_DEFS keyword is NO. Specify any of the following values in any order within parentheses:

PKREL: Primary keys and relationships
INDEX: Indexes
VIEW: Views
ALIAS: Aliases
SYN: Synonyms
FPROC: Column Field Procedure Names
TRIG: Triggers
UDEF: User Defined Types and Functions
SPROC: Stored Procedures

EXCLUDE_OBJECT

Exclude specific object types by name if the OBJECT_DEFS keyword is YES. Specify any of the following values in any order within parentheses:

PKREL: Primary keys and relationships
INDEX: Indexes
VIEW: Views
ALIAS: Aliases
SYN: Synonyms
FPROC: Column Field Procedure Names
TRIG: Triggers
UDEF: User Defined Types and Functions
SPROC: Stored Procedures

INCLUDE_DATA

Indicate whether row data from the DB2 tables is to be included in the output file.

YES: Include row data (default).
NO: Omit row data.

ROW_SELECT_BY

Indicate whether selection criteria or Point-and-Shoot List should be used when Access Definition specifies both for the Start Table.

ROWLIST: Apply Point-and-Shoot List only (default).
BOTH: Apply Point-and-Shoot List and selection criteria.

Extract File Keyword

EXTRACT_FILE: EXTRACT_FILE must be included in the EXTRACT statement, and must precede any file processing keywords (e.g., IMAGE_COPY). See File Allocation Parameters

Unload Utility Keywords

Use the following keywords to specify an unload utility:

UNLOAD_UTILITY

The name of the unload utility used to retrieve the data. If omitted, DB2 is used to retrieve the data. This keyword is required to extract data from an image copy.

BMC: Use the UNLOAD PLUS utility.
Note: Support for this utility has been deprecated.
IBM®: Use the High Performance Unload utility.
CDB: Use the Auto-Unload utility.
Note: Support for this utility has been deprecated.
CDBO: Use the Auto-Online Unload utility.
Note: Support for this utility has been deprecated.

Note: You must add the appropriate utility DD statements. See the utility documentation for the required DD statements.

IMAGE_COPY

The image copy file to use as input. If omitted, DB2 files are used as input. This keyword is allowed only if the UNLOAD_UTILITY keyword is specified.

IMAGE_COPY keywords must be enclosed in parentheses. MODE is required; other keywords and operands are optional, depending on the value of MODE.

Note: To extract from DB2 image copy data sets in multiple partitions of the same tablespace stored on the same tape volume, you must manually edit the JCL to allocate the data sets. Multiple image copy data sets cataloged on the same tape volume can not be allocated using dynamic allocation. This is a z/OS limitation. If you attempt to use dynamic allocation, the extract process fails with a dynamic allocation error. Refer to File Allocation Parameters for details.

MODE

The image copy file to use:

L: Use the latest file.
A: Use the first file created on or after the specified DATE and TIME.
B: Use the first file created on or before the specified DATE and TIME.
D: Use the file named in DSNAME.

DATE

Date criteria for the image copy file. Specify the date in this format: yyyy-mm-dd.

TIME

Time criteria for the image copy file. Specify the time in this format: hh.mm.ss. If omitted, a value of 00.00.01 is assumed.

DSNAME

The fully qualified name of the image copy file.

PARTITIONS

The numbers of the partitions to be processed. If omitted, all partitions are processed. This keyword applies only if the UNLOAD_UTILITY keyword is specified and the Start Table is partitioned.

part

The partition numbers, enclosed in parentheses and separated by commas.

Note: You must leave a space after a comma that precedes a numeric value if the DB2 setup specifies a comma as the decimal point value.

You may specify partitions in any order. (Partitions that are not specified are not extracted.)

OTHER_PARTITIONS_SAME

Use this operand to indicate whether to process tables in all partitions or only the tables in the partitions specified in the PARTITIONS operand for the Start Table.

YES: Limit processing to the tables in the partitions specified in the PARTITIONS operand for the Start Table. This assumes that the rows selected from related tables are physically located in the same partitions as the rows selected from the Start Table.

NO: Do not limit processing to the tables in partitions specified in the PARTITIONS operand for the Start Table. If needed, process tables in additional partitions to select rows from related tables. This is the default.

Processing Keywords

Use the following keywords to specify processing options for the Extract File.

WITH_UR

Indicate whether to extract uncommitted data from the database. This keyword is valid only when the Site Option, Use Uncommitted Reads, is set to User. Specify:

NO: Do not extract uncommitted data from the database. This is the default.
YES: Extract uncommitted data from the database.
Note: If you choose to extract uncommitted data, the relational integrity of the data in the Extract File may be compromised. Use caution if restoring data in any Extract File with uncommitted data.

ROW_LIMIT

The maximum number of rows that can be extracted. If omitted, the site limit is used.

n: The maximum number of extracted rows. Specify a value in the range 1 to 4,294,967,295

DEFAULT_KEY_LIMIT

The default maximum number of keys used at one time to process a table with key lookup. Applies only if the Access Definition does not specify a limit.

n: 1 - 100 (default is 1)

SKIP_EXT_CATALOG

Indicate whether to skip creation of a Directory entry for the extract file.

YES: Skip creating a Directory entry for the extract file (default), unless the EXTRACT file is being written to TAPE.

NO: Create an entry for the extract file.

REPLACE_EXT_DIR

The action taken if a Directory entry already exists for the specified extract file.

YES: Replace the existing entry.

NO: Stop the extract process (default).

CONVERT_PERFORM

Indicate whether the Convert Process should be invoked to transform the extracted data after the Extract Process is complete.

NO: Do not convert extracted data (default).
YES: Convert extracted data.

CONVERT_SORT

Indicate whether to sort rows for destination tables with a cluster index. This keyword applies only if CONVERT_PERFORM is YES. Specify:

NO: Do not sort rows (default).
YES: Sort rows.

CONVERT_MAX_DISCARDS

The maximum number of discarded rows for the Convert Process. If the maximum value is exceeded, the Convert Process terminates. Omit this keyword to allow an unlimited number of rows to be discarded. This keyword applies only if CONVERT_PERFORM is YES.

n: Number in the range 1 - 4,294,967,295.

CONVERTED_FILE: Identify the output file to which the converted data will be written. Use the keywords in File Allocation Parameters to name and allocate the file. If you omit this keyword, the converted data is written back to the Extract File. This keyword applies only if CONVERT_PERFORM is YES

CONVERSION_ERROR_RC4

Option to enforce how the return code is set when conversion errors are detected, or rows discarded from user exit or data privacy function processing.

NO: Set the return code to 0 regardless of any conversion errors that were detected. This is the default.
YES: Set the return code to 4 to indicate that one or more conversion errors were detected.

CONTROL_FILE: Identify the Control File. This keyword is required. Use the keywords in File Allocation Parameters to name and allocate the Control File. This keyword applies only if CONVERT_PERFORM is YES.

TABLE_MAP

The name of the Table Map to be used. This keyword applies only if CONVERT_PERFORM is YES.

mapid.name: Fully qualified name of a Table Map in the Optim Directory.

TABLE_MAP_DEFINE

The Optim online process generates this keyword when it creates a CONVERT job for batch execution.

When you create a CONVERT job outside of the Optim online process, the best practice is to use the TABLE_MAP keyword to refer to a named Table Map in the Optim Directory.

Place the TABLE_MAP_DEFINE parameters within parentheses. A semicolon must follow the close parenthesis. (See TABLE_MAP_DEFINE Parameters for the allowable parameters.)

AGING: This keyword indicates that date values in the source columns are to be aged. It provides parameters to be used in the aging process. This keyword applies only if CONVERT_PERFORM is YES.
See AGING Keyword Parameters for detailed information on using this keyword.

REPORT_LEVEL

The level of detail provided in the Process Report.

DETAIL: Produce a detailed report (default).
SUMMARY: Produce a summary report.

EXTRACT_LENGTH_ERROR_RC4

(Only applies to Legacy.) Option for the return code used when Optim detects that records in the source extract file are shorter than the logical record defined in the Legacy Table definition. In this case, a warning message is issued.

NO: RC of jobs is not affected when this condition is detected.

YES: RC of 4 is returned for the job step when this condition is detected. This is the default.

BYPASS_ROW_SQLCODE_20421

Prior releases of Optim required that DB2 translated the column values to the Optim plan CCSID. There are several circumstances that can cause the characters to be untranslatable. DB2 will use a substitution character for non-XML columns but returns an SQL CODE -20421 for XML columns that have characters that cannot be translated. The BYPASS_ROW_SQLCODE_20421 setting controls whether the row with non-translatable XML data is bypassed.

Note: This SQLCODE will never be encountered in release 11.7 of Optim. This keyword has been deprecated.

NO: Optim will not bypass the row with non-translatable XML data.

YES: Optim will bypass the row with non-translatable XML data. The count of the number of rows bypassed is reported. Each failing row is reported in the Optim Trace, which is turned on dynamically to record the information.

Example

Use the following statement to create an Extract File named PSTUSER.EXTRACT.CUST, using the Access Definition PSTUSER.AD.CUSTOMERS. This example also uses selection criteria and executes the UNLOAD PLUS utility.


EXTRACT
   ACCESS_DEFINITION PSTUSER.AD.CUSTOMERS
   EXTRACT_FILE (DSNAME PSTUSER.EXTRACT.CUST)
   SELECT (PSTUSER.CUSTOMERS, AND, AGE, GR, 21, AREA, EQ, ‘WEST')
   UNLOAD_UTILITY BMC