IBM Mainframe Forum

by **Vicky007** » Thu Sep 30, 2010 11:57 pm

Hi Frank,

I have to join more than 50 records as I said in my earlier post; when I was checking some of the posts in this forum
syncsort-synctool/topic4000.html
I happened to see this, they were discussing similar kind of requirement; they are using the 'SUM' and binary zeros to join the records into one row.

But, I didn't understand how it was achieved as it didn't had any explanation. Could you please have a look at it and let me whether it solves my problem of 'joining more than 50 records'? Also, if you could give a piece of code for this that would be great.

Thanks in advance!
Vicky SK

by **skolusu** » Fri Oct 01, 2010 4:31 am

Vicky07,

The following DFSORT JCL will give you the desired results. I showed for 60 records but you can expand it to a max of 30,000/80 = 375 records per group.

Important notes :

1. You should have all your when=group statements on the INREC before this last IFTHEN=(WHEN=(30009,3,ZD,EQ,1),OVERLAY= ... statement. This statement will clean up any additional data that was pushed from the previous group from pos 81 towards the end.

2. We also use Sections and TRAILER3 for pulling the last record from each group with all the records moved into their respective position. The max length on trailer field is 256 bytes.. So I used the field position in the increments of 256.

Here I showed for 60 records per group, if you increase the record limit within each group, make sure to increment the values on trailer3 in the increments of 256.

Select all

//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=your input FB 80 byte lrecl file,DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=COPY
INREC IFOUTLEN=30008,
IFTHEN=(WHEN=INIT,BUILD=(30012:1,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30012,1,CH,EQ,C'@'),
PUSH=(30001:ID=8,SEQ=3)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,01),PUSH=(00001:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,02),
END=(30009,3,ZD,EQ,1),PUSH=(00081:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,03),
END=(30009,3,ZD,EQ,1),PUSH=(00161:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,04),
END=(30009,3,ZD,EQ,1),PUSH=(00241:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,05),
END=(30009,3,ZD,EQ,1),PUSH=(00321:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,06),
END=(30009,3,ZD,EQ,1),PUSH=(00401:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,07),
END=(30009,3,ZD,EQ,1),PUSH=(00481:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,08),
END=(30009,3,ZD,EQ,1),PUSH=(00561:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,09),
END=(30009,3,ZD,EQ,1),PUSH=(00641:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,10),
END=(30009,3,ZD,EQ,1),PUSH=(00721:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,11),
END=(30009,3,ZD,EQ,1),PUSH=(00801:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,12),
END=(30009,3,ZD,EQ,1),PUSH=(00881:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,13),
END=(30009,3,ZD,EQ,1),PUSH=(00961:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,14),
END=(30009,3,ZD,EQ,1),PUSH=(01041:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,15),
END=(30009,3,ZD,EQ,1),PUSH=(01121:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,16),
END=(30009,3,ZD,EQ,1),PUSH=(01201:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,17),
END=(30009,3,ZD,EQ,1),PUSH=(01281:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,18),
END=(30009,3,ZD,EQ,1),PUSH=(01361:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,19),
END=(30009,3,ZD,EQ,1),PUSH=(01441:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,20),
END=(30009,3,ZD,EQ,1),PUSH=(01521:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,21),
END=(30009,3,ZD,EQ,1),PUSH=(01601:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,22),
END=(30009,3,ZD,EQ,1),PUSH=(01681:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,23),
END=(30009,3,ZD,EQ,1),PUSH=(01761:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,24),
END=(30009,3,ZD,EQ,1),PUSH=(01841:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,25),
END=(30009,3,ZD,EQ,1),PUSH=(01921:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,26),
END=(30009,3,ZD,EQ,1),PUSH=(02001:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,27),
END=(30009,3,ZD,EQ,1),PUSH=(02081:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,28),
END=(30009,3,ZD,EQ,1),PUSH=(02161:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,29),
END=(30009,3,ZD,EQ,1),PUSH=(02241:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,30),
END=(30009,3,ZD,EQ,1),PUSH=(02321:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,31),
END=(30009,3,ZD,EQ,1),PUSH=(02401:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,32),
END=(30009,3,ZD,EQ,1),PUSH=(02481:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,33),
END=(30009,3,ZD,EQ,1),PUSH=(02561:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,34),
END=(30009,3,ZD,EQ,1),PUSH=(02641:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,35),
END=(30009,3,ZD,EQ,1),PUSH=(02721:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,36),
END=(30009,3,ZD,EQ,1),PUSH=(02801:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,37),
END=(30009,3,ZD,EQ,1),PUSH=(02881:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,38),
END=(30009,3,ZD,EQ,1),PUSH=(02961:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,39),
END=(30009,3,ZD,EQ,1),PUSH=(03041:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,40),
END=(30009,3,ZD,EQ,1),PUSH=(03121:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,41),
END=(30009,3,ZD,EQ,1),PUSH=(03201:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,42),
END=(30009,3,ZD,EQ,1),PUSH=(03281:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,43),
END=(30009,3,ZD,EQ,1),PUSH=(03361:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,44),
END=(30009,3,ZD,EQ,1),PUSH=(03441:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,45),
END=(30009,3,ZD,EQ,1),PUSH=(03521:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,46),
END=(30009,3,ZD,EQ,1),PUSH=(03601:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,47),
END=(30009,3,ZD,EQ,1),PUSH=(03681:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,48),
END=(30009,3,ZD,EQ,1),PUSH=(03761:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,49),
END=(30009,3,ZD,EQ,1),PUSH=(03841:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,50),
END=(30009,3,ZD,EQ,1),PUSH=(03921:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,51),
END=(30009,3,ZD,EQ,1),PUSH=(04001:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,52),
END=(30009,3,ZD,EQ,1),PUSH=(04081:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,53),
END=(30009,3,ZD,EQ,1),PUSH=(04161:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,54),
END=(30009,3,ZD,EQ,1),PUSH=(04241:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,55),
END=(30009,3,ZD,EQ,1),PUSH=(04321:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,56),
END=(30009,3,ZD,EQ,1),PUSH=(04401:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,57),
END=(30009,3,ZD,EQ,1),PUSH=(04481:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,58),
END=(30009,3,ZD,EQ,1),PUSH=(04561:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,59),
END=(30009,3,ZD,EQ,1),PUSH=(04641:30012,80)),
IFTHEN=(WHEN=GROUP,BEGIN=(30009,3,ZD,EQ,60),
END=(30009,3,ZD,EQ,1),PUSH=(04721:30012,80)),
IFTHEN=(WHEN=(30009,3,ZD,EQ,1),
OVERLAY=(81:4095X,4095X,4095X,4095X,4095X,4095X,4095X,1255X))

OUTFIL REMOVECC,NODETAIL,BUILD=(1,30000),
SECTIONS=(30001,8,
TRAILER3=(00001,256,
00257,256,
00513,256,
00769,256,
01025,256,
01281,256,
01537,256,
01793,256,
02049,256,
02305,256,
02561,256,
02817,256,
03073,256,
03329,256,
03585,256,
03841,256,
04097,256,
04353,256,
04609,256,
04865,256,
05121,256,
05377,256,
05633,256,
05889,256,
06145,256,
06401,256,
06657,256,
06913,256,
07169,256,
07425,256,
07681,256,
07937,256,
08193,256,
08449,256,
08705,256,
08961,256,
09217,256,
09473,256,
09729,256,
09985,256,
10241,256,
10497,256,
10753,256,
11009,256,
11265,256,
11521,256,
11777,256,
12033,256,
12289,256,
12545,256,
12801,256,
13057,256,
13313,256,
13569,256,
13825,256,
14081,256,
14337,256,
14593,256,
14849,256,
15105,256))
//*

by **Vicky007** » Fri Oct 01, 2010 8:45 pm

Hi Kolusu,

Thanks alot! Highly appreciate for your quick response. I used your sortcard and I got the desired results of joining more than 50 records. Thanks again!

But the first record of each '@' alone printed twice. I will explain with an example
Input file (FB,LRECL=80):
@AAAAA
BBBBB
CCCCC
DD
@CCC
AAAAA
@FFFF
@DDDDDD
PPPPP
VVVVV
GGGGG
HHHHHH

Output - When I used the above code
@AAAAA
@AAAAABBBBBCCCCCDD
@CCC
@CCCAAAAA
@FFFF
@FFFF
@DDDDDD
@DDDDDDPPPPPVVVVVGGGGGHHHHHH

But the correct output should be
@AAAAABBBBBCCCCCDD
@CCCAAAAA
@FFFF
@DDDDDDPPPPPVVVVVGGGGGHHHHHH

I know there wil be a simple change to get this correct. I tried myself fixing it but couldn't as am just a beginner in sort.
However, the primary thing to join more than 50 records is achieved. Thanks Kolusu for your help.

Could you please let me know where the change has to be done to get the correct output?

Thanks in advance!
Vicky SK

by **skolusu** » Fri Oct 01, 2010 9:11 pm

Vicky007 wrote:Thanks alot! Highly appreciate for your quick response. I used your sortcard and I got the desired results of joining more than 50 records. But the first record of each '@' alone printed twice. I will explain with an example

Vicky,

The Job i have shown does produce only 1 record per group. I am guessing that you overlaid the group id number in bytes 30001-8 when you changed my job to accomodate more than 60 occurances. That is the reason as to why you got 2 records for each group.

I already accounted for 29,920 bytes of spaces on the very last ifthen using overlay , you dont have to change anything unless you changed the IFOUTLEN number.

You can remove the complete outfil statements and then look at the output and check the bytes(30001-8) and make sure that you have the correct seqnum(1,2,...)

If you still have problems solving it then show us the control cards you used and the complete sysout.

by **Vicky007** » Fri Oct 01, 2010 10:01 pm

OK Kolusu, I'll check for the things which you have said and get back to you monday morning. May be I did some mistake somwhere.

Thanks,
Vicky SK

by **Vicky007** » Fri Oct 08, 2010 8:06 pm

Hi Kolusu,

My initial requirement was to process 80 byte length record but the requirement changed to 90 byte length record when I used your code. I did the required changes needed to your code but missed to reduce the length in the overlay from 1255 to 1245. This had caused the trouble of writing an extra line. I changed it and tested.

It works perfectly. Thanks alot for your help. I did this testing only today, that's why the delay in responding to you. Sorry about that.

Thanks again to you and Frank!

Thanks,
Vicky SK

IBM Mainframe Forum

combine multiple rows into one row using sort

Re: combine multiple rows into one row using sort

Re: combine multiple rows into one row using sort

Re: combine multiple rows into one row using sort

Re: combine multiple rows into one row using sort

Re: combine multiple rows into one row using sort

Re: combine multiple rows into one row using sort