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