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

Unmatched records needed
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Tue Nov 20, 2012 8:55 pm
 Reply with quote
File-1:Recordlength-5,Recordformat-FB
Code:
1234Y
2345N
5674Y
2378Y


File-2:Recordlength-5,Recordformat-FB
Code:
1234Y
1345Y
5074Y
2308Y


Output:Recordlength-5,Recordformat-FB
Code:
1345Y
5074Y
2308Y
5674N
2378N


Here all unmatched records from file2 and unmatched records from file1 but 'Y' of file1 records is to be replaced with 'N'.Also here all the matched records is to be eliminated and records with 'N' value from file1 is to be also eliminated. for e.g:2345N

Join Key is the complete record length :5.


Could you please suggest the sort card for this?
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: Tue Nov 20, 2012 9:37 pm
 Reply with quote
Hello,

Why is the sequence of the input and the output not consistent?
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Tue Nov 20, 2012 9:47 pm
 Reply with quote
Code:
File1:
1234Y
2345N
2378Y
5674Y


Code:
File:2
1234Y
1345Y
2308Y
5074Y


Code:
Output:
1345Y
2308Y
2378N
5074Y
5674N


Dick: Thanks for the correction.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
PostPosted: Tue Nov 20, 2012 9:49 pm
 Reply with quote
Rohit,

Your explanation is pretty bad

I am pretty confused with what you want

WHY NOT REUSE YOUR OLD POST AND EXPERIENCE icon_evil.gif
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Tue Nov 20, 2012 9:59 pm
 Reply with quote
1)Match F1 and F2 based on 1-5 position
2)Get all unmatched records from F1 into output file
3)Get all unmatched records from F2 but replace 'Y' by 'N'
4)Eliminate blindly if any records from F1 is suffixed with 'N'.

@Pandora: Is this make sense now?
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
PostPosted: Tue Nov 20, 2012 10:09 pm
 Reply with quote
ok As said above what did you try ?
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 Nov 20, 2012 10:38 pm
 Reply with quote
Rohit Umarjikar wrote:
1)Match F1 and F2 based on 1-5 position
2)Get all unmatched records from F1 into output file
3)Get all unmatched records from F2 but replace 'Y' by 'N'
4)Eliminate blindly if any records from F1 is suffixed with 'N'.


So, you already know you want JOINKEYS.
Code:
  JOIN UNPAIRED,F1,F2,ONLY


Will get you all the unmatched records.

In the JNFN1CNTL you can INCLUDE/OMIT the 'N's blindly.

From the REFORMAT record, replace any 'Y' on F2 by 'N'.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Tue Nov 20, 2012 10:45 pm
 Reply with quote
Code:
//S1     EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTJFN1 DD DSN=Input.File1
//SORTJNF2 DD DSN=Input.File2
//SORTOUT DD DSN=Unmatched.Records from F2
//SYSIN    DD *
  JOINKEYS FILE=F1,FIELDS=(1,5,A)
  JOINKEYS FILE=F2,FIELDS=(1,5,A)
  JOIN UNPAIRED,F2                     
  REFORMAT FIELDS=(F2:1,5)             
  SORT FIELDS=COPY


This will be for file 2 and same I have to do it for File1 as well and then concatenate both the files. But I was looking somitng to do it in one step.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Tue Nov 20, 2012 10:46 pm
 Reply with quote
Thanks Bill for the directions, I will try to write a card.
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 Nov 20, 2012 11:36 pm
 Reply with quote
OK, let us know if you get problems.

Did you run the step you showed? What did it do for you with the matched records?
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Wed Nov 21, 2012 12:22 am
 Reply with quote
Bill: I should have written ,

Code:
JOINKEYS FILE=F1,FIELDS=(1,5,A)
  JOINKEYS FILE=F2,FIELDS=(1,5,A)
  JOIN UNPAIRED,F2,Only                     
  REFORMAT FIELDS=(F2:1,5)             
  SORT FIELDS=COPY


If I do not write 'ONLY' then it gives matched and unmatched records from file2
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Wed Nov 21, 2012 12:39 am
 Reply with quote
Code:
//PS010    EXEC PGM=SORT         
//SORTJNF1 DD *                   
1114Y                             
1117Y                             
2346N                             
//SORTJNF2 DD *                   
1114Y                             
1119Y                             
2347Y                             
//SORTOUT  DD SYSOUT=*           
//SYSUDUMP DD SYSOUT=*           
//SYSPRINT DD SYSOUT=*           
//SYSOUT   DD SYSOUT=*           
//SYSIN    DD *                   
  JOINKEYS FILE=F1,FIELDS=(1,5,A)
  JOINKEYS FILE=F2,FIELDS=(1,5,A)
  JOIN UNPAIRED,F1,F2,ONLY       
  REFORMAT FIELDS=(F2:1,5)       
  SORT FIELDS=COPY               
           
//*                             
//JNF1CNTL DD *                 
  INCLUDE COND=(5,1,CH,NE,C'N') 
//*                             


Bill: Can you please suggest about
Quote:
From the REFORMAT record, replace any 'Y' on F2 by 'N'.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Wed Nov 21, 2012 12:42 am
 Reply with quote
Rohit Umarjikar wrote:
1)Match F1 and F2 based on 1-5 position
2)Get all unmatched records from F1 into output file
3)Get all unmatched records from F2 but replace 'Y' by 'N'
4)Eliminate blindly if any records from F1 is suffixed with 'N'.

To cover point #2 from your requirement, you would need JOIN UNPAIRED,F1,F2,ONLY as communicated earlier.

Thanks,
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Wed Nov 21, 2012 12:46 am
 Reply with quote
@sqlcode1
:That is what I included already, can you suggest on point#3?
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Wed Nov 21, 2012 12:53 am
 Reply with quote
See if below UNTESTED helps...
Code:
//PS010    EXEC PGM=SORT                                         
//SORTJNF1 DD *                                                   
1114Y                                                             
1117Y                                                             
2346N                                                             
//SORTJNF2 DD *                                                   
1114Y                                                             
1119Y                                                             
2347Y                                                             
//SORTOUT  DD SYSOUT=*                                           
//SYSOUT   DD SYSOUT=*                                           
//SYSIN    DD *                                                   
  JOINKEYS FILE=F1,FIELDS=(1,5,A)                                 
  JOINKEYS FILE=F2,FIELDS=(1,5,A)                                 
  JOIN UNPAIRED,F1,F2,ONLY                                       
  REFORMAT FIELDS=(F1:1,5,F2:1,5,?)                               
  INREC IFTHEN=(WHEN=(11,1,CH,EQ,C'2',AND,10,1,CH,EQ,C'Y'),       
        OVERLAY=(10:C'N'))                                       
  SORT FIELDS=COPY                                               
  OUTREC IFOUTLEN=5,IFTHEN(WHEN=(11,1,CH,EQ,C'2'),OVERLAY=(1:6,5))
                                                                 
//*                                                               
//JNF1CNTL DD *                                                   
  INCLUDE COND=(5,1,CH,NE,C'N')                                   
//*                                                               

Thanks,
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Wed Nov 21, 2012 1:02 am
 Reply with quote
output is :
Code:
1117Y
1119N
2347N


Can you please tell me what is

Code:
11,1,CH,EQ,C'2'
used for?

Thanks for the help
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Wed Nov 21, 2012 2:50 am
 Reply with quote
Rohit Umarjikar wrote:
output is :
Code:
1117Y
1119N
2347N


Isn't this what you expected?

11,1,CH,EQ,C'2' tells you that it is unpaired record from file2 defined by SORTJNF2. More information can be found here

Thanks,
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Wed Nov 21, 2012 2:56 am
 Reply with quote
Output should have been,

Code:
1117N
1119Y
2347Y


Thanks for the other information.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Wed Nov 21, 2012 3:09 am
 Reply with quote
Rohit Umarjikar wrote:
1)Match F1 and F2 based on 1-5 position
2)Get all unmatched records from F1 into output file
3)Get all unmatched records from F2 but replace 'Y' by 'N'
4)Eliminate blindly if any records from F1 is suffixed with 'N'.

Code:
//SORTJNF1 DD *                   
1114Y                             
1117Y                             
2346N                             
//SORTJNF2 DD *                   
1114Y                             
1119Y                             
2347Y

Rohit Umarjikar wrote:
Output should have been,

Code:
1117N
1119Y
2347Y


1117(Y/N) is an unpaired record from file1. Your requirement doesn't state anything about replacing unpaired records from file1 with Y to N. Rather it is to do the same for file2 records. Same with the other 2 records. 1119(Y/N) and 2347(Y/N) are unpaired records from File2 with Y and you wanted Y be replaced with N for unmatched FILE2 records.

Am I missing something here?

Thanks,
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Wed Nov 21, 2012 3:35 am
 Reply with quote
Quote:
1)Match F1 and F2 based on 1-5 position
2)Get all unmatched records from F2 into output file
3)Get all unmatched records from F1but replace 'Y' by 'N'
4)Eliminate blindly if any records from F1 is suffixed with 'N'.


Amended.

Sorry for all the confusion.Your understanding is perfectly right,
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Wed Nov 21, 2012 8:19 pm
 Reply with quote
Rohit Umarjikar,

Are you using DFSORT or Syncsort? Please post your complete sysout from your sort job which will help determine us the level of DFSORT you have.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Wed Nov 21, 2012 8:32 pm
 Reply with quote
Code:
ICE411I 0 THIS IS THE JOINKEYS MAIN TASK FOR JOINING F1 AND F2                 
ICE416I 0 JOINKEYS IS USING THE F1 SUBTASK FOR SORTJNF1 - SEE JNF1JMSG MESSAGES
ICE416I 1 JOINKEYS IS USING THE F2 SUBTASK FOR SORTJNF2 - SEE JNF2JMSG MESSAGES
ICE419I 0 JOINED RECORDS: TYPE=F, LENGTH=11                                     
ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1                         
ICE162I 0 ICEIEXIT CHANGED ONE OR MORE OPTIONS IN EFFECT                       
ICE751I 0 C5-K76982 C6-K90026 C7-K58148 C8-K67572 E9-K60824 C9-BASE   E5-K76585
ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED                                 
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AN
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R12 - 14:37 ON TUE NO
           JOINKEYS FILE=F1,FIELDS=(1,5,A)                                     
            JOINKEYS FILE=F2,FIELDS=(1,5,A)                                     
            JOIN UNPAIRED,F1,F2,ONLY                                           
            REFORMAT FIELDS=(F1:1,5,F2:1,5,?)                                   
            INREC IFTHEN=(WHEN=(11,1,CH,EQ,C'2',AND,10,1,CH,EQ,C'Y'),           
                  OVERLAY=(10:C'N'))                                           
            SORT FIELDS=COPY                                                   
            OUTREC IFOUTLEN=5,IFTHEN(WHEN=(11,1,CH,EQ,C'2'),OVERLAY=(1:6,5))   
ICE411I 0 THIS IS THE JOINKEYS MAIN TASK FOR JOINING F1 AND F2                 
ICE416I 0 JOINKEYS IS USING THE F1 SUBTASK FOR SORTJNF1 - SEE JNF1JMSG MESSAGES
ICE416I 1 JOINKEYS IS USING THE F2 SUBTASK FOR SORTJNF2 - SEE JNF2JMSG MESSAGES
ICE419I 0 JOINED RECORDS: TYPE=F, LENGTH=11                                     
ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1                         
ICE162I 0 ICEIEXIT CHANGED ONE OR MORE OPTIONS IN EFFECT                       
ICE751I 0 C5-K76982 C6-K90026 C7-K58148 C8-K67572 E9-K60824 C9-BASE   E5-K76585
ICE193I 0 ICEAM1 INVOCATION ENVIRONMENT IN EFFECT - ICEAM1 ENVIRONMENT SELECTED
ICE252I 1 PARMLIB OPTIONS WERE MERGED WITH INSTALLATION MODULE DEFAULTS         
ICE089I 0 RFYYY13A.PS010   .        , INPUT LRECL = 11, TYPE = F               
ICE093I 0 MAIN STORAGE = (MAX,6291456,6262784)                                 
ICE156I 0 MAIN STORAGE ABOVE 16MB = (6201133,6201133)                           
ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,
ICE128I 0 OPTIONS: SIZE=6291456,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ERE
ICE129I 0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=SHORT,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=
ICE130I 0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=N,WRKREL=Y,OUTREL=Y,CKPT=
ICE130I 0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=N,WRKREL=Y,OUTREL=Y,CKPT=
ICE131I 0 OPTIONS: TMAXLIM=6291456,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW=
ICE132I 0 OPTIONS: VLSHRT=N,ZDPRINT=N,IEXIT=Y,TEXIT=N,LISTX=N,EFS=NONE    ,EXITC
ICE133I 0 OPTIONS: HIPRMAX=0      ,DSPSIZE=0   ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAMI
ICE235I 0 OPTIONS: NULLOUT=RC0                                                 
ICE236I 0 OPTIONS: DYNAPCT=10 ,MOWRK=Y                                         
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTOUT                                   
ICE751I 1 EF-BASE   F0-K66717 E8-K70685                                         
ICE421I 0 JOINED RECORDS: COUNT=4                                               
ICE090I 0 OUTPUT LRECL = 5, BLKSIZE = 5, TYPE = FB                             
ICE055I 0 INSERT 4, DELETE 0                                                   
ICE054I 0 RECORDS - IN: 0, OUT: 4                                               
ICE052I 0 END OF DFSORT                                                         
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Wed Nov 21, 2012 9:15 pm
 Reply with quote
use the following DFSORT JCL which will give you the desired results
Code:

//STEP0100 EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//INA      DD *                                                       
1114Y                                                                 
1117Y                                                                 
2346N                                                                 
//INB      DD *                                                       
1114Y                                                                 
1119Y                                                                 
2347Y                                                                 
//SORTOUT  DD SYSOUT=*                                                 
//SYSIN    DD *                                                       
  OPTION COPY                                                         
  JOINKEYS F1=INA,FIELDS=(1,5,A)                                       
  JOINKEYS F2=INB,FIELDS=(1,5,A)                                       
  JOIN UNPAIRED,F1,F2,ONLY                                             
  REFORMAT FIELDS=(F1:1,5,F2:1,5,?)                                   
  INREC IFOUTLEN=5,                                                   
  IFTHEN=(WHEN=(11,1,CH,EQ,C'1',AND,5,1,CH,EQ,C'Y'),OVERLAY=(5:C'N')),
  IFTHEN=(WHEN=(11,1,CH,EQ,C'2'),BUILD=(6,5))                         
//*                                                                   
//JNF1CNTL DD *                                                       
  OMIT COND=(5,1,CH,EQ,C'N')                                           
//*                                                                   
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Wed Nov 21, 2012 9:40 pm
 Reply with quote
Thanks a ton, Skolusu and sqlcode1.
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 -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Compare 2 files(F1 & F2) and writ... JCL & VSAM 8
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts EZT program to build a flat file with... All Other Mainframe Topics 9
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us