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

sort card to get all records from two files using syncsort?
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
kalyan.v

New User


Joined: 04 Feb 2008
Posts: 65
Location: Hyd
PostPosted: Tue Dec 23, 2008 11:44 am
 Reply with quote
Hi,

I have two inputs which are in below mentioned format.
I am trying to reformat them using join in sync sort. The output format shown below.



Code:


Input 1:
1    4          11(30)                     42(15)                 58(8)
BC 008034 aaaaaaaaaaaaaaaaaaaaaaaaaa       xxxxxxxxxxxxx          1
BC 012088 bbbbbbbbbbbbbbbbbbbbbbbbbb       yyyyyyyyyyyyy          1
AB 012564 ccccccccccccccccccccccccc        zzzzzzzzzzzzz          2


Input 2:
1(30)                           32   35        42(8)
aaaaaaaaaaaaaaaaaaaaaaaaaa      BC 008034        4
bbbbbbbbbbbbbbbbbbbbbbbbbb      BC 012088        7
dddddddddddddddddddddddddd      BC 005143        5


Output:
BC 008034 aaaaaaaaaaaaaaaaaaaaaaaaaa       xxxxxxxxxxxxx   1     4     
BC 012088 bbbbbbbbbbbbbbbbbbbbbbbbbb       yyyyyyyyyyyyy   1     7
AB 012564 ccccccccccccccccccccccccc        zzzzzzzzzzzzz   2
BC 005143 dddddddddddddddddddddddddd                             5




Inputs format is :: FB and LRECL=100

I am getting only matched fields using join.
Can i get it using join in syncsort?? If yes, let me know how to do.


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

Senior Member


Joined: 03 Jul 2007
Posts: 1287
Location: Chennai, India
PostPosted: Tue Dec 23, 2008 12:10 pm
 Reply with quote
Kalyan,

Quote:
I am getting only matched fields using join.
Can i get it using join in syncsort?? If yes, let me know how to do.


Using syncsort once can get the matched, unmatched records from two files. Your requirement is not clear. Do provide some more details on the output format.
Back to top
View user's profile Send private message
kalyan.v

New User


Joined: 04 Feb 2008
Posts: 65
Location: Hyd
PostPosted: Tue Dec 23, 2008 12:38 pm
 Reply with quote
Hi Aaru,

My requirement is to get all the records which r in both input files.
Here some records may present only in one file.
For these records the fields which are drawn from other file must be spaces.
( Ex : The record with key 'AB 012564' only present in input1.
and other record with key 'BC 005143' only present in input2)

Can i do this using Joins??


Thanks,
Kalyan
Back to top
View user's profile Send private message
kalyan.v

New User


Joined: 04 Feb 2008
Posts: 65
Location: Hyd
PostPosted: Tue Dec 23, 2008 6:09 pm
 Reply with quote
Hi All,

If there is any method to get the mentioned output format using syncsort
pls let me know.

Thanks,
Kranthi V
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Tue Dec 23, 2008 6:58 pm
 Reply with quote
kalyan.v,
Quote:
If there is any method to get the mentioned output format using syncsort
Yes, you can use SyncSort's JOIN feature to achieve this.
You have numeric fields at 58(8) - file1 and 42(8) - file2, What is the format for this, is it right justified witth leading spaces? Can you show an example which has 8 digit numbers also.
Back to top
View user's profile Send private message
ramsri

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India
PostPosted: Tue Dec 23, 2008 7:00 pm
 Reply with quote
The SPLICE operator should do the trick for you if you have latest TPF installed at your site icon_rolleyes.gif
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Tue Dec 23, 2008 7:04 pm
 Reply with quote
Ramsri,

SyncSort has JOIN feature from Release 1.2 onwards which makes file-matching easier than the SPLICE approach using SYNCTOOL.
Back to top
View user's profile Send private message
ramsri

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India
PostPosted: Tue Dec 23, 2008 10:23 pm
 Reply with quote
I tried SPLICE but unsuccessfully and got partial results ! icon_confused.gif icon_sad.gif

Code:

//SORT0001 EXEC PGM=ICETOOL                                       
//TOOLMSG  DD SYSOUT=*                                             
//SSMSG    DD SYSOUT=*                                             
//IN1      DD *                                                   
BC 008034 AAAAAAAAAAAAAAAAAAAAAAAAAA       XXXXXXXXXXXXX          1
BC 012088 BBBBBBBBBBBBBBBBBBBBBBBBBB       YYYYYYYYYYYYY          1
AB 012564 CCCCCCCCCCCCCCCCCCCCCCCCC        ZZZZZZZZZZZZZ          2
//IN2      DD *                                                   
AAAAAAAAAAAAAAAAAAAAAAAAAA      BC 008034        4                 
BBBBBBBBBBBBBBBBBBBBBBBBBB      BC 012088        7                 
DDDDDDDDDDDDDDDDDDDDDDDDDD      BC 005143        5                 
//IN3      DD DSN=&&T1,                                           
//            DISP=(MOD,PASS,DELETE),                             
//            SPACE=(TRK,(1,1),RLSE),                             
//            UNIT=SYSDA,                                         
//            DCB=(LRECL=80,RECFM=FB,BLKSIZE=0)                   
//OUT      DD SYSOUT=*                                             
//TOOLIN   DD *                                                   
 COPY FROM(IN1) TO(IN3)                                           
 COPY FROM(IN2) TO(IN3) USING(RAM1)           
 SPLICE FROM(IN3) TO(OUT) ON(4,6,CH) WITH(70,1)
/*                                             
//RAM1CNTL DD *                               
 OUTREC BUILD=(1:33,9,X,11:1,26,33X,70:50,1)   
/*


Am really not sure how to apply SPLICE on non-matching records to get OP's required output ..... sorry. icon_surprised.gif

Output:
Code:

BC 008034 AAAAAAAAAAAAAAAAAAAAAAAAAA       XXXXXXXXXXXXX          1  4
BC 012088 BBBBBBBBBBBBBBBBBBBBBBBBBB       YYYYYYYYYYYYY          1  7


It will be a great help if someone could help the SPLICE here.

Thanks.
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia
PostPosted: Wed Dec 24, 2008 3:24 am
 Reply with quote
Hi,

try changing
Code:
 SPLICE FROM(IN3) TO(OUT) ON(4,6,CH) WITH(70,1)


to
Code:
 SPLICE FROM(IN3) TO(OUT) ON(4,6,CH) WITH(70,1) KEEPNODUPS 



Gerry
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Wed Dec 24, 2008 8:54 am
 Reply with quote
kalyan.v,

If you post a few records from your "real" file, I can show you a much simpler and efficient way of doing this using SyncSort JOIN.


kalyan.v wrote:
I am getting only matched fields using join.
Can i get it using join in syncsort?? If yes, let me know how to do

ramsri,

The OP was asking for a SyncSort JOIN solution and not SPLICE. Since you already have SyncSort, I wonder why you went for a SPLICE.
Back to top
View user's profile Send private message
kalyan.v

New User


Joined: 04 Feb 2008
Posts: 65
Location: Hyd
PostPosted: Wed Dec 24, 2008 11:39 am
 Reply with quote
Hi Arun,

In the inputs, I gave counters starting positions.
In input1: The count starts from 58th position with suppressed leading zeros.

In input2: The count starts from 42nd position with suppressed leading zeros.




Code:

Input 1:
1    4          11(30)                     42(15)                 58(8)
BC 008034 aaaaaaaaaaaaaaaaaaaaaaaaaa       xxxxxxxxxxxxx          00000001
BC 012088 bbbbbbbbbbbbbbbbbbbbbbbbbb       yyyyyyyyyyyyy          00000001
AB 012564 ccccccccccccccccccccccccc        zzzzzzzzzzzzz          00000002


Input 2:
1(30)                           32 35            42(8)
aaaaaaaaaaaaaaaaaaaaaaaaaa      BC 008034        00000004
bbbbbbbbbbbbbbbbbbbbbbbbbb      BC 012088        00000007
dddddddddddddddddddddddddd      BC 005143        00000005




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

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India
PostPosted: Wed Dec 24, 2008 2:16 pm
 Reply with quote
Gerry, Thanks a lot for the help...... icon_biggrin.gif Yes. Now, the output is coming as expected by Kalyan.... icon_smile.gif

Arun, I was trying the JOIN but could not achieve it icon_sad.gif I tried it till 11:30 pm but was not able to reach the expected output. So, I thought will give a try with SPLICE icon_wink.gif

It will be a great help if you could post the Syncsort version (using JOIN) also.

Job:
Code:

//SORT0001 EXEC PGM=ICETOOL                                       
//TOOLMSG  DD SYSOUT=*                                             
//SSMSG    DD SYSOUT=*                                             
//IN1      DD *                                                   
BC 008034 AAAAAAAAAAAAAAAAAAAAAAAAAA       XXXXXXXXXXXXX          1
BC 012088 BBBBBBBBBBBBBBBBBBBBBBBBBB       YYYYYYYYYYYYY          1
AB 012564 CCCCCCCCCCCCCCCCCCCCCCCCC        ZZZZZZZZZZZZZ          2
//IN2      DD *                                                   
AAAAAAAAAAAAAAAAAAAAAAAAAA      BC 008034        4                 
BBBBBBBBBBBBBBBBBBBBBBBBBB      BC 012088        7                 
DDDDDDDDDDDDDDDDDDDDDDDDDD      BC 005143        5                 
//IN3      DD DSN=&&T1,                                           
//            DISP=(MOD,PASS,DELETE),                             
//            SPACE=(TRK,(1,1),RLSE),                             
//            UNIT=SYSDA,                                         
//            DCB=(LRECL=80,RECFM=FB,BLKSIZE=0)                   
//OUT1     DD DSN=&&T2,                                           
//            DISP=(MOD,PASS,DELETE),                             
//            SPACE=(TRK,(1,1),RLSE),                             
//            UNIT=SYSDA,                                 
//            DCB=(LRECL=80,RECFM=FB,BLKSIZE=0)           
//OUT2     DD SYSOUT=*                                     
//TOOLIN   DD *                                           
 COPY FROM(IN1) TO(IN3)                                   
 COPY FROM(IN2) TO(IN3) USING(RAM1)                       
 SPLICE FROM(IN3) TO(OUT1) ON(4,6,CH) WITH(70,1) KEEPNODUPS
 SORT FROM(OUT1) TO(OUT2) USING(RAM2)                     
/*                                                         
//RAM1CNTL DD *                                           
 OUTREC BUILD=(1:33,9,X,11:1,26,33X,70:50,1)               
/*                                                         
//RAM2CNTL DD *                                           
 SORT FIELDS=(11,5,CH,A)                                   
/*                                                         


Output:
Code:

BC 008034 AAAAAAAAAAAAAAAAAAAAAAAAAA       XXXXXXXXXXXXX          1  4
BC 012088 BBBBBBBBBBBBBBBBBBBBBBBBBB       YYYYYYYYYYYYY          1  7
AB 012564 CCCCCCCCCCCCCCCCCCCCCCCCC        ZZZZZZZZZZZZZ          2   
BC 005143 DDDDDDDDDDDDDDDDDDDDDDDDDD                                 5


Merry X'Mas to all.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Wed Dec 24, 2008 7:31 pm
 Reply with quote
Quote:
It will be a great help if you could post the SyncSort version (using JOIN) also.
The below SyncSort job does what you are looking for. Here you don't need the additional data passes involved in the SPLICE solution posted above.
Code:
//PS100    EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTJNF1 DD DISP=SHR,DSN=Input file1 -----> FB,LRECL=100
//SORTJNF2 DD DISP=SHR,DSN=Input file2 -----> FB,LRECL=100
//SORTOUT  DD SYSOUT=*                                                 
//SYSIN    DD *                                                       
  JOINKEYS FILE=F1,FIELDS=(01,9,A)                                     
  JOINKEYS FILE=F2,FIELDS=(32,9,A)                                     
  REFORMAT FIELDS=(F1:1,65,F2:1,30,32,18)                             
  JOIN UNPAIRED                                                       
  SORT FIELDS=COPY                                                     
  INREC IFTHEN=(WHEN=(66,1,CH,EQ,C' '),BUILD=(01,65,9X)),             
        IFTHEN=(WHEN=(01,1,CH,EQ,C' '),BUILD=(96,9,X,66,30,26X,106,8)),
        IFTHEN=(WHEN=NONE,BUILD=(1,65,X,106,8))                       
/*   
SORTOUT would have the below records for your latest input
Code:
AB 012564 ccccccccccccccccccccccccc      zzzzzzzzzzzzz   00000002       
BC 005143 dddddddddddddddddddddddddd                              0000005
BC 008034 aaaaaaaaaaaaaaaaaaaaaaaaaa     xxxxxxxxxxxxx   00000001 0000004
BC 012088 bbbbbbbbbbbbbbbbbbbbbbbbbb     yyyyyyyyyyyyy   00000001 0000007
Back to top
View user's profile Send private message
ramsri

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India
PostPosted: Wed Dec 24, 2008 7:58 pm
 Reply with quote
Arun, shouldn't the first record be third record as per the original requirement?
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Wed Dec 24, 2008 9:00 pm
 Reply with quote
Quote:
shouldn't the first record be third record

The OP has not mentioned that he needs the output records in some particular order. I am not trying to understand how he arrived at that order.

kalyan.v,

The output shown by my job will be sorted on the key field. If you want the output to be written in some specific order, post it here and I'd modify it for you.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Wed Dec 24, 2008 9:16 pm
 Reply with quote
Quote:
I am not trying to understand how he arrived at that order
Bad typing day icon_evil.gif, Actually I meant "I am".
Back to top
View user's profile Send private message
kalyan.v

New User


Joined: 04 Feb 2008
Posts: 65
Location: Hyd
PostPosted: Fri Dec 26, 2008 10:15 am
 Reply with quote
HI Arun,

The syncsort job, u gave is working.
I sorted it according to my requirement.

Thanks to Arun and Ramsri.

Thanks,
Kalyan V
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 Compare 2 files and retrive records f... DFSORT/ICETOOL 3
No new posts Compare 2 files(F1 & F2) and writ... JCL & VSAM 8
No new posts Need to set RC4 through JCL SORT DFSORT/ICETOOL 5
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
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