IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Join 2 files using sync sort


IBM Mainframe Forums -> SYNCSORT
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Tharageshwari Marukkutti

New User


Joined: 10 Oct 2007
Posts: 12
Location: India

PostPosted: Tue Feb 26, 2013 4:17 pm
Reply with quote

I need to merge 2 files based on the first column in File1 and File 2. Thefirst 2 columns are of variable length. first column in the file2 should match with the prefix of first column in the File1.
How could I get this using SYnc sort ... Please assist

File1
Code:
AABZXTEASDFASDFG     500       FB
BGWERASDFASDFGG      600      VB
GTASDFASDFASDF         200       FB
BGWEASDFASDFASD      400       FB
GTASDFASDFASDFAS     100       FB


File2
Code:
AABZ    PPM     
BGWE   POR
GTASD  ESS


OUTPUT
Code:

AABZXTEASDFASDFG     500       FB    PPM
BGWERASDFASDFGG      600      VB    POR
GTASDFASDFASDFAS     200       FB    ESS
BGWEASDFASDFASD      400       FB    POR
GTASDFASDFASDFAS     100       FB     ESS


Code'd
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Feb 26, 2013 4:22 pm
Reply with quote

If you have SyncSort, please post your questions in the JCL part of the forum.
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Tue Feb 26, 2013 5:30 pm
Reply with quote

I'm not sure if I understood it very well - what do you call as "prefix of first column in the File1"? Also, what is it telling - "Thefirst 2 columns are of variable length"? Did you mean, input files are VB?

With your example, it looks like -- you want to compare two files and keep the duplicates as well. Perhaps, JOINKEYs example will you give some clue until you explain better.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Feb 26, 2013 5:41 pm
Reply with quote

I'm "guessing", from the rest of the data, that the "first field" is a DSN. So the first element of that field is the HLQ, of variable-length, ending with ".".

JOINKEYS, yes. But with your SyncSort, do you have the JNFnCNTL files available? If so, PARSE the HLQ to a temporary extension to the record, use that in the JOINKEYS.

If not, you'll need another step to make the extension "permanent" (across the two steps, can be a temporary dataset) and then do the JOINKEYS in the second step.
Back to top
View user's profile Send private message
Tharageshwari Marukkutti

New User


Joined: 10 Oct 2007
Posts: 12
Location: India

PostPosted: Tue Feb 26, 2013 5:42 pm
Reply with quote

"prefix of first column in the File1" Means first column in File1 matches with the starting few characters of first column in File1

yes File1 and File2 are of variable lenght.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Feb 26, 2013 5:56 pm
Reply with quote

You mean my guess was bad, and what you want is some type of "wildcard" search?

Is your sample data representative of the format of the data you are processing, ie is it all one long bunch of characters, with nothing to "delimit" it?
Back to top
View user's profile Send private message
Tharageshwari Marukkutti

New User


Joined: 10 Oct 2007
Posts: 12
Location: India

PostPosted: Tue Feb 26, 2013 7:23 pm
Reply with quote

File1 first column is the dataset name (44 characters ie tsotmee.test.file.batch) and File2 first column is the dataset pattern ( i.e tsotmee.test)
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


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

PostPosted: Wed Feb 27, 2013 7:02 am
Reply with quote

Quote:
first column in File1 matches with the starting few characters

How can 1 column (1 byte in sort terms) match several columns? Or did you mean field/s?
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Wed Feb 27, 2013 12:15 pm
Reply with quote

At first, it looked like if you wnat to match two files on some keys and wants to keep the duplicates, but now, I don't have any clue of what you're trying to accomplish.
Back to top
View user's profile Send private message
Tharageshwari Marukkutti

New User


Joined: 10 Oct 2007
Posts: 12
Location: India

PostPosted: Wed Feb 27, 2013 2:29 pm
Reply with quote

I'm looking for SORT jcl. I believe my example clearly tell what i really need.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Wed Feb 27, 2013 2:46 pm
Reply with quote

Tharageshwari Marukkutti

If your example clearly tells us what you need, then why are there so many questions being asked.

You need to give as much detail as possible, including in the sample data supplied. If the data is meant to represent a DSN or a part DSN, then show your data like that.

The more detail that YOU give cuts down on the ambiguity of peoples understanding of your questions, and also any lost time trying to help you because they have interpretted the requirement incorrectly.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Feb 27, 2013 3:50 pm
Reply with quote

Tharageshwari Marukkutti,

Even the title you gave causes confusion. "Merge", in terms of Sort products, means something which bears no relation to the very little information that you have provided.

Show some good sample data, and expected output, for what you want.

Have you tried anything, had any thoughts about how to approach it?

You feel you have been clear. You have not. Be clear, and be aware that if you "change your mind" later (ie don't describe what you want to the fullest degree), you are even less likely to get a solution.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


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

PostPosted: Fri Mar 01, 2013 2:19 pm
Reply with quote

Quote:
I'm looking for SORT jcl

Well, the jcl is clearly presented in one of the sort manuals. Probably the 'getting started' one. Basically it starts with EXEC PGM=SORT and continues with SYSOUT and SORTIN and SORTOUT statements. The rest of the manual is not to do with SORT JCL but with sort control cards so if you want some info on those that is where to start.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Fri Mar 01, 2013 4:01 pm
Reply with quote

Well, after sowing nothing but confusion, Tharageshwari Marukkutti seems to have left the building. Waste of a topic.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Sun Mar 03, 2013 9:02 pm
Reply with quote

Hello,

Quote:
I believe my example clearly tell what i really need.
TS obviously does not understand that it does not matter what s/he believes. What matters is if things are clear to those who would help.

Maybe there will be followup, but doesn't seem likely. . .
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Sun Mar 03, 2013 9:43 pm
Reply with quote

Well, since I've gone to the bother of thinking of a solution, it would be nice :-)
Back to top
View user's profile Send private message
Tharageshwari Marukkutti

New User


Joined: 10 Oct 2007
Posts: 12
Location: India

PostPosted: Sun Mar 03, 2013 10:30 pm
Reply with quote

Sorry the topic i had put in as "Merge" was wrong. Actually I need to join 2 files

Here is what i have to do

FILE1 is of length 80, first field starts at position 1 and of length 44, second field starts at 45 and length of 2

FILE1
Code:

P.DRMAPP.TEST.JCL                           FB
TSOTJOM.TEST.JCL                            FB
AUTO.TEST                                   VB
P.ASDLFHKA.R010.STEP001.AUDIT               VB
P.ASDLFHKA.R010.STEP002.AUDIT               VB
P.ASDLFZZZ.R010.STEP003.AUDIT               VB
AUTO.SAMPLE.JCL                             VB
P.DRMSTOR.R100.STEP65.CONFRM                FB


FILE2 is of length of 80, first field start at position 1 and of length 44, second field starts at 45 and length of 3


FILE2
Code:
P.DRM                                       ESS
TSO                                         XJA
AUTO.T                                      AUT
P.ASDLFHKA                                  ASD



The output should be

OUTPUT:

Code:
P.DRMAPP.TEST.JCL                           FB   ESS
TSOTJOM.TEST.JCL                            FB   XJA
AUTO.TEST                                   VB   AUT
P.ASDLFHKA.R010.STEP001.AUDIT               VB   ASD
P.ASDLFHKA.R010.STEP002.AUDIT               VB   ASD
P.DRMSTOR.R100.STEP65.CONFRM                FB   ESS


Code'd (again) and demangled
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Mar 06, 2013 6:14 pm
Reply with quote

OK, once the sample data was shown, it was clear that earlier thoughts were a waste of time.

There is a JOINKEYS solution, but it would not that easily keep the order of the output file without another Sort.

So, here's a step to generate Sort Control cards for the following step. Since any blank lines in the input would produce invalid control cards, I treat them as "comments" (blank in column one) and so decided to support "comments" as well, "*" in column one. Examples of what are treated as comments are included in the data.

SORTOUT will be fed into the next step.

It might seem long, due to the use of symbols/SYMNAMES and attempting to further make the code easier to follow, so that it can be adapted for other situations without requiring decryption skills. The Control Cards after symbol-substitution are included, it can be seen to be not really all that long.

It works by counting the length of the field starting in position one on the "data to be updated from" file, using a version of HUMPHREY for fixed-length records. If there were variable-length records, base the counting on the original HUMPHREY.

Code:
//HUMPHREY EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTOUT DD DSN=&&CODE,DISP=(,PASS),SPACE=(TRK,1),UNIT=SYSDA
//SYMNAMES DD *
INPUT-FIRST-BYTE,1,1,CH
INPUT-DATA,1,17,CH
INPUT-CODE,45,3,CH
INPUT-RECORD,1,80,CH
  SAVE-CODE,*,3,CH
  DATA-COPY,*,17,CH
  RECORD-EXTENSION,*,=,CH
  RECORD-EXTENSION-1ST-BYTE,=,1,ZD
SKIP,6
  RECORD-EXTENSION-7TH-BYTE,*,=,CH
* FOR OUTPUT
POSITION,INPUT-RECORD
  OUTPUT-FIRST-RECORD,1,80,CH
POSITION,DATA-COPY
  OUTPUT-SECOND-RECORD,*,80,CH
POSITION,OUTPUT-SECOND-RECORD
SKIP,79
  LAST-BYTE-OF-RECORDS,*,1,CH
  OUTPUT-HLQ-PARTS,30,20,CH
  OUTPUT-COUNT,50,2,ZD
* CONSTANTS
SEARCH-CHAR-OR-STRING,C' '
ARBITRARY-STRING,C''
DIGITS-17,'01234567890123456'
MAX-COUNT,+17
ONE-SPACE,C' '
COMMENT-MARKER,C'*'
//SYMNOUT DD SYSOUT=*
//SYSIN DD *
                                                                   
 OMIT COND=(INPUT-FIRST-BYTE,EQ,ONE-SPACE,
          OR,INPUT-FIRST-BYTE,EQ,COMMENT-MARKER)
                                                                   
 OPTION COPY
                                                                   
 INREC IFOUTLEN=80,
       IFTHEN=(WHEN=INIT,
                OVERLAY=(DATA-COPY:INPUT-DATA,
                         RECORD-EXTENSION:DIGITS-17)),
                                                                   
       IFTHEN=(WHEN=INIT,
                FINDREP=(IN=SEARCH-CHAR-OR-STRING,
                         OUT=ARBITRARY-STRING,
                         STARTPOS=81,
                         ENDPOS=97)),
                                                                   
       IFTHEN=(WHEN=INIT,
                 OVERLAY=(OUTPUT-COUNT:
                           RECORD-EXTENSION-1ST-BYTE,
                           TO=ZD,LENGTH=2)),
                                                                   
       IFTHEN=(WHEN=(RECORD-EXTENSION-7TH-BYTE,EQ,ONE-SPACE),
                 OVERLAY=(OUTPUT-COUNT:OUTPUT-COUNT,
                           ADD,+10,
                           TO=ZD,LENGTH=2))
                                                                   
  OUTREC IFTHEN=(WHEN=INIT,
            BUILD=(C'  IFTHEN=(WHEN=(1,',MAX-COUNT,SUB,OUTPUT-COUNT,
                                           TO=ZD,LENGTH=2,
                                     C',CH,EQ,C''',INPUT-DATA,
                                     C'''),',SAVE-CODE:INPUT-CODE)),

         IFTHEN=(WHEN=INIT,
            OVERLAY=(OUTPUT-HLQ-PARTS:OUTPUT-HLQ-PARTS,
                       SQZ=(SHIFT=LEFT),
                     DATA-COPY:
                       C'        OVERLAY=(50:C''',SAVE-CODE,C''')),',
                     LAST-BYTE-OF-RECORDS:X))
                                                                     
  OUTFIL    REMOVECC,
            HEADER1=(C'  OPTION COPY',/,/,
                     C'  INREC IFOUTLEN=80,'),
            TRAILER1=(C'  IFTHEN=(WHEN=NONE,OVERLAY=(1:1,1))',/,/,
                      C'  OUTFIL OMIT=(50,3,CH,EQ,C''   '')'),
            BUILD=(OUTPUT-FIRST-RECORD,/,
                   OUTPUT-SECOND-RECORD)
//SORTIN  DD *
P.DRM                                       ESS
                                                                     
TSO                                         XJA
 A COMMENT
* ANOTHER COMMENT
AUTO.T                                      AUT
* YET ANOTHER COMMENT
P.ASDLFHKA                                  ASD


OMIT COND=(1,1,CH,EQ,C' ',OR,1,1,CH,EQ,C'*')
OPTION COPY
Code:
INREC IFOUTLEN=80,
      IFTHEN=(WHEN=INIT,OVERLAY=(84:1,17,101:C'01234567890123456')),
      IFTHEN=(WHEN=INIT,FINDREP=(IN=C' ',OUT=C'',STARTPOS=81,ENDPOS=97)),
      IFTHEN=(WHEN=INIT,OVERLAY=(50:101,1,ZD,TO=ZD,LENGTH=2)),
      IFTHEN=(WHEN=(101,17,CH,EQ,C' '),OVERLAY=(50:+17,TO=ZD,LENGTH=2)),
      IFTHEN=(WHEN=(108,1,CH,EQ,C' '),OVERLAY=(50:50,2,ZD,ADD,+10,TO=ZD,LENGTH=2))     
OUTREC IFTHEN=(WHEN=INIT,BUILD=(C'  IFTHEN=(WHEN=(1,',+17,SUB,50,2,ZD,TO=ZD,
               LENGTH=2,C',CH,EQ,C''',1,17,C'''),',81:45,3)),
       IFTHEN=(WHEN=INIT,OVERLAY=(30:30,20,SQZ=(SHIFT=LEFT),84:C' 
                    OVERLAY=(50:C''',81,3,C''')),',163:X))           
OUTFIL REMOVECC,HEADER1=(C'  OPTION COPY',/,/,C'  INREC IFOUTLEN=80,'),
       TRAILER1=(C'  IFTHEN=(WHEN=NONE,OVERLAY=(1:1,1))'),BUILD=(1,80,/,84,80)                                       


Here are the generated Control Cards:

Code:
  OPTION COPY                             
                                         
  INREC IFOUTLEN=80,                     
  IFTHEN=(WHEN=(1,05,CH,EQ,C'P.DRM'),     
        OVERLAY=(50:C'ESS')),             
  IFTHEN=(WHEN=(1,03,CH,EQ,C'TSO'),       
        OVERLAY=(50:C'XJA')),             
  IFTHEN=(WHEN=(1,06,CH,EQ,C'AUTO.T'),   
        OVERLAY=(50:C'AUT')),             
  IFTHEN=(WHEN=(1,10,CH,EQ,C'P.ASDLFHKA'),
        OVERLAY=(50:C'ASD')),             
  IFTHEN=(WHEN=NONE,OVERLAY=(1:1,1))


Here is the input for the generated control cards:
Code:
P.DRMAPP.TEST.JCL                           FB
TSOTJOM.TEST.JCL                            FB
AUTO.TEST                                   VB
P.ASDLFHKA.R010.STEP001.AUDIT               VB
P.ASDLFHKA.R010.STEP002.AUDIT               VB
P.ASDLFZZZ.R010.STEP003.AUDIT               VB
AUTO.SAMPLE.JCL                             VB
P.DRMSTOR.R100.STEP65.CONFRM                FB

And the output:

Code:
P.DRMAPP.TEST.JCL                           FB   ESS
TSOTJOM.TEST.JCL                            FB   XJA
AUTO.TEST                                   VB   AUT
P.ASDLFHKA.R010.STEP001.AUDIT               VB   ASD
P.ASDLFHKA.R010.STEP002.AUDIT               VB   ASD
P.DRMSTOR.R100.STEP65.CONFRM                FB   ESS



EDIT: Noticed whilst realigning the data in the sample, that non-matching records are excluded, so the OUTFIL for the first step has been updated and new output shown.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> SYNCSORT

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts JCL sort card - get first day and las... JCL & VSAM 9
No new posts Sort First/last record of a subset th... DFSORT/ICETOOL 7
Search our Forums:

Back to Top