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
 

 

use of icetool to concatenate records from different files
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
bhavya_sha

New User


Joined: 16 Apr 2005
Posts: 33

PostPosted: Fri Jan 01, 2010 3:28 pm    Post subject: use of icetool to concatenate records from different files
Reply with quote

Hi,

i have fil1 with record length 260 and file2 with record length 200.requirement id to compare the records from both the files based on data in different position (like file1-1,10 and file2 111-10 should match).
if records are matching then write data from file2(1:191(191bytes from position 1)) and from file1(13:15) as a single record in output file kindly help me out..this is very much needed(ASAP).

here iam pasting the jcl i've written(i need icetool since dfsort's joinkey is not working here).

your edit profile using the command RECOVERY ON.
Code:
//V0C2663L JOB (610YJ000100,1780,01,99),'JOBCARD',
//          CLASS=N,
//          NOTIFY=V0C2663,MSGCLASS=H
//STEP01  EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD  DSN=V0C2663.INFILE1,DISP=SHR
//IN2 DD  DSN=V0C2663.INFILE2,DISP=SHR
//T1  DD  DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD  DSN=V0C2663.MATCH.OUTPUT,DISP=(NEW,CATLG,DELETE),
//            DCB=(RECFM=FB,LRECL=250,BLKSIZE=2500),
//            UNIT=SYSDA,SPACE=(TRK,(5,1),RLSE)
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(01,10,CH) -
 WITH(1,461) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC BUILD=(01:111,10,261:C'11')
/*
//CTL2CNTL DD *
   INREC OVERLAY=(261:C'22')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,INCLUDE=(261,2,CH,NE,C'11'),
  BUILD=(?)
/*



Thanx in advance
Back to top
View user's profile Send private message

Lijo

New User


Joined: 24 Aug 2008
Posts: 41
Location: Cochin

PostPosted: Fri Jan 01, 2010 4:04 pm    Post subject: Reply to: use of icetool to concatenate records from differe
Reply with quote

Try this out. I've just modified your control criteria.

Code:
//STEP01 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=V0C2663.INFILE1,DISP=SHR
//IN2 DD DSN=V0C2663.INFILE2,DISP=SHR
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=V0C2663.MATCH.OUTPUT,DISP=(NEW,CATLG,DELETE),
// DCB=(RECFM=FB,LRECL=250,BLKSIZE=2500),
// UNIT=SYSDA,SPACE=(TRK,(5,1),RLSE)
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(261,10,CH) -
KEEPNODUPS WITH(271,16) USING(CTL3)
/*
//CTL1CNTL DD *
INREC OVERLAY=(1,260,261:1,10,271:13,15,286:C'11')
/*
//CTL2CNTL DD *
INREC OVERLAY=(1,200,60X,261:111,10,15X,286:C'22')
/*
//CTL3CNTL DD *
OUTFIL FNAMES=OUT,INCLUDE=(286,2,CH,EQ,C'12'), -
BUILD=(1,191,192:271,15)
/*
Back to top
View user's profile Send private message
bhavya_sha

New User


Joined: 16 Apr 2005
Posts: 33

PostPosted: Fri Jan 01, 2010 4:27 pm    Post subject: Reply to: use of icetool to concatenate records from differe
Reply with quote

hi ,
thank you very much for your reply.
but the problem here is iam not understanding where is the comparision of data happening here?...u hv mentioned ON (261,10)..but with what?

anyhow i tried running jcl provided by u but the output file is empty.where there are records matching criteria.

Kindly help me.
Regards,
Bhavya
Back to top
View user's profile Send private message
Lijo

New User


Joined: 24 Aug 2008
Posts: 41
Location: Cochin

PostPosted: Fri Jan 01, 2010 5:02 pm    Post subject: Reply to: use of icetool to concatenate records from differe
Reply with quote

In CTL1 and CTL2, I am copying key fields (which is to be matched) to position 261,10. That's the reason for splicing on 261,10. There was a small mistake in previously posted control card. I've used file2 in place of file1. Corrected control cards as below:

Code:
COPY FROM(IN2) TO(T1) USING(CTL2)
COPY FROM(IN1) TO(T1) USING(CTL1)
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: Sat Jan 02, 2010 10:46 pm    Post subject:
Reply with quote

bhavya_sha,

Showing a job you wrote that doesn't work without telling us clearly what you want to do does not help us help you.

You need to step back and describe what you want to do with a good example of input and expected output. Specifically:

Please show an example of the records in each input file (relevant fields only) and what you expect for output. Explain the "rules" for getting from input to output. Give the starting position, length and format of each relevant field. Give the RECFM and LRECL of the input files. If file1 can have duplicates within it, show that in your example. If file2 can have duplicates within it, show that in your example.
Back to top
View user's profile Send private message
bhavya_sha

New User


Joined: 16 Apr 2005
Posts: 33

PostPosted: Mon Jan 04, 2010 9:44 am    Post subject: Reply to: use of icetool to concatenate records from differe
Reply with quote

Hi,

File1 is with record length 260.
File2 is with record length 200.
Outputfile is of record length 250.

Requirement is to compare the data i.e,
data @ position 1-10 from file1 should match with data at position 111-10 in file2 and entire record need not match.

if data is matching then write data i.e, 1-191 bytes from file2(to 1-191 bytes in outputfile) and data from
13th position to 15 bytes from file1(to 192-197 in outputfile) as a single record in to output file .

Duplicates doesn't matter they can be present @ any file.

Notepad is having data at proper position .since it was not clear in web site i've attached file.

EX:

Code:
FILE1:(1 record of length 260)
20211Z5271  TUBE ASSY-TAIL           STKM A1   AB3751971218J1A960711        T2021009Z66     A2   AB3751                             9609 2021009Z66  00AB375101                                                                                                     

FILE2:(2 records of length 200)
0MKB210KHHB   1CH   10011U1681CB201C1U0     010001  20211Z5271  111QCG09123                       20211Z5271  20211Z5271  01100111QCG09123  4370251      00020101G2  9         11112 MS31091217         

0MKB210LHHC   1B2   10011U2651CB201C1U0     110001  20211Z5271  111QCG09123                       20211Z5271  20211Z5279  01100111QCG09123  4370251      00020101G2  9         11112 MS31091217       


OUTPUT FILE:(2 records)
0MKB210KHHB   1CH   10011U1681CB201C1U0     010001  20211Z5271  111QCG09123                       20211Z5271  20211Z5271  01100111QCG09123  4370251      00020101G2  9         11112 MS31091217TUBE ASSY-TAIL
                                           
0MKB210LHHC   1B2   10011U2651CB201C1U0     110001  20211Z5271  111QCG09123                       20211Z5271  20211Z5279  01100111QCG09123  4370251      00020101G2  9         11112 MS31091217


In the above example

20211Z5271 @ 111 position in file2 is matching with first 10 bytes in file1 hence output file is having first 191 bytes
from file2 and 192 onwards 15 bytes from file1.

file2 is having another record whose key is not having matching record in file1 hence the data from file2(1-192) is written
and position where data from file1 has to be picked is empty.

Kindly let me know if it's still not very clear

Thanks in advance.
Bhavya M S
Back to top
View user's profile Send private message
bhavya_sha

New User


Joined: 16 Apr 2005
Posts: 33

PostPosted: Mon Jan 04, 2010 11:22 am    Post subject: Reply to: use of icetool to concatenate records from differe
Reply with quote

hi lijo,

what's the option get even duplicate matching records into output file?

Regards
bhavya M S
Back to top
View user's profile Send private message
Lijo

New User


Joined: 24 Aug 2008
Posts: 41
Location: Cochin

PostPosted: Mon Jan 04, 2010 5:15 pm    Post subject: Reply to: use of icetool to concatenate records from differe
Reply with quote

You can change the SPLICE criteria to retain duplicates as below.

Code:
SPLICE FROM(T1) TO(OUT) ON(261,10,CH) -
WITHALL WITH(271,16) USING(CTL3)


Use the following link to know more about WITHALL option:
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA20/6.13?DT=20060615185603
Back to top
View user's profile Send private message
bhavya_sha

New User


Joined: 16 Apr 2005
Posts: 33

PostPosted: Mon Jan 04, 2010 5:42 pm    Post subject: Reply to: use of icetool to concatenate records from differe
Reply with quote

hi
Iam getting matching records and able to pick data from both he files , how do i get records not matching?

Working code is as below
Code:
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(261,10,CH) WITHALL WITH(271,200)-
 USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(1,260,261:1,10,471:C'11')
/*
//CTL2CNTL DD *
   INREC OVERLAY=(261:111,10,271:1,200,471:C'22')
/*
//CTL3CNTL DD *
   OUTFIL FNAMES=OUT,INCLUDE=(471,2,CH,EQ,C'11'),
   BUILD=(271,191,13,15,44C' ')
/*


edited to add the code tags
Back to top
View user's profile Send private message
Lijo

New User


Joined: 24 Aug 2008
Posts: 41
Location: Cochin

PostPosted: Mon Jan 04, 2010 6:02 pm    Post subject: Reply to: use of icetool to concatenate records from differe
Reply with quote

Use 'KEEPNODUPS' along with SPLICE. Usage and syntax are available in above posted link.

But, are you sure that you're getting matched records using this???

Code:
OUTFIL FNAMES=OUT,INCLUDE=(471,2,CH,EQ,C'11')
Back to top
View user's profile Send private message
bhavya_sha

New User


Joined: 16 Apr 2005
Posts: 33

PostPosted: Mon Jan 04, 2010 6:45 pm    Post subject: yes
Reply with quote

yes iam getting the proper output that's what even iam wondering i tested it many times...icon_smile.gif
Back to top
View user's profile Send private message
bhavya_sha

New User


Joined: 16 Apr 2005
Posts: 33

PostPosted: Mon Jan 04, 2010 7:09 pm    Post subject: Reply to: use of icetool to concatenate records from differe
Reply with quote

but how do i differrentiate that if record is from file1 or file2 using keepnodups?
Back to top
View user's profile Send private message
bhavya_sha

New User


Joined: 16 Apr 2005
Posts: 33

PostPosted: Mon Jan 04, 2010 7:51 pm    Post subject: Reply to: use of icetool to concatenate records from differe
Reply with quote

hi frank,
the icetool iam using is as below to get the output described as above.
could u please comment on this.

Code:
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD  DSN=V0C2663.INFILE1,DISP=SHR
//IN2 DD  DSN=V0C2663.INFILE2,DISP=SHR
//*T1  DD  DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T1   DD  DSN=V0C2663.MATCH.POC,DISP=(MOD,CATLG,DELETE),
//            DCB=(RECFM=FB,LRECL=500,BLKSIZE=5000),
//            UNIT=SYSDA,SPACE=(TRK,(5,1),RLSE)
//OUT DD  DSN=V0C2663.MATCH.OUTPUT,DISP=(NEW,CATLG,DELETE),
//            DCB=(RECFM=FB,LRECL=250,BLKSIZE=2500),
//            UNIT=SYSDA,SPACE=(TRK,(5,1),RLSE)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(261,10,CH) WITHALL WITH(271,200)-
 USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(1,260,261:1,10,471:C'11')
/*
//CTL2CNTL DD *
   INREC OVERLAY=(261:111,10,271:1,200,471:C'22')
/*
//CTL3CNTL DD *
   OUTFIL FNAMES=OUT,INCLUDE=(471,2,CH,EQ,C'11'),
   BUILD=(271,191,13,15,44C' ')
/*
//STEP02  EXEC PGM=IEFBR14
//D2   DD  DSN=V0C2663.MATCH.POC,DISP=(MOD,DELETE,DELETE),
//            DCB=(RECFM=FB,LRECL=500,BLKSIZE=5000),
//            UNIT=SYSDA,SPACE=(TRK,(5,1),RLSE)
//STEP01  EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN3 DD  DSN=V0C2663.INFILE1,DISP=SHR
//IN4 DD  DSN=V0C2663.INFILE2,DISP=SHR
//T2   DD  DSN=V0C2663.MATCH.POC,DISP=(MOD,CATLG,DELETE),
//            DCB=(RECFM=FB,LRECL=500,BLKSIZE=5000),
//            UNIT=SYSDA,SPACE=(TRK,(5,1),RLSE)
//OUT DD  DSN=V0C2663.MATCH.OUTPUT,DISP=MOD
//TOOLIN DD *
COPY FROM(IN3) TO(T2) USING(CTL1)
COPY FROM(IN4) TO(T2) USING(CTL2)
SPLICE FROM(T2) TO(OUT) ON(261,10,CH) KEEPNODUPS WITH(271,200)-
 USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(1,260,261:1,10,471:C'11')
/*
//CTL2CNTL DD *
   INREC OVERLAY=(261:111,10,271:1,200,471:C'22')
/*
//CTL3CNTL DD *
   OUTFIL FNAMES=OUT,INCLUDE=(471,2,CH,EQ,C'22'),
   BUILD=(271,191,59C' ')
/*
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: Mon Jan 04, 2010 11:36 pm    Post subject:
Reply with quote

Quote:
FILE1:(1 record of length 260)


Does FILE1 really only have 1 record, or can it have more than one record?

Quote:
Duplicates doesn't matter they can be present @ any file.


Are you saying that input file1 can have duplicates within it and input file2 can have duplicates within it? If so, please show an example of input and expected output that covers all of those cases. Your download file doesn't work for me, so please send me a .txt file offline (yaeger@us.ibm.com) instead.
Back to top
View user's profile Send private message
bhavya_sha

New User


Joined: 16 Apr 2005
Posts: 33

PostPosted: Tue Jan 05, 2010 11:52 am    Post subject: Reply to: use of icetool to concatenate records from differe
Reply with quote

hi,
is it's possible to write both matching and nonmatching records to same outputfile with single TOOLINDD*.

i tried with
//TOOLIN DD *
COPY FROM(IN1) TO(T2) USING(CTL1)
COPY FROM(IN2) TO(T2) USING(CTL2)
SPLICE FROM(T2) TO(OUT) ON(261,10,CH) KEEPNODUPS WITH(271,200)-
USING(CTL3)
SPLICE FROM(T2) TO(OUT) ON(261,10,CH) WITHALL WITH(271,200)-
USING(CTL3)
/*
BUT SECOND SPLICE OVERWRITES THE FIRST SPLICE RECORDS.

IF I USE 2 DIFFERENT EXEC STEPS THEN THE SEQUENCE IS LIKE FIRST SET OF RECORDS ARE MATCHING RECORDS. NEXT SET ARE UNIQUE RECORDS FROM FILE1.

I NEED TO HAVE RECORDS IN THE SAME ORDER LIKE
1ST RECORD(MATCHING)
2ND RECORD(NOT MATCHING (PRESENT IN FILE2 NOT IN FILE1)
3RD RECORD MATCHING

THANKS & REGARDS,
BHAVYA m s
Back to top
View user's profile Send private message
Lijo

New User


Joined: 24 Aug 2008
Posts: 41
Location: Cochin

PostPosted: Tue Jan 05, 2010 11:59 am    Post subject: Reply to: use of icetool to concatenate records from differe
Reply with quote

Quote:
yes iam getting the proper output that's what even iam wondering i tested it many times...



Code:
SPLICE FROM(T1) TO(OUT) ON(261,10,CH) WITHALL WITH(271,200)-
 USING(CTL3)


You're splicing with 271,200 which is suppose to be your file2's data. When there is a match, 271 to 200 bytes will come to file1 data, but which will still have '11' at 471 as you've not used 471st or 472nd bytes in splicing. Also, you're not using 'KEEPNODUPS'. Hence all unmatched records will be removed. As a result, after splicing only matched records will remain and which you're extracting using CTL3.

Try the following JCL to get matched/unmatched records and duplicates within file2. (Changes from your JCL are, modfied the WITH condition and included two new files).
Franks's question still remains as you've not confirmed on kind of duplicates...

Code:
//STEP01  EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD  DSN=<FILE1>
//IN2 DD  DSN=<FILE2>
//T1   DD  DSN=V0C2663.MATCH.POC,DISP=(MOD,CATLG,DELETE),
//            DCB=(RECFM=FB,LRECL=500,BLKSIZE=5000),
//            UNIT=SYSDA,SPACE=(TRK,(5,1),RLSE)
//OUT DD  DSN=<MATCH FILE>
//OUT1 DD  DSN=<FILE1 - FILE2>
//OUT2 DD  DSN=<FILE2 - FILE1>
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(261,10,CH) WITHALL WITH(271,201)-
 KEEPNODUPS USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(1,260,261:1,10,471:C'11')
/*
//CTL2CNTL DD *
   INREC OVERLAY=(261:111,10,271:1,200,471:C'22')
/*
//CTL3CNTL DD *
   OUTFIL FNAMES=OUT,INCLUDE=(471,2,CH,EQ,C'21'),
   BUILD=(271,191,13,15,44C' ')
   OUTFIL FNAMES=OUT1,INCLUDE=(471,2,CH,EQ,C'11'),
   BUILD=(271,191,59C' ')
   OUTFIL FNAMES=OUT2,INCLUDE=(471,2,CH,EQ,C'22'),
   BUILD=(271,191,59C' ')
/*
Back to top
View user's profile Send private message
bhavya_sha

New User


Joined: 16 Apr 2005
Posts: 33

PostPosted: Tue Jan 05, 2010 5:24 pm    Post subject: Reply to: use of icetool to concatenate records from differe
Reply with quote

how do i get the records in outputfile in the same order as that of file2(matching and nonmatching)

Note:
edited to lower case
please do not use all caps (UPPER CASE)(*)
using all caps is equivalent to shouting
it' s not our fault if You do not get the expected results
(*) not shouting, just showing the difference between lower and upper case :D
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 Jan 06, 2010 12:54 am    Post subject:
Reply with quote

Based on the information you sent me offline, I believe this DFSORT/ICETOOL job will do what you asked for:

Code:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN1 DD DSN=... input file1 (FB/260)
//IN2 DD DSN=... input file1 (FB/200)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/250)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(111,10,CH) KEEPNODUPS WITHALL -
  WITH(1,191) WITH(251,1) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC BUILD=(111:1,10,192:13,15,251:C'BB')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(251:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,INCLUDE=(251,2,SS,EQ,C'VB,VV'),
    BUILD=(1,250)
/*
Back to top
View user's profile Send private message
bhavya_sha

New User


Joined: 16 Apr 2005
Posts: 33

PostPosted: Wed Jan 06, 2010 11:36 am    Post subject:
Reply with quote

Hi Frank,
thank you very much for the reply.

but for a record in FILE2 and not in file1 1-191 chars should be from file2 and 191onwards it has to be blank but with this job it's taking some data from file1 to fill in.

Could u pls clarify what does SS do here.It would be great if you can tell me what each value here in include does
INCLUDE=(251,2,SS,EQ,C'VB,VV'),
Back to top
View user's profile Send private message
bhavya_sha

New User


Joined: 16 Apr 2005
Posts: 33

PostPosted: Wed Jan 06, 2010 11:57 am    Post subject: Reply to: use of icetool to concatenate records from differe
Reply with quote

also why is with(251,9) used?

could you please explain below message too

WITH(1,191) WITH(251,9) USING(CTL3)

Thanks
Bhavya
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
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 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 Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm


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