Connect ETL can also apply logic to keep or discard records. The logic can be conditional
like INCLUDE and OMIT or based on record count like
SKIPREC and STOPAFT. In Connect ETL this logic is
implemented using filters and conditions. Both types of filters can be used on any task type,
with the exception of join tasks. Only conditional filters are allowed in join tasks. If your
JCL job step uses SKIPREC and STOPAFT with a join, you will
need to break the step into two tasks and place them in a job (see Create a
Connect ETL Job) or add an additional job step in the JCL to do part of the
processing.
The table below summarizes the mappings between the mainframe sort keywords and Connect ETL filter types:
| Mainframe Sort | Connect ETL |
SKIPREC
n
|
Bulk filter: discard the first n records |
STOPAFT
n
|
Bulk filter: retain the next n records |
INCLUDE COND=(condition) |
Conditional filter: condition |
OMIT COND=(condition) |
Conditional filter: not(condition) |
Note that bulk filters can only be added to the main branch of the tree in the Task Editor. It is not possible to perform bulk filtering for an individual target. If you apply both operations in one task, records will be discarded first and the count of records retained will begin at the first record that is not discarded.
Conditional filters can be added to the main branch of the tree or to individual targets. A conditional filter consists of a condition that is either a Boolean expression or a named condition that is saved as metadata in the task. Conditions can be used to compare fields to other fields or to compare fields to constants (values). Some sample conditions are shown below. Refer to the Connect ETL help for more details on the syntax of values and conditions.
| Mainframe Sort | Connect ETL |
INCLUDE COND=(10,4,CH,EQ,25,4,CH)
|
recordlayout1.field2 = recordlayout1.field5 |
OMIT COND=(11,5,CH,EQ,C'ABCDE')
|
recordlayout1.field3 != 'ABCDE' |
INCLUDE COND=(1,6,GT,C' ')
|
recordlayout1.field1 > 6 * ' ' |
OMIT COND=((7,3,EQ,C'ABC'),OR,
(20,10,GT,C' '))
|
not(recordlayout1.field2 = 'ABC' and recordlayout1.field4 > ' ') |