|
View previous topic :: View next topic
|
| Author |
Message |
abdulrafi
Active User
Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore
|
|
|
|
Hi,
Could you please help me if I could achieve the below via JCL. I tried it with JOIN keys but could not get the expected one.
Input file1:
| Code: |
CNTLBOS AUTOMOTIVE PRODU 003010030I P862PO 000001
CNTLBOS AUTOMOTIVE PRODU 003020030I P862PO 000002
CNTLGRUPO ANTOLIN KENTUC 007720077I P862PO 000003
CNTLCHUHATSU NORTH AMERI 008000080I P862PO 000004
CNTLAISIN WORLD CORP OF 010000100I P862PO 000005
CNTLAISIN WORLD CORP OF 010040100I P862PO 000006
CNTLAISIN WORLD CORP OF 010070100I P862PO 000007
CNTLAISIN MEXICANA S.A. 010100101I P862PO 000008
|
Input file2:
| Code: |
BFR 00 001 DLA191029191101191025 000003
N1 MI02 000003
N1 SU BOS AUTOMOTIVE PRODU92 00301 000003
LIN1 BP 649100E080C0RC VYA2ZZ 000003
UNT PC 000003
PID COVER ASSY, TONNEAU 000003
PO41 00036 000003
BFR 00 001 DLA191029191031191025 000004
N1 MI02 000004
N1 SU BOS AUTOMOTIVE PRODU92 00302
000004
LIN1 BP 663110E010B0RC HYC1ZZ 000004
UNT PC 000004
PID HOOK, ROPE 000004
PO41 00060 000004
PRS 1 000004
|
Expected output:
It has to match the Input file 1 with input file 2 and fetch the corresponding records based on sequence numbers.
| Code: |
CNTLGRUPO ANTOLIN KENTUC 007720077I P862PO
000003
BFR 00 001 DLA191029191101191025 000003
N1 MI02 000003
N1 SU BOS AUTOMOTIVE PRODU92 00301 000003
LIN1 BP 649100E080C0RC VYA2ZZ 000003
UNT PC 000003
PID COVER ASSY, TONNEAU 000003
PO41 00036 000003
CNTLCHUHATSU NORTH AMERI 008000080I P862PO
000004
BFR 00 001 DLA191029191031191025 000004
N1 MI02 000004
N1 SU BOS AUTOMOTIVE PRODU92 00302 000004
LIN1 BP 663110E010B0RC HYC1ZZ 000004
UNT PC 000004
PID HOOK, ROPE 000004
PO41 00060 000004
PRS 1 000004
|
Sorry for the sequence number. Not able to align it properly. |
|
| Back to top |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
OK. Did you try anything or just seeking for ready solutions? This task looks very basic for JOINKEYs and you can try by learning form JOINKEYS application examples. If you don't like this link then there are plenty on this website that you can go thru.
If you still face any issues then post back with the details and we will assist your further. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2288
Location: USA
|
|
|
|
| abdulrafi wrote: |
Hi,
Could you please help me if I could achieve the below via JCL. I tried it with JOIN keys but could not get the expected one. |
JCL cannot perform any JOIN. It is just not able to do so.
JCL is used to start ready-to-use programs (e.g. load modules), and define datasets to be used by those programs. Nothing else.
Period. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2288
Location: USA
|
|
|
|
| abdulrafi wrote: |
Hi,
Sorry for the sequence number. Not able to align it properly. |
If you cannot align the sequence numbers by yourself, then JCL definitely wouldn't do it for you.
This is the major reason why you cannot get what you want.  |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1442
Location: Bamberg, Germany
|
|
|
|
It is not fully clear to me if the second dataset is single line with seqno or multi line. At first however it looks like a big mess.. 
Assuming seqno is always the last value in a line you could try the following:
- On INREC temporary extend the record by LRECL using OVERLAY/BUILD
- At Position LRECL+1 use 1,LRECL,JFY=(SHIFT=RIGHT))
- You now have fixed columns for the seqno at the very right
- Do some preps for keeping the right sequence of records as well
- Joinkeys as desired
- Postprocessing
Dirty deeds done dirt cheap. |
|
| Back to top |
|
|
abdulrafi
Active User
Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore
|
|
|
|
Hi Rohit,
Joinkeys are padding the records adjacent to it like,
1-250(first record with seq no as 00001)1-250(second record with seq no as 00001) - This happens when I give the LRECL to 500. If I give it as 250 I do not get the record from second file at all.
| Code: |
//JS040 EXEC PGM=SORT,REGION=0M
//SYSOUT DD SYSOUT=*,DCB=(RECFM=FBA,LRECL=133,BLKSIZE=6118)
//SORTJNF1 DD DSN=A04124T.TEST.T02.EDI862.WHDR.AZ.SORTED,DISP=SHR
//SORTJNF2 DD DSN=A04124T.TEST.T02.EDI862.WOHDR.AZ,DISP=SHR
//SORTOUT DD DSN=A04124T.TEST.T02.EDI862.WOHDR.AZ.F1,
// DISP=(NEW,CATLG,DELETE),
// UNIT=PRD,
// SPACE=(CYL,(100,100),RLSE),
// DCB=(RECFM=FB,LRECL=500,DSORG=PS)
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(245,6,A)
JOINKEYS FILE=F2,FIELDS=(245,6,A)
REFORMAT FIELDS=(F1:1,250,F2:1,250)
SORT FIELDS=(245,6,CH,A)
|
But I would need,
1-250 (first record) - assuming seq no is 00001
1-250(second record) - assuming seq no is 00001
1-250(second record) - assuming seq no is 00001
1-250 (first record) - assuming seq no is 00002
1-250(second record) - assuming seq no is 00002
1-250(second record) - assuming seq no is 00002
1-250(second record) - assuming seq no is 00002
Note: Sequence number is the key. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2288
Location: USA
|
|
|
|
| Code: |
//*=====================================================================
//* JOIN GARBAGE
//*=====================================================================
//GARBAGE EXEC PGM=SORT
//*
//SYSOUT DD SYSOUT=*
//*
//HEADERS DD *
CNTL BOS AUTOMOTIVE PRODU 00301 0030I P862PO 000001
CNTL BOS AUTOMOTIVE PRODU 00302 0030I P862PO 000002
CNTL GRUPO ANTOLIN KENTUC 00772 0077I P862PO 000003
CNTL CHUHATSU NORTH AMERI 00800 0080I P862PO 000004
CNTL AISIN WORLD CORP OF 01000 0100I P862PO 000005
CNTL AISIN WORLD CORP OF 01004 0100I P862PO 000006
CNTL AISIN WORLD CORP OF 01007 0100I P862PO 000007
CNTL AISIN MEXICANA S.A. 01010 0101I P862PO 000008
//*-+----1----+----2----+----3----+----4----+----5----+----6----+----7-
//*
//GARBAGE DD *
BFR 00 001 DLA1910291911 000003
N1 MI02 000003
N1 SU BOS AUTOMOTIVE PRODU92 00301 000003
LIN1 BP 649100E080C0RC VYA2ZZ 000003
UNT PC 000003
PID COVER ASSY, TONNEAU 000003
PO41 00036 000003
BFR 00 001 DLA1910291910 000004
N1 MI02 000004
N1 SU BOS AUTOMOTIVE PRODU92 00302 000004
LIN1 BP 663110E010B0RC HYC1ZZ 000004
UNT PC 000004
PID HOOK, ROPE 000004
PO41 00060 000004
PRS 1 000004
//*-+----1----+----2----+----3----+----4----+----5----+----6----+----7-
//*
//SORTOUT DD SYSOUT=*
//*
//SYSIN DD *
JOINKEYS F1=HEADERS,FIELDS=(66,6,A)
JOINKEYS F2=GARBAGE,FIELDS=(66,6,A)
*
REFORMAT FIELDS=(F2:1,80,?,F1:1,80)
*
SORT FIELDS=COPY
*
OUTFIL IFTHEN=(WHEN=GROUP,
KEYBEGIN=(66,6),
PUSH=(162:SEQ=3)),
IFTHEN=(WHEN=(162,3,ZD,EQ,+1),
BUILD=(/,82,80)),
IFTHEN=(WHEN=NONE,
BUILD=(01,80))
END
//*
//*===================================================================== |
| Code: |
********************************* TOP OF DATA ****************************
CNTL GRUPO ANTOLIN KENTUC 00772 0077I P862PO 000003
N1 MI02 000003
N1 SU BOS AUTOMOTIVE PRODU92 00301 000003
LIN1 BP 649100E080C0RC VYA2ZZ 000003
UNT PC 000003
PID COVER ASSY, TONNEAU 000003
PO41 00036 000003
CNTL CHUHATSU NORTH AMERI 00800 0080I P862PO 000004
N1 MI02 000004
N1 SU BOS AUTOMOTIVE PRODU92 00302 000004
LIN1 BP 663110E010B0RC HYC1ZZ 000004
UNT PC 000004
PID HOOK, ROPE 000004
PO41 00060 000004
PRS 1 000004
******************************** BOTTOM OF DATA ************************** |
P.S.
This thing is called SORT utility; nothing to do with JCL, at least no more than with a "computer". |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1442
Location: Bamberg, Germany
|
|
|
|
Shouldn't it be that on KEYBEGIN the line from DD:HEADERS is to be taken and all others with same seqno from DD:GARBAGE?
| Code: |
OUTFIL REMOVECC,SECTIONS=(66,6,SKIP=L,
HEADER3=(82,80)),
BUILD=(1,80)
END |
Result:
| Code: |
CNTL GRUPO ANTOLIN KENTUC 00772 0077I P862PO 000003
BFR 00 001 DLA1910291911 000003
N1 MI02 000003
N1 SU BOS AUTOMOTIVE PRODU92 00301 000003
LIN1 BP 649100E080C0RC VYA2ZZ 000003
UNT PC 000003
PID COVER ASSY, TONNEAU 000003
PO41 00036 000003
CNTL CHUHATSU NORTH AMERI 00800 0080I P862PO 000004
BFR 00 001 DLA1910291910 000004
N1 MI02 000004
N1 SU BOS AUTOMOTIVE PRODU92 00302 000004
LIN1 BP 663110E010B0RC HYC1ZZ 000004
UNT PC 000004
PID HOOK, ROPE 000004
PO41 00060 000004
PRS 1 000004 |
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2288
Location: USA
|
|
|
|
| Joerg.Findeisen wrote: |
| Shouldn't it be that on KEYBEGIN the line from DD:HEADERS is to be taken and all others with same seqno from DD:GARBAGE? |
Sorry, my fault. First line was missing after the header
| Code: |
. . . . . . . .
OUTFIL IFTHEN=(WHEN=GROUP,
KEYBEGIN=(66,6),
PUSH=(162:SEQ=3)),
IFTHEN=(WHEN=(162,3,ZD,EQ,+1),
BUILD=(/,82,80,/,01,80)),
IFTHEN=(WHEN=NONE,
BUILD=(01,80))
. . . . . . . . |
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2288
Location: USA
|
|
|
|
| HEADER3 is also another option to do this. |
|
| Back to top |
|
|
abdulrafi
Active User
Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore
|
|
|
|
Hi,
Thanks for your help.
I do have one query. I did not understand this build statement,
| Code: |
| BUILD=(/,82,80,/,01,80)), |
Can you please let me know how does it work. ? |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
| What do you not understand about it? Have you looked in the manual for how it works? |
|
| Back to top |
|
|
abdulrafi
Active User
Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore
|
|
|
|
I tried to use the same logic for my code and i modified it as follows based on the LRECL I need, but I got duplicates getting fetched. I did not get the correct output. I did not understand why 162 was chosen to add sequence. I believe its a imaginary position chosen to add seq no. I explored to find the options for BUILD but could not get what I need.
This is what I modified,
From 246, 6 bytes is the key 000001. I added seq numbers from 251, 3 bytes. The increment is coming correctly. Now I assume my record will be like,
1 - 244 (File 1) | 245 - 250 (seq no 6 bytes) | 251 - 253 (seq no 3 bytes via PUSH) | 1- 250 (File 2). The I gave the build to split and write the record,
| Code: |
| BUILD=(/,254,250,/,01,250)), |
I got many duplicate records. So need your help to confirm if my understanding is correct ?.
| Code: |
JOINKEYS FILE=F1,FIELDS=(245,6,A)
JOINKEYS FILE=F2,FIELDS=(245,6,A)
REFORMAT FIELDS=(F2:1,250,?,F1:1,250)
SORT FIELDS=COPY
OUTFIL IFTHEN=(WHEN=GROUP,
KEYBEGIN=(245,6),
PUSH=(251:SEQ=3)),
IFTHEN=(WHEN=(251,3,ZD,EQ,+1),
BUILD=(/,254,250,/,01,250)),
IFTHEN=(WHEN=NONE,
BUILD=(01,250)) |
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
| Code: |
JOINKEYS FILE=F1,FIELDS=(245,6,A)
JOINKEYS FILE=F2,FIELDS=(245,6,A) |
and
| Quote: |
| From 246, 6 bytes is the key 000001 |
They don't certainly match everywhere they referenced , so fix all that and Rerun. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2288
Location: USA
|
|
|
|
| abdulrafi wrote: |
I tried to use the same logic
| Code: |
REFORMAT FIELDS=(F2:1,250,?,F1:1,250)
|
|
Minor clues.
This statement creates intermediate records of 250+1+250=501 bytes long each.
The '?' stands for "matching indicator byte"; in fact it's not used in this particular case (but you might need it in further adjustments for new requirements, so leave it in place for not to re-calculate byte offsets in the future). That's why the second part of combined record begins not in position 251, but 252!
Please, re-consider the new offsets you have updated in your sample of code.
The parameter
| Code: |
| BUILD=(/,N1,L1,/,N2,L2) |
creates TWO output records from ONE combined record (plus empty record before each group, if needed).
As said before, another option is: using parameters SECTIONS=, and HEADER3=. It may be more obvious in some cases.
If this is not clear, then:
1) RTFM
2) Switch to the Beginner's Forum. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
