Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Functions and Options in the External SORT

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Prokis

New User


Joined: 10 Dec 2004
Posts: 1
Location: bangalore

PostPosted: Mon Dec 13, 2004 10:41 am    Post subject: Functions and Options in the External SORT
Reply with quote

hi,
good day to you.

how many types are there in sort(external)? what r they? can u give me an example for each.
Back to top
View user's profile Send private message

jz1b0c

Active User


Joined: 25 Jan 2004
Posts: 160
Location: Toronto, Canada

PostPosted: Mon Dec 13, 2004 11:59 pm    Post subject:
Reply with quote

good day!

there are too many,
Let me know your E-mail Id I can forward some live sorts

Just pasting a document prepared by team, not sure if the format is readable.

Table of Contents

1. INCLUDE Control Statement 2
2. OMIT Control Statement 10
3. INREC Control Statement 12
4. OUTREC Control Statement 16
5. COPY Operator 18
6. SELECT Operator 19
7. MERGE Control Statement 23
8. OUTFIL Control Statements 26
9. SORT Control Statement 32
10. SUM Control Statement 34
11. Current Date Table Formats 35
12. Control Fields Format and Length 36
13. Internal representation of Numeric Items 37



1. INCLUDE Control Statement

Use an INCLUDE statement if you want only certain records to appear in the output data set. The INCLUDE statement selects the records you want to include.

SYNTAX

INCLUDE COND = ( logical expression )

If the logical expression is true for a given record, the record is included in the output data set.

Logical Expression

A logical expression is one or more relational conditions logically combined, based on
fields in the input record, and can be represented at a high level as follows:

relational-conditional-1, AND/OR, relational-condition-2

If the logical expression is true for a given record, the record is included in the output data set.

Four types of relational conditions can be used as follows:
1. Comparisons:

Two kinds of Relational Format can be used :

a) (p1, m1, f1, EQ/ NE/ GT/ GE/ LE/ LT, p2, m2, f2)

p1 : starting position of the compare field in the input file
m1 : length of the compare field
f1 : specifies the format of the data in the compare field

b) (p1, m1, EQ/ NE/ GT/ GE/ LE/ LT, p2, m2 )

If all the compare fields contain the same type of data, this value can be omitted, in which case you must use the FORMAT=f operand.









Examples ?1 :
INCLUDE COND=(5,8,GT,13,8,|,105,4,LE,1000),FORMAT=CSF

This example illustrates how to only include records in which:
? The floating sign number in bytes 5 through 12 is greater than the floating sign number in bytes 13 through 20
? ?|? stands for OR
? The floating sign number in bytes 105 through 108 is less than or equal to 1000.
Q: What FORMAT=CSF stands for ?
And : CSF OR FS (signed numeric with optional leading floating sign).

The floating sign format can be represented as follows:
<s>d . . .d
Examples :
Value Treated As :
34 +34
+34 +34
00034 +34
-003 -3
-1234 -1234
1234 +1234
+01234 +1234
0 +0

Example ? 2 :
INCLUDE COND=(1,10,CH,EQ,C'STOCKHOLM', AND,21,8,ZD,GT,+50000, OR,31,4,CH,NE,C'HERR')
This example illustrates how to only include records in which:
? The first 10 bytes contain STOCKHOLM (this nine-character string was padded on the right with a blank) AND the zoned-decimal number in bytes 21 through 28 is greater than 50 000 OR
? Bytes 31 through 34 do not contain HERR.
Note that the AND is evaluated before the OR. Also note that ending a line with a comma or semicolon followed by a blank indicates that the parameters continue on the next line, starting in any position from columns 2 through 71.


Example ? 3 :
INCLUDE COND=(7,2,CH,EQ,C'T1',OR, (1,2,BI,GE,X'001A',AND,20,2,CH,EQ,25,2,CH))
This example shows the effects of VLSCMP/NOVLSCMP and VLSHRT/NOVLSHRT on INCLUDE processing when short records are present.

Consider the records shown in Figure 1.0 :
? If VLSCMP is in effect, the first record is included because bytes 7-8 are equal to C'T1', even though the comparison of bytes 20-21 to 25-26 involves short fields. The second record is included or omitted based on the comparison of bytes 20-21 to bytes 25-26.
? If NOVLSCMP and VLSHRT are in effect, the first record is omitted because the comparison of bytes 20-21 to 25-26 involves short fields. The second record is included or omitted based on the comparison of bytes 20-21 to bytes 25-26.
? If NOVLSCMP and NOVLSHRT are in effect, the first record causes message ICE015A or ICE218A to be issued because the comparison of bytes 20-21 to bytes 25-26 involves short fields.
________________________________________________________________________
| |
| |
| |
| |
|________________________________________________________________________|
Figure 1.0 : Sample Records








2. Substring Comparison Tests

Two formats for the relational condition can be used:

(p1, m1, f1, SS, EQ/NE , constant )

If FORMAT=SS is not used then

(p1, m1, f1, EQ/NE , constant )

Example ? 1 :
Select records based on whether a field value is found in a constant. Here's an example of an INCLUDE statement using this type of test:

INCLUDE COND=(11,6,SS,EQ,C'HAMMER,CHISEL,SAW ,WRENCH')

Records with HAMMER, CHISEL, SAW or WRENCH in positions 11-16 will be
included in the output data set.
Note that the comma is used within the constant to separate the valid 6-character values ; any character that will not appear in the field value can be used as a separator in the constant . SAW must be padded with three blanks on the right to make it a 6-Character value so that it will be properly compared to the 6-character field in positions 11-16.
The single condition above can replace the following four conditions:

INCLUDE COND=(11,6,CH,EQ,C'HAMMER',OR,
11,6,CH,EQ,C'CHISEL',OR,
11,6,CH,EQ,C'SAW',OR,
11,6,CH,EQ,C'WRENCH')

