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 an existing column with values from another file

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

New User


Joined: 20 Jun 2006
Posts: 9

PostPosted: Tue Feb 06, 2007 5:33 pm    Post subject: Replace an existing column with values from another file
Reply with quote

Hi,

Please find below my requirement.
This is a translation process where I want to replace an existing column(3 digit) with the new one(four digit).

For example:
Input files:

INPUT1:
01239xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
03507xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
00208yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy

INPUT2:
0200204
1231234
3503500

In INPUT1, column 2 to 4 is the code that has to be replaced with the correponding values from INPUT2.
INPUT 2 has from COL 1 - 3 the code and 4-7 its equivalent value. example, for code 123, the equivalent value is 1234.

Col 4 in INPUT1 is the check digit added to these codes, ie, 350 is written in input file as 3507. Instead of the code with check digit(eg:1239), we want the output file to have the equivalent value of the code(1234) from INPUT2.

OUTPUT has to be
01234xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
03500xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
00204yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy

We tried this sucessfully with ICETOOL utility. But there is considerable degradetion in performance, as the INPUT1 file has around 20 million records and the job takes almost 30 min to complete.
The INPUT File is Sequential, FB format & LRECL = 135.

Can anyone suggest a solution for this code conversion or to fine tune the ICETOOL utility?
Back to top
View user's profile Send private message

William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Tue Feb 06, 2007 5:40 pm    Post subject:
Reply with quote

It might help if you would post your ICETOOL jcl and control data.
Back to top
View user's profile Send private message
drajagopal

New User


Joined: 20 Jun 2006
Posts: 9

PostPosted: Tue Feb 06, 2007 5:50 pm    Post subject: Re: Replace an existing column with values from another file
Reply with quote

Hi,

Please find the attachment.


Thank you.
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: Tue Feb 06, 2007 9:43 pm    Post subject:
Reply with quote

I can't read your download file. It appears to be one big line. But from what I can tell, it appears to be doing more operations than necessary.

Here's a DFSORT/ICETOOL job that will do what you asked for. See if you can adapt it to what you need and if it runs faster than the one you're using.

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/135)
//IN2 DD DSN=...  input file2 (FB/135)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (FB/135)
//TOOLIN   DD    *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(144,3,CH) -
  WITHALL WITH(1,1) WITH(6,139)
SORT FROM(T2) TO(OUT) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC BUILD=(2:4,4,144:1,3)
/*
//CTL2CNTL DD *
  INREC OVERLAY=(136:SEQNUM,8,ZD,144:2,3)
/*
//CTL3CNTL DD *
  SORT FIELDS=(136,8,ZD,A)
  OUTREC BUILD=(1,135)
/*
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Feb 06, 2007 10:01 pm    Post subject:
Reply with quote

Hello,

If the attached file is right-clicked/saved as to a local drive, it opens ok. When opened vie IE, several of these have jumbled.

Here is the content.
Code:

NOTES: INPUT.&JOBNMQ..* - *
ALL permanent intermediate files ,
RECFM -FB, LEECL - 160, CYL size- 6000
&FILEIN and &FILEOUT - input & output dataset ,
RECFM - FB, LRECL - 135, has 19 million records.
INPUT.STORCONV - has 3 digit code & equivalent 4 digit code at 140th position.
(140-142 - 3 digit code & 143-146 - 4 digit code); LRECL - 160. JCL:
//STEP0010 EXEC PGM=ICETOOL
//SYSOUT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//DFSPARM DD DSN=FH.CMDLIB(NFDPARM),DISP=SHR
//INFILE DD DSN=&FILEIN,DISP=SHR
//STORCONV DD DSN=INPUT.STORCONV,DISP=SHR
//INFILESQ DD DSN=INPUT.INFILESQ.FILE,DISP=SHR
//DATAI DD DSN=INPUT.DATAI.FILE,DISP=SHR
//DATAX DD DSN=INPUT.DATAX.FILE,DISP=SHR
//TEMPI DD DSN=INPUT.TEMPI.FILE,DISP=SHR
//CONCATFL DD DSN=INPUT.CONCATFL.FILE,DISP=MOD
//SPLICOUT DD DSN=INPUT.SPLICOUT.FILE,DISP=SHR
//SPLICFMT DD DSN=INPUT.SPLICFMT.FILE,DISP=SHR
//MERGFILE DD DSN=INPUT.MERGFILE.FILE,DISP=MOD
//OUTFILE DD DSN=&FILEOUT,DISP=SHR
//OUTCNT1 DD DSN=&&OUTCNT1,
// DISP=(MOD,DELETE,DELETE),
// UNIT=&UNIT,
// SPACE=(TRK,(1,0,0),RLSE),
// DCB=(DSORG=PS,RECFM=FB)
//OUTCNT2 DD DSN=&&OUTCNT2,
// DISP=(NEW,DELETE,DELETE),
// UNIT=&UNIT,
// SPACE=(TRK,(1,0,0),RLSE),
// DCB=(DSORG=PS,RECFM=FB)
//SYSOUT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=D
//SYMNAMES DD DSN=FH.CMDLIB(SKE120T1),DISP=SHR
//SYMNOUT DD SYSOUT=*
//TOOLIN DD DSN=FH.CMDLIB(SK120TLN),DISP=SHR
//NFC1CNTL DD DSN=FH.CMDLIB(SKC1CNTL),DISP=SHR
//NFC2CNTL DD DSN=FH.CMDLIB(NFC2CNTL),DISP=SHR
//NFC3CNTL DD DSN=FH.CMDLIB(SKC3CNTL),DISP=SHR
//NFC4CNTL DD DSN=FH.CMDLIB(SKC4CNTL),DISP=SHR
//NFC5CNTL DD DSN=FH.CMDLIB(NFC5CNTL),DISP=SHR
//NFS1CNTL DD DSN=FH.CMDLIB(NFS2CNTL),DISP=SHR
NFDPARM: --------
OPTION DYNSPC=900,DYNALLOC=(,010)
SKE120T1: ----------
RECLEN,1,135
FIELD1,1,5
VALUEH,C'00000'
VALUET,C'99999'
DATA1,1,1
DATA2,6,130
DATA3,2,3
SPLICE_FIELD,140,3,CH
REVERT_BACK_FIRST,1,1
REVERT_BACK_SECOND,143,4
REVERT_BACK_THIRD,2,130
SORT_SEQ,150,10,ZD
SK120TLN: ---------
COPY FROM(INFILE) USING(NFC1)
COPY FROM(INFILESQ) USING(NFC2)
COPY FROM(DATAI) TO(TEMPI) USING(NFC3)
COPY FROM(STORCONV) TO(CONCATFL)
COPY FROM(TEMPI) TO(CONCATFL)
SPLICE FROM(CONCATFL) TO(SPLICOUT) ON(SPLICE_FIELD) -
WITH(1,139) WITH(150,10) WITHALL
COPY FROM(SPLICOUT) TO(SPLICFMT) USING(NFC4)
COPY FROM(SPLICFMT) TO(MERGFILE)
COPY FROM(DATAX) TO(MERGFILE)
SORT FROM(MERGFILE) TO(OUTFILE) USING(NFS1)
COPY FROM(INFILE) TO(OUTCNT1) USING(NFC5)
COPY FROM(OUTFILE) TO(OUTCNT1) USING(NFC5)
SELECT FROM(OUTCNT1) TO(OUTCNT2) ON(1,15,ZD) NODUPS COUNT FROM(OUTCNT2) NOTEMPTY
SKC1CNTL: ---------
OUTFIL FNAMES=INFILESQ,
OUTREC=(RECLEN,150:SEQNUM,10,ZD,160:X)
NFC2CNTL: -----------
OUTFILE FNAMES=DATAI,
INCLUDE=(FIELD1,CH,NE,VALUEH,AND,FIELD1,CH,NE,VALUET)
OUTFILE FNAMES=DATAX,
INCLUDE=(FIELD1,CH,EQ,VALUEH,OR,FIELD1,CH,EQ,VALUET)
SKC3CNTL: ---------
OUTREC FIELDS=(DATA1,DATA2,140:DATA3,150:150,10)
SKC4CNTL: ---------
OUTREC FIELDS=(REVERT_BACK_FIRST,REVERT_BACK_SECOND, -
REVERT_BACK_THIRD,150:150,10)
NFC5CNTL: ---------
OUTFIL FNAMES=OUTCNT1, REMOVECC, NODETAIL, TRAILER1=(COUNT=(M11,LENGTH=15))
NFS2CNTL: ---------
SORT FIELDS=(1,5,CH,A, X 6,1,CH,A, X 7,8,CH,A, X 15,1,CH,A) END
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. Fetching data from BAI File arunsoods JCL & VSAM 1 Wed Jul 19, 2017 4:28 pm
No new posts Write out NODUPS but just from one file Jay Villaverde DFSORT/ICETOOL 8 Fri Jul 14, 2017 12:44 am
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 18 Thu Jul 13, 2017 2:11 pm
No new posts How to add header with Date(YYMMDD) i... Rajan Moorthy DFSORT/ICETOOL 2 Thu Jul 06, 2017 11:44 pm
No new posts How to generate a new unique Input fi... for1ranjith CLIST & REXX 11 Sat Jul 01, 2017 12:09 pm


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