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

Splice Files using DFSORT


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
vjai6977

New User


Joined: 08 Aug 2008
Posts: 19
Location: Chennai

PostPosted: Fri Aug 15, 2008 8:19 pm
Reply with quote

Hi , I have two Input files both are results of some scan stuffs. (Input File1: LRECL = 133 / Input File2: LRECL = 120)

The common keys on which ICETOOL should merge the two files are not located in same column positions on both files, (i.e., On the job CPY1 & CPY2 the first 32 bytes /4 = 8 each bytes are the 4 different keys to be matched data (34:23,45,133:X) from FILE1 followed by 80:41,35,120:X) as one record should get listed in OUTREC.

Also, what is the exact usage of, WITHEACH WITH parameter in this context , I know it used for eliminating the duplicates, but not a through understanding on it. The temp file T1 and out file OUT with DD names are of size 133 bytes.

The job I used is...

Code:

//STEP01   EXEC PGM=ICETOOL                                           
//TOOLMSG  DD SYSOUT=*                                               
//DFSMSG   DD SYSOUT=*                                               
//IN1      DD DISP=SHR,DSN=UDUA.XTEST.FILE1                           
//IN2      DD DISP=SHR,DSN=UDUA.XTEST.FILE2                           
//T1       DD DSN=UDU.TEST.T1,DISP=MOD                               
//*1       DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)   
//OUT      DD DSN=UDUA.TEST.MERGEOUT,DISP=OLD                         
//TOOLIN   DD *                                                       
COPY FROM(IN1) TO(T1) USING(CPY1)                                     
COPY FROM(IN2) TO(T1) USING(CPY2)                                     
SPLICE FROM(T1) TO(OUT) ON(1,8,CH) -                               
           WITHEACH WITH(79,43) USING(CPY3)                           
/*                                                                   
//CPY1CNTL DD *                                                       
    OUTREC FIELDS=(1:69,8,9:89,8,17:98,8,25:107,8,34:23,45,133:X)     
/*                                                                   
//CPY2CNTL DD *                                                       
    OUTREC FIELDS=(1:1,8,9:9,8,17:17,8,25:25,8,80:41,35,120:X)       
/*                                           
//CPY3CNTL DD *                               
   OUTFIL FNAMES=OUT,OUTREC=(1,133)           
/*                                           
//
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Fri Aug 15, 2008 9:20 pm
Reply with quote

vjai6977,

Did you have a look at the DFSORT manuals given at the top of this forum?

Here's a link to the SPLICE operator in DFSORT where you'll find all that you need.

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

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

DFSORT Developer


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

PostPosted: Fri Aug 15, 2008 9:56 pm
Reply with quote

Quote:
Also, what is the exact usage of, WITHEACH WITH parameter in this context , I know it used for eliminating the duplicates, but not a through understanding on it. The temp file T1 and out file OUT with DD names are of size 133 bytes


Why did YOU choose to use WITHEACH if you don't know what it does? The choice to use WITHEACH, WITHALL, WITHANY (new) or none of them depends on exactly what you're trying to do which you haven't explained. However, given that you only have one WITH field, WITHEACH is probably the wrong choice.

If you need more help on this, show an example of the records in each input file (relevant fields only) and what you expect for output. Give the starting position, length and format of each relevant field. Explain the "rules" for getting from input to output.
Back to top
View user's profile Send private message
vjai6977

New User


Joined: 08 Aug 2008
Posts: 19
Location: Chennai

PostPosted: Sat Aug 16, 2008 1:54 am
Reply with quote

Hi, Thanks for your suggestions.

Yaeger, Good Morning, Here i have the detailed data as follows,


Input File 1 (LRECL = 133)
--+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0----+----1----
***************************** Top of Data **************************************************
UDUAUA.XTEST11.TST.TSTLSDTL(0) " TST3060 JOB3060 TST3060 TST3060B LOSSES
UDUAUA.XTEST11.TST.MTLDTL " TST3060 JOB3060 TST3060 TST3060E SORTIN
UDUAUA.XTEST11.TST.TRAN " TST3060 JOB3060 TST3060 TST3060E SORTIN
UDUAUA.XTEST11.TST3060B.LUALOSS " TST3060 JOB3060 TST3060 TST3060E SORTIN
UDUAUA.XTEST11.INS.LOSSTBL " TST3060 JOB3060 TST3060 TST3060F CRTAFI
UDUAUA.XTEST11.TST.MTLFC(+1) " TST3070 JOB3070 TST3070 TST3070K CDATEFILE


Keys from Input File 1:CPY1CNTL
-------------------------------
DSN NAME : (23,45)
Job Name : (69,8)
Proc Name : (89,8)
Proc Step : (98,8)
DD NAME : (107,8)


***



Input File 2 (LRECL = 120)
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----
***************************** Top of Data ******************************************
TST3060 TST3060 TST3060 TST3060BLOSSES PRD.TST.TSTLSDTL(0)
TST3060 TST3060 TST3060 TST3060BLUALOSS PRD.TST3060B.LUALOSS
TST3060 TST3060 TST3060 TST3060BUUICLOSSPRD.TST3060B.UUICLOSS
TST3060 TST3060 TST3060 TST3060CALFLOSSOPRD.TST.ALFLOSS.EXPAND(+1)
TST3060 TST3060 TST3060 TST3060COUTOFBALPRD.TST.COUTB
TST3060 TST3060 TST3060 TST3060SORTIN PRD.TST.MTLDTL
TST3060 TST3060 TST3060 TST3060SORTIN PRD.TST.TRAN
TST3060 TST3060 TST3060 TST3060SORTIN PRD.TST3060B.LUALOSS
TST3060 TST3060 TST3060 TST3060CLOSSIN PRD.TST3060B.UUICLOSS
TST3070 TST3070 TST3070 TST3070CDATEFILEPRD.TST.MTLFC(+1)
TST3060 TST3060 TST3060 TST3060CLOSSTEMPPRDAUP.TSTST.TST3060C.LOSSTEMP

Keys from Input File 1:CPY2CNTL
-------------------------------
DSN NAME : (41,43)
Job Name : (1,8)
Proc Name : (17,8)
Proc Step : (25,8)
DD NAME : (33,8)

***


OutPut File (LRECL = 133)
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0----+----1----+----2----+----3---
***************************** Top of Data *******************************************************************************************
TST3060 TST3060 TST3060BLOSSES UDUAUA.XTEST11.TST.TSTLSDTL(0) PRD.TST.TSTLSDTL(0)
TST3060 TST3060 TST3060SORTIN UDUAUA.XTEST11.TST.MTLDTL PRD.TST.MTLDTL
TST3060 TST3060 TST3060SORTIN UDUAUA.XTEST11.TST.TRAN PRD.TST.TRAN
TST3060 TST3060 TST3060SORTIN UDUAUA.XTEST11.TST3060B.LUALOSS PRD.TST3060B.LUALOSS
TST3070 TST3070 TST3070CDATEFILEUDUAUA.XTEST11.TST.MTLFC(+1) PRD.TST.MTLFC(+1)


Keys from Input File 1:CPY2CNTL
-------------------------------
Job Name : (1,8)
Proc Name : (9,8)
Proc Step : (17,8)
DD NAME : (25,8)
TEST DSN : (33,45)
PROD DSN : (79,40)





My requirement is to write outfile with overlaying matching keys (JOB NAME, PROC NAME, DD NAME, PROC STEP) of two input files,
and their corresponding DSN Name on same row. This i need for a task of comparison.

Considering that in JOB TST3060 and ddname SORTIN IS a concertinaed DD. I modified the job as below…


//STEP01 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DISP=SHR,DSN=UDUA.XTEST.FILE1
//IN2 DD DISP=SHR,DSN=UDUA.XTEST.FILE2
//T1 DD DSN=UDU.TEST.T1,DISP=MOD
//*1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=UDUA.TEST.MERGEOUT,DISP=OLD
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CPY1)
COPY FROM(IN2) TO(T1) USING(CPY2)
SPLICE FROM(T1) TO(OUT) ON(1,8,CH) -
WITHEACH WITH(79,40) USING(CPY3)
/*
//CPY1CNTL DD *
OUTREC FIELDS=(1:69,8,9:89,8,17:98,8,25:107,8,33:23,45,133:X)
/*
//CPY2CNTL DD *
OUTREC FIELDS=(1:1,8,9:17,8,17:25,8,25:33,8,79:41,40,120:X)
/*
//CPY3CNTL DD *
OUTFIL FNAMES=OUT,OUTREC=(1,133)
/*
//

For this modified job, i am getting output good for the matching, but, i still find, only first concatenated dataset displayed, with out FILE2 equivalent,
and for all FILE2 equivalent which still doesn’t have an entry on FILE1 gets into OUTFILE. Please suggest on this.


Regards,
-----------
Jai-Chennai
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Sat Aug 16, 2008 4:05 am
Reply with quote

I see several problems.

You don't need WITHEACH since you only have one WITH field. (WITHEACH is for splicing multiple WITH fields one at a time.)

You say you want to match by JOB NAME, PROC NAME, DD NAME, PROC STEP, but you're using ON(1,8,CH) in the SPLICE statement which only matches by JOBNAME. I believe you should be using ON(1,32,CH) to match on the four fields.

Your data for the four fields you want to match on doesn't match for some of the output records you show for file1 and file2. File1 has TST3070K CDATEFILE and file2 has TST3070CDATEFILE so those don't match. File1 has several TST3060E SORTIN values, but File2 has several TST3060SORTIN values which are missing the E. So those records don't match up.

And if you did have the E for the three file2 records, they would all have the same JOB NAME, PROC NAME, DD NAME and PROC STEP values so you couldn't match them one-for-one as you seem to want to do,. It wouldn't be a match for the first one from file1 and the first one from file2, a match for the second one from file1 and the second one from file2, and a match for the third one from file1, and the third one from file2, so each of those pairs could be spliced. It would actually be a match for all 6 records from file1 and file2 so the SPLICE won't do what you want.

If the file1 and file2 records with SORTIN actually have differences in the four fields, then you just have to show me the correct data. If the file1 and file2 records with SORTIN don't have any differences in the four fields, then you need to explain how you want to handle those multiple "matches" (one-by-one from file1 and file2 or some other way).

If you verify that you really want to macth on the four fields, not the one field, and you show me valid matching input records and expected output, and explain what you want to do about the SORTIN records, then I can show you how to do what you want to do. But at the moment, I can't unravel it enough to do that.
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Sat Aug 16, 2008 10:15 am
Reply with quote

vjai6977,
Your file data and code are easier to read if you use BBCode.
Back to top
View user's profile Send private message
vjai6977

New User


Joined: 08 Aug 2008
Posts: 19
Location: Chennai

PostPosted: Mon Aug 18, 2008 10:57 pm
Reply with quote

----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
***************************** Top of Data **************************************
SSNNOSDLNONAME1
CMS3060 CMS3060 CMS3060BLOSSES USU#UA.R2.ISO1.CMS.CMSLSDTL(0) "


Hi , Thanks for looking into the issue. Ooops while setting up of the test data, I made the typo errors that you had
mentioned on both files 1 & file 2. Here are the corrected two input files.


Input File 1 (LRECL = 133)
--+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0----+----1----
***************************** Top of Data **************************************************
UDUAUA.XTEST11.TST.TSTLSDTL(0) " TST3060 JOB3060 TST3060 TST3060B LOSSES
UDUAUA.XTEST11.TST.MTLDTL " TST3060 JOB3060 TST3060 TST3060E SORTIN
UDUAUA.XTEST11.TST.TRAN " TST3060 JOB3060 TST3060 TST3060E SORTIN
UDUAUA.XTEST11.TST3060B.LUALOSS " TST3060 JOB3060 TST3060 TST3060E SORTIN
UDUAUA.XTEST11.INS.LOSSTBL " TST3060 JOB3060 TST3060 TST3060F CRTAFI
UDUAUA.XTEST11.TST.MTLFC(+1) " TST3070 JOB3070 TST3070 TST3070K CDATEFILE
Keys from Input File 1:CPY1CNTL
-------------------------------
DSN NAME : (23,45)
Job Name : (69,8)
Proc Name : (89,8)
Proc Step : (98,8)
DD NAME : (107,8)



Input File 2 (LRECL = 120)
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----
***************************** Top of Data ******************************************
TST3060 TST3060 TST3060 TST3060BLOSSES PRD.TST.TSTLSDTL(0)
TST3060 TST3060 TST3060 TST3060BLUALOSS PRD.TST3060B.LUALOSS
TST3060 TST3060 TST3060 TST3060BUUICLOSSPRD.TST3060B.UUICLOSS
TST3060 TST3060 TST3060 TST3060CALFLOSSOPRD.TST.ALFLOSS.EXPAND(+1)
TST3060 TST3060 TST3060 TST3060COUTOFBALPRD.TST.COUTB
TST3060 TST3060 TST3060 TST3060ESORTIN PRD.TST.MTLDTL
TST3060 TST3060 TST3060 TST3060ESORTIN PRD.TST.TRAN
TST3060 TST3060 TST3060 TST3060ESORTIN PRD.TST3060B.LUALOSS
TST3060 TST3060 TST3060 TST3060CLOSSIN PRD.TST3060B.UUICLOSS
TST3070 TST3070 TST3070 TST3070KDATEFILEPRD.TST.MTLFC(+1)
TST3060 TST3060 TST3060 TST3060CLOSSTEMPPRDAUP.TSTST.TST3060C.LOSSTEMP

Keys from Input File 2:CPY2CNTL
-------------------------------
DSN NAME : (41,43)
Job Name : (1,8)
Proc Name : (17,8)
Proc Step : (25,8)
DD NAME : (33,8)



OutPut File (LRECL = 133)
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0----+----1----+----2----+----3---
***************************** Top of Data *******************************************************************************************
TST3060 TST3060 TST3060BLOSSES UDUAUA.XTEST11.TST.TSTLSDTL(0) PRD.TST.TSTLSDTL(0)
TST3060 TST3060 TST3060ESORTIN UDUAUA.XTEST11.TST.MTLDTL PRD.TST.MTLDTL
TST3060 TST3060 TST3060ESORTIN UDUAUA.XTEST11.TST.TRAN PRD.TST.TRAN
TST3060 TST3060 TST3060ESORTIN UDUAUA.XTEST11.TST3060B.LUALOSS PRD.TST3060B.LUALOSS
TST3070 TST3070 TST3070KDATEFILEUDUAUA.XTEST11.TST.MTLFC(+1) PRD.TST.MTLFC(+1)

Out put file layout
---------------------
Job Name : (1,8)
Proc Name : (9,8)
Proc Step : (17,8)
DD NAME : (25,8)
TEST DSN : (33,45)
PROD DSN : (79,40)


1. The file1 & file2 records in SORTIN, do not have any difference in four key field.
2. I want to handle the multiple matches One-By-One from file1 & file2. (i.e., in case SORTIN the rows 2,3,4 from file1 should exactly match with rows 6,7,8 of file2.
3. As a whole we need to get the output as one-by-one for the matching keys
(JOB NAME, PROC NAME, PROC STEP, DD NAME)
4. Yes, the matching keys should be ON(1,32,CH) as you had mentioned.

For the above three points what changes should be done in ICETOOL SPLICE command and respective control cards to get the required output.


Have a good day.


Regards,
Jai-Chennai
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Tue Aug 19, 2008 1:43 am
Reply with quote

TST3070K CDATEFILE from file1 and TST3070KDATEFILE from file2 still don't match, but I get the idea. Here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//STEP01   EXEC PGM=ICETOOL
//TOOLMSG  DD SYSOUT=*
//DFSMSG   DD SYSOUT=*
//IN1 DD DISP=SHR,DSN=UDUA.XTEST.FILE1
//IN2 DD DISP=SHR,DSN=UDUA.XTEST.FILE2
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=UDUA.TEST.MERGEOUT,DISP=OLD
//TOOLIN   DD *
COPY FROM(IN1) TO(T1) USING(CPY1)
COPY FROM(IN2) TO(T1) USING(CPY2)
SPLICE FROM(T1) TO(OUT) ON(1,32,CH) ON(134,8,ZD) -
  WITH(79,40) USING(CPY3)
/*
//CPY1CNTL DD *
  INREC IFTHEN=(WHEN=INIT,
    BUILD=(1:69,8,9:89,8,17:98,8,25:107,8,33:23,45)),
   IFTHEN=(WHEN=INIT,OVERLAY=(134:SEQNUM,8,ZD,RESTART=(1,32)))
/*
//CPY2CNTL DD *
  INREC IFTHEN=(WHEN=INIT,
    BUILD=(1:1,8,9:17,8,17:25,8,25:33,8,79:41,40)),
   IFTHEN=(WHEN=INIT,OVERLAY=(134:SEQNUM,8,ZD,RESTART=(1,32)))
/*
//CPY3CNTL DD *
   OUTFIL FNAMES=OUT,OUTREC=(1,133)
/*
Back to top
View user's profile Send private message
vjai6977

New User


Joined: 08 Aug 2008
Posts: 19
Location: Chennai

PostPosted: Wed Aug 20, 2008 2:06 am
Reply with quote

Frank,

thanks to you for your guidance on this task, this time it really worked for me icon_smile.gif The matching was perfect for same keys (concatenated DD'S).

The RESTART parameter here in CPY1 & CPY2 takes care of breaking the sequence numbering when same keys are encountered, Just wanted to know how this is happening. How the same keys are identified and numbered in a sequence

You, have a good day.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Wed Aug 20, 2008 2:19 am
Reply with quote

I'm glad I could help.

SEQNUM,8,ZD,RESTART=(1,32):

Every time a new value for your 32 byte key is encountered, the sequence number starts over at 1. For the same value, the sequence number increments by 1. Records with unique 32 byte values will each have 1 for the sequence number in each file. But records with duplicate 32 bytes values will have different sequence numbers which will match in each file. For example, for the first SORTIN record in file1 and file2, the sequence number will be 1, for the second SORTIN record in file1 and file2, the sequence number will be 2, etc. Does that help?
Back to top
View user's profile Send private message
vjai6977

New User


Joined: 08 Aug 2008
Posts: 19
Location: Chennai

PostPosted: Thu Aug 21, 2008 7:47 am
Reply with quote

Yaeger, thanks for the explanation, i got it , i will try to work around with this. good day to you
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 -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Calling DFSORT from Cobol, using OUTF... DFSORT/ICETOOL 5
Search our Forums:

Back to Top