Note that unlike the SS case above, DFSORT will automatically pad SAW with three blanks in this case.

3. Bit Logic Tests:

Test the state (on or off) of selected bits in a binary field using a bit or hexadecimal mask or a bit constant.

Two formats for the relational condition can be used:

________________________________________________________________________
| |
| >>__(p1,m1,BI,__ _ALL_____ __,__mask__)_____________________________>< |
| |_SOME____| |
| |_NONE____| |
| |_NOTALL__| |
| |_NOTSOME_| |
| |_NOTNONE_| |
| |_BO______| |
| |_BM______| |
| |_BZ______| |
| |_BNO_____| |
| |_BNM_____| |
| |_BNZ_____| |
|________________________________________________________________________|

Or, if the FORMAT=BI operand is used:

________________________________________________________________________
| |
| >>__(p1,m1,__ _ALL_____ __,__mask__)________________________________>< |
| |_SOME____| |
| |_NONE____| |
| |_NOTALL__| |
| |_NOTSOME_| |
| |_NOTNONE_| |
| |_BO______| |
| |_BM______| |
| |_BZ______| |
| |_BNO_____| |
| |_BNM_____| |
| |_BNZ_____| |
|________________________________________________________________________|


ALL or BO
All mask bits are on in the input field
SOME or BM
Some, but not all mask bits are on in the input field
NONE or BZ
No mask bits are on in the input field
NOTALL or BNO
Some or no mask bits are on in the input field
NOTSOME or BNM
All or no mask bits are on in the input field
NOTNONE or BNZ
All or some mask bits are on in the input field

A hexadecimal string or bit string that indicates the bits in the field selected for testing. If a mask bit is on (1), the corresponding bit in the field is tested. If a mask bit is off (0), the corresponding bit in the field is ignored.




Hexadecimal String Format : The format for coding a hexadecimal string mask is:
________________________________________________________________________
| |
| X'yy...yy' |
| |
|________________________________________________________________________|

The value yy represents any pair of hexadecimal digits that constitute a byte (8 bits). Each bit must be 1 (test bit) or 0 (ignore bit). You can specify up to 256 pairs of hexadecimal digits.

Bit String Format : The format for coding a bit string mask is:
________________________________________________________________________
| |
| B'bbbbbbbb...bbbbbbbb' |
| |
|________________________________________________________________________|
The value bbbbbbbb represents 8 bits that constitute a byte. Each bit must be 1 (test bit) or 0 (ignore bit). You can specify up to 256 groups of 8 bits. The total number of bits in the mask must be a multiple of 8. A bit mask string can only be used with a bit operator.

Including Records in the Output Data Set--Bit Operator Test Examples

Example - 1 :

INCLUDE COND=(27,1,CH,EQ,C'D',AND,18,1,BI,ALL,B'10000000')
This example illustrates how to only include records in which:
? Byte 27 contains D
AND
? Byte 18 has bit 0 on.
Example ? 2 :
INCLUDE COND=(11,1,BI,BM,X'85')
This example illustrates how to only include records in which byte 11 has some, but not all of bits 0, 5 and 7 on. Results for selected field values are shown below:

Table 8. Bit Comparison Example 2: Results for Selected Field Values
11,1,BI Value 11,1,BI Result Action
X'85' False Omit Record
X'C1' True Include Record
X'84' True Include Record
Example ? 3 :
INCLUDE COND=(11,2,ALL,B'0001001000110100', OR,21,1,NONE,B'01001100'),FORMAT=BI
This example illustrates how to only include records in which:
? Bytes 11 through 12 have all of bits 3, 6, 10, 11 and 13 on
OR
? Byte 21 has none of bits 1, 4, or 5 on.
Results for selected field values are shown below:

Table 9. Bit Comparison Example 3: Results for Selected Field Values
11,2,BI Value 11,2,BI Result 21,1,BI Value 21,1,BI Result Action
X'1234' True X'4C' False Include Record
X'02C4' False X'81' True Include Record
X'0204' False X'40' False Omit Record
X'F334' True X'00' True Include Record
X'1238' False X'4F' False Omit Record
4. Date Comparisons : Two formats for the relational condition can be used:
________________________________________________________________________
| |
| |
| >>__(p1,m1,Y2x,__ _EQ_ __,__ _p2,m2,Y2x_ __)________________________>< |
| |_NE_| |_constant__| |
| |_GT_| |
| |_GE_| |
| |_LT_| |
| |_LE_| |
| |
| |
|________________________________________________________________________|

Or, if the FORMAT=Y2x operand is not used:
________________________________________________________________________
| |
| |
| >>__(p1,m1,__ _EQ_ __,__ _p2,m2____ __)_____________________________>< |
| |_NE_| |_constant_| |
| |_GT_| |
| |_GE_| |
| |_LT_| |
| |_LE_| |
| |
| |
|________________________________________________________________________|

Comparison operators are as follows:
EQ - Equal to
NE - Not equal to
GT - Greater than
GE - Greater than or equal to
LT - Less than
LE - Less than or equal to.
Example ? 1 :
INCLUDE COND=(21,8,ZD,GT,DATE1P)

This example illustrates how to include records in which a zoned-decimal
date of the form Z'yyyymmdd' in bytes 21-28 is greater than today's date.
DATE1P generates a decimal number for the current date in the form
+yyyymmdd.

Example ? 2 :

Here's an example of an INCLUDE statement that compares a packed decimal
date in the form P'yyyymmdd', and a character date in the form
C'yyyy-ddd', to the current date:

INCLUDE COND=(21,5,PD,GT,DATE1P,OR, 3,8,CH,GT,DATE3(-))

Records with a P'yyyymmdd' date or a C'yyyy-ddd' date greater than today's
date will be included in the output data set.

Example 3 :
INCLUDE COND=(2,3,Y2X,LT,36,5,Y2T)
This example illustrates how to only include records in which a P'dddyy' date field in bytes 2 through 4 is less than a Z'yyddd' date field in bytes 36 through 40.

Note that the century window in effect will be used to interpret real dates in the P'dddyy' and Z'yyddd' date fields. However, the century window will not be used to interpret special indicators in the P'dddyy' and Z'yyddd' date fields.






2. OMIT Control Statement

Use an OMIT statement if you do not want all of the input records to appear in the output data sets. The OMIT statement selects the records you do not want to include.

You can specify either an INCLUDE statement or an OMIT statement in the same DFSORT run, but not both.

SYNTAX :
OMIT COND = ( logical expression )

A logical expression is one or more relational conditions logically combined, based on fields in the input record, and can be represented at a high level as follows:

relational-condition-1, AND/OR, relational-condition-2

If the logical expression is true for a given record, the record is OMIT from the output data set.

Four types of relational conditions can be used as follows:

Example ? 1 :

OMIT COND=(27,1,CH,EQ,C'D',&, (22,2,BI,SOME,X'C008',|, 28,1,BI,EQ,B'.1....01'))

This statement omits records in which:
? Byte 27 contains D
AND
? Bytes 22 through 23 have some, but not all of bits 0, 1 and 12 on OR byte 28 is equal to the specified pattern of bit 1 on, bit 6 off and bit 7 on.
Note that the AND and OR operators can be written with the AND and OR signs, and that parentheses are used to change the order in which AND and OR are evaluated.

Example ? 2 :

Here's an example of an OMIT statement that compares a ZD field to a PD
field, and compares a BI field to a decimal number.

OMIT COND=(7,3,ZD,EQ,18,2,PD,AND,12,1,BI,EQ,+25)

Records in which the ZD and PD values are equal, and the BI value is 25,
will be omitted from the output data set.

Example ? 3 :

OMIT COND=(25,3,SS,EQ,C'D05D08D12',AND,32,2,PD,GT,130)

Records with D05, D08 or D12 in positions 25-27 and with the PD value in positions 32-33 greater than 130 will be excluded from the output data set. Note that commas are not used to separate the valid 3-character values in the constant, but could be used to improve readability.

NOTE :
Recently IBM has increased the length of the compare fields using the Sub-String ( SS ) operator from 256 bytes to 32752 bytes

Example ? 4 :

+----------------------------------------------+
|OMIT COND=(1,19,CH,GT,DATE4) |
+----------------------------------------------+
This example illustrates how to omit only those records with a
C'yyyy-mm-dd-hh.mm.ss' date value in positions 1-19 greater than the DATE4 character string for the run.

Note: When a field is shorter than the DATE4 character string it's compared to, DFSORT truncates the DATE4 string on the right. You can take advantage of this to compare a field to only part of the DATE4 timestamp when appropriate. For example:

OMIT COND = (1,13,CH,GT,DATE4)

would compare the field in positions 1-13 to the truncated DATE4 constant
C'yyyy-mm-dd-hh'.










3. INREC Control Statement

The INREC control statement allows you to reformat the input records before they are processed; that is, to define which parts of the input record are to be included in the reformatted input record, in what order they are to appear, and how they are to be aligned.
You do this by defining one or more fields from the input record. The reformatted input record consists of only those fields, in the order in which you have specified them, and aligned on the boundaries or in the columns you have indicated.


+------------------------------------------------------------------------+
? Table N. Examples of Valid and Invalid Column Alignment ?
+------------------------------------------------------------------------?
? Validity ? Specified ? Result ?
+-----------+------------------------+-----------------------------------?
? Valid ? 33:C'State ' ? Columns 1-32 -- blank ?
? ? ? Columns 33-38 -- 'State ' ?
+-----------+------------------------+-----------------------------------?
? Valid ? 20:5,4,30:10,8 ? Columns 1-19 -- blank ?
? ? ? Columns 20-23 -- input field (5,4)?
? ? ? Columns 24-29 -- blank ?
? ? ? Columns 30-37 -- input field (10,8?
+-----------+------------------------+-----------------------------------?
? Invalid ? 0:5,4 ? Column value cannot be zero. ?
+-----------+------------------------+-----------------------------------?
? Invalid ? :25Z ? Column value must be specified. ?
+-----------+------------------------+-----------------------------------?
? Invalid ? 32753:21,8 ? Invalid -- column value must be |
? ? ? less than 32753. ?
+-----------+------------------------+-----------------------------------?
? Invalid ? 5:10:2,5 ? Column values cannot be adjacent. ?
+-----------+------------------------+-----------------------------------?
? Invalid ? 20,10,6:C'AB' ?Column value overlaps previous field.
+------------------------------------------------------------------------+

nX Blank separation. n bytes of EBCDIC blanks (X'40') are to appear in the
reformatted input records. n can range from 1 to 4095. If n is omitted, 1 is
used.

Examples of valid and invalid blank separation are shown in Table O.
+------------------------------------------------------------------------+
? Table O. Examples of Valid and Invalid Blank Separation ?
+------------------------------------------------------------------------?
? Validity ? Specified ? Result ?
+-------------+--------------+-------------------------------------------?
? Valid ? X or 1X ? 1 blank ?
+-------------+--------------+-------------------------------------------?
? Valid ? 4095X ? 4095 blanks ?
+-------------+--------------+-------------------------------------------?
? Invalid ? 5000X ? Too many repetitions. Use two adjacent ?
? ? ? separation fields instead (2500X,2500X, ?
? ? ? for example) ?
+-------------+--------------+-------------------------------------------?
? Invalid ? 0X ? 0 is not allowed. ?
+------------------------------------------------------------------------+
nZ Binary zero separation. n bytes of binary zeros (X'00') are to appear in the
reformatted input records. n can range from 1 to 4095. If n is omitted, 1 is used.

Examples of valid and invalid binary zero separation are shown in Table P.
+------------------------------------------------------------------------+
? Table P. Examples of Valid and Invalid Binary Zero Separation ?
+------------------------------------------------------------------------?
? Validity ? Specified ? Result ?
+-------------+--------------+-------------------------------------------?
? Valid ? Z or 1Z ? 1 binary zero ?
+-------------+--------------+-------------------------------------------?
? Valid ? 4095Z ? 4095 binary zeros ?
+-------------+--------------+-------------------------------------------?
? Invalid ? 4450Z ? Too many repetitions. Use two adjacent ?
? ? ? separation fields instead (4000Z,450Z for ?
? ? ? example). ?
+-------------+--------------+-------------------------------------------?
? Invalid ? 0Z ? 0 is not allowed. ?
+------------------------------------------------------------------------+

+------------------------------------------------------------------------+
? Table Q. Examples of Valid and Invalid Character String Separation ?
+------------------------------------------------------------------------?
? Validity? Specified ? Result ? Length ?
+---------+--------------------+-------------------------------+---------?
? Valid ? C'John Doe' ? John Doe ? 8 ?
+---------+--------------------+-------------------------------+---------?
? Valid ? C'JOHN DOE' ? JOHN DOE ? 8 ?
+---------+--------------------+-------------------------------+---------?
? Valid ? C'$@#' ? $@# ? 3 ?
+---------+--------------------+-------------------------------+---------?
? Valid ? C'+0.193' ? +0.193 ? 6 ?
+---------+--------------------+-------------------------------+---------?
? Valid ? 4000C' ' ? 8000 blanks ? 8000 ?
+---------+--------------------+-------------------------------+---------?
? Valid ? 20C'**FILLER**' ? **FILLER** repeated 20 times ? 200 ?
+---------+--------------------+-------------------------------+---------?
+---------+--------------------+-------------------------------+---------?
? Valid ? C'Frank''s' ? Frank's ? 7 ?
+---------+--------------------+-------------------------------+---------?
? Invalid ? C''''' ? Apostrophes not paired ? n/a ?
+---------+--------------------+-------------------------------+---------?
? Invalid ? 'ABCDEF' ? C identifier missing ? n/a ?
+---------+--------------------+-------------------------------+---------?
? Invalid ? C'ABCDE ? Apostrophe missing ? n/a ?
+---------+--------------------+-------------------------------+---------?
? Invalid ? 4450C'1' ? Too many repetitions. Use two ? n/a ?
? ? ? adjacent separation fields ? ?
? ? ? instead (4000C'1',450C'1', ? ?
? ? ? for example). ? ?
+---------+--------------------+-------------------------------+---------?
? Invalid ? 0C'ABC' ? 0 is not allowed ? n/a ?
+---------+--------------------+-------------------------------+---------?
? Invalid ? C'' ? No characters specified ? n/a ?
+---------+--------------------+-------------------------------+---------?
? Invalid ? C'Frank's' ? Two single apostrophes needed ? n/a ?
? ? ? for one ? ?
+------------------------------------------------------------------------+
Example ? 1 :

INREC Method
INCLUDE COND=(5,1,GE,C'M'),FORMAT=CH INREC FIELDS=(10,3,20,8,33,11,5,1) SORT FIELDS=(4,8,CH,A,1,3,FI,A) SUM FIELDS=(17,4,BI)

OUTREC Method
INCLUDE COND=(5,1,GE,C'M'),FORMAT=CH OUTREC FIELDS=(10,3,20,8,33,11,5,1) SORT FIELDS=(20,8,CH,A,10,3,FI,A) SUM FIELDS=(38,4,BI)

The above examples illustrate how a fixed-length input data set is sorted and reformatted for output. Unnecessary fields are eliminated from the output records using INREC or OUTREC. The SORTIN LRECL is 80.
Records are also included or excluded by means of the INCLUDE statement, and summed by means of the SUM statement.
The reformatted input records are fixed length with a record size of 23 bytes. SOLRF (the IBM-supplied default) is in effect, so unless the SORTOUT LRECL is specified or available, it will automatically be set to the reformatted record length of 23. The reformatted records look as follows after INREC or OUTREC processing:
Position Contents
1-3
Input positions 10 through 12
4-11
Input positions 20 through 27
12-22
Input positions 33 through 43
23
Input position 5
Identical results are achieved with INREC or OUTREC. However, use of OUTREC makes it easier to code the SORT and SUM statements. In either case, the INCLUDE COND parameters must refer to the fields of the original input records. However, with INREC, the SUM and SORT FIELDS parameters must refer to the fields of the reformatted input records, while with OUTREC, the SUM and SORT FIELDS parameters must refer to the fields of the original input records.
Example ? 2 :

IEW AGOPRPR.RETCURR.CNTLLIB(BZ7JSRT1) - 01.00 Columns 00001 00072
command ===> Scroll ===> CSR
***** ***************************** Top of Data ******************************
00100 ************************************************************************
00120 ** BY : MS606
00130 ** NAME: BZ7JSRT1
00131 **
00200 ** PARAMETER SORTS THE BZ7J GENERATED FILE BY REGION-CODE & LIABILITY
00300 ** NUMBER
00400 **
00500 ** DEVELOPED FOR SORTING BZ7J, NOV 03
00600 ************************************************************************
00610 INCLUDE COND=(1,1,ZD,EQ,2)
00700 INREC FIELDS=(1:3280,2,3:3282,8,11:8,5,16:13,10)
00800 SORT FIELDS=(1,2,CH,A, **REGION-CODE
00820 3,8,CH,A) **LIABILITY-NUM
01000 END
***** **************************** Bottom of Data ****************************

Example ? 3 :

















4. OUTREC Control Statement

The OUTREC control statement allows you to reformat the input records before they are output. That is, to define which parts of the input record are included in the reformatted output record, in what order they are to appear, and how they are to be aligned.
Example ? 1 :
OUTREC FIELDS=(11,32)

This statement specifies that the output record is to contain 32 bytes beginning with byte 11 of the input record. This statement can be used only with fixed-length input records, because it does not include the first 4 bytes.
Example ? 2 :
OUTREC FIELDS=(1,4,11,32,D,101)

This statement is for variable-length records of minimum length 100 bytes, and specifies that the output record is to contain an RDW plus 32 bytes of the input record starting at byte 11 (aligned on a doubleword boundary, relative to the start of the record) plus the entire variable portion of the input record.
Note that no extra comma is coded to indicate the omission of the first alignment parameter. If you do include an extra comma, DFSORT issues a message and terminates processing.

Example ? 3 :
SORT FIELDS=(20,4,CH,D,10,3,CH,D) OUTREC FIELDS=(7:20,4,C' FUTURE ',20,2,10,3,1Z,1,9,13,7, 24,57,TRAN=LTOU,6X'FF')
This example illustrates how a fixed-length input data set can be sorted and reformatted for output. The SORTIN LRECL is 80 bytes.

The reformatted output records are fixed length with a record size of 103 bytes. SOLRF (the IBM-supplied default) is in effect, so unless the SORTOUT LRECL is specified or available, it will automatically be set to the reformatted record length of 103. The reformatted records look as follows:

Position Contents
1-6
EBCDIC blanks for column alignment
7-10
Input positions 20 through 23
11-18
Character string: C' FUTURE '
19-20
Input positions 20 through 21
21-23
Input positions 10 through 12
24
Binary zero
25-33
Input positions 1 through 9
34-40
Input positions 13 through 19
41-97
Input positions 24 through 80 with lowercase EBCDIC letters
| converted to uppercase EBCDIC letters
98-103
Hexadecimal string: X'FFFFFFFFFFFF'

Example ? 4 :
NUMERIC CONVERSION

OUTREC=(21,5,ZD,TO=PD,8,4,ZD,TO=FI,LENGTH=2)

The zoned decimal values in positions 21-25 and 8-11 of the input records are converted, respectively, to a packed decimal value in positions 1-3 and a fixed-point value in positions 4-5 of the output records.

Example ? 5 :

OUTREC FIELDS=(5,2,5C'*',18,6,C'ABC',X'000F',80:X)

The output records for the SORTOUT data set will contain the following:
? In output positions 1-2, the characters from input positions 5-6.
? In output positions 3-7, five asterisks.
? In output positions 8-13, the characters from input positions 18-23.
? In output positions 14-16, the characters ABC.
? In output positions 17-18, the hexadecimal characters 000F.
? Blanks in output positions 19-80.



5. COPY Operator

Copies an input data set to one or more output data sets.
Example ?1 :
* Method 1 COPY FROM(MASTER) TO(PRINT,TAPE,DASD)

* Method 2 COPY FROM(MASTER) TO(DASD,TAPE,PRINT) SERIAL
This example shows two different methods for creating multiple output data sets.

Method 1 requires one call to DFSORT, one pass over the input data set, and allows the output data sets to be specified in any order. The COPY operator copies all records from the MASTER data set to the PRINT (SYSOUT), TAPE, and DASD data sets, using OUTFIL processing.
Method 2 requires three calls to DFSORT, three passes over the input data set, and imposes the restriction that the SYSOUT data set must not be the first TO data set. The COPY operator copies all records from the MASTER data set to the DASD data set and then copies the resulting DASD data set to the TAPE and PRINT (SYSOUT) data sets. Since the first TO data set is processed three times (written, read, read), placing the DASD data set first is more efficient than placing the TAPE data set first. PRINT must not be the first in the TO list because a SYSOUT data set cannot be read.
Example ?2 :
________________________________________________________________________
| |
| COPY FROM(IN1,IN2) TO(OUT1,OUT2,OUT3) USE |
| USTART |
| INCLUDE COND=(31,4,CH,EQ,C'HERR') |
| UEND |
| COPY FROM(VSAMIN) TO(VSAMOUT) |
| COPY FROM(INPUT) TO(DASD,TAPE1,TAPE2) |
| |
|________________________________________________________________________|


The first COPY operator copies the records included from the IN1 and IN2 files to the OUT1 file and then copies the resulting OUT1 file to the OUT2 and OUT3 files. The DFSORT/VSE INCLUDE control statement is used for the first COPY operation.

The second COPY operator copies the entire VSAMIN file to the VSAMOUT file.

The third COPY operator copies the entire INPUT file to the DASD file and then copies the resulting DASD file to the TAPE1 and TAPE2 files. Since the first TO file is processed three times (written, read, read), placing the DASD file first is more efficient than placing the TAPE1 and TAPE2 files first.

6. SELECT Operator

Selects records from an input data set for inclusion in an output data set based on meeting criteria for the number of times specified numeric or character field values occur. This makes it possible to only keep records with duplicate field values, only keep records with no duplicate field values, only keep records with field values that occur more than, less than, or exactly n times, or only keep the first or last record with each unique field value. From 1 to 10 fields can be specified. At least one ON(VLEN) or ON(p,m,f) field must be specified; all such ON fields specified will be used to determine the "value count" (that is, the number of times the ON values occur) to be matched against the criteria.

DISCARD(savedd) can be used to save the records which do not meet the criteria (that is, the discarded records), in the savedd data set.
_________________________________________________________________________________
| |
| |
| <_.___________ |
| >>__SELECT__FROM(indd)__TO(outdd)___ _ON(p,m,f)_ |__ _ALLDUPS___ ____> |
| |_ON(VLEN)__| |_NODUPS____| |
| |_HIGHER(x)_| |
| |_LOWER(y)__| |
| |_EQUAL(v)__| |
| |_FIRST_____| |
| |_LAST______| |
| |
| >__ _____________ __ _________________ _____________________________>< |
| |_VSAMTYPE(x)_| |_DISCARD(savedd)_| |
|________________________________________________________________________|
Example ? 1 :
________________________________________________________________________
| |
| SELECT FROM(INPUT) TO(DUPS) ON(11,8,CH) ON(30,44,CH) ALLDUPS |
|________________________________________________________________________|

Sorts the INPUT data set to the DUPS data set, selecting only the records from INPUT with characters in positions 11-18 and characters in positions 30-73 that occur more than once (that is, only records with duplicate ON field values).

The DUPS data set might look as follows (several records are shown for illustrative purposes):
________________________________________________________________________
| |
| USR002 EISSLER 12 DOC.EXAMPLES |
| DFSRT2 EISSLER 5 DOC.EXAMPLES |
| DFSRT5 MADRID 20 MYDATA |
| DFSRT1 MADRID 20 MYDATA |
| SYS003 MADRID 20 MYDATA |
| DFSRT2 MADRID 20 SORTST1.TEST |
| USR003 MADRID 20 SORTST1.TEST |
| . . . . |
| . . . . |
|________________________________________________________________________|
ALLDUPS
Limits the records selected to those with ON values that occur more than once (value count > 1). You can use this operand to keep just those records with duplicate field values.

Example ? 2 :
________________________________________________________________________
| |
| SELECT FROM(INPUT) TO(ONLYONE) ON(23,3,FS) NODUPS |
|________________________________________________________________________|

Sorts the INPUT data set to the ONLYONE data set, selecting only the records from INPUT with floating sign values in positions 23-25 that occur just once (that is, only records with no duplicate ON field values).

The ONLYONE data set might look as follows (several records are shown for illustrative purposes):
________________________________________________________________________
| |
| DFSRT2 EISSLER 5 DOC.EXAMPLES |
| DFSRT1 PACKER 8 ICETOOL.SMF.RUNS |
| USR002 EISSLER 12 DOC.EXAMPLES |
| SYS003 YAEGER 32 ICETOOL.TEST.CASES |
| DFSRT2 MCNEILL 108 FS.TEST.CASES |
| . . . . |
| . . . . |
| . . . . |
|________________________________________________________________________|

NODUPS
Limits the records selected to those with ON values that occur only once
(value count = 1). You can use this operand to keep just those records with no duplicate field values.

Example ? 3 :
________________________________________________________________________
| |
| SELECT FROM(FAILURES) TO(CHECKOUT) ON(28,8,CH) ON(1,5,CH) - |
| HIGHER(3) |
|________________________________________________________________________|

Sorts the FAILURES data set to the CHECKOUT data set, selecting only the records from FAILURES with characters in positions 28-35 and characters in positions 1-5 that occur more than three times (that is only records with four or more duplicate ON field values).

The CHECKOUT data set might look as follows (several records are shown for illustrative purposes):



________________________________________________________________________
| |
| 03/12/91 08:36:59 A3275647 |
| 03/12/91 09:27:32 A3275647 |
| 03/12/91 09:03:18 A3275647 |
| 03/12/91 08:56:13 A3275647 |
| 03/06/91 15:12:01 C3275647 |
| 03/06/91 14:57:00 C3275647 |
| 03/06/91 15:43:19 C3275647 |
| 03/06/91 16:06:39 C3275647 |
| 03/06/91 15:22:08 C3275647 |
| . . . |
| . . . |
| . . . |
|________________________________________________________________________|

HIGHER(x)
Limits the records selected to those with ON values that occur more than x times (value count > x). You can use this operand to keep just those records with field values that occur more than x times.
x must be specified as n or +n where n can be 0 to 99.

LOWER(y)
Limits the records selected to those with ON values that occur less than y times (value count < y). You can use this operand to keep just those records with field values that occur less than y times.
y must be specified as n or +n where n can be 0 to 99.

EQUAL(v)
Limits the records selected to those with ON values that occur v times (value count = v). You can use this operand to keep just those records with field values that occur v times.
v must be specified as n or +n where n can be 0 to 99.

Example ? 4 :
________________________________________________________________________
| |
| SELECT FROM(BOOKS) TO(PUBLISHR) ON(29,10,CH) FIRST |
|________________________________________________________________________|

Sorts the BOOKS data set to the PUBLISHR data set, selecting only the records from BOOKS with characters in positions 29-38 that occur only once and the first record of those with characters in positions 29-38 that occur more than once (that is, one record for each unique ON field value).

The PUBLISHR data set might look as follows (several records are shown for illustrative purposes):
________________________________________________________________________
| |
| Banana Slugs I Have Known Brent Animals |
| Toads on Parade Cooper Animals |
| Pets Around the World Davis Animals |
| . . . |
| . . . |
| . . . |
|________________________________________________________________________|

FIRST
Limits the records selected to those with ON values that occur only once
(value count = 1) and the first record of those with ON values that occur more than once (value count > 1). You can use this operand to keep just the first record for each unique field value.

LAST
Limits the records selected to those with ON values that occur only once
(value count = 1) and the last record of those with ON values that occur more than once (value count > 1). You can use this operand to keep just the last record for each unique field value.

Example ? 5 :
________________________________________________________________________
| |
| SELECT FROM(BOOKS) TO(PUBLISHR) ON(29,10,CH) FIRST - |
| DISCARD(SAVEREST) |
|________________________________________________________________________|

This example creates the same PUBLISHR data set as Example 4. In addition, it creates a SAVEREST data set which contains all of the records not written to the PUBLISHR data set. The SAVEREST data set might look as follows (several records are shown for illustrative purposes):
________________________________________________________________________
| |
| How to Talk to Your Amoeba Brent Animals |
| What Buzzards Want Davis Animals |
| Birds of Costa Rica Davis Animals |
| . |
| . |
|________________________________________________________________________|

DISCARD(savedd)

Specifies the ddname of the output data set to which DFSORT will write the records it does not select for this operation (that is, the records that do not meet the specified criteria). Thus, the savedd data set will contain the records discarded by ALLDUPS, NODUPS, HIGHER(x), LOWER(y), EQUAL(v), FIRST or LAST.

A savedd DD statement must be present and must define an output data set that conforms to the rules for DFSORT's OUTFIL data set.




7. MERGE Control Statement

The MERGE control statement must be used when a merge operation is to be performed; this statement describes the control fields in the input records on which the input data sets have previously been sorted.
A MERGE statement can also be used to specify a copy application.

NOTE : Up to 9 files can be merged or copied
Example 1 :
MERGE FIELDS=(2,5,CH,A),FILSZ=29483
FIELDS : The control field begins on byte 2 of each record in the input data sets. The field is 5 bytes long and contains character (EBCDIC) data that has been presorted in ascending order.
FILSZ The input data sets contain exactly 29483 records.
Example ? 2 :
MERGE FIELDS=(3,8,ZD,E,40,6,CH,D)
FIELDS : The major control field begins on byte 3 of each record, is 8 bytes long, and contains zoned decimal data that is modified by your routine before the merge examines it.
The second control field begins on byte 40, is 6 bytes long, and contains character data in descending order.
Example ? 3 :
MERGE FIELDS=(25,4,A,48,8,A),FORMAT=ZD
FIELDS : The major control field begins on byte 25 of each record, is 4 bytes long, and contains zoned decimal data that has been placed in ascending sequence.
The second control field begins on byte 48, is 8 bytes long, is also in zoned decimal format, and is also in ascending sequence. The FORMAT parameter can be used because both control fields have the same data format.
Example ? 4 :
________________________________________________________________________
| |
| MERGE FIELDS=(2,5,CH,A),FILES=3 |
|________________________________________________________________________|

FIELDS The control field begins on byte 2 of each record in the input files, is 5 bytes long, and contains character (EBCDIC) data that has been presorted in ascending order.

FILES Three input files are to be merged.


Example ? 5 :


AGOPRPR.RETCURR.PROCLIB(BZPMRGP) - 01.00 Columns 00001 00072
d ===> Scroll ===> CSR
//SORTIN01 DD DSN=HFPR.BZPR.BZCADXT.FILE(&GENIN),
// DISP=SHR
//SORTIN02 DD DSN=HFPR.BZPR.BZCIDXT.FILE(&GENIN),
// DISP=SHR
//SORTIN03 DD DSN=HFPR.BZPR.BZCRDXT.FILE(&GENIN),
// DISP=SHR
//SORTIN04 DD DSN=HFPR.BZPR.BZCBDXT.FILE(&GENIN),
// DISP=SHR
//SORTOUT DD DSN=&&CUSTLIST,
// DISP=(NEW,PASS,DELETE),UNIT=&UNITDA,
// DCB=(SYS1.MODLBL,RECFM=FB,LRECL=55,BLKSIZE=23430,
// DSORG=PS),SPACE=(23430,(18000,1800),RLSE)
//SYSIN DD DSN=AGOTOPR.CIBCAPPL.CNTL(BZSRT1),DISP=SHR


AGOPRPR.RETCURR.CNTLLIB(BZSRT1) - 01.00 Colum
===> Sc
***************************** Top of Data ***************
MERGE FIELDS=(2,4,CH,A,8,10,ZD,A)
**************************** Bottom of Data *************
Example ? 6 :

File 1 :
111 abc
113 csd
112 sde

File 2 :
115 ade
114 jfk

File 3 :
118 sde
117 swe

//SORTIN01 DD DSN=FILE1,DISP=SHR
//SORTIN02 DD DSN=FILE2,DISP=SHR
//SORTIN03 DD DSN=FILE3,DISP=SHR
//OUTFILE DD DSN=FILE4,DISP=(NEW,CATLG,DELETE)
// SYSIN DD*
MERGE FIELDS = (1,3,A,CH)
/*

File 4:
111 abc
112 sde
113 csd
114 jfk
115 ade
117 swe
118 sde















































8. OUTFIL Control Statements

OUTFIL control statements allow you to create one or more output data sets for a sort, copy, or merge application from a single pass over one or more input data sets. You can use multiple OUTFIL statements, with each statement specifying the OUTFIL processing to be performed for one or more output data sets. OUTFIL processing begins after all other processing ends (that is, after processing for exits, options, and other control statements).

SORT FIELDS=COPY
OUTFIL FILES=1,
OUTREC=(1:7,169),CONVERT
AGOPRPR.RETCURR.JCLLIB(BZALBU)
Example ? 1 :

OPTION COPY OUTFIL INCLUDE=(15,6,CH,EQ,C'MSG005'),FNAMES=M005 OUTFIL INCLUDE=(15,6,CH,EQ,C'MSG022'),FNAMES=M022 OUTFIL INCLUDE=(15,6,CH,EQ,C'MSG028'),FNAMES=M028 OUTFIL INCLUDE=(15,6,CH,EQ,C'MSG115'),FNAMES=M115 OUTFIL SAVE,FNAMES=UNKNOWN
This example illustrates how records can be distributed to different OUTFIL data sets based on criteria you specify:
? Input records with MSG005 in bytes 15 through 20 will be written to the OUTFIL data set associated with ddname M005.
? Input records with MSG022 in bytes 15 through 20 will be written to the OUTFIL data set associated with ddname M022.
? Input records with MSG028 in bytes 15 through 20 will be written to the OUTFIL data set associated with ddname M028.
? Input records with MSG115 in bytes 15 through 20 will be written to the OUTFIL data set associated with ddname M115.
? Input records with anything else in bytes 15 through 20 will be written to the OUTFIL data set associated with ddname UNKNOWN
Example 2
SORT FIELDS=(15,6,ZD,A) OUTFIL FNAMES=USA, HEADER2=(5:'Parts Completion Report for USA',2/, 5:'Printed on ',DATE, ' at ',TIME=(12:),3/, 5:'Part ',20:'Completed',35:' Value ($)',/, 5:'------',20:'---------',35:'------------'), OUTREC=(5:15,6,ZD,M11, 20:3,4,ZD,M12,LENGTH=9, 35:38,8,ZD,M18,LENGTH=12, 132:X) OUTFIL FNAMES=FRANCE, HEADER2=(5:'Parts Completion Report for France',2/, 5:'Printed on ',DATE=(DM4/), ' at ',TIME,3/, 5:'Part ',20:'Completed',35:' Value (F)',/, 5:'------',20:'---------',35:'------------'), OUTREC=(5:15,6,ZD,M11, 20:3,4,ZD,M16,LENGTH=9, 35:38,8,ZD,M22,LENGTH=12, 132:X) OUTFIL FNAMES=DENMARK, HEADER2=(5:'Parts Completion Report for Denmark',2/, 5:'Printed on ',DATE=(DMY-), ' at ',TIME=(24.),3/, 5:'Part ',20:'Completed',35:' Value (kr)',/, 5:'------',20:'---------',35:'------------'), OUTREC=(5:15,6,ZD,M11, 20:3,4,ZD,M13,LENGTH=9, 35:38,8,ZD,M19,LENGTH=12, 132:X)
This example illustrates how reports for three different countries can be produced from sorted fixed-length input records. The reports differ only in the way that date, time, and numeric formats are specified:
1. The first OUTFIL statement produces a report that has the date, time, and numeric formats commonly used in the United States.
2. The second OUTFIL statement produces a report that has the date, time, and numeric formats commonly used in France.
3. The third OUTFIL statement produces a report that has the date, time, and numeric formats commonly used in Denmark.
Of course, any one of the three reports can be produced by itself using a single OUTFIL statement instead of three OUTFIL statements. (This may be necessary if you are sorting character data according to a specified locale for that country.)
The FNAMES parameter specifies the ddname (USA, FRANCE, DENMARK) associated with the fixed-length data set for that report.
The HEADER2 parameter specifies the page header to appear at the top of each page for that report, which will consist of:
? A line of text identifying the report. Note that all English text in the report can be replaced by text in the language of that country.
? A blank line (2/).
? A line of text showing the date and time. Note that variations of the DATE, DATE=(abcd), TIME, and TIME=(abc) operands are used to specify the date and time in the format commonly used in that country.
? Two blank lines (3/).
? Two lines of text showing headings for the columns of data. Note that the appropriate currency symbol can be included in the text.
The OUTREC parameter specifies the three columns of data to appear for each input record as follows:
? A 6-byte edited numeric value produced by transforming the ZD value in bytes 15 through 20 according to the pattern specified by M11. M11 is a pattern for showing integers with leading zeros.
? A 9-byte (LENGTH=9) edited numeric value produced by transforming the ZD value in bytes 3 through 6 according to the pattern for integer values with thousands separators commonly used in that country. M12 uses a comma for the thousands separator. M16 uses a blank for the thousands separator. M13 uses a period for the thousands separator.
? A 12-byte (LENGTH=12) edited numeric value produced by transforming the ZD value in bytes 38 through 45 according to the pattern for decimal values with thousands separators and decimal separators commonly used in that country. M18 uses a comma for the thousands separator and a period for the decimal separator. M22 uses a blank for the thousands separator and a comma for the decimal separator. M19 uses a period for the thousands separator and a comma for the decimal separator.
Table 31 in topic 1.13 shows the twenty-seven pre-defined edit masks (M0-M26) from which you can choose.

132:X is used at the end of the OUTREC parameter to ensure that the data records are longer than the report records. This will result in an LRECL of 132 for the fixed-length OUTFIL data sets (1 byte for the ANSI control character and 131 bytes for the data).
The three reports might look as follows:
__________________________________________________________________________________
| |
| Parts Completion Report for USA |
| |
| Printed on 03/25/95 at 01:56:20 pm |
| |
| |
| Part Completed Value ($) |
| ------ --------- ------------ |
| 000310 562 8,317.53 |
| 001184 1,234 23,456.78 |
| 029633 35 642.10 |
| 192199 3,150 121,934.65 |
| 821356 233 2,212.34 |
| |
|__________________________________________________________________________________|

__________________________________________________________________________________
| |
| Parts Completion Report for France |
| |
| Printed on 25/03/1995 at 13:56:20 |
| |
| |
| Part Completed Value (F) |
| ------ --------- ------------ |
| 000310 562 8 317,53 |
| 001184 1 234 23 456,78 |
| 029633 35 642,10 |
| 192199 3 150 121 934,65 |
| 821356 233 2 212,34 |
| |
|__________________________________________________________________________________|

__________________________________________________________________________________
| |
| Parts Completion Report for Denmark |
| |
| Printed on 25-03-95 at 13.56.20 |
| |
| |
| Part Completed Value (kr) |
| ------ --------- ------------ |
| 000310 562 8.317,53 |
| 001184 1.234 23.456,78 |
| 029633 35 642,10 |
| 192199 3.150 121.934,65 |
| 821356 233 2.212,34 |
| |
|__________________________________________________________________________________|

Example ? 3 :

OUTFIL Ranges :

The OUTFIL operands STARTREC and ENDREC can be used to select a range of records to be included in each output data set. STARTREC starts processing at a specific input record while ENDREC ends processing at a specific input record.
Here's an example of OUTFIL ranges:

OUTFIL FNAMES=FRONT,ENDREC=500
OUTFIL FNAMES=MIDDLE,STARTREC=501,ENDREC=2205
OUTFIL FNAMES=BACK,STARTREC=2206

? Inp
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Sort Card to Remove Duplicate records... raj4neo SYNCSORT 2 Wed Jan 25, 2017 4:44 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us