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 card to get all records from two files using syncsort?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
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    Post subject: sort card to get all records from two files using syncsort?
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: 1288
Location: Chennai, India

PostPosted: Tue Dec 23, 2008 12:10 pm    Post subject: Reply to: sort card to get all records from two files using
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    Post subject:
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    Post subject:
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: 2158
Location: @my desk

PostPosted: Tue Dec 23, 2008 6:58 pm    Post subject:
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    Post subject: Reply to: sort card to get all records from two files using
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: 2158
Location: @my desk

PostPosted: Tue Dec 23, 2008 7:04 pm    Post subject:
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    Post subject: Reply to: sort card to get all records from two files using
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: 1703
Location: Australia

PostPosted: Wed Dec 24, 2008 3:24 am    Post subject:
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: 2158
Location: @my desk

PostPosted: Wed Dec 24, 2008 8:54 am    Post subject:
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    Post subject:
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    Post subject: Reply to: sort card to get all records from two files using
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: 2158
Location: @my desk

PostPosted: Wed Dec 24, 2008 7:31 pm    Post subject:
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    Post subject: Reply to: sort card to get all records from two files using
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: 2158
Location: @my desk

PostPosted: Wed Dec 24, 2008 9:00 pm    Post subject:
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: 2158
Location: @my desk

PostPosted: Wed Dec 24, 2008 9:16 pm    Post subject:
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    Post subject:
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    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 syncsort: copy lines after the keyword shreya19 SYNCSORT 1 Fri Dec 02, 2016 9:47 am
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 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 Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm


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