Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Comparing Two Files using DFSORT

 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
karthick anandaraman

New User


Joined: 05 Jan 2010
Posts: 6
Location: chennai

PostPosted: Wed Aug 17, 2011 11:22 pm    Post subject: Comparing Two Files using DFSORT
Reply with quote

Hi All, Please help me to solve the below Issue. I have one Key File (LRECL 80) and One Master file (LRECL) 280. MY O/P file is LRECL is also 280

I need to compare the Key file with Master file based on the position (1,17,CH) and write the O/P file with all the Matching records present in MASTER file.

For Example If there are 5000 records present in Key file , I need to retreive these 5000 records from MASTER file and write in to the O/P file.

Both the files have the matching records present in the position 1,17 of 17 CHAR length.

Please help me to acheive this using DFSORT.

Let me know if im not clear with any details. Thank you all in advance.
Back to top
View user's profile Send private message

Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Aug 17, 2011 11:37 pm    Post subject:
Reply with quote

Assuming there are no duplicate keys within the Master file, and no duplicate keys within the Key file, you can use a DFSORT/ICETOOL job like the following:

Code:

//S1    EXEC  PGM=ICETOOL                                     
//TOOLMSG DD SYSOUT=*                                         
//DFSMSG  DD SYSOUT=*                                         
//CON DD DSN=...  Master input file (FB/280)
//    DD DSN=...  Key input file (FB/280)                         
//OUT DD DSN=...  output file (FB/280)                 
//TOOLIN DD *                                                 
SELECT FROM(CON) TO(OUT) ON(1,17,CH) FIRSTDUP     
/*           


(If either file can have duplicates within it, show that with an example of the input records and expected output records.)
Back to top
View user's profile Send private message
karthick anandaraman

New User


Joined: 05 Jan 2010
Posts: 6
Location: chennai

PostPosted: Wed Aug 17, 2011 11:50 pm    Post subject:
Reply with quote

Hi Frank,
Both of The files will not have duplicates. I tried teh above jcl and im getting But im gettin INVALID DATSET ATTRIBUTES error message.

The two i/p files are of different LRECL

1ICE200I 0 IDENTIFIER FROM CALLING PROGRAM IS 0001
ICE143I 0 BLOCKSET SORT TECHNIQUE SELECTED
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES A
ICE000I 0 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R10 - 15:13 ON WED A
0 DEBUG NOABEND,ESTAE
OPTION MSGDDN=DFSMSG,LIST,MSGPRT=ALL,RESINV=0,SORTIN=CON,SORTOUT=OUT,
NALLOC,SZERO,EQUALS,NOVLSHRT,LOCALE=NONE,NOCHECK
SORT FIELDS=(1,17,CH,A)
MODS E35=(ICE35DU,12288)
ICE043A 3 INVALID DATA SET ATTRIBUTES: CON LRECL - REASON CODE IS 05
ICE751I 0 C5-K48846 C6-K90014 C7-K45047 C8-K46331 E7-K48846
ICE052I 3 END OF DFSORT [/code]
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Aug 18, 2011 12:04 am    Post subject:
Reply with quote

Sorry, I misread your original post. I thought both of your input files had LRECL=280, but rereading it, I see that one has 80 and the other has 280.
I'll post a solution for that after my lunch.
Back to top
View user's profile Send private message
karthick anandaraman

New User


Joined: 05 Jan 2010
Posts: 6
Location: chennai

PostPosted: Thu Aug 18, 2011 12:20 am    Post subject:
Reply with quote

Take Your time sir !! . As a Temporary workaround I made my Key file also as LRECL 280 and Your JCL is Pefectly working for that..

Thanks a lot !!
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Aug 18, 2011 1:09 am    Post subject:
Reply with quote

Here's a DFSORT JOINKEYS job that will do what you asked for:

Code:

//S1    EXEC  PGM=SORT
//SYSOUT DD SYSOUT=*
//MASTER DD DSN=...  Master input file (FB/280)
//KEY DD DSN=...     Key input file (FB/80)
//SORTOUT DD DSN=...  output file (FB/280)
//SYSIN DD *
  JOINKEYS F1=MASTER,FIELDS=(1,17,A)
  JOINKEYS F2=KEY,FIELDS=(1,17,A)
  REFORMAT FIELDS=(F1:1,280)
  OPTION COPY
/*
Back to top
View user's profile Send private message
jaganmoni1

New User


Joined: 31 Oct 2008
Posts: 33
Location: CANADA

PostPosted: Mon Nov 28, 2011 10:31 pm    Post subject: Reply to: Comparing Two Files using DFSORT
Reply with quote

To continue to above query, apart from the matching key output; is there any possibility to write key file record which doesn’t have matching record in master file?
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Mon Nov 28, 2011 11:26 pm    Post subject: Reply to: Comparing Two Files using DFSORT
Reply with quote

yes.. I is possible..
read through paper by Frank.

ftp://ftp.software.ibm.com/storage/dfsort/mvs/sortugpg.pdf
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Mon Nov 28, 2011 11:47 pm    Post subject:
Reply with quote

Quote:
To continue to above query, apart from the matching key output; is there any possibility to write key file record which doesn’t have matching record in master file?


It's not clear to me what you want to do.

Please show an example of the records in each input file (relevant fields only) and what you expect for output. Explain the "rules" for getting from input to output. Give the starting position, length and format of each relevant field. Give the RECFM and LRECL of the input files. If file1 can have duplicates within it, show that in your example. If file2 can have duplicates within it, show that in your example.
Back to top
View user's profile Send private message
jaganmoni1

New User


Joined: 31 Oct 2008
Posts: 33
Location: CANADA

PostPosted: Mon Nov 28, 2011 11:55 pm    Post subject: Reply to: Comparing Two Files using DFSORT
Reply with quote

Hi Frank,

File1 (Key file) and File2 (Master file) has unique records and in sorted order.
File1 attributes:
LRECL – 12
RECFM – FB
Key Position – column 1 to column 12

File2 attributes:
LRECL – 2200
RECFM – VB
Key Position – column 1 to column 12


File 1 sample date:
----+----1--
************
000260006106
000260006188
000260006256
001410009941
001410009942
001410009943
001410009944
001410009945
001410009946
001410009947
001410009948
001410009949

If file1 key (column: 1 to 12) has match in file2 (column: 1 to 12), then I need file2 record in output1 file.
If file1 key has no match in file2, then I need file1 record in output2 file.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Nov 29, 2011 12:40 am    Post subject:
Reply with quote

If I understand correctly what you're trying to do, then you can use a DFSORT job like the following:

Code:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/12)
//IN2 DD DSN=...  input file2 (VB/2200)
//OUT1 DD DSN=...  output file1 (VB/2200)
//OUT2 DD DSN=...  output file2 (FB/12)
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(1,12,A)
  JOINKEYS F2=IN2,FIELDS=(5,12,A)
  REFORMAT FIELDS=(F2:1,4,?,F1:1,12,F2:5)
  JOIN UNPAIRED,F1
  OPTION COPY
  OUTFIL FNAMES=OUT1,INCLUDE=(5,1,CH,EQ,C'B'),
    BUILD=(1,4,18)
  OUTFIL FNAMES=OUT2,INCLUDE=(5,1,CH,EQ,C'1'),
    VTOF,BUILD=(6,12)
/*
Back to top
View user's profile Send private message
jaganmoni1

New User


Joined: 31 Oct 2008
Posts: 33
Location: CANADA

PostPosted: Tue Nov 29, 2011 1:40 am    Post subject: Reply to: Comparing Two Files using DFSORT
Reply with quote

Hi Frank,

In our organization we have both DFSORT and SYNCSORT. When I use EXEC PGM=SORT it is using syncsort and i am getting following syntax errors.
SYNCSORT LICENSED FOR CPU SERIAL NUMBER 37FA6, MODEL 2097 605
SYSIN :
JOINKEYS F1=IN1,FIELDS=(1,12,A)
*
JOINKEYS F2=IN2,FIELDS=(5,12,A)
*
REFORMAT FIELDS=(F2:1,4,?,F1:1,12,F2:5)
*
JOIN UNPAIRED,F1
OPTION COPY
OUTFIL FNAMES=OUT1,INCLUDE=(5,1,CH,EQ,C'B'),
BUILD=(1,4,18)
OUTFIL FNAMES=OUT2,INCLUDE=(5,1,CH,EQ,C'1'),
VTOF,BUILD=(6,12)
WER268A JOINKEYS STATEMENT: SYNTAX ERROR
WER268A JOINKEYS STATEMENT: SYNTAX ERROR
WER268A REFORMAT STATEMENT: SYNTAX ERROR
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000


Is there any option to specify in jcl to use DFSORT instead of SYNCSORT during execution?
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Nov 29, 2011 2:38 am    Post subject:
Reply with quote

Since you posted in the DFSORT Forum and had "DFSORT" in the Subject, I gave you a DFSORT solution. My job works fine with DFSORT.

I'm a DFSORT developer. DFSORT and Syncsort are competitive products. I'm happy to answer questions on DFSORT and DFSORT's ICETOOL, but I don't answer questions on Syncsort.

If you want a Syncsort solution, you should post in the correct Forum for that.

If you're asking how to invoke DFSORT instead of Syncsort with PGM=SORT, you need to ask your System Programmers which libraries you need to JOBLIB to for DFSORT.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies.    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 High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts How to get the remainder and quotient... vnktrrd DFSORT/ICETOOL 2 Mon Oct 31, 2016 10:59 am
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us