Posted: Tue Jun 30, 2009 7:41 pm Post subject: Joining Records
I would like to join two files and write the output from both the files if they are matched, if it is only in File A then write the record from file A, if it is only in File B then write from File B
Input - file A
Code:
234342 AB 7
234343 VO 9
234343 DC 7
234343 HR 18
Input - file B
Code:
234342 Y 7
234343 Y 1
234343 Y Y 33
Expected O/P
Code:
TEST REPORT 06/30/09 19:46:31
ABBDD DCC Cnt ABBDD D V M COUNT
2552858 VO 7 2552858 Y 7
7100299 AH 9 7100299 Y 1
7100299 DC 7 7100299 Y Y 33
7100299 PM 18 0
---- ----
TOTAL 41 41
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Wed Jul 01, 2009 12:41 am Post subject:
gopurs,
I read it a couple times to understand the matching logic but , I have no clue as to how the joining is done. How did 2552858,7100299 numbers get in the output? Those numbers are not available in any of the files shown. Use code tags to preserve the data formatting as well as it will be easy to read . I added the code tags but not sure if that is the right format of the data.
1. What is the LRECL and RECFM of both input files and output files?
2. What is the position and format of the matching key.
3. Do both files have duplicates?
4. Last but not least what are the rules to be followed for matching?
5. Run the following step and show me the sysout which helps to determine the level of DFSORT
Thanks for your reply. Please find the answers for your questions
1. What is the LRECL and RECFM of both input files and output files?
Both having LRECL 80 and RECFM = FB
2. What is the position and format of the matching key.
Both files the key is at pos 1 to 7
3. Do both files have duplicates?
Yes(Many to many). There are chances that file A is having a key but FileB is not having
4. Last but not least what are the rules to be followed for matching?
If keys are matched then write the output from File A and File B in one line ie fileA records followed by File B records.
else if key is not matched and only records are found in FILEA then wirte from File A and File B structure in output remains blank.
else if key is not matched and records are only found in File B then write the records from file b keeping file A sturcture in the output blank.
5. Run the following step and show me the sysout which helps to determine the level of DFSORT
I don't have mainframe connection right now, but the above answers should give you a better idea on what I am looking. If you still need this DFSORT to be run then I will do it as soon as possible and send it to you.
The output which I assumed would be
1. Key 1111111 has 3 records in file 1 and 2 records in file 2. So the first 2 sets records will be written with 160 bytes of date (file 1 80 bytes + file 2 80 bytes), but for the 3rd record in file 1 will have spaces in 81 thru 80 bytes
2. key 2222222/5555555 has no matching key in File 1 so the first 80 bytes will be spaces
3. key 3333333 has no matching key in File 2 so the pos 81 thru 160 will be spaces
If this is the output you want then I can show you a job to do it.
You also mention having it as a report with summing on certain fields. what is the position and format of the summing field. _________________ Kolusu
www.linkedin.com/in/kolusu
Your understanding is correct in matching the records. Only one thing is that I am not writing the entire 80 bytes even though both the files are having LRECL 80. The actual data will be available in the first 40 bytes and so I am writing only the first 40 bytes from both the files based on the matches.
Your output is exactly what I am looking for. If you can provide the code for the same then it would be great. Highly appreciated.
Yes, I want to total the counts at the end. The count is at pos 20 for a length of 9 in FILEA and in FILEB it is at pos 25 for a length of 9 bytes. Since the output I want is a report I would like to add a header and trailer also. If it cannot be done in one sort then I can write another sort just to add the header and trailer.
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Wed Jul 01, 2009 8:25 pm Post subject:
gopurs,
The following DFSORT/ICETOOL JCL will give you the desired results. Concatenate a single line with HDR as keyword before each file which would be used to identify the record as to which file it belongs.
SYNCSORT FOR Z/OS 1.3.1.1RI
CTL1CNTL :
INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:1,7,SEQNUM,8,ZD,RESTART=(1,7))),
IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'HDR'),PUSH=(96:ID=1)),
*
WER161B ALTERNATE PARM USED
WER268A INREC STATEMENT : SYNTAX ERROR
The * is showing at GROUP. Is it because I don't have the latest version of the product. If that is the case then do i have any alternate method to acheive the results.
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Wed Jul 01, 2009 10:20 pm Post subject:
gopurs,
Your Error messages indicate you are using syncsort. I'm a DFSORT developer. DFSORT and Syncsort are competitive products. I'm happy to answer questions on DFSORT and DFSORT's ICETOOL, but I don't answer questions on Syncsort. _________________ Kolusu
www.linkedin.com/in/kolusu
We have DFSORT also. It is possible to invoke ICETOOL of DFSORT rather than SYNCSORT by providing any specific loadlib or so. I think when I invoke ICETOOL it automaticaly invokes ICETOOL of SYNSORT.
Also is it possible to do the same in DFSORT itself without using ICETOOL
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Thu Jul 02, 2009 10:05 am Post subject:
If SYNCTOOL is invoked when you use PGM=ICETOOL, then Syncsort is installed as the primary sort product at your site. If you also have DFSORT, then you can invoke it by specifying a JOBLIB or STEPLIB pointing to the libraries where your system programmers installed DFSORT. Ask your System Programmers which library or libraries you need to use. _________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
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
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
I read it a couple times to understand the matching logic but , I have no clue as to how the joining is done. How did 2552858,7100299 numbers get in the output? Those numbers are not available in any of the files shown. Use code tags to preserve the data formatting as well as it will be easy to read . I added the code tags but not sure if that is the right format of the data.
