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

Sort to fetch unmatched records from file f2


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
d_sarlie

New User


Joined: 22 Apr 2008
Posts: 42
Location: coimbatore

PostPosted: Wed Mar 07, 2012 2:37 pm
Reply with quote

I have 2 files f1,f2.I have to fetch unmatched records from file f2.
to the file f2 data I have to append date from file f3 and party key from other file f4.
is that possible for me to do all the things in a single sort step.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Mar 07, 2012 2:45 pm
Reply with quote

wouldn' t it be smarter to use a more descriptive title ?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Mar 07, 2012 2:49 pm
Reply with quote

Quote:
is that possible for me to do all the things in a single sort step.
yes
Back to top
View user's profile Send private message
d_sarlie

New User


Joined: 22 Apr 2008
Posts: 42
Location: coimbatore

PostPosted: Wed Mar 07, 2012 3:55 pm
Reply with quote

I have 2 files f1,f2.I have to fetch unmatched records whose status code is 5 from file f2 .To the file f2 data I have to append date from file f3 for this a new join step I have to write and for party key from other file f4 i ve to write an other join.is that possible for me to do all the things in a single sort step.

f1

jeba 5
kiruba 5

f2

jeba 5
kiruba 5
praba 3
selva 5
leroy 5

f3

20121212

f4

Selva 90909090
leroy 80808080

Desired output

Selva 20121212 90909090
leroy 20121212 80808080
Back to top
View user's profile Send private message
d_sarlie

New User


Joined: 22 Apr 2008
Posts: 42
Location: coimbatore

PostPosted: Wed Mar 07, 2012 6:34 pm
Reply with quote

The data in f1 will be always in F2 .. But F2 may or may not ve excess record
Back to top
View user's profile Send private message
elango_K

New User


Joined: 18 Aug 2011
Posts: 44
Location: India

PostPosted: Wed Mar 07, 2012 7:47 pm
Reply with quote

You did not get my third question .

My question was whether there are duplicates in file 1 and duplicates in File2.
Like

F1:
Code:

jeba 5
jeba 5
kiruba 5
kiruba 5



F2:

Code:

jeba 5
kiruba 5
praba 3
selva 5
leroy 5
praba 3
selva 5
leroy 5
Back to top
View user's profile Send private message
d_sarlie

New User


Joined: 22 Apr 2008
Posts: 42
Location: coimbatore

PostPosted: Wed Mar 07, 2012 7:57 pm
Reply with quote

No duplicates as u mentioned .
Back to top
View user's profile Send private message
d_sarlie

New User


Joined: 22 Apr 2008
Posts: 42
Location: coimbatore

PostPosted: Wed Mar 07, 2012 8:15 pm
Reply with quote

There will not be any duplicates within the files .
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Wed Mar 07, 2012 8:33 pm
Reply with quote

d_sarlie,

1) What is the LRECL and RECFM for each file?

2) Does FILE1 only have records with record type 5? Is it possible to have any other record type in FILE1?

3) What are the field positions for each fields in each file. Also provide field definition for each field as well.

4) I am assuming there can be only 1 record in the FILE3. Let me know if that's not correct.

5) Do you care about upper case or lower case, when matching unmatched records from FILE2 with FILE4 on name for the population of party key? For example, Selva vs. selva


Last but not the least, my 2 cents....

You should be concentrating on the most efficient solution which is easy to maintain as well,and not something written in a single sort step.

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

New User


Joined: 22 Apr 2008
Posts: 42
Location: coimbatore

PostPosted: Wed Mar 07, 2012 9:20 pm
Reply with quote

Now I have got unmatched record from f1 and f2 my resultant output is

selva 5
leroy 5


My File 3 has date. I want to include the date in both the records

f3

20121212

selva 5 20121212
leroy 5 20121212
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Mar 07, 2012 9:23 pm
Reply with quote

Elango and sqlcode1

jeba thinks that you are dentists
Back to top
View user's profile Send private message
d_sarlie

New User


Joined: 22 Apr 2008
Posts: 42
Location: coimbatore

PostPosted: Wed Mar 07, 2012 9:35 pm
Reply with quote

icon_smile.gif
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Mar 07, 2012 10:49 pm
Reply with quote

d_sarlie wrote:
dbzTHEdinosauer wrote:

jeba thinks that you are dentists


icon_smile.gif


Glad you are laughing when they are trying to pull the teeth. However you need to open your mouth completely, so that the dentist can finish his work.

Go back and answer EVERY question Sqlcode1 has asked.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Wed Mar 07, 2012 10:52 pm
Reply with quote

d_sarlie,
Personally, I will never turn this to production but see if below works... Specially beware of writting large number of records to &&TMP file. Field lengths and positions assumed.

EDITED :- Kolusu, sorry our posts crossed path. My solution assumes so many things which OP didn't answer originally. Feel free to delete the post if needed.


Code:

//STEP0001 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTIN   DD *                                                 
20121212                                                       
/*                                                             
//SORTOUT  DD DSN=&&DT,UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(,PASS)
//SYSIN    DD *                                                 
  OPTION COPY                                                   
  OUTFIL REMOVECC,NODETAIL,BUILD=(80X),                         
    TRAILER1=('DT,''',1,8,'''')                                 
/*                                                             
//*                                                             
//STEP0002 EXEC PGM=ICETOOL                                     
//TOOLMSG  DD SYSOUT=*                                         
//DFSMSG   DD SYSOUT=*                                         
//SYMNAMES DD DSN=&&DT,DISP=(OLD,PASS)                         
//IN1      DD  *                                               
JEBA            5                                               
KIRUBA          5                                               
/*                                                             
//IN2      DD  *                                               
JEBA            5                                               
KIRUBA          5                                               
PRABA           3                                               
SELVA           5                                               
LEROY           5                                               
/*                                                             
//IN3      DD  *                                               
SELVA           90909090                                       
LEROY           80808080                                       
/*                                                             
//TMP      DD DSN=&&TMP,DISP=(MOD,PASS),SPACE=(CYL,(10,5),RLSE)   
//OUT      DD SYSOUT=*                                         
//TOOLIN   DD *                                                 
 COPY JKFROM TO(TMP) USING(CTL1)                               
 COPY JKFROM USING(CTL2)                                       
/*                                                             
//CTL1CNTL DD *                                                 
 JOINKEYS F1=IN1,FIELDS=(01,16,A),TASKID=T1       
 JOINKEYS F2=IN2,FIELDS=(01,16,A),TASKID=T1       
 JOIN UNPAIRED,F2,ONLY                             
 REFORMAT FIELDS=(F2:1,16)                         
 OUTFIL FNAMES=TMP,BUILD=(1,16,DT,X)               
/*                                                 
//CTL2CNTL DD *                                   
 JOINKEYS F1=TMP,FIELDS=(01,16,A),SORTED           
 JOINKEYS F2=IN3,FIELDS=(01,16,A)                 
 REFORMAT FIELDS=(F1:01,25,F2:17,08)               
 OUTFIL FNAMES=OUT                                 
/*                                                 
//T1F1CNTL DD *                                   
 INCLUDE COND=(17,1,CH,EQ,C'5')                   
/*                                                 

OUTPUT
Code:

LEROY           20121212 80808080
SELVA           20121212 90909090

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

New User


Joined: 22 Apr 2008
Posts: 42
Location: coimbatore

PostPosted: Thu Mar 08, 2012 8:45 am
Reply with quote

I am getting this error.

********************************* TOP OF DATA *******************
SYT000I SYNCTOOL RELEASE 1.6.2 - COPYRIGHT 2007 SYNCSORT INC.
SYT001I INITIAL PROCESSING MODE IS "STOP"

SYT302I SYMNAMES STATEMENTS IN USE
DT,'20121212'
*
*** SYMBOL IS A RESERVED WORD ***

SYT303E SYMNAMES STATEMENT ERROR
SYT304E SYMNAMES ERRORS FOUND

SYT004I SYNCTOOL PROCESSING COMPLETED WITH RETURN CODE 12
******************************** BOTTOM OF DATA *****************
Back to top
View user's profile Send private message
d_sarlie

New User


Joined: 22 Apr 2008
Posts: 42
Location: coimbatore

PostPosted: Thu Mar 08, 2012 9:43 am
Reply with quote

d_sarlie wrote:
I am getting this error.

********************************* TOP OF DATA *******************
SYT000I SYNCTOOL RELEASE 1.6.2 - COPYRIGHT 2007 SYNCSORT INC.
SYT001I INITIAL PROCESSING MODE IS "STOP"

SYT302I SYMNAMES STATEMENTS IN USE
DT,'20121212'
*
*** SYMBOL IS A RESERVED WORD ***

SYT303E SYMNAMES STATEMENT ERROR
SYT304E SYMNAMES ERRORS FOUND

SYT004I SYNCTOOL PROCESSING COMPLETED WITH RETURN CODE 12
******************************** BOTTOM OF DATA *****************


This error I edited with DAT instead DA

Now getting an other error

SYT000I SYNCTOOL RELEASE 1.6.2 - COPYRIGHT 2007 SYNCSORT INC.
SYT001I INITIAL PROCESSING MODE IS "STOP"

SYT302I SYMNAMES STATEMENTS IN USE
SYT002I "TOOLIN" INTERFACE BEING USED

COPY JKFROM TO(OUT1) USING(CTL1)

SYT005I PERFORM NECESSARY SYMBOL SUBSTITUTION

COPY JKFROM TO(OUT1) USING(CTL1)

SYT050E INVALID OPERAND ON "COPY" STATEMENT
SYT030I OPERATION COMPLETED WITH RETURN CODE 12

SYT015I PROCESSING MODE CHANGED FROM "STOP" TO "SCAN" DUE TO OPERATION FAILURE

COPY JKFROM USING(CTL2)

SYT005I PERFORM NECESSARY SYMBOL SUBSTITUTION

COPY JKFROM USING(CTL2)

SYT050E INVALID OPERAND ON "COPY" STATEMENT

SYT004I SYNCTOOL PROCESSING COMPLETED WITH RETURN CODE 12
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia

PostPosted: Thu Mar 08, 2012 10:27 am
Reply with quote

Hi,

this is an old quote from the master himself, Frank Yaeger
Quote:
The job I showed works fine with DFSORT which supports JKFROM.

However, the SYT messages indicate you're using Syncsort, not DFSORT.
Syncsort does not support JKFROM.



Gerry
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 -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Access to non cataloged VSAM file JCL & VSAM 18
No new posts Compare only first records of the fil... SYNCSORT 7
Search our Forums:

Back to Top