Examples sub-command

This section contains examples of the EXTRACT sub-command.

Example 1

In one pass of the ORDER database: for ORDER segments ORDR010, ORDR020 and ORDR030, generate three extract data sets to be used as input to load three DB2 tables representing the three DL/I segments. Note that for dependent segments, the keys of the higher level segments are included in the extract record.

SELECT SEGMENT=ORDR010 WHERE ---
       SEGMENT=ORDR020
       SEGMENT=ORDR030
EXTRACT1 SEGMENT=ORDR010
         FIELD=(ORDER-NUMBER,CUSTOMER-NUMBER,etc.)
EXTRACT2 SEGMENT=ORDR010
         FIELD=(ORDER-NUMBER)
         SEGMENT=ORDR020
         FIELD=(LINE-NUMBER,ORDER-TYPE,LINE-STATUS,etc.)
EXTRACT3 SEGMENT=ORDR010
         FIELD=(ORDER-NUMBER)
         SEGMENT=ORDR020
         FIELD=(LINE-NUMBER)
         SEGMENT=ORDR030
         FIELD=(PROCESS-IND,MATERIAL-TYPE-IND,etc.);

Example 2

SELECT SEGMENT=ORDR010 WHERE ---
       SEGMENT=ORDR020
       SEGMENT=ORDR030
EXTRACT4 SEGMENT=ORDR030
         FIELD=(PROCESS-IND)
         SEGMENT=ORDR010
         FIELD=(NUMBER-UNITS-STARTED(1))
         SEGMENT=ORDR030
         FIELD=(MATERIAL-TYPE-IND)
         SEGMENT=ORDR010
         FIELD=(ORDER-NUMBER);

In this example, no fields from SEGMENT ORDR020 are present. The order of the fields in the extract record are:

PROCESS-IND — from ORDR030
NUMBER-UNITS-STARTED occurrence 1 — from ORDR010
MATERIAL-TYPE-IND — from ORDR030
ORDER-NUMBER — from ORDR010

Example 3

In this example, CUSTOMER-NAME, whose data type is character, has all bytes set to slash as the fill value. CUSTOMER-NUMBER, whose data type is packed, has the fill value of a packed +0. NUMBER-UNITS-STARTED whose data type is packed, has a fill value of packed +0 for occurrence 1.

FIELD=(CUSTOMER-NAME(FILL=’/’),CUSTOMER-NUMBER(FILL=X’0C’),
NUMBER-UNITS-STARTED(FILL=X’0C’,1))

Example 4

In this example, the first field comes from bytes 73 through 80 of the segment, has a data type of character and a fill value of character 0. The second field comes from byte 1 through 10 of the segment, has a data type of character (the default), and the default fill value for a character field. The third field comes from bytes 11 through 15 of the segment, has a data type of packed, and uses the default packed fill value. The fourth field comes from bytes 41 through 44 of the segment, has a data type of "other" and uses the default fill value set by OFILL or X’00’ if OFILL is not specified. The fifth field comes from bytes 50 through 53 of the segment. The data type of N causes the default fill value to be X’00’ or NFILL (if specified). However, the explicit fill value of X’FF’ is provided.

FIELD=((73,8,C,FILL=’0’),(1,10),(11,5,P),(41,4,O),(50,4,N,FILL=X’FF’))

Example 5

In this example, the field start byte of 0 is used to place 5 blanks between each of the fields extracted from the DL/I segment.

SELECT SEGMENT=ORDR010 WHERE ---
       SEGMENT=ORDR020
       SEGMENT=ORDR030
EXTRACT4 SEGMENT=ORDR030
         FIELD=(PROCESS-IND)
         SEGMENT=ORDR030
         FIELD=((0,5))
         SEGMENT=ORDR030
         FIELD=(MATERIAL-TYPE-IND)
         SEGMENT=ORDR030
         FIELD=((0,5))
         SEGMENT=ORDR010
         FIELD=(ORDER-NUMBER);

EXTRACT4 could also be written as follows:

EXTRACT4 SEGMENT=ORDR030
         FIELD=(PROCESS-IND,(0,5),MATERIAL-TYPE-IND,(0,5))
         SEGMENT=ORDR010
         FIELD=(ORDER-NUMBER);