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
 

 

Replace some columns in a dataset using columns from another

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
ms0033436

New User


Joined: 20 Nov 2008
Posts: 15
Location: Pune

PostPosted: Tue Jun 08, 2010 7:22 pm    Post subject: Replace some columns in a dataset using columns from another
Reply with quote

I need to replace some columns from one dataset with the specific columns from another dataset. I have around 65000 records in both the files. Both are of fixed block type

for example:

2 files x1 & x2

Code:
x1: abcd12345678ansi
    abcd45697856sint

x2: ac45678994chg
    bc98756123ang


I want to replace the columns 5 to 13 in x1 with columns 2 to 11 from X2

I have tried using OUTREC OVERLAY but this is replacing all rows with the same value

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

Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Jun 08, 2010 10:40 pm    Post subject:
Reply with quote

ms0033436,

What is the LRECL and RECFM of the input and output files?
Back to top
View user's profile Send private message
ms0033436

New User


Joined: 20 Nov 2008
Posts: 15
Location: Pune

PostPosted: Wed Jun 09, 2010 11:07 am    Post subject: Reply to: Replace some columns in a dataset using columns fr
Reply with quote

Hi,

Following are the details of both the files

Record format . . . : FB
Record length . . . : 2005

Thanks,
Mani
Back to top
View user's profile Send private message
Alexis Sebastian

New User


Joined: 04 Mar 2010
Posts: 38
Location: DC

PostPosted: Wed Jun 09, 2010 2:22 pm    Post subject:
Reply with quote

Mani,

Is there any common key in both files? If so, what is the starting position and its length?
Back to top
View user's profile Send private message
ms0033436

New User


Joined: 20 Nov 2008
Posts: 15
Location: Pune

PostPosted: Wed Jun 09, 2010 2:25 pm    Post subject: Reply to: Replace some columns in a dataset using columns fr
Reply with quote

Hi,

I don't have any common keys in both the files
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Wed Jun 09, 2010 3:39 pm    Post subject:
Reply with quote

Hello,

So u want to merge record like first record of X1 with first record of x2 ?
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida

PostPosted: Wed Jun 09, 2010 3:40 pm    Post subject: Reply to: Replace some columns in a dataset using columns fr
Reply with quote

Mani,

There is something wrong with your column lengths

Quote:
I want to replace the columns 5 to 13 in x1 with columns 2 to 11 from X2


13-5 = 8
11-2 = 9

Do you want to replace 8 byte field with 9? or it was some typo error ?

You have 2 options logically
1) You may try using splice operator for this type of record by record concatenation
2) You can also use new Reformat Fields operator used with join keys.

You need to create temporary sequence no. for each record for both files then join files on that sequence no.
Back to top
View user's profile Send private message
ms0033436

New User


Joined: 20 Nov 2008
Posts: 15
Location: Pune

PostPosted: Wed Jun 09, 2010 3:42 pm    Post subject: Reply to: Replace some columns in a dataset using columns fr
Reply with quote

Sorry that was a typo error
Back to top
View user's profile Send private message
anshul_gugnani

New User


Joined: 02 Nov 2009
Posts: 73
Location: Mumbai

PostPosted: Wed Jun 09, 2010 3:47 pm    Post subject:
Reply with quote

Hi hikaps14,

I was thinking to the second option u mentioned but could not understand can we everything in single ort step, I mean adding the temp seq no to 2 files as well as joining dem -

In the below sort card, Hoe can we add the logic to create temp se no in at posn 17 in file1 and 13 in file2 -

Quote:

JOINKEYS FILES=F1,FIELDS=(17,08,A)
JOINKEYS FILES=F2,FIELDS=(13,08,A)
REFORMAT FIELDS=(F1:01,04,F2:03,08,F1:13,4)
OPTION COPY
BUILD=(1:1,4,05:05,08,13:13,4)


Please suggest
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida

PostPosted: Wed Jun 09, 2010 4:06 pm    Post subject: Reply to: Replace some columns in a dataset using columns fr
Reply with quote

