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
 

 

Combine two files of different Lrecls
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
skhorizon

New User


Joined: 14 May 2008
Posts: 10
Location: bangalore

PostPosted: Wed May 14, 2008 3:08 pm    Post subject: Combine two files of different Lrecls
Reply with quote

I have 2 different files with different length, how can I combine the contents of the 2 files based on one key into one output file.

For example: File 1 and File2 are going to combine on column key1. If record from File1 is not found in File2 on key1 then it will be assigned blanks for other columns.

files are in below format

File1

Code:

key1 col1 col2 col3
----------------------------
aaa  1111 1111 1111
bbb  2222 2222 2222
ccc  3333 3333 3333
ddd  4444 4444 4444


File2

Code:

key2 col4 col5
----------------------
aaa  1000 1000
bbb  2000 2000


Output

Code:

key1 col1 col2 col3 col4
-------------------------------
aaa  1111 1111 1111 1000
bbb  2222 2222 2222 2000
ccc  3333 3333 3333
ddd  4444 4444 4444


I wanted to do using DFSORT.
Back to top
View user's profile Send private message

CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Wed May 14, 2008 6:06 pm    Post subject:
Reply with quote

Look at the SPLICE Operator, it also has some good examples.....
Back to top
View user's profile Send private message
skhorizon

New User


Joined: 14 May 2008
Posts: 10
Location: bangalore

PostPosted: Wed May 14, 2008 6:38 pm    Post subject:
Reply with quote

hi ,

SPLICE operator is used by ICETOOL .
I think we cant use while running EXEC PGM=SORT .

So is there way to do with EXEC PGM=SORT .
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Wed May 14, 2008 7:21 pm    Post subject:
Reply with quote

It shouldn't matter, if you have DFSort, you have ICETOOL.
And DFSORT is called to sort the input data sets so the order shouldn't matter either.
Check out the two "Join fields from two files" in Smart DFSORT Tricks
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 May 14, 2008 9:17 pm    Post subject:
Reply with quote

skhorizon,

Here's a DFSORT/ICETOOL job that will do what you asked for. If you have DFSORT, then you have DFSORT's ICETOOL (it has been available with DFSORT since 1991).

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB)
//IN2 DD DSN=...  input file2 (FB)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/24)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,3,CH) WITH(21,4) KEEPNODUPS
/*
//CTL1CNTL DD *
  INREC BUILD=(1,19,24:X)
/*
//CTL2CNTL DD *
  INREC BUILD=(1,3,21:6,4)
/*
Back to top
View user's profile Send private message
skhorizon

New User


Joined: 14 May 2008
Posts: 10
Location: bangalore

PostPosted: Thu May 15, 2008 11:53 am    Post subject:
Reply with quote

Hi frank , thanks for ur response

But the result is not as expected .

Code:

 AAA111111111111     0010AAA                 0010
 BBB222222222222     0020BBB                 0020
 CCC333333333333          BBB                 0020
 DDD444444444444          BBB                 0020
 EEE555555555555           BBB                 0020


let me tell u both input file length was 80 .

And the size of fileds is :
key1 (3 chars) , col1,col2 col3 (4 chars )
key2(3 chars) , col4,col5 (4 chars)

File 1 can have duplicate key values , but File2 will have one key values .So takin key value from File1 corrsponding to that value from File2 we have to find out value of " col4 " and if not found in File2 then assign blank in output value.
In output file it will have all records as file1 with new column " col4 " derived on basis key
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: Thu May 15, 2008 8:38 pm    Post subject:
Reply with quote

Quote:
But the result is not as expected .


Garbage in - garbage out. icon_rolleyes.gif

I suppose you expected me to read your mind to figure out that even though you showed blanks between the fields they weren't there, and that even though you didn't show duplicates they were there.

Do both files have RECFM=FB and LRECL=80?

Can input file2 have a key that isn't in input file1?

Can input file1 have a key that isn't in input file2?

Show me a good example of the input records in each file and what you expect for output with all of the possible variations. Since you say input file1 can have duplicate key values, show that in your example.
Back to top
View user's profile Send private message
skhorizon

New User


Joined: 14 May 2008
Posts: 10
Location: bangalore

PostPosted: Thu May 15, 2008 10:34 pm    Post subject:
Reply with quote

Sorry that i have not shown in my previous post about duplicate rows .

Yes ,both files have RECFM=FB and LRECL=80 .

Yes ,input file2 can have a key that isn't in input file1 .

Yes ,input file1 can have a key that isn't in input file2.

Basically the requirement is that file1 have 1 key field and 3 other column . file1 can have duplicate key values .
file2 have 1 key field and 2 other columns. All key values in file2 are unique.

Then ,output file will have all records from file1 with one more column from file2 .
we have to match the key from file1 to file2 , and get the column value from file2 and append to output file .if the key is not found in file2 then assign blank value in the output file for that new column .


file1
------------

key1,col1,col2,col3
3CHAR,4CHAR,4CHAR,4CHAR

Code:

K11AAA1BBB1CCC1
K11AAA2BBB2CCC2
K11AAA3BBB3CCC3
K22AAA1BBB1CCC1
K22AAA4BBB4CCC4
K33AAA5BBB5CCC5
K44AAA6BBB6CCC6


file2
--------

col4,key1,col5
(4CHAR,3CHAR,4CHAR)

Code:

ZZZ1K11YYY1
ZZZ2K22YYY2




file3
--------

col5,key1,col1,col2,col3
3CHAR,4CHAR,4CHAR,4CHAR

Code:

YYY1K11AAA1BBB1CCC1
YYY1K11AAA2BBB2CCC2
YYY1K11AAA3BBB3CCC3
YYY2K22AAA1BBB1CCC1
YYY2K22AAA4BBB4CCC4
    K33AAA5BBB5CCC5
    K44AAA6BBB6CCC6


No worry about the what the value in columns in file1 ,if key match in file2 then get the column value from file2 and if not found assign balnk.
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: Thu May 15, 2008 11:50 pm    Post subject:
Reply with quote

Here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/80)
//IN2 DD DSN=...  input file2 (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=&&O1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(5,3,CH) KEEPNODUPS KEEPBASE -
  WITHALL WITH(5,15) WITH(81,1) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC BUILD=(1:8,4,5:5,3,81:C'BB')
/*
//CTL2CNTL DD *
  INREC BUILD=(5:1,15,81:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,OMIT=(81,2,CH,EQ,C'BB'),
    BUILD=(1,80)
/*
Back to top
View user's profile Send private message
skhorizon

New User


Joined: 14 May 2008
Posts: 10
Location: bangalore

PostPosted: Fri May 16, 2008 7:48 pm    Post subject:
Reply with quote

Thanks Frank , Its working .

I m getting the expected output .

I have not much idea about DFSORT/ICETOOL ,so can u please explain what does the following line means .i tried to get answer from DFSORT programming guide but was not able to understand clearly :

1 .SPLICE FROM(T1) TO(OUT) ON(5,3,CH) KEEPNODUPS KEEPBASE -
WITHALL WITH(5,15) WITH(81,1) USING(CTL3) , what does WITHALL WITH(5,15) WITH(81,1) means ?

2. INREC BUILD=(1:8,4,5:5,3,81:C'BB') what does 'BB' means here ?

3. INREC BUILD=(5:1,15,81:C'VV') what does 'VV' means here ?

4. OUTFIL FNAMES=OUT,OMIT=(81,2,CH,EQ,C'BB'),
BUILD=(1,80) , what does OMIT=(81,2,CH,EQ,C'BB') means ?

5. why we are using 81 here , whats its signinficance ?

6. how it is working : step by step concept ?
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: Fri May 16, 2008 9:53 pm    Post subject:
Reply with quote

Everything about SPLICE is explained here:

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

Also, see the "Create files with matching and non-matching records" Smart DFSORT Trick at:

http://www.ibm.com/servers/storage/support/software/sort/mvs/tricks/

It might also help if you display the records in T1 so you can see what they look like before they are spliced, and if you comment out the control statements in CTL3CNTL so you can see what the spliced records look like before OUTFIL removes the BB records and the ids.

I can give you some brief answers to your questions, but it may not help if you haven't spent the time to understand SPLICE.

Quote:
1 .SPLICE FROM(T1) TO(OUT) ON(5,3,CH) KEEPNODUPS KEEPBASE -
WITHALL WITH(5,15) WITH(81,1) USING(CTL3) , what does WITHALL WITH(5,15) WITH(81,1) means ?


WITHALL splices the base record (from file2) with each matching overlay record (from file1). WITH(5,15) and WITH(81,1) gives the fields from the overlay record to be spliced into the base record.

Quote:
2. INREC BUILD=(1:8,4,5:5,3,81:C'BB') what does 'BB' means here ?


BB is an id code we're using to identify the records from file2. The file2 records are the base records.

Quote:
3. INREC BUILD=(5:1,15,81:C'VV') what does 'VV' means here ?


VV is an id code we're using to identify the records from file1. The file1 records are the overlay records.

Quote:
4. OUTFIL FNAMES=OUT,OMIT=(81,2,CH,EQ,C'BB'),
BUILD=(1,80) , what does OMIT=(81,2,CH,EQ,C'BB') means ?


We're removing the records with an id of BB (records with keys that only appear in file2). We're keeping the records with an id of VV (records with keys that only appear in file1), and with an id of VB (records that appear in file2 and file1).

Quote:
5. why we are using 81 here , whats its signinficance ?


We're adding the ids after the end of the records. The LRECL is 80, so the position after the end of the records is 81.

Quote:
6. how it is working : step by step concept ?


See the explanations above and the referenced Smart DFSORT Trick.
Back to top
View user's profile Send private message
skhorizon

New User


Joined: 14 May 2008
Posts: 10
Location: bangalore

PostPosted: Mon May 19, 2008 8:44 pm    Post subject:
Reply with quote

Hi Frank ,

Thanks for so much information .

I wanted to know what will be the change in the code if the file length will be equal to record length .Then both input file will be of different record length and output will be different lrecl .

so according to above data :

File1 : RECFM=FB and LRECL=15

File2 : RECFM=FB and LRECL=11

File3 : RECFM=FB and LRECL=19

what will be the change in above code ?

Thanks in advance ..
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 May 19, 2008 9:11 pm    Post subject:
Reply with quote

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file (FB/15)
//IN2 DD DSN=...  input file (FB/11)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/19)
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(5,3,CH) KEEPNODUPS KEEPBASE -
  WITHALL WITH(5,15) WITH(20,1) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC BUILD=(1:8,4,5:5,3,20:C'BB')
/*
//CTL2CNTL DD *
  INREC BUILD=(5:1,15,20:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,OMIT=(20,2,CH,EQ,C'BB'),
    BUILD=(1,19)
/*
Back to top
View user's profile Send private message
skhorizon

New User


Joined: 14 May 2008
Posts: 10
Location: bangalore

PostPosted: Fri May 23, 2008 12:24 pm    Post subject:
Reply with quote

Hi Frank ,

The code is working in run member .
now the problem is that we have to use same code in many jobs. So i tried to write in PROC .calling the same proc in all jobs.

If run through PROC , then it gives JCL error as RC for the job . Its not recognizing from the statement
//TOOLIN DD *
.
.
/* (till last )

And same thing if written in job its giving RC=0 and running successfully.

Please help in finding the solution for this problem .
Do i need to add any system libary for running ICETOOL pgm . ?

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

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Fri May 23, 2008 3:20 pm    Post subject:
Reply with quote

Put the TOOLIN, CTL1CNTL, CTL2CNTL and CTL3CNTL data in PDS members and change the DDs to point to those members.
Back to top
View user's profile Send private message
skhorizon

New User


Joined: 14 May 2008
Posts: 10
Location: bangalore

PostPosted: Fri May 23, 2008 3:35 pm    Post subject:
Reply with quote

Actually i wanted to run the whole STEP from a proc . But when i am running through proc it is giving JCL error saying INVALID JCL STATEMENT for all below mentiioned lines .

//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(5,3,CH) KEEPNODUPS KEEPBASE -
WITHALL WITH(5,15) WITH(20,1) USING(CTL3)
/*
//CTL1CNTL DD *
INREC BUILD=(1:8,4,5:5,3,20:C'BB')
/*
//CTL2CNTL DD *
INREC BUILD=(5:1,15,20:C'VV')
/*
//CTL3CNTL DD *
OUTFIL FNAMES=OUT,OMIT=(20,2,CH,EQ,C'BB'),
BUILD=(1,19)

And same thing running through job , is running successfully .
So i wanted in whole step running through proc .
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Fri May 23, 2008 4:01 pm    Post subject:
Reply with quote

skhorizon wrote:
Actually i wanted to run the whole STEP from a proc . But when i am running through proc it is giving JCL error saying INVALID JCL STATEMENT for all below mentiioned lines .
Quote:
And same thing running through job , is running successfully .
So i wanted in whole step running through proc .
Try looking up the error....
Try reading the fine manual....
In-stream Data for Cataloged or In-stream Procedures
A cataloged or in-stream procedure cannot contain a DD * statement. When you call a procedure, you can add input stream data to a procedure step by placing in the calling step one or more DD * or DD DATA statements, each followed by data.

Put them in a PDS....
Back to top
View user's profile Send private message
skhorizon

New User


Joined: 14 May 2008
Posts: 10
Location: bangalore

PostPosted: Fri May 23, 2008 5:49 pm    Post subject:
Reply with quote

Instead of giving it as //TOOLIN DD * ,i have written as

//TOOLIN DD DSN=FIRR036.DEVTST.UTILOPC(TOOL1),DISP=SHR
//CTL1CNTL DD DSN=FIRR036.DEVTST.UTILOPC(TOOL2),DISP=SHR
//CTL2CNTL DD DSN=FIRR036.DEVTST.UTILOPC(TOOL3),DISP=SHR
//CTL3CNTL DD DSN=FIRR036.DEVTST.UTILOPC(TOOL4),DISP=SHR
//*

but still the JOB is failing with RC =16 :

ICE632I 0 SOURCE FOR ICETOOL STATEMENTS: TOOLIN


ICE630I 0 MODE IN EFFECT: STOP

COPY FROM(IN2) TO(T1) USING(CTL1)
ICE606I 0 DFSORT CALL 0001 FOR COPY FROM IN2 TO T1 USING CTL1CNTL TERMINATED
ICE602I 0 OPERATION RETURN CODE: 16
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri May 23, 2008 5:55 pm    Post subject:
Reply with quote

skhorizon wrote:
Instead of giving it as //TOOLIN DD * ,i have written as

//TOOLIN DD DSN=FIRR036.DEVTST.UTILOPC(TOOL1),DISP=SHR
//CTL1CNTL DD DSN=FIRR036.DEVTST.UTILOPC(TOOL2),DISP=SHR
//CTL2CNTL DD DSN=FIRR036.DEVTST.UTILOPC(TOOL3),DISP=SHR
//CTL3CNTL DD DSN=FIRR036.DEVTST.UTILOPC(TOOL4),DISP=SHR


Do those members exist in that PDS and what do they contain?
Back to top
View user's profile Send private message
skhorizon

New User


Joined: 14 May 2008
Posts: 10
Location: bangalore

PostPosted: Fri May 23, 2008 8:16 pm    Post subject: Reply to: Combine two files of different Lrecls
Reply with quote

All those members(tool1,tool2,tool3,tool4) exist ...

these all contains control statements defined under it as in above jcls
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 High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm
No new posts Adding records from two files into on... shiitiizz SYNCSORT 4 Mon Sep 19, 2016 8:41 pm
No new posts Icetool or Joinkeys example to compar... ishant chauhan DFSORT/ICETOOL 21 Sat Aug 20, 2016 2:40 am


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