Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Join the keys via JCL

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

Active User


Joined: 14 Sep 2009
Posts: 174
Location: Coimbatore

PostPosted: Mon Oct 28, 2019 9:45 pm    Post subject: Join the keys via JCL
Reply with quote

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
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2298
Location: NY,USA

PostPosted: Tue Oct 29, 2019 12:47 am    Post subject:
Reply with quote

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
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 607
Location: Maryland

PostPosted: Tue Oct 29, 2019 8:31 pm    Post subject: Re: Join the keys via JCL
Reply with quote

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
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 607
Location: Maryland

PostPosted: Tue Oct 29, 2019 8:35 pm    Post subject: Re: Join the keys via JCL
Reply with quote

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. icon_axe.gif
Back to top
View user's profile Send private message
Joerg.Findeisen

New User


Joined: 15 Aug 2015
Posts: 44
Location: Germany

PostPosted: Wed Oct 30, 2019 2:26 am    Post subject:
Reply with quote

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.. icon_exclaim.gif

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
View user's profile Send private message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 174
Location: Coimbatore

PostPosted: Wed Oct 30, 2019 4:26 pm    Post subject: Reply to: Join the keys via JCL
Reply with quote

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
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 607
Location: Maryland

PostPosted: Wed Oct 30, 2019 9:11 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Joerg.Findeisen

New User


Joined: 15 Aug 2015
Posts: 44
Location: Germany

PostPosted: Wed Oct 30, 2019 9:51 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 607
Location: Maryland

PostPosted: Wed Oct 30, 2019 11:13 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 607
Location: Maryland

PostPosted: Wed Oct 30, 2019 11:22 pm    Post subject:
Reply with quote

HEADER3 is also another option to do this.
Back to top
View user's profile Send private message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 174
Location: Coimbatore

PostPosted: Thu Oct 31, 2019 3:14 pm    Post subject: Reply to: Join the keys via JCL
Reply with quote

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
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2247
Location: Hampshire, UK

PostPosted: Thu Oct 31, 2019 4:17 pm    Post subject: Reply to: Join the keys via JCL
Reply with quote

What do you not understand about it? Have you looked in the manual for how it works?
Back to top
View user's profile Send private message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 174
Location: Coimbatore

PostPosted: Thu Oct 31, 2019 7:05 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2298
Location: NY,USA

PostPosted: Thu Oct 31, 2019 7:58 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 607
Location: Maryland

PostPosted: Fri Nov 01, 2019 12:33 am    Post subject:
Reply with quote

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
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 INNER JOIN [F1] OUT OF SEQ ERROR balaji81_k DFSORT/ICETOOL 8 Wed Jan 30, 2019 3:21 am
No new posts Dates compare on specific dates using... balaji81_k DFSORT/ICETOOL 2 Thu Jan 24, 2019 12:03 am
No new posts Fetch first first row in inner join! Vignesh Sid DB2 13 Tue Dec 04, 2018 7:25 pm
No new posts Process only last key record in a fil... Naveen.katkuri24 DFSORT/ICETOOL 8 Tue Oct 09, 2018 9:39 am
No new posts ICETOOL - Finding keys within a file nartcr DFSORT/ICETOOL 10 Mon Sep 17, 2018 6:39 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us