EXTRACT

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 an 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 } ]
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.
IBM®
Use the High Performance Unload utility.
CDB
Use the Auto-Unload utility.
CDBO
Use the Auto-Online Unload utility.
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.
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
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.
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.

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
Parent topic: Processing Utilities