Home      Mainframe Forum      Mainfarme Tutorials      IBM Manuals      Mainframe Interview Questions      Mainframe Books      IT News     SiteMap     Downloads


     
 
MAINFRAME - TIP OF THE DAY : When you specified V for RECFM parameter, LRECL value is largest record in the file plus 4 bytes. These four bytes contain the actual length of each variable length record in the file

Google
 
Web mainframegurukul.com

Merging two files into one based on certain key fields:

 
Post new topic   Reply to topic    mainframegurukul.com Forum Index -> DFSORT , ICETOOL & Utilities
  View previous topic :: View next topic  
Author Message
MVS_blob
Member


Joined: 24 Oct 2007
Posts: 6

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Wed Oct 24, 2007 5:38 am    Post subject: Merging two files into one based on certain key fields: Reply with quote

Hi,

I've got a tricky one for those up for a challenge. Very Happy

I have two input files, and want to create an output file. I'm adding a two digit number to a file containing a header and detail records. Note: the two digit number is dependent on the header and detail records.

INPUT1:
Code:
AAA 7777777777 56
BBB 3333333333 47
CCC 1111111111 84
MMM 2222222222 35
PPP 2222222222 69


INPUT2:
Code:
HEADER2222222222
DATAXXXXXXXXXXXXXXXXXXXXXX
DEATIL MMM
DATAXXXXXXXXXXXXXXXXXXXXXX
DATAXXXXXXXXXXXXXXXXXXXXXX

HEADER1111111111
DATAXXXXXXXXXXXXXXXXXXXXXX
DEATIL CCC
DATAXXXXXXXXXXXXXXXXXXXXXX
DATAXXXXXXXXXXXXXXXXXXXXXX
DETAIL CCC
DATAXXXXXXXXXXXXXXXXXXXXXX

HEADER3333333333
DATAXXXXXXXXXXXXXXXXXXXXXX
DEATIL BBB
DATAXXXXXXXXXXXXXXXXXXXXXX
DATAXXXXXXXXXXXXXXXXXXXXXX

HEADER2222222222
DATAXXXXXXXXXXXXXXXXXXXXXX
DEATIL PPP
DATAXXXXXXXXXXXXXXXXXXXXXX
DATAXXXXXXXXXXXXXXXXXXXXXX
DEATIL PPP
DATAXXXXXXXXXXXXXXXXXXXXXX
DATAXXXXXXXXXXXXXXXXXXXXXX

HEADER7777777777
DATAXXXXXXXXXXXXXXXXXXXXXX
DEATIL AAA
DATAXXXXXXXXXXXXXXXXXXXXXX
DATAXXXXXXXXXXXXXXXXXXXXXX


Output file:
Code:
HEADER2222222222        35
DATAXXXXXXXXXXXXXXXXXXXXXX
DEATIL MMM
DATAXXXXXXXXXXXXXXXXXXXXXX
DATAXXXXXXXXXXXXXXXXXXXXXX

HEADER1111111111        84
DATAXXXXXXXXXXXXXXXXXXXXXX
DEATIL CCC
DATAXXXXXXXXXXXXXXXXXXXXXX
DATAXXXXXXXXXXXXXXXXXXXXXX
DETAIL CCC
DATAXXXXXXXXXXXXXXXXXXXXXX

HEADER3333333333        47
DATAXXXXXXXXXXXXXXXXXXXXXX
DEATIL BBB
DATAXXXXXXXXXXXXXXXXXXXXXX
DATAXXXXXXXXXXXXXXXXXXXXXX

HEADER2222222222        69
DATAXXXXXXXXXXXXXXXXXXXXXX
DEATIL PPP
DATAXXXXXXXXXXXXXXXXXXXXXX
DATAXXXXXXXXXXXXXXXXXXXXXX
DEATIL PPP
DATAXXXXXXXXXXXXXXXXXXXXXX
DATAXXXXXXXXXXXXXXXXXXXXXX

HEADER7777777777        56
DATAXXXXXXXXXXXXXXXXXXXXXX
DEATIL AAA
DATAXXXXXXXXXXXXXXXXXXXXXX
DATAXXXXXXXXXXXXXXXXXXXXXX


Constraints:
---------------

INPUT1:
column 1 and column 3 are unique
column 2 is not unique

INPUT2:
DATAXXXXXXXXXXXXXXXXXXXXXX is some data between the header and detail lines. These are specific to the header and detail lines, and should be kept as is.

OUTPUT:
column 3 of INPUT1 has been added to the header file of INPUT2.


I've tried to split up the task into multiple steps and work at each step with limited success. Been working on this one for a while now, and am getting no where, so any help would be greatly appreciated.

I know this can be done via COBOL/EASYTRIEVE, but if I can achieve this via JCL/DFSORT/ICETOOL, it'd be great.
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.

MVS_blob
Member


Joined: 24 Oct 2007
Posts: 6

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Wed Oct 24, 2007 12:56 pm    Post subject: Reply with quote

Been working on this problem today, and have gotten somewhere with it.

My steps were: (excuse my noob-ness)

1. program: DFSORT
-extract all HEADER rows to FILEA & append a 6 digit sequence number to the end
-extract all DETAIL rows to FILEB & append a 6 digit sequence number to the end
2. program: DFSORT
- remove duplicates in FILEB (DEATIL rows)
3. program: DFSORT
- sort FILEB according to the 6 digit sequence number.
- re-number the 6 digit sequence number append to FILEA
4. program: ICETOOL
- SPLICE the FILEA to get FILEC which looks like
Code:
MMM 2222222222    000001
CCC 1111111111    000002
BBB 3333333333    000003
PPP 2222222222    000004
AAA 7777777777    000005

5. program: SORTD
- append INPUT1 to FILEC
6. program: ICETOOL
- SPLICE FILEC
7. program: SORTD
- sort FILED according to the sequence number. don't output the sequence number:
Code:
MMM 2222222222 35
CCC 1111111111 84
BBB 3333333333 47
PPP 2222222222 69
AAA 7777777777 56


which lists the headers in the order they appear, and includes the two digit field (column 3 of INPUT1).

This is probably the long winded way, and can probably be done in alot fewer steps. If you have a shorter way, please don't hesitate to post up a suggestion.

Also, as the actual files are quite large, performance isn't too great at the moment, so a short way would be MUCH appreciated.

Now to somehow merge the 2 digit field with my INPUT2 file. hmmm... any ideas?

... am I totally off track?
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
Frank Yaeger
Moderator


Joined: 18 Feb 2006
Posts: 812
Location: San Jose, CA

Answer was approved by Question owner.
Useful Post
3 votes
Not an useful post
Greate Post!! Salute Author
4 salutes

PostPosted: Wed Oct 24, 2007 10:57 pm    Post subject: Reply with quote

Yes, this is tricky.

Here's a DFSORT/ICETOOL job that will do what I think you asked for. I assumed your input files have RECFM=FB and LRECL=80, but the job can be changed appropriately for other attributes.

I also assumed that:
- positions 5-14 of input file1 are to be matched with positions 7-16 of the HEADER record in input file2, AND
- positions 1-3 of input file1 are to be matched with positions 8-10 of the first DEATIL record after the HEADER record in input file2

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)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//T3 DD DSN=&&T3,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/80)
//TOOLIN DD *
COPY FROM(IN2) TO(T2) USING(CTL1)
SPLICE FROM(T2) TO(T1) ON(81,8,ZD) -
 WITHEACH WITH(28,3)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T3) ON(7,10,CH) ON(28,3,CH) -
 WITH(25,2)
SPLICE FROM(T3) TO(OUT) ON(81,8,ZD) ON(89,8,CH) KEEPNODUPS -
 WITH(25,2) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,8,ZD)),
        IFTHEN=(WHEN=(1,6,CH,EQ,C'HEADER'),
                OVERLAY=(81:SEQNUM,8,ZD)),
        IFTHEN=(WHEN=NONE,
                OVERLAY=(89:SEQNUM,8,ZD,
                         81:81,8,ZD,SUB,89,8,ZD,M11,LENGTH=8))
  OUTFIL FNAMES=T2,
     INCLUDE=(1,6,SS,EQ,C'HEADER,DEATIL'),
     IFTHEN=(WHEN=(1,6,CH,EQ,C'DEATIL'),
       BUILD=(28:8,3,81:81,16))
  OUTFIL FNAMES=T3
/*
//CTL2CNTL DD *
  INREC BUILD=(7:5,10,25:16,2,28:1,3,81:16X)
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,BUILD=(1,80)
/*

_________________
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
MVS_blob
Member


Joined: 24 Oct 2007
Posts: 6

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Thu Oct 25, 2007 7:30 am    Post subject: Reply with quote

Frank, you're a genius!!! Mr. Green

Thanks soo much! It works perfectly.

My original code was 100+ lines, was slow, and only did 1/3 of the things yours does.

Great work!
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
Frank Yaeger
Moderator


Joined: 18 Feb 2006
Posts: 812
Location: San Jose, CA

Answer was approved by Question owner.
Useful Post
1 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Thu Oct 25, 2007 9:06 pm    Post subject: Reply with quote

Thanks for the feedback and the kind words. Glad I could help.
_________________
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
academyindia4
Guest





Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Mon Jan 25, 2016 11:04 pm    Post subject: Topic deleted by Admin Reply with quote

<< Content deleted By Admin >>
Back to top
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
Display posts from previous:   
Post new topic   Reply to topic    mainframegurukul.com Forum Index -> DFSORT , ICETOOL & Utilities All times are GMT + 5 Hours
Page 1 of 1



 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Cobol Tutorial
This cobol tutorial covers most of the important topics like STRING, UNSTRING, COMP, COMP-3.....
DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming. Explains in simple language. Some Chapters are locked, Forum members have free access to these chapters
CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming...
JCL Tutorial
This is most popular JCL tutorial from mainframegurukul. It does contain important jcl ....
SORT Tutorial
This Tutorial covers all important aspects of DFSORT. Has more SORT examples
INTERVIEW Questions
This page covers important interview questions
Mainframe Jobs
Mainframe Jobs posted by members of mainframegurukul forum
FaceBook Page
MainframeGurukul FaceBook Page
LinkedIn Page
MainframeGurkul Linkedin Page

This widget requires Flash Player 9 or better







Go to top of the page
 

Online ABEND Reference ||  JCL References ||  COBOL References ||  VSAM References ||  Tutorials by Drona Series ||  SQL tutorial ||  BOOKS  ||  DB2 INTERVIEW QUESTIONS ||  COBOL INTERVIEW QUESTIONS  ||  JCL INTERVIEW QUESTIONS ||  JCL2 INTERVIEW QUESTIONS ||  VSAM INTERVIEW QUESTIONS ||  CICS INTERVIEW QUESTIONS  ||  Online tutorials ||  Online ABEND Reference ||  JCL References ||  COBOL References ||  VSAM References ||  Tutorials by Drona Series ||  SQL tutorial ||  BOOKS  ||  SiteMap  ||  Expeditor Tutorial  ||  FILE-AID Tutorial  ||  Changeman Tutorial  ||  COBOL   ||  DB2   ||  JCL  ||  CICS  ||  VSAM  ||  DB2 Interview Questions ( 110 )   || Simple JCL Tutorials  || JCL Tutorial from MainframeGurukul.com   || Simple JCL Tutorial - Chapter1 ;|| Mainframe Forum - Tutorials  || Mainframe Tutorials || Mainframe CICS tutorial|| Mainframe COBOL Tutorial

Drona Educational Forums - Mainframe Cobol DB2 CICS Board
Powered by phpBB

mainframetopic-2829.html