tablefullpac

Posted: Fri Jan 01, 2010 3:28 pm Post subject: use of icetool to concatenate records from different files Hi, i have fil1 with record length 260 and file2 with record length 200.requirement id to compare the records from both the files based on data in different position (like file1-1,10 and file2 111-10 should match). Concatenating ISPF libraries. There are also a bunch of links to IBM doc in here this time.) Some libraries are used by both TSO/ISPF and native TSO. This includes some basic load libraries (loadlibs) containing programs in executable format (e.g., load modules), such as might be found on a STEPLIB DD statement in the TSO Logon JCL procedure. DFSORT – Merge two files using SORT. If the first set of data is not sorted, your JOB shall abend with S000 U0016. To resolve this abend – make sure your 1st set of data is sorted. Lets say, we have below records for 1st set. They are employee name, employee salary and employee department.

1. Concantenate Records In Dfsort 5

I have a 6 row input file which consists of a field(Position 1 to 6) that contains a different value on every line. Based on the different values contained in this field the other fields (From position 7 -80) will be moved to to a single row in output.

E.G.

Input:

Output:

I need to find out how to read these all in as different rows and then output to a single row. I've tried using something similar to the code to this:

But this will move the data onto the correct position on seperate rows like this:

So now I think I need to do a sort in one step and a merge in another step. I would prefer to do it in one if it's possible however. I'd appreciate any help on this. Thanks.

1 Answer

You add a sequence-number to each record.

The use WHEN=GROUP to copy data from one record to one or more subsequent records.

You use OUTFIL INLUDE= to just pick up the final record.