Try the below job

Code:
//STEP01    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file1
//IN2 DD DSN=...  input file2
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file
//TOOLIN DD *
 COPY FROM(IN1) TO(T1) USING(CTL1)
 COPY FROM(IN2) TO(T1) USING(CTL2)
 SPLICE FROM(T1) TO(OUT) ON(2006,8,ZD) WITH(5,8) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC BUILD=(1,2005,2006:SEQNUM,8,ZD)
/*
//CTL2CNTL DD *
  INREC BUILD=(5:3,8,2006:SEQNUM,8,ZD)
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,BUILD=(1,2005)
/*


I have assumed that you are replacing 5-12 in X1 with 3-10 in X2. All I/P and O/p files are FB and lrecl=2005.

I have tries the above code but with different lrecl and position of files.
Back to top
View user's profile Send private message
anshul_gugnani

New User


Joined: 02 Nov 2009
Posts: 73
Location: Mumbai

PostPosted: Wed Jun 09, 2010 5:06 pm    Post subject:
Reply with quote

Hi,

In the above sort card, I understood that all the records are in the temp file and then SPLICED in the seqnum added, but could understand the WITH part.
Quote:
SPLICE FROM(T1) TO(OUT) ON(2006,8,ZD) WITH(5,8) USING(CTL3)


Could you please explain.

Also, Can you please suggest how can we accomplish this using the joinkeys.

Thanks in advance.
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida

PostPosted: Wed Jun 09, 2010 5:35 pm    Post subject: Reply to: Replace some columns in a dataset using columns fr
Reply with quote

Splice Operator:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA10/6.13?DT=20050222160456

Try going through the above link.

In short WITH is the keyword which helps you in picking the Column of other records with same key (key is defined using ON keyword). There are other options of WITHALL and WITHEACH. These are well defined wih examples in above link.

I would try and let u know about the Joinkeys job. Still, did the earlier job run fine and produce the desired result ?
Back to top
View user's profile Send private message
anshul_gugnani

New User


Joined: 02 Nov 2009
Posts: 73
Location: Mumbai

PostPosted: Wed Jun 09, 2010 6:23 pm    Post subject:
Reply with quote

Hi,

Thanks for the info.
Yes it did give the desired result.

But was curious to know if can be done with joinkeys since was trying it.
Please let me know if find anything on that.

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

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida

PostPosted: Wed Jun 09, 2010 9:45 pm    Post subject: Reply to: Replace some columns in a dataset using columns fr
Reply with quote

Hi,

Have you tried finding out on Google. There is a pdf 'Smart DFSORT Tricks'. This should help

ftp://ftp.software.ibm.com/storage/dfsort/mvs/sorttrck.pdf

There is topic 'Join fields from two files record-by-record'. You should find it interesting and useful. You will get a chance to try and experiment more on dfsort tricks.

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

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Jun 16, 2010 2:45 am    Post subject:
Reply with quote

Quote:
In the below sort card, Hoe can we add the logic to create temp se no in at posn 17 in file1 and 13 in file2


With the appropriate INREC statement in JNF1CNTL (for file1) and in JNF2CNTL (for file2). INREC in JNFnCNTL is applied to the Fn file before JOIN processing. This is all explained in my paper at:

http://www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000174
Back to top
View user's profile Send private message
anshul_gugnani

New User


Joined: 02 Nov 2009
Posts: 73
Location: Mumbai

PostPosted: Wed Jun 16, 2010 11:16 pm    Post subject:
Reply with quote

Hello Frank,

Thanks a lot. This indeed is an awesome user Guide. icon_smile.gif
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts FTP - JCL failed while passing FTP co... Suneetha1612 JCL & VSAM 12 Wed Nov 16, 2016 7:33 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts Replace repeated JCL with looping REXX prino CLIST & REXX 3 Thu Oct 13, 2016 5:30 am
No new posts To trim spaces in a pipe delimited da... Nish84 COBOL Programming 16 Mon Oct 10, 2016 1:54 pm


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