View previous topic :: View next topic
Author
Message
kalyan.v New User Joined: 04 Feb 2008Posts: 65 Location: Hyd
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
Aaru Senior Member Joined: 03 Jul 2007Posts: 1287 Location: Chennai, India
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
kalyan.v New User Joined: 04 Feb 2008Posts: 65 Location: Hyd
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
kalyan.v New User Joined: 04 Feb 2008Posts: 65 Location: Hyd
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
Arun Raj Moderator Joined: 17 Oct 2006Posts: 2481 Location: @my desk
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
ramsri Active User Joined: 18 Oct 2008Posts: 380 Location: India
The SPLICE operator should do the trick for you if you have latest TPF installed at your site
Back to top
Arun Raj Moderator Joined: 17 Oct 2006Posts: 2481 Location: @my desk
Ramsri,
SyncSort has JOIN feature from Release 1.2 onwards which makes file-matching easier than the SPLICE approach using SYNCTOOL.
Back to top
ramsri Active User Joined: 18 Oct 2008Posts: 380 Location: India
I tried SPLICE but unsuccessfully and got partial results !
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.
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
gcicchet Senior Member Joined: 28 Jul 2006Posts: 1702 Location: Australia
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
Arun Raj Moderator Joined: 17 Oct 2006Posts: 2481 Location: @my desk
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
kalyan.v New User Joined: 04 Feb 2008Posts: 65 Location: Hyd
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
ramsri Active User Joined: 18 Oct 2008Posts: 380 Location: India
Gerry, Thanks a lot for the help...... Yes. Now, the output is coming as expected by Kalyan....
Arun, I was trying the JOIN but could not achieve it 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
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
Arun Raj Moderator Joined: 17 Oct 2006Posts: 2481 Location: @my desk
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
ramsri Active User Joined: 18 Oct 2008Posts: 380 Location: India
Arun, shouldn't the first record be third record as per the original requirement?
Back to top
Arun Raj Moderator Joined: 17 Oct 2006Posts: 2481 Location: @my desk
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
Arun Raj Moderator Joined: 17 Oct 2006Posts: 2481 Location: @my desk
Quote:
I am not trying to understand how he arrived at that order
Bad typing day , Actually I meant "I am".
Back to top
kalyan.v New User Joined: 04 Feb 2008Posts: 65 Location: Hyd
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
Please enable JavaScript!