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
 

 

Sort to fetch unmatched records from file f2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
d_sarlie

New User


Joined: 22 Apr 2008
Posts: 32
Location: coimbatore

PostPosted: Wed Mar 07, 2012 2:37 pm    Post subject: Sort to fetch unmatched records from file f2
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Wed Mar 07, 2012 2:45 pm    Post subject: Reply to: Sort
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    Post subject:
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: 32
Location: coimbatore

PostPosted: Wed Mar 07, 2012 3:55 pm    Post subject: Using more than 2 file in joins
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: 32
Location: coimbatore

PostPosted: Wed Mar 07, 2012 6:34 pm    Post subject:
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    Post subject:
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: 32
Location: coimbatore

PostPosted: Wed Mar 07, 2012 7:57 pm    Post subject:
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: 32
Location: coimbatore

PostPosted: Wed Mar 07, 2012 8:15 pm    Post subject:
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: 578
Location: USA

PostPosted: Wed Mar 07, 2012 8:33 pm    Post subject:
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: 32
Location: coimbatore

PostPosted: Wed Mar 07, 2012 9:20 pm    Post subject: fetching unmatched records from f2 and include date from f3
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    Post subject:
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: 32
Location: coimbatore

PostPosted: Wed Mar 07, 2012 9:35 pm    Post subject:
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    Post subject:
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: 578
Location: USA

PostPosted: Wed Mar 07, 2012 10:52 pm    Post subject:
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: 32
Location: coimbatore

PostPosted: Thu Mar 08, 2012 8:45 am    Post subject: Sort to fetch unmatched records from file f2
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: 32
Location: coimbatore

PostPosted: Thu Mar 08, 2012 9:43 am    Post subject: Re: Sort to fetch unmatched records from file f2
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: 1703
Location: Australia

PostPosted: Thu Mar 08, 2012 10:27 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm


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