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

Replace file1 column with file2 column data


IBM Mainframe Forums -> SYNCSORT
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
lavakumar149

New User


Joined: 25 Jul 2013
Posts: 7
Location: India

PostPosted: Tue Aug 06, 2013 4:29 pm
Reply with quote

Hi,

I need to replace file1 column with file2 column data. Can anyone give the jcl for this.

File1: RECFM=V,
RECLENGTH=4096

File 2: RECFM=FB,
RECLENGTH=80

o/p file: RECFM=V,
RECLENGTH=4096

File1 data:
Code:
11111##########AAAAA
22222##########BBBBBBBBB
33333##########CCCCCCCCCC
44444##########DDDDDDDDDDDD
55555##########EEEEEEEEEEEEEEEEEE


File2 data:
Code:
0987654321
0987654322


I want to replace the file2 telephone numbers in file1
6th position(6-10-->##########), remaining data should be same as file1.

o/p file result should come like following.

Code:
111110987654321AAAAA
222220987654322BBBBBBBBB
333330987654321CCCCCCCCCC
444440987654322DDDDDDDDDDDD
555550987654321EEEEEEEEEEEEEEEEEE


Can anyone provide the jcl for this.

Code'd
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Tue Aug 06, 2013 4:36 pm
Reply with quote

First of all, you need to explain the matching of the two files. Are there keys, if so what position, format and length of them. If not, what are the rules.

Second - which sort product and release level are you using, run the code below and post the SYSOUT segment from the ouput
Code:

//SORTCOPY EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*   
//SORTIN   DD *         
DUMMY                   
/*                       
//SORTOUT  DD SYSOUT=*   
//SYSIN    DD *         
  OPTION COPY           


Third DO NOTask for a solution without giving any evidence that you have tried something yourself, unless you are willing to pay the going daily rate of approx 750 euro.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Tue Aug 06, 2013 5:21 pm
Reply with quote

Are the record numbers in Sync in File1 & File2?
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Aug 06, 2013 5:28 pm
Reply with quote

Please use the Code tags.

It looks like you are alternating the File 2 values on File 1. Is that correct? Will there always be two records on File 2?

I'd generate symbols/SYMNAMES for the two values, identify odd/even records using a sequence number, (see here for an exampple of that) and just put the constant values on.
Back to top
View user's profile Send private message
lavakumar149

New User


Joined: 25 Jul 2013
Posts: 7
Location: India

PostPosted: Tue Aug 06, 2013 5:43 pm
Reply with quote

Hi expat,

1) Actually there are no keys to match in the files. file2 contains valid telephone numbers. in file1 TN's are masked. So, i need to replace these valid telephone numbers in the masking field of file1(6th-10 position).
Suppose file1 have 5 records and file2 has 2 records. i need to replace the file2 records
Code:
like     1
                                  2
                                  1
                                  2
                                  1     

the o/p file result i wrote previously.

2) i ran your given code to find out the sort product. check the sysout segment in below.

Code:
SYNCSORT FOR Z/OS  1.4.1.0R    U.S. PATENTS: 4210961, 5117495   (C) 2010 SYNCSO
                                          VERIZON WIRELESS   z/OS   1.13.0     
SYNCSORT LICENSED FOR CPU SERIAL NUMBER C31F6, MODEL 2817 615             LICEN
SYSIN :                                                                       
  OPTION COPY                                                                 
WER901I  **WARNING** SYNCSORT 1.4.1.0 WILL EXPIRE IN 65 DAYS                   
WER276B  SYSDIAG= 4606124, 6434467, 6434467, 6141525                           
WER164B  7,920K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,             
WER164B     0 BYTES RESERVE REQUESTED, 332K BYTES USED                         
WER146B  20K BYTES OF EMERGENCY SPACE ALLOCATED                               
WER108I  SORTIN   : RECFM=FB   ; LRECL=    80; BLKSIZE=    80                 
WER073I  SORTIN   : DSNAME=Z616702.Z616702V.JOB32460.D0000101.?               
WER110I  SORTOUT  : RECFM=FB   ; LRECL=    80; BLKSIZE=    80                 
WER074I  SORTOUT  : DSNAME=Z616702.Z616702V.JOB32460.D0000104.?               
WER410B  5,868K BYTES OF VIRTUAL STORAGE AVAILABLE ABOVE THE 16MEG LINE,       
WER410B     0 BYTES RESERVE REQUESTED, 216K BYTES USED                         
WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000                                 
WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE                                 
WER416B  BSAM WAS USED FOR SORTIN                                             
WER416B  BSAM WAS USED FOR SORTOUT                                             
WER054I  RCD IN          1, OUT          1                                     
WER169I  RELEASE 1.4 BATCH 0520 TPF LEVEL 1.0                                 
WER052I  END SYNCSORT - Z616702V,SORTCOPY,,DIAG=E200,60CE,CA0E,00C6,AAF2,4CC3,8


3) I tried this requirement with ICETOOL by using the following code
Code:
//STEP01    EXEC  PGM=ICETOOL                                       
//TOOLMSG DD SYSOUT=*                                               
//DFSMSG  DD SYSOUT=*                                               
//IN1     DD DSN=VISDND1.LK.TSFILE1,DISP=SHR                       
//IN2     DD DSN=VISDND1.LK.TFILE2,DISP=SHR                         
//T1      DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS) 
//OUT     DD DSN=VISDND1.LK.TSFILE3,DISP=OLD                       
//TOOLIN  DD *                                                     
 COPY FROM(IN1) TO(T1) USING(CTL1)                                 
 COPY FROM(IN2) TO(T1) USING(CTL2)                                 
 SPLICE FROM(T1) TO(OUT) ON(81,8,ZD) WITH(11,10) USING(CTL3)       
/*                                                                 
//CTL1CNTL DD *                                                     
  INREC BUILD=(1,80,81:SEQNUM,8,ZD)                                 
/*                                                                 
//CTL2CNTL DD *                                                     
  INREC BUILD=(11:1,10,81:SEQNUM,8,ZD)                             
/*                                                                 
//CTL3CNTL DD *                                                     
  OUTFIL FNAMES=OUT,BUILD=(1,80)                                   
/*                                                                 



But i got o/p like

Code:
111110987654321AAAAA
222220987654322BBBBBBBBB
33333########CCCCCCCCCC
44444########DDDDDDDDDDDD
55555########EEEEEEEEEEEEEEEEEE

last 3 records are not replaced with telephone numbers. I tried on both vb files (reclength=80).

The above code generates the sequence number for every record then it compares and replaces like follow.

1) file1 becomes:
Code:
111110987654321AAAAA                                      00000001
222220987654322BBBBBBBBB                              00000002
333330987654321CCCCCCCCCC                          00000003
444440987654322DDDDDDDDDDDD                      00000004
555550987654321EEEEEEEEEEEEEEEEEE                  00000005


2)file2 becomes:
Code:
0987654321                                                         00000001
0987654322                                                         00000002


So, the sequnce number is matched for first 2 records and these telephone numbers are replaced in first 2 recrods only.

So, is there any way to generate seqnumber from start=1 and end=2.
so that all records will replaced properly Or
is there any best way to solve this problem.

Code'd
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Aug 06, 2013 5:48 pm
Reply with quote

Please, again, use the Code tags. Please post SyncSort questions in the JCL forum, not in the DFSORT forum.

Did you read my suggestion?
Back to top
View user's profile Send private message
lavakumar149

New User


Joined: 25 Jul 2013
Posts: 7
Location: India

PostPosted: Tue Aug 06, 2013 5:56 pm
Reply with quote

Hi Bill Woodger,

you are correct. file2 values are replacing again if there are less records. Means we need to read file2 values and replace those in file1. if file2 records are finished then we need to read file2 again and start replacing.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Aug 07, 2013 1:11 pm
Reply with quote

Bill Woodger wrote:
Will there always be two records on File 2?
lavakumar149,

What about this?
Back to top
View user's profile Send private message
lavakumar149

New User


Joined: 25 Jul 2013
Posts: 7
Location: India

PostPosted: Wed Aug 07, 2013 2:14 pm
Reply with quote

Hi Arun Raj,

it is not possible that file2 will always have only 2 records. But file2 will have less records than file1. So, i need to read file2 and replace file2 telephone numbers in file1(6:10 positon). if file2 records are finished and file1 is still have records then we need read file2 again and replace in file1.

Please look into the following for your reference.

File1: RECFM=V,
RECLENGTH=4096

File 2: RECFM=FB,
RECLENGTH=80

o/p file: RECFM=V,
RECLENGTH=4096

File1 data:
Code:
11111##########AAAAA
22222##########BBBBBBBBB
33333##########CCCCCCCCCC
44444##########DDDDDDDDDDDD
55555##########EEEEEEEEEEEEEEEEEE


File2 data:
Code:
0900111111
0900222222


I want to replace the file2 telephone numbers in file1
6th position(6-10-->##########), remaining data should be same as file1.

o/p file result should come like following.

Code:
111110900111111AAAAA
222220900222222BBBBBBBBB
333330900111111CCCCCCCCCC
444440900222222DDDDDDDDDDDD
555550900111111EEEEEEEEEEEEEEEEEE

Note: there are no keys in both files to match. need to read sequentially and replace.

Can you provide the jcl for above requirement. Or suggest me, if there is any other way to achieve this.

[/code]
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Wed Aug 07, 2013 3:00 pm
Reply with quote

The JCL is simple - the sort control records are another matter altogether. Which do you want?
Back to top
View user's profile Send private message
lavakumar149

New User


Joined: 25 Jul 2013
Posts: 7
Location: India

PostPosted: Wed Aug 07, 2013 3:10 pm
Reply with quote

Hi Nic,

Provide any, which is not complex.[/code]
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Wed Aug 07, 2013 4:53 pm
Reply with quote

Well, I could provide the JCL but it is well documented in the manual - you need a jobcard, DD cards for input file, output file and messages and a SYSIN card for where your sort control cards go. all fairly standard JCL. I cannot help with the sort control cards though.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Aug 07, 2013 4:58 pm
Reply with quote

lavakumar149,

You've finally submitted under Arun Raj's questioning.

You absolutely never "open the file again and start reading it again", even when writing a program where you could actually do that.

Look at CHANGE. Work out how to "generate" a CHANGE statement and other things necessary.

Process is to set a sequence number which will be replace by your telephone number. RESTART the sequence number according to the number of telephone numbers you have. Use CHANGE to change each successive sequence number to each successive telephone number.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Wed Aug 07, 2013 5:03 pm
Reply with quote

Perhaps a curious mind .............. but

Why would you want to propogate the same telephone number across numerous records in a file with no way of matching a number against an account or any other relevant field in the receiving file.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Aug 07, 2013 5:11 pm
Reply with quote

With sort it is doable, but might take too many passes. It could be better writing some code for this.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Wed Aug 07, 2013 5:31 pm
Reply with quote

Quote:
Why would you want to propogate the same telephone number across numerous records in a file with no way of matching a number against an account or any other relevant field in the receiving file.


Looks to me as a testdata preparation icon_wink.gif
Back to top
View user's profile Send private message
lavakumar149

New User


Joined: 25 Jul 2013
Posts: 7
Location: India

PostPosted: Thu Aug 08, 2013 10:45 am
Reply with quote

Hi,

file2 contains valid telephone numbers which are unloaded from a table. in file1 TN's are masked(which comes from production). So, i need to replace these valid telephone numbers in the masking field of file1(6th-10 position).

Final requirement is file1 should have the valid telephone numbers similar to file2 telephone numbers.

Note: file1 should not contain masking data for telephone number position.

Suppose if file2 has 50 telephone numbers. it is not mandatory to
replace all 50 numbers in file1. if we replace first 10 telephone numbers repeatedly also fine.

File1 should have the valid telephone numbers like fil2. this is the o/p result i am expecting.
Can anyone provide any sort card or suggest me if there is any better way to achieve this. [/code]
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Aug 08, 2013 11:19 am
Reply with quote

Quote:
Can anyone provide any sort card or suggest me if there is any better way to achieve this
If you need just a first few telephone numbers from file-2, you could write some code to read and store them in an array. Read each record from file-1, attach the phone number from the array and write into output. Restart the array index to 1 upon reaching the end of array. Repeat until end-of-file-1
Back to top
View user's profile Send private message
lavakumar149

New User


Joined: 25 Jul 2013
Posts: 7
Location: India

PostPosted: Thu Aug 08, 2013 11:58 am
Reply with quote

Hi Arun,

I already got the result through a cobol program. But, I am looking whether any sort logic is there to do this in a smart way.

Can we do this in another way like following.
compare file1(6:10 position) values with file2(1:10) position values. if no match found then replace file2 telephone number in file1(6:10 position).
As file1 has masking data like ######### match will never found.

If this is possible, can you provide any sort/icetool logic to replace when no match found.

File1: RECFM=V,
RECLENGTH=4096

File 2: RECFM=FB,
RECLENGTH=80

o/p file: RECFM=V,
RECLENGTH=4096

File1 data:
Code:

11111##########AAAAA
22222##########BBBBBBBBB
33333##########CCCCCCCCCC
44444##########DDDDDDDDDDDD
55555##########EEEEEEEEEEEEEEEEEE


File2 data:
Code:

0900111111
0900222222


o/p file result expecting:
Code:

111110900111111AAAAA
222220900222222BBBBBBBBB
333330900111111CCCCCCCCCC
444440900222222DDDDDDDDDDDD
555550900111111EEEEEEEEEEEEEEEEEE
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Thu Aug 08, 2013 12:38 pm
Reply with quote

I dont think this could be achieved in sort

If you are able to group the records for the count of records in File2

So then you could join through joinkeys
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Aug 08, 2013 12:41 pm
Reply with quote

Pandora-Box wrote:
I dont think this could be achieved in sort
I think it can be achieved using sort. But there are things that could be done but not advisable to do, especially when there is already a better working solution.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Thu Aug 08, 2013 1:44 pm
Reply with quote

As all you are trying to do is "mask the mask" why not just hard-code one set of digits - they do not have to be a real telephone number. Then it is just a find mask/replace with string.
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 -> SYNCSORT

 


Similar Topics
Topic Forum Replies
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts How to load to DB2 with column level ... DB2 6
No new posts SCOPE PENDING option -check data DB2 2
No new posts RC query -Time column CA Products 3
Search our Forums:

Back to Top