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

How to capture data from 2 files "column wise"


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Fri Feb 15, 2008 12:46 pm
Reply with quote

Hi All,

Please help me with the below issue:

I have 2 files with FB:

Data in file1 :
Code:
Name    SSN     Total           
Jim      0001    876           
Kim      0003    987           
Joe      0004    787
Hen      0002    098
Loe      0005    876       

Date in file2:
Code:
Name    SSN    Total   M1   M2   Mtl    D1   D2
Jim      0001   876    34   76   110    43   45   
Joe      0004   787    65   35   100    32   21
Kim      0003   987    55   45   100    41   43

Output file needs to be like below:
Code:
Name    SSN    Total    M1  Mtl   D1   
Jim      0001    876    34   76   43 
Hen      0002    098
Kim      0003    987    55   45   41   
Joe      0004    787    65   35   32 
Loe      0005    876 


The above 2 input files are not in sorted order.

Please let me know the solution for the above issue at the earliest.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Fri Feb 15, 2008 2:22 pm
Reply with quote

The issue has been debated many many times
a forum search on "two files match" will certaily provide You with
lots of useful pointers

if You are not able to find anything useful by Yourself
here is a pointer to a thread that resembles what You are looking for

http://www.ibmmainframes.com/viewtopic.php?t=27023&highlight=file+match
Back to top
View user's profile Send private message
ParagChouguley

Active User


Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)

PostPosted: Fri Feb 15, 2008 2:57 pm
Reply with quote

Kiran,
Your requirement is not so clear. Still if I could have understood it correctly, here is a JCL. Also you haven't mentioned properly which fields you want in output, I'm displaying all of them. You can change last OUTREC statement as per your requirement.

Code:

//S1      EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//*
//IN1     DD *
NAME SSN TOTAL
JIM 0001 876
KIM 0003 987
JOE 0004 787
HEN 0002 098
LOE 0005 876
/*
//IN2     DD *
NAME SSN TOTAL M1 M2 MTL D1 D2
JIM 0001 876   34 76 110 43 45
JOE 0004 787   65 35 100 32 21
KIM 0003 987   55 45 100 41 43
/*
//TEMP1    DD DSN=&&TEMP1,DISP=(MOD,PASS,DELETE),
//  UNIT=SYSDA,SPACE=(CYL,(5,5))
//TEMP2    DD DSN=&&TEMP2,DISP=(,PASS,DELETE)
//   UNIT=SYSDA,SPACE=(CYL,(5,5))
//OUT1    DD DSN=...  output file
//TOOLIN  DD *
COPY FROM(IN1) TO(TEMP1) USING(SRT1)
COPY FROM(IN2) TO(TEMP1) USING(SRT2)
SPLICE FROM(TEMP1) TO(TEMP2) ON(1,14,CH) WITH(15,17) -
    KEEPNODUPS USING(SRT3)
SORT FROM(TEMP2) TO(OUT1) USING(SRT4)
/*
//SRT1CNTL DD *
  OUTREC BUILD=(1,14,16X)
/*
//SRT2CNTL DD *
  OUTREC BUILD=(1,30)
/*
//SRT3CNTL DD *
  OUTFIL FNAMES=TEMP2,
    IFTHEN=(WHEN=(1,4,CH,EQ,C'NAME'),
      BUILD=(C'A',1,30)),
    IFTHEN=(WHEN=NONE,
      BUILD=(C'B',1,30))
/*
//SRT4CNTL DD *
  SORT FIELDS=(1,1,CH,A,6,4,ZD,A)
  OUTREC BUILD=(2,30)
/*


Output:
Code:

NAME SSN TOTAL M1 M2 MTL D1 D2 
JIM 0001 876   34 76 110 43 45 
HEN 0002 098                   
KIM 0003 987   55 45 100 41 43 
JOE 0004 787   65 35 100 32 21 
LOE 0005 876                   



--Parag
Back to top
View user's profile Send private message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Fri Feb 15, 2008 3:22 pm
Reply with quote

Hi Parag,

Sorry If I haven't explained you my requirement properly.
Your assumption was right.

The output should not have all the fields"NAME SSN TOTAL M1 M2 MTL D1 D2 "

But the Output file should have only "Name" "SSN" " Total" "M1" "Mtl" and "D1"

Please let me know if we can get the output of the above required fields only.
Back to top
View user's profile Send private message
ParagChouguley

Active User


Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)

PostPosted: Fri Feb 15, 2008 4:25 pm
Reply with quote

Then change last sort card SRT4CNTL
to
Code:

//SRT4CNTL DD *                   
    SORT FIELDS=(1,1,CH,A,6,4,ZD,A)
    OUTREC FIELDS=(2,18,23,6)     
/*                                 


which will be able to give you following output
Code:

NAME SSN TOTAL M1 MTL D1
JIM 0001 876   34 110 43
HEN 0002 098           
KIM 0003 987   55 100 41
JOE 0004 787   65 100 32
LOE 0005 876           


--Parag
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Fri Feb 15, 2008 4:29 pm
Reply with quote

Quote:
Please let me know if we can get the output of the above required fields only.


Yes it can be done,
but it would be very rewarding for the responders if You' d carry on a little bit of research on Your own

AS I wrote in my previous post there are many samples in this forum on how to build an output record from non contiguous fields
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Fri Feb 15, 2008 4:49 pm
Reply with quote

enrico... this reminds me about
give a man a fish and he will eat for a day....teach a man to fish and he will eat for a lifetime.
Back to top
View user's profile Send private message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Fri Feb 15, 2008 6:03 pm
Reply with quote

Thanks for response...

Can you please brief me how the ICETOOL and DFSORT control statements work.
Back to top
View user's profile Send private message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Fri Feb 15, 2008 9:05 pm
Reply with quote

Hi Priyesh..

Thanks for overwhelming response for my query..
I am being an amateur to this Platform and forum, I am trying to learn as much as I can.
Most importantly,the reason I put in this query was I had a High Priority issue on this, which needs to be solved within a short span of time.
We respect encouragment from people like you.
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 Feb 15, 2008 11:12 pm
Reply with quote

Kiran,

If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

Use [URL] BBCode for External Links
Back to top
View user's profile Send private message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Sat Feb 16, 2008 6:29 pm
Reply with quote

Hi Parag,

Thanks for your response.

There is change in my requirement now.

The input file 1 has a length of 120 chars,whose record format is FB.
The input file 2 has a length of 150 chars,whose record format is also FB.

Data in file1

Indicator SSN NAME Dept
2 0076 Nick 09
5 0089 Leo 23
6 0021 Bill 87
7 7898 Neo 08
1 9879 Phil 89

Data in file2

Year Amt1 SSN Amt2 M1 M2
2008 679 9879 980 12 13
2007 765 0021 987 76 32
2008 987 0076 980 87 16

The above input file1 and file2 must be compared on KEY(i.e.,SSN) and the when both the SSNs match then corresponding values M1 and M2 needs to moved to output file.If file1 SSN is not present in file2 ,then only file1 record needs to be written to be output file.

The Output file should look something like below.

Data in Output file .

Indicator SSN NAME Dept M1 M2
6 0021 Bill 87 76 32
2 0076 Nick 09 87 16
5 0089 Leo 23
7 7898 Neo 08
1 9879 Phil 89 12 13

I will pleased to get a quick response on this.
Awaiting response.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Sat Feb 16, 2008 6:47 pm
Reply with quote

Quote:
Most importantly,the reason I put in this query was I had a High Priority issue on this, which needs to be solved within a short span of time.

Quote:
I will pleased to get a quick response on this.
Awaiting response.


Remember the urgency is a problem only on Your side
You should give us better reasons than just Your urgency,
for us to take time to please You with a quick reply icon_lol.gif

do You realize that the problem is exactly the same as before, only with the column offsets changed?
it would be nice if at least You would have tried

But maybe that' s just homework icon_confused.gif

a very smart politician once said
Quote:
To think evil is a sin, but most of the time You are right
Back to top
View user's profile Send private message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Sat Feb 16, 2008 7:32 pm
Reply with quote

Hi Enrico,

Yes,I agree that urgency is from my end but still I have been trying to figure out this problem for last 3 days.
One of my colleagues suggested post a query on this forum,where I can get a glimpse of the solution.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Sat Feb 16, 2008 7:56 pm
Reply with quote

Why don' t You try to help yourself a little bit,
rather than waiting for a diapering solution form the forum icon_lol.gif
it' s saturday and for most people is not a working day

hint...
look at the record layout from Your first question,
look at the record layout of the last question
meditate about the analogy of your last question with the first solution given to you
build the new ICETOOL parameters with the new fields positions/offsets
Back to top
View user's profile Send private message
ParagChouguley

Active User


Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)

PostPosted: Sat Feb 16, 2008 9:09 pm
Reply with quote

Hi Kiran,
Could you please use Code tag to show your Input and output, which will show exact position and length of the fields. And also you can give a try to modify the jcl given by me to suit your requirements. The forum members are always there to help you in that !
icon_biggrin.gif

--Parag
Back to top
View user's profile Send private message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Sat Feb 16, 2008 9:43 pm
Reply with quote

Hi Parag,

Thanks for your response.I have been trying the Jcl which you posted last night.Its working fine but I m facing problem in last SORT card SRT4CNTL.
I tried putting all efoort to get the output.
But I m not able to find the problem.
Can you please help me with the below requirement.

Input file1

Code:
Indicator SSN    NAME   Dept
    2     0076   Nick   09
    5     0089   Leo    23
    6     0021   Bill   87
    7     7898   Neo    08
    1     9879   Phil   89


Input file2

Code:
Year Amt1 SSN   Amt2 M1 M2
2008 679  9879  980  12 13
2007 765  0021  987  76 32
2008 987  0076  980  87 16


The above input file1 and file2 must be compared on KEY(i.e.,SSN) and the when both the SSNs match then corresponding values M1 and M2 needs to moved to output file.If file1 SSN is not present in file2 ,then only file1 record needs to be written to be output file.

The Output file should look something like below.

Data in Output file .

Code:
Indicator SSN  NAME Dept M1 M2
     6    0021 Bill  87  76 32
     2    0076 Nick  09  87 16
     5    0089 Leo   23
     7    7898 Neo   08
     1    9879 Phil  89  12 13
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Sat Feb 16, 2008 9:45 pm
Reply with quote

Quote:
facing problem in last SORT card SRT4CNTL.


if You had posted the job sysout it would have been easier for everybody to help You
Back to top
View user's profile Send private message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Sun Feb 17, 2008 5:48 pm
Reply with quote

Hi Parag,

I m just getting the ouput in sorted order but not as per my requirement and a character "B" is appended in the 1st column of the record in the output file.

Can please tell me how sort card#3 SRT3CNTL works.

I tried finding it on the forum and manuals.But I am not able to find one such case.

Is comparison done on column names in different files?
If I want to compare fields in different files and which are at different positions.That is, field name RR-SSN(Positioned at 2) of file1 and field MM-SSN(Positioned at 5) of file2.How do we put this in the condition "IFTHEN=(WHEN=(condition))".And If there is no match,how condition can be?

I just want to confirm one thing below:

Code:
//TOOLIN  DD *
COPY FROM(IN1) TO(TEMP1) USING(SRT1)
COPY FROM(IN2) TO(TEMP1) USING(SRT2)
SPLICE FROM(TEMP1) TO(TEMP2) ON(1,14,CH) WITH(15,17) -
    KEEPNODUPS USING(SRT3)
SORT FROM(TEMP2) TO(OUT1) USING(SRT4)


Per above, are we copying IN1 and IN2 to same temp file (TEMP1).


ParagChouguley wrote:
Hi Kiran,
Could you please use Code tag to show your Input and output, which will show exact position and length of the fields. And also you can give a try to modify the jcl given by me to suit your requirements. The forum members are always there to help you in that !
icon_biggrin.gif

--Parag
Back to top
View user's profile Send private message
ParagChouguley

Active User


Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)

PostPosted: Mon Feb 18, 2008 10:58 am
Reply with quote

Hi Kiran,
Its a good thing that you are using proper tags for code part and also you did some research ! Great ! icon_biggrin.gif

Now, you say
Quote:

Per above, are we copying IN1 and IN2 to same temp file (TEMP1)


Yes, we are copying data from both the input files to a single temporary file and rearranging it so as SPLICE operator can compare it !

You also have many other questions, but it's tough for me to answer them.

Could you please use COLS command before you copy your input and expected output here. So that the forum members can get exact idea about the position and length of fields.

--Parag
Back to top
View user's profile Send private message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Mon Feb 18, 2008 11:55 am
Reply with quote

Hi,

If (2,9) of file1 = (5,9) of file2 then I would like to append (93,12) of file2 to the end of first file record and that record needs to be written to output file.
If (2,9) of first file is not equal to (5,9) second file then I would like to write only the record of file1 to output file.

Can you please let me know how the above condition can be handled through IFTHEN condition.

Or do we have any other way for the above.

I am not able find any one such case in the forum or manual.
Awaiting reply.
Back to top
View user's profile Send private message
ParagChouguley

Active User


Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)

PostPosted: Mon Feb 18, 2008 12:28 pm
Reply with quote

Hi Kiran,
Again there is discrepancy between what you are saying now and what you said earlier.
Now you say (2,9) of file1, but as per your sample input file 1, SSN starts at 11th byte.
Also you say (5,9) of file2, but as per your sample input file 1, SSN starts at 11th byte.

Please provide correct information in order forum members to help you.
icon_smile.gif

--Parag
Back to top
View user's profile Send private message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Mon Feb 18, 2008 1:25 pm
Reply with quote

Hi Parag,

Sorry for the discrepany.Actually the inputs were just an example.
Later when I looked into my actual inputs, I found SSNs positions differ.

Now I m putting in actual scenario below.Please accept this and provide me JCL for this.

Details are below:

Input file1

Code:
I SSN  NAME DEPT
2 0001 NICK 20
4 0005 LOE  30
3 0002 POLO 87
6 0004 PHIL 76
7 9898 JACK 87


Input file2

Code:
YEAR SSN  NAME MTOT M1 M2 D1 D2
2008 0002 POLO 200  78 76 43 65
2008 0004 PHIL 345  98 80 13 45
2008 9898 JACK 987  78 89 23 24



If (3,4) of file1 = (6,4) of file2 then I would like to append (20,11) of file2 to end of record file1
If (3,4) of file1 is not equal to (6,4) of file2 then I would like to write
only the record of file1 to output file
I want the records to be sorted on SSNs

The output file should look like below

Code:
I SSN  NAME DEPT M1 M2 D1 D2
2 0001 NICK 20   
3 0002 POLO 87   78 76 43 65
6 0004 PHIL 76   98 80 13 45
4 0005 LOE  30   
7 9898 JACK 87   78 89 23 24


I feel sorry for the discrepancies before.
And please let me know if you need any other information.

ParagChouguley wrote:
Hi Kiran,
Again there is discrepancy between what you are saying now and what you said earlier.
Now you say (2,9) of file1, but as per your sample input file 1, SSN starts at 11th byte.
Also you say (5,9) of file2, but as per your sample input file 1, SSN starts at 11th byte.

Please provide correct information in order forum members to help you.
icon_smile.gif

--Parag
Back to top
View user's profile Send private message
ParagChouguley

Active User


Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)

PostPosted: Mon Feb 18, 2008 2:57 pm
Reply with quote

Now, that's what I needed and here is what you need ! icon_biggrin.gif

Code:

//S1      EXEC PGM=ICETOOL                   
//TOOLMSG DD SYSOUT=*                       
//DFSMSG  DD SYSOUT=*                       
//*                                         
//IN1     DD *                               
I SSN  NAME DEPT                             
2 0001 NICK 20                               
4 0005 LOE  30                               
3 0002 POLO 87                               
6 0004 PHIL 76                               
7 9898 JACK 87                               
/*                                           
//IN2     DD *                                         
YEAR SSN  NAME MTOT M1 M2 D1 D2                       
2008 0002 POLO 200  78 76 43 65                       
2008 0004 PHIL 345  98 80 13 45                       
2008 9898 JACK 987  78 89 23 24                       
/*                                                     
//TEMP1    DD DSN=&&TEMP1,DISP=(MOD,PASS,DELETE),     
//      DSORG=PS,RECFM=FB                             
//TEMP2    DD DSN=&&TEMP2,DISP=(MOD,PASS,DELETE),     
//      DSORG=PS,RECFM=FB                             
//OUT1    DD DSN=OUTPUT-FILE-NAME,                     
//      DSORG=PS,RECFM=FB,                             
//      DISP=(MOD,CATLG,DELETE)                       
//TOOLIN  DD *                                                       
    COPY FROM(IN1) TO(TEMP1) USING(SRT1)                             
    COPY FROM(IN2) TO(TEMP1) USING(SRT2)                             
    SPLICE FROM(TEMP1) TO(TEMP2) ON(6,4,CH) WITH(3,1) WITH(20,12) -   
    KEEPNODUPS USING(SRT3)                                           
    SORT FROM(TEMP2) TO(OUT1) USING(SRT4)                             
/*                                                                   
//SRT1CNTL DD *                                                       
    OPTION COPY                                                       
    OUTREC IFTHEN=(WHEN=(3,3,CH,EQ,C'SSN'),                           
           BUILD=(C'1AA',1,16,12X)),                                 
           IFTHEN=(WHEN=NONE,                                         
           BUILD=(C'2AA',1,16,12X))                                   
/*                                                                   
//SRT2CNTL DD *                                             
    OPTION COPY                                             
    OUTREC FIELDS=(C'2BB',2X,6,4,10X,20,12)                 
/*                                                         
//SRT3CNTL DD *                                             
    OUTREC FIELDS=(1,31)                                   
/*                                                         
//SRT4CNTL DD *                                             
    INCLUDE COND=(2,2,CH,EQ,C'AA',OR,2,2,CH,EQ,C'AB')       
    SORT FIELDS=(1,1,CH,A,6,4,ZD,A)                         
    OUTREC FIELDS=(4,28)                                   
/*                                                         


Output :
Code:

----+----1----+----2----+---
I SSN  NAME DEPT M1 M2 D1 D2
2 0001 NICK 20             
3 0002 POLO 87   78 76 43 65
6 0004 PHIL 76   98 80 13 45
4 0005 LOE  30             
7 9898 JACK 87   78 89 23 24


--Parag
Back to top
View user's profile Send private message
n.kirankumar
Warnings : 1

New User


Joined: 14 Feb 2008
Posts: 16
Location: Hyderabad

PostPosted: Mon Feb 18, 2008 4:30 pm
Reply with quote

Thanks Parag.

I tried executing the JCL provided by you.
I am getting a RC = 12 and i found the below statement in Spool:

Code:
OUTREC STATEMENT FOUND BUT NOT ALLOWED - USE OUTFIL STATEMENT INSTEAD
OPERATION RETURN CODE:  12                                   


I tried executing the jcl by replacing the OUTREC with OUTFIL.

It's throwing a RC=16 with below statement in spool:

Code:
    OUTFIL FIELDS=(1,31)       
           $                   
INVALID OUTFIL STATEMENT OPERAND


Please help me with this.
Back to top
View user's profile Send private message
ParagChouguley

Active User


Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)

PostPosted: Mon Feb 18, 2008 4:56 pm
Reply with quote

Kiran,
I guess FIELDS doesn't work with OUTFIL. Instead use OUTFIL BUILD=

Now here comes the role of Frank, as I too am unaware about internal details of DFSORT/ICETOOL.

Let us wait for Frank's reply !

--Parag
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 Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
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 Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts How to load to DB2 with column level ... DB2 6
No new posts SCOPE PENDING option -check data DB2 2
Search our Forums:

Back to Top