| View previous topic :: View next topic |
| Author |
Message |
radkrish82
Beginner
Joined: 19 Feb 2009
Posts: 50
Topics: 10
|
 Posted: Tue Jan 31, 2012 10:48 am Post subject: Easytrieve - match criteria - order not change/sorted |
 |
|
I have the below reqmt to be done in eztrieve.
The output file is written only when file B matches with file A for the below match criteria.
Match criteria bw file A and B
------------------------------
trans-dt
acct
branch#
group#
ind
value
when first record match happens, the field "result#" in file A is called as Result head account and populated to result-head and the record immediately following to the next match criteria will be called as Result-sub account for the same account. "result#' is populated to result-sub of output file. Rest records are ignored and moved on to read/find next ecord match from file B within A. If any FileB record is not matched with fileA, it is skipped, only match records are considered.
eg;
AAAAAAA 311 54320 A 1120130 30.00000 1517A151 - head account
AAAAAAA 311 54320 A 1120130 30.00000 1517A151
AAAAAAA 610 44121 A 1120130 30.00000 1517A152 - sub account (first change of match criteria)
AAAAAAA 610 44121 A 1120130 30.00000 1517A152
AAAAAAA 312 71573 A 1120130 30.00000 1517A153
AAAAAAA 312 71573 A 1120130 30.00000 1517A153
Input files provided with size and positions. The order of the file should not be changed.
| Code: | Input file A :500FB
acct-9(Alpha) branch# 3(PD) group# 4(PD) ind 1(Alphq) Trans-dt4(PD) value 8(PD) result# 8 (Alpha)
5-13 15-17 18-21 28-28 74-77 84-91
AAAAAAA 311 54320 A 1120130 30.00000 1517A151
AAAAAAA 311 54320 A 1120130 30.00000 1517A151
AAAAAAA 610 44121 A 1120130 30.00000 1517A152
AAAAAAA 610 44121 A 1120130 30.00000 1517A152
AAAAAAA 312 71573 A 1120130 30.00000 1517A153
AAAAAAA 312 71573 A 1120130 30.00000 1517A153
BBBBBBB 321 54321 B 1120130 30.00000 1666A155
DDDDDDD 622 71573 A 1120130 30.00000 1517A153
DDDDDDD 623 72221 A 1120130 30.00000 1517A154
CCCCCCC 611 54320 A 1120130 30.00000 AAA7A1C1
CCCCCCC 611 44121 A 1120130 30.00000 AAA7A1C2
CCCCCCC 611 44121 A 1120130 30.00000 AAA7A1C3
Input file B : 69FB
trans-dt 8(num) acct-9(alpha) branch# 3(num) group# 6(num) ind 1(Alpha) value 5(PD)
1-8 [CCYYMMDD] 9-17 18-20 21-26 27-27 28-32
20120104 AAAAAAA 311 54320 A 30.00000
20120104 BBBBBBB 610 44121 B 30.00000
20120104 ZZZZZZZ 711 66678 B 30.00000
20120104 GGGGGGG 811 76675 A 27.00000
20120104 CCCCCCC 611 54320 A 30.00000
20120104 DDDDDDD 611 44121 A 30.00000
Output file :
acct Result-head Result-Sub trans-dt branch# group#
AAAAAAA 1517A151 1517A152 20120104 311 54320
BBBBBBB 1666A155 20120104 321 54321
CCCCCCC AAA7A1C1 AAA7A1C2 20120104 611 44121
DDDDDDD 1517A153 1517A154 20120104 622 71573
|
Header account and sub account from file A, the other fields are populated from file B. Since the records should not be sorted, i am finding it difficult to handle the logic. can anyone please help me on this to be done in easytrieve of If easytrieve not acheivable, can sync SORT fulfill this reqmt.
Thanks for your help.
regards,
radkrish |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose
|
| Posted: Tue Jan 31, 2012 11:43 am Post subject: Re: Easytrieve - match criteria - order not change/sorted |
|
|
| radkrish82 wrote: | Since the records should not be sorted, i am finding it difficult to handle the logic. can anyone please help me on this to be done in easytrieve of If easytrieve not acheivable, can sync SORT fulfill this reqmt.
Thanks for your help.
regards,
radkrish |
Radkrish,
Why can't you sort the input files? Sort the input files to a temporary dataset and perform the matching. It is a simple requirement , do NOT complicate it with unnecessary restrictions.
what is the format of the trans-dt in file1? The file2 seems to have CCYYMMDD format. Does File2 have duplicates on the composite key?
if you Duplicates then it becomes a MANY TO MANY match and easytrieve is not the right tool to do that.
Kolusu |
|
| Back to top |
|
|
radkrish82
Beginner
Joined: 19 Feb 2009
Posts: 50
Topics: 10
|
| Posted: Wed Feb 01, 2012 12:56 am Post subject: |
|
|
Thanks Kolusu.
| Quote: | | what is the format of the trans-dt in file1? The file2 seems to have CCYYMMDD format. Does File2 have duplicates on the composite key? |
eg.,trans-dt 1120130 of file A which is 4 Packed is read as old IBM date format. C=0 for 1900,C=1 FOR 2000
CC PIC 9(1)
YY PIC 9(2)
MM PIC 9(2)
DD PIC 9(2)
yes I noticed now File2 has duplicates on one account out of 3000 accounts only on match criteria fields. But by populating seq no of 4 byte numeric, these duplicate records are differentiated. For match fields, duplicates exists. In such case, duplicate record is removed keeping the first. we can do it in easytrieve writing duplicate records too and next step we can remove the duplicates.
The fileA should not be sorted because it is arranged in the order of Header followed by its trailer.
regards,
radkrish |
|
| Back to top |
|
|
radkrish82
Beginner
Joined: 19 Feb 2009
Posts: 50
Topics: 10
|
| Posted: Wed Feb 01, 2012 4:36 am Post subject: |
|
|
| If its not an optmised code when we do it in easytrieve, can you please help me to achieve it in synsort. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose
|
| Posted: Wed Feb 01, 2012 12:03 pm Post subject: |
|
|
| radkrish82 wrote: | Thanks Kolusu.
yes I noticed now File2 has duplicates on one account out of 3000 accounts only on match criteria fields. But by populating seq no of 4 byte numeric, these duplicate records are differentiated. For match fields, duplicates exists. In such case, duplicate record is removed keeping the first. we can do it in easytrieve writing duplicate records too and next step we can remove the duplicates. |
How do you plan to do that without sorting the file ? How are you appending the seqnum to the records without sorting?
| radkrish82 wrote: |
The fileA should not be sorted because it is arranged in the order of Header followed by its trailer. |
You are sorting the input dataset to a temporary file and you will use that temporary file to match.
| radkrish82 wrote: | | ]If its not an optmised code when we do it in easytrieve, can you please help me to achieve it in 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. |
|
| Back to top |
|
|
radkrish82
Beginner
Joined: 19 Feb 2009
Posts: 50
Topics: 10
|
| Posted: Thu Feb 02, 2012 2:54 am Post subject: |
|
|
| Quote: | | How do you plan to do that without sorting the file ? How are you appending the seqnum to the records without sorting? |
FILE B records are searched in FILEA. The program in previous step takes care of populating seq no if duplicates found in FILEB. Without sorting the file FILEA, I couldnt handle the logic.
| Quote: | | You are sorting the input dataset to a temporary file and you will use that temporary file to match. |
Apologise, Kolusu. File B can be sorted. But FILEA can't be sorted. I tried sorting this to a temporary file and wrote easy trieve with usual matching logic :
If acct of file A > acct of file B
read fileB
If acct of file A < acct of file B
read filec
If acct of file A = acct of file B
process logic to get result head and sub#.
It didnt work.
DFSORT is allowed only for temporary fix in my shop. The above logic which I tried in easytrieve didnt fulfill the requirement. If you can provide dfsort, i will try to use it for temporary fix. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose
|
| Posted: Thu Feb 02, 2012 11:35 am Post subject: |
|
|
| radkrish82 wrote: | | But FILEA can't be sorted. |
radkrish82,
Why can't you sort? Are you under the impression that if you sort the file to a temporary dataset it will also alter the contents of the input file? Your input file will remain as is.
| radkrish82 wrote: | | DFSORT is allowed only for temporary fix in my shop. The above logic which I tried in easytrieve didnt fulfill the requirement. If you can provide dfsort, i will try to use it for temporary fix. |
You said your shop has syncsort.
Kolusu |
|
| Back to top |
|
|
radkrish82
Beginner
Joined: 19 Feb 2009
Posts: 50
Topics: 10
|
| Posted: Fri Feb 03, 2012 12:18 am Post subject: |
|
|
dont know why they are not allowing DFSORT techniques.They have their decade old standards still un changed.they allow dfsort logic only as temporary fix (overrides) or an immediate fix JUST to close the issue asap to escape from SLA's. Out of my 3 shops, only this shop clients not willing to change.
| Quote: | | Are you under the impression that if you sort the file to a temporary dataset it will also alter the contents of the input file? Your input file will remain as is. |
I may be in correct, but I meant it will alter the functionality if it is sorted.
I dont know the logic. I know by overlaying seq num to the end of FB file, we can remove duplicates.
[/quote] |
|
| Back to top |
|
|
Anuj Dhawan
Intermediate
Joined: 19 Jul 2007
Posts: 298
Topics: 7
Location: Mumbai,India
|
| Posted: Fri Feb 03, 2012 4:00 am Post subject: |
|
|
What you say about DFSort, SyncSort and temporary fixes - does not sound practical. You said: | Quote: | | They have their decade old standards still un changed.they allow dfsort logic only as temporary fix (overrides) or an immediate fix JUST to close the issue asap to escape from SLA's. |
I wonder if you change the LPAR itself for the fix, executes the fix in other LPAR or you mention some specific STEPLIBs to pick up the SORT of your interest. It's confusing.
_________________
Regards,
Anuj |
|
| Back to top |
|
|
radkrish82
Beginner
Joined: 19 Feb 2009
Posts: 50
Topics: 10
|
| Posted: Fri Feb 03, 2012 4:42 am Post subject: |
|
|
| Quote: | | What you say about DFSort, SyncSort and temporary fixes - does not sound practical. |
Its live. To implement in production for permanent fix, you do require to submit rfs which follows review checklist from prod monitoring team. If ICETOOL is present in permanent move to prod, it is getting rejected. I have also enjoyed working in shops which accepts DFSORT. current shop doesnt. |
|
| Back to top |
|
|
radkrish82
Beginner
Joined: 19 Feb 2009
Posts: 50
Topics: 10
|
| Posted: Fri Feb 03, 2012 5:10 am Post subject: |
|
|
| if anyone knows the solution, pls provide. thanks in advance. |
|
| Back to top |
|
|
radkrish82
Beginner
Joined: 19 Feb 2009
Posts: 50
Topics: 10
|
| Posted: Mon Feb 06, 2012 10:30 am Post subject: |
|
|
I tried in easytrieve through JOB INPUT (FILEA KEY (fields)
filebKEY (fields))
It resulted me ascending sequence error on account field.
For now, I am searching each record one by one through so called dirty open/close procedure.(open and close for each search).
Avg 2000 records in file A and 2500 records in fileb.
Thanks, Kolusu , thanks everyone for your replies. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose
|
| Posted: Mon Feb 06, 2012 11:29 am Post subject: |
|
|
| radkrish82 wrote: | | For now, I am searching each record one by one through so called dirty open/close procedure.(open and close for each search). . |
radkrish82,
2000 open and close statements woah. What happens if you had a million records? I still don't see a reason as to why you are so adamant about sorting the input file to a temp file and using that. Looks like you work for a shop who doesn't care about performance. Oh well |
|
| Back to top |
|
|
radkrish82
Beginner
Joined: 19 Feb 2009
Posts: 50
Topics: 10
|
| Posted: Tue Feb 07, 2012 12:57 am Post subject: |
|
|
Thanks Kolusu.
3500 is the maximum we get for month end run for cumulative accts(31 days), No year end processing. it will run in EST night 3AM as the last batch. It ran for 34 minutes with 2000 records in dev. I didnt have any efficient solution for this immediate fix.
I tried with SORT FIELDS (for key fields) with EQUAL option. It didn't work. I don't know any alternate logic. Please provide if you have any efficient solution, I am glad to use it. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose
|
| Posted: Tue Feb 07, 2012 11:58 am Post subject: |
|
|
| radkrish82 wrote: | It ran for 34 minutes with 2000 records in dev. I didnt have any efficient solution for this immediate fix.
I tried with SORT FIELDS (for key fields) with EQUAL option. It didn't work. I don't know any alternate logic. Please provide if you have any efficient solution, I am glad to use it. |
radkrish82,
As I said earlier it is a simple request. Ideally with that amount of data you have the job should run in less than a minute.
Here is the pseudo-code of what you need to do.
Step0100 : Sort File-A on trans-dt,acct,branch#,group#,ind,value to a temp file. Use inrec to convert the date to CCYYMMDD format so that you can match it against the file 2 date. Remove the header and trailer records if you have them. Remember that your input file remains the same , it is your temp file that will have the sorted data.
Step0200 : Sort File-A on trans-dt,acct,branch#,group#,ind,value to another temp file and remove the dups using sum fields=none.
Step0300 : Use easytrieve to do one-to-many match using the 2 temp files created which should be simple and easy. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