You need to do a bit of planning. The sixth record will contain all the data, but perhaps not yet in the order that you want. Either with an IFTHEN=(WHEN=(logical expression) (to identify the sixth record) on the INREC or with the BUILD on the OUTREC, you can do your final formatting.

You need to change the something each time, it will be receivingposition:sourceposition,length

Concantenate Records In Dfsort 5

The DFSORT manuals are very good, there is a Getting Started for those new to the product, and everything you'll ever need is in the Application Programming Guide,

Not the answer you're looking for? Browse other questions tagged mergemainframejcldfsort or ask your own question.

• Other names that can be used to invoke SORT are ICEMAN,IERRCO00,IGHRCOOO and SYNCSORT.
• Sorting can be done from ISPF prompt also. For this open the file to be sorted in edit mode. Then use the command 'SORT 1 22' to sort the file from 1st to 22nd characters.


• DFSORT is invoked using JCL.
SampleJCL:
//STEPSORTEXEC PGM=SORT,PARM=sort-parms
//SORTINDD DSN=G1SG00AT.SORTIN1,DISP=SHR Input for sort
//SORTINnnDD DSN=G1SG00AT.SORTIN2,DISP=SHR Input for merge
// DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(2,2),RLSE),
//SORTOFnnDD DSN=G1SG00AT.SORTOUT, Output data sets
//SORTXSUMDD DSN=G1SG00AT.SORTOUT, Records eliminated by SUM
//SORTWK01DD SPACE=(CYL,(20,15),RLSE),UNIT=SYSDA
//SYSOUTDD SYSOUT=*


FollowingDDNAMES are required to code a DFSORT job.
1. Signals the initiation of a job. JOB statement contains all normal parameters as in normal JCL.
2. Signals the initiation of a step. Tells OS which program is to be executed. For DFSORT the program to be executed is SORT.

1. //STEPNAME EXEC PGM=SORT
3. Defines the output dataset for messages.


4. Defines the input dataset that needs to be sorted. Input Could be sequential, extended sequential, PDSE member, PDS member or a VSAM dataset. Files can be concatenated if the RECFM is the same for all files. For variable length files the file with largest record length must be first.
5. Used for merge request input files . Up to 100 SORTINnn DD's may be specified. The 'nn' suffix can be any number in the range 00 thru 99; they may be skipped or used out of order. The files must already be sorted in the proper sequence for the MERGE.


6. From 1 to 100 sort work files can be allocated using the SORTWKnn DD statement. The 'nn' suffix can be any number in the range 00 thru 99.SORTWKnn DD's are not used for a MERGE or a copy operation, or if the DYNALLOC parmis used. Each SORTWKnn MUST be on one unit & one volume.
7. Defines the output dataset. It can be sequential, extended sequential data set ,a PDS member or a PDSE member.
8. Output file for a SORT, MERGE, or copy function. Up to 100 SORTOFnn DD's may be specified. The 'nn' suffix can be any number in the range 00 through 99. They may be skipped or used out of order. The FNAMES/FILES parameter on the OUTFILE control statement specifies the DD names of the output files and the output records that are written to each SORTOFnn data set.
9. Output file for a SORT or MERGE function. The records eliminated by SUM processing are written to the SORTXSUM DD.
10. Defines the sort cards.


• Sorting data:


Sorting means Rearranging data in either ascending ordescending order.
SORT FIELDS=(Startingposition of field, Length of the field, datatype, Ascending or Descendingorder)
SORTFIELDS=(pos1,len1,type1,opt1,pos2,len2,type2,opt2,...)
{,FILSZ=n eN}
{,SKIPREC=n} --> skipthese many records and then start the sort operation
{,EQUALS NOEQUALS}


Determines whether or not the sort will preserve theorder of records with identical sort fields.
NOEQUALS is the default, and causes equal-keyed recordsto bewritten in a random order, not in the order they were encountered.
Example:
SORT FIELDS=(16,15,CH,A)--> Sorting on single field(in ascending order)
SORT FIELDS=(16,15,CH,A,31,15,CH,A) --> Sorting onmultiple Fields.
We can also write the above statement as
SORT FIELDS=(16,15,A,31,15,A),FORMAT=CH --> Use FORMATif the format of all fields being sorted is same.


1. EBCDIC character: CH
2. ASCII character: AC
3. BINARY : BI (COMP fields)
4. ZONED Decimal: ZD (numeric fields with USAGE as display)
5. Packed Decimal : PD (COMP-3)
6. Alternate Sequence: AQ


• Merging datasets:


Merge two or more datasets to form a single dataset.
1. Datasets should have same LRECL and RECFM
2. Datasets should be sorted prior to Merge.


Remember that merging is different from concatenating.Concatenating means data is just appended back to back. With merging sort orderis maintained.
Merge Syntax is :
MERGE FIELDS=...{,FILES=n}{,EQUALS NOEQUALS}{,CKPT CHKPT}{,CENTWIN={0 s f}}
Example;
MERGE FIELDS=(16,15,A,31,15,A),FORMAT=CH
The sort order 'A' indicates the order in which the datais already sorted in the input files.
When using Merge do not use SORTWKnn DD statement.
Instead of SORTIN DD we use SORTINnn DD. One SORTINnnstatement for each of the input file. Nn stands for 01 to 16.


• Processing Order of control statements:
1. INCLUDE /OMIT
2. INREC Statement
3. SORT/MERGE
4. OUTREC Statement


• Copying datasets:
The SORT/MERGE control statement with FIELDS=COPY definesthe application as a COPY application.
SORT FIELDS=COPY


Note that with sort, merge we should always use FIELDS=(even of we are just requesting a copy application)
Following is the format:
SORT FIELDS=COPY{,SKIPREC=n}{,STOPAFT=n}{,CKPT CHKPT}or
MERGE FIELDS=COPY{,SKIPREC=n}{,STOPAFT=n}{,CKPT CHKPT}
SKIPREC indicatesthe number of records to be skipped.(With OUTFIL equivalent is STARTREC. Notethat STARTREC can be used only with OUTFIL)
STOPAFT indicatesthe number of records to process. (With OUTFIL equivalent is ENDREC. Note thatENDREC can be used only with OUTFIL)
CKPT/CHKPTindicates a checkpoint is desired at the end of volume of a SORTOUT data setwhen OUTFIL is not used.
Note that we could also code OPTION COPY to perform the copy application
• INCLUDE Statement:


Used to establish selection criteria for records to beincluded in the output dataset.
We can compare the contents of its fields to a constantor another field in the record.
Include Statement cannot appear with OMIT statement onthe same SORT operation.
Format Ofinclude:
INCLUDE COND={({c1,{AND OR},c2},...){,FORMAT=x} }
{NONE}
• COND=ALL means all input records are to be included.


• COND=NONE means none of the input records are to be included.
• AND may be specified as &. OR may be specified as ¦.
The AND and OR relational operators are used to linktogether as many repetitions of the 'expression' field as desired.
Example:
SORT FIELDS=(16,15,CH,A)
INCLUDE COND=(16,15,CH,EQ,31,15,CH) --> comparing withother field in the record
SORT FIELDS=COPY
INCLUDE COND=(46,10,BI,GT,2000,OR,46,10,BI,LT,10000)--> Comparing Against constants
INCLUDE COND=((10,3,CH,EQ,C'REV'),AND,(67,2,CH,NE,C'GA'))--> Using logical operator AND
INCLUDECOND=((10,3,EQ,C'REV'),AND,(67,2,NE,C'GA')),FORMAT=CH
The INCLUDE above will only pass through records with thecharacter string 'REV' inposition 10 and the state abbreviation 'GA' inposition 67.
INCLUDECOND=((21,1,BI,EQ,X'22'),OR,(21,1,BI,EQ,X'47'),OR,(21,1,BI,EQ,X'F3')) -->Using logical operator OR.


• OMIT statement:
Used to set up selection criteria for records to beomitted from the output dataset.
We can omit a record by comparing contents of its fieldsto a constant or another field in the record.
Omit Statement cannot appear with INCLUDE on the sameSORT execution.
Format of OMIT:
OMIT COND={({c1,{AND OR},c2},...){,FORMAT=x} }
{NONE}
Example:
SORT FIELDS=COPY


OMIT COND=((10,3,CH,EQ,C'REV'),AND,(67,2,CH,NE,C'GA')) OR
OMITCOND=((10,3,EQ,C'REV'),AND,(67,2,NE,C'GA')),FORMAT=CH
Comparison Operators that can be used with INCLUDE andOMIT are EQ,NE,GT,GE,LE,LT
• SUMming fields:


The SUM control statement deletes records with equalcontrol fields and optionally summarizes specified numeric fields on thoserecords. If numeric fields are to be summarized, the data in the summary fieldsis added, the sum is placed in one of the records, and the other record isdeleted.


{FIELDS={NONE}}
{FIELDS=(p1,l1{,p2,l2) ... ),FORMAT=f } {,XSUM}
Where XSUM means the dropped records are written in thedataset specified by SORTXSUM data set.
INCLUDE COND=(31,4,CH,EQ,C'MIKE')
SUM FIELDS=(46,10,BI)
The result will contain a Single row which is top mostrowof the Sorted fields including the condition mentioned in INCLUDE Statement,and the Price Fields will contain the Sum of prices of all books whose Authoris MIKE.
• Eliminating duplicates on a field:


SUM FIELDS=NONE
We could use XSUM if we want to save off the remainingrecords.
SUM FIELDS=NONE,XSUM
In this case we must have a additional DD calledSORTXSUM. This is where the discards will go.


• Reformatting output:
We can perform reformatting using INREC,OUTREC andOUTFIL.
We can use INREC,OUTREC,OUTFIL to
1. Delete fields
2. Reorder fields
3. Insert strings or blanks
4. Convert data to hexadecimal representation.
5. Convert case.
6. Edit numeric values
7. Convert numeric values to another format.
8. Perform arithmetic operations.
9. Insert sequence number.
10. Change record Length.
INREC: Formatting will be done before sorting ,merging or copying of the records is done. Hence we will refer the originalfile layout when using INREC. In short operation will be performed on the inputfile.
Use of INREC improves the sort performance by reducingthe number of bytes that must be processed.
The format of the INREC statement is INREC FIELDS=(...).
The FIELDS parameter simply identifies the fields thatshould be processed.
A sample INREC statement is as follows:


In this statement, 3 data fields are specified asfollows:
• The first field begins in byte 1 of the input record and is 20 bytes long.
It will be in position 1 of the output record.
• The second field begins in byte 40 of the input record and is a 15-byte ZD field.
The field will be converted to PD. It will be in position21 of the output record.
• The third field begins in byte 60 of the input record and is 5 bytes long.
It will be in position 29 of the output record.
IN the above example we used absolute positioning using':'. We can also just specify the position in input file and it willcreate fields back to back.


OUTREC: Formatting will be done after sortingor merging is done. In short Operation will be performed on the output file.
• Delete or repeat segments of the input records.
• Insert character strings between data fields.
• Insert binary zeros.
• Create a sequence number field.
• Convert numeric data to printable format or to another numeric data format.
• Perform arithmetic operations (multiply, divide, add, subtract) and minimum and maximum functions with numeric fields and constants.
• Convert data to printable hexadecimal format.Select, realign, and reorder data fields.
• Convert a variable length record input file to a fixed length record output file.


OUTFIL: The OUTFIL control statement describesthe output file or files.
OUTFILis used for following purposes:
• Create multiple output files:
This task uses these parameters: FILES,FNAMES,INCLUDE/OMIT, STARTREC, ENDREC, SAVE, OUTREC, CONVERT, SPLIT2.
• Use the Sort Writer facility:
This task uses these parameters: HEADER1, HEADER2,LINES,NODETAIL, SECTIONS, TRAILER1, TRAILER2.
• Reformatting records: This task uses these parameters: OUTREC.


Theformat of the OUTFIL statement is:
OUTFIL{FILES={fileid}{(fileid1 {fileid2}...} --> Specifies fileid's . This isused when the output DDNAMES are of type SORTOFnn. The number nn will be usedas fileid.
{,FNAMES={ddname}{(ddname1 {ddname2} ...)} --> Here we specify theactual ddname. This will be used if we don't use the names of output files asSORTOFnn.
{,INCLUDE={(c1,{,&, } C2... } --> specifies the include criteria toselect records to be copied to a file specified by FILES or FNAMEs
{,OMIT= { {,OR, } }{ {,¦, } } --> Specified the omit criteria to skip the records from beingcopied to a file specified by FILES or FNAMES
{,STARTREC=n} -->Indicates the record number of startworking for output file specified by FNAMES or FILES
{,ENDREC=n} --> Indicates the record number of stop working foroutput file specified by FNAMES or FILES
{,SPLIT}--> Split data frominput equally among output files
{,OUTREC=(field1, {,field2} ... )} --> similar to ourrec processing.Note that on OUTFIL we use OUTREC and not INREC.
{,HEADER1=(field1, {,field2} ... )}--> used for reporting purpose.
{,TRAILER1=(field1, {,field2} ... )}
{,SECTIONS=(field1, {,field2} ... )}


Examples:
1. Deleting a field:
SORT FIELDS=COPY


This will Create a output file without the 1stfield(1-15characters)
2. Reordering the fields:
SORT FIELDS=COPY




3. Insert a new field with zeros:
SORT FIELDS=COPY


4. Inserting Blanks:
SORT FIELDS=COPY


This will put 20 blank fields before 1st field and 16blank spaces before 2nd field.


5. Creating multiple copies of same dataset:
OPTION COPY


OUTPUT1,OUTPUT2,OUTPUT3 are the 3 ddnames in the JCL.
6. Selecting records to different files with STARTREC and ENDREC options


OUTFIL FNAMES=OUT1,ENDREC=5
OUTFIL FNAMES=OUT3,STARTREC=11
STARTREC,ENDREC are used only with OUTFIL processing.When not using OUTFIL we can use STOPAFT.
7. Separating records to different files based upon record contents:


OUTFIL FNAMES=OUT1,INCLUDE=(31,15,CH,EQ,C'MIKE')
OUTFIL FNAMES=OUT3,INCLUDE=(31,15,CH,EQ,C'GAND')
Note that we don’t write COND when using INCLUDE on theOUTFIL.
8. BUILD command:


Build command can us be used with OUTREC and INREC torecreate the record layout. OVERLAY can be used to keep the existing layoutsame and add/replace some data column.
SORT FIELDS=COPY


SEQNUM can be used to add sequence numbers to rows.
9. SPLIT


Suppose we have a file and we don’t know the number ofrecords and we want to divide them equally among 3 files then we could use theSPLIT option as shown below:
OPTION COPY


Above example will create 3 files each of 1/3rd size of the original file.


• Editing Numeric values:
Thereare Around 27 Predefined Edit masks named M0 to M26, which can be used toconvert non Edited data in to the Edited form.
Wecan also manually create edited forms:
Example:
SORTFIELDS=(16,15,CH,A)
OUTRECFIELDS=(5X,16,15,5X,46,8,BI,EDIT=(SIII,III,TT.TT),SIGNS=(,-))
T:- if there is no value or it is a zero thenZero will be printed Else if there is Some value that value will be printed.
e.gif EDIT=(TTTT) is give and value is 4 then it will be printed as 0004 in thereport.
I:-If there is no value Zero will not beinserted
e.g if EDIT=(IIII) is given and if the value ininput file Is 0004 then it will be printed as 4.
S:-indicated the Sign.
SIGNS=(,-) Will Print ±if the input value is negativeand does space when it is Positive.