|
View previous topic :: View next topic
|
| Author |
Message |
Thirumurgann
New User
Joined: 13 Feb 2008
Posts: 36
Location: India
|
|
|
|
Hi Friends,
My req is to compare the INFILE3 and INFILE4 and generate the output file OUTFIL2 when there are any inserts/updates happened in the INFILE3.
| Code: |
FILE INFILE3
IN-RECORD-1 01 30082 A
IN-PDF-UPN1 01 09 N
IN-PDF-MEM-OF-UPN1 11 09 N
IN-PDF-BU1 21 40 A
IN-PDF-PRI-PROD-CD1 62 20 A
IN-PDF-PROD-CODE1 83 30000 A
FILE INFILE4
IN-RECORD-2 01 30082 A
IN-PDF-UPN2 01 09 N
IN-PDF-MEM-OF-UPN2 11 09 N
IN-PDF-BU2 21 40 A
IN-PDF-PRI-PROD-CD2 62 20 A
IN-PDF-PROD-CODE2 83 30000 A
FILE OUTFIL2
OUT-RECORD 01 30084 A
OUT-PDF-UPN 01 09 N
OUT-PDF-FILLER-1 10 01 A
OUT-PDF-MEM-OF-UPN 11 09 N
OUT-PDF-FILLER-2 20 01 A
OUT-PDF-BU 21 40 A
OUT-PDF-FILLER-3 61 01 A
OUT-PDF-PRI-PROD-CD 62 20 A
OUT-PDF-FILLER-4 82 01 A
OUT-PDF-PROD-CODE 83 30000 A
OUT-PDF-UPDATE-FLAG 30082 02 A
FILE SORT3 F 30082 VIRTUAL
ST-PDF-UPN1 01 09 N
ST-PDF-MEM-OF-UPN1 11 09 N
ST-PDF-BU1 21 40 A
ST-PDF-PRI-PROD-CD1 62 20 A
ST-PDF-PROD-CODE1 83 30000 A
*
FILE SORT4 F 30082 VIRTUAL
ST-PDF-UPN2 01 09 N
ST-PDF-MEM-OF-UPN2 11 09 N
ST-PDF-BU2 21 40 A
ST-PDF-PRI-PROD-CD2 62 20 A
ST-PDF-PROD-CODE2 83 30000 A
SORT INFILE3 TO SORT3 USING (IN-PDF-UPN1, IN-PDF-MEM-OF-UPN1, +
IN-PDF-BU1)
SORT INFILE4 TO SORT4 USING (IN-PDF-UPN2, IN-PDF-MEM-OF-UPN2, +
IN-PDF-BU2)
JOB INPUT (SORT3 KEY(ST-PDF-UPN1, ST-PDF-MEM-OF-UPN1, +
ST-PDF-BU1), +
SORT4 KEY(ST-PDF-UPN2, ST-PDF-MEM-OF-UPN2, +
ST-PDF-BU2))
IF MATCHED
IF (ST-PDF-UPN1 = ST-PDF-UPN2) AND +
(ST-PDF-MEM-OF-UPN1 = ST-PDF-MEM-OF-UPN2) AND +
(ST-PDF-BU1 = ST-PDF-BU2) AND +
(ST-PDF-PRI-PROD-CD1 = ST-PDF-PRI-PROD-CD2) AND +
(ST-PDF-PROD-CODE1 = ST-PDF-PROD-CODE2)
ELSE
OUT-PDF-UPDATE-FLAG = '|U'
OUT-PDF-UPN = ST-PDF-UPN1
OUT-PDF-MEM-OF-UPN = ST-PDF-MEM-OF-UPN1
OUT-PDF-BU = ST-PDF-BU1
OUT-PDF-PRI-PROD-CD = ST-PDF-PRI-PROD-CD1
OUT-PDF-PROD-CODE = ST-PDF-PROD-CODE1
PUT OUTFIL2
END-IF
ELSE
IF SORT3
OUT-PDF-UPDATE-FLAG = '|I'
OUT-PDF-UPN = ST-PDF-UPN1
OUT-PDF-MEM-OF-UPN = ST-PDF-MEM-OF-UPN1
OUT-PDF-BU = ST-PDF-BU1
OUT-PDF-PRI-PROD-CD = ST-PDF-PRI-PROD-CD1
OUT-PDF-PROD-CODE = ST-PDF-PROD-CODE1
PUT OUTFIL2
END-IF
END-IF
*
OUT-PDF-UPDATE-FLAG = ' '
OUT-PDF-PRI-PROD-CD = ' '
OUT-PDF-PROD-CODE = ' '
*
GO TO JOB |
Code'd
The issue here is the fields OUT-PDF-PRI-PROD-CD , OUT-PDF-PROD-CODE are not getting populated correctly when the successive records are read. Since these are not initialized after each successive fetch the output OUTFIL2 values overriden in the fileds OUT-PDF-PRI-PROD-CD , OUT-PDF-PROD-CODE. pls help to get this fixed.
Thanks,
Thiru |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
When posting code and/or jcl and/or data, use copy/paste and the code tag to preserve alignment and improve readability. Your code was "Code'd" but did not align because you added spaces manually. There is a Preview feature so you can see your post as it will appear to the forum. When the post appears as you want, Submit.
You need to post some sample data from the input files and the output that is happening now. You also need to post the expected output when the sample data is processed. |
|
| Back to top |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
| Code: |
IF MATCHED
IF (ST-PDF-UPN1 = ST-PDF-UPN2) AND +
(ST-PDF-MEM-OF-UPN1 = ST-PDF-MEM-OF-UPN2) AND +
(ST-PDF-BU1 = ST-PDF-BU2) AND +
(ST-PDF-PRI-PROD-CD1 = ST-PDF-PRI-PROD-CD2) AND +
(ST-PDF-PROD-CODE1 = ST-PDF-PROD-CODE2)
|
The first 3 compares are part of your match keys, why dont you
make the other 2 compares also part of your match keys?
Then you could do something like :
| Code: |
IF MATCHED
END-IF
IF NOT SORT3
END-IF
IF NOT SORT4
END-IF
|
Or use the whole record as a key. |
|
| Back to top |
|
|
Thirumurgann
New User
Joined: 13 Feb 2008
Posts: 36
Location: India
|
|
|
|
Hi Guys,
Sorry for improper alignment. I will take care going forward.
Input file sample:
| Code: |
INFILE3 is
000016611|000091588|Farmers Insurance |27MA09 |27MA09 ,27MA10 ,27MA11 ,27MA12
000016769|000017481|xyz |27MC11 |27MC11
000017155|000017481|xyz |27MC04 |27MC15
000017196|000017481|xyz |27MC06 |27MC06
000017282|000017481|xyz |27MC17 |27MC17
000017360|000091588|xyz |27MA05 |27MA05
000017384|000024744|xyz |27MB73 |27MB73
000017481|000000000|xyz |27MC01 |27MC01
000017495|000017481|xyz |27MC05 |27MC27
000019041|000023019|NEWWWWWWWWWWWWWWWWW |111111 |37MA09 ,37MA10 ,37MA11 ,37MA12
000019052|000023019|NEWWWWWWWWWWWWWWWWW |222222 |37MA09 ,37MA10 ,37MA11 ,37MA12
000019063|000023019|NEWWWWWWWWWWWWWWWWW |333333 |37MA09 ,37MA10 ,37MA11 ,37MA12
000019074|000023019|NEWWWWWWWWWWWWWWWWW |444444 |37MA09 ,37MA10 ,37MA11 ,37MA12
000019085|000023019|NEWWWWWWWWWWWWWWWWW |555555 |37MA09 ,37MA10 ,37MA11 ,37MA12
INFILE4
000016611|000091588|Farmers Insurance |27MA09 |27MA09 ,
000016769|000017481|xyz |27MC11 |27MC11
000017155|000017481|xyz |27MC04 |27MC15
000017196|000017481|xyz |27MC06 |27MC06
000017282|000017481|xyz |27MC17 |27MC17
000017360|000091588|xyz |27MA05 |27MA05
000017384|000024744|xyz |27MB73 |27MB73
000017481|000000000|xyz |27MC01 |27MC01 ,27MC02
000017495|000017481|xyz |27MC05 |27MC27
000019031|000023019|xyz |62MT28 |62MT28 |
My Output file generated is:
| Code: |
OUTFIL2
000016611|000091588|xyz |27MA09 |27MA09 ,27MA10 ,27MA11 ,27MA12
000016769|000017481|xyz |27MC11 |27MC11 ,27MA10 ,27MA11 ,27MA12
000017155|000017481|xyz |27MC04 |27MC15 ,27MA10 ,27MA11 ,27MA12
000017196|000017481|xyz |27MC06 |27MC06 ,27MA10 ,27MA11 ,27MA12
000017282|000017481|xyz |27MC17 |27MC17 ,27MA10 ,27MA11 ,27MA12
000017360|000091588|xyz |27MA05 |27MA05 ,27MA10 ,27MA11 ,27MA12
000017384|000024744|xyz |27MB73 |27MB73 ,27MA10 ,27MA11 ,27MA12
000017481|000000000|xyz |27MC01 |27MC01 ,27MA10 ,27MA11 ,27MA12
000017495|000017481|xyz |27MC05 |27MC27 ,27MA10 ,27MA11 ,27MA12
000019041|000023019|NEWWWWWWWWWWWWWWWWW |111111 |37MA09 ,37MA10 ,37MA11 ,37MA12
000019052|000023019|NEWWWWWWWWWWWWWWWWW |222222 |37MA09 ,37MA10 ,37MA11 ,37MA12
000019063|000023019|NEWWWWWWWWWWWWWWWWW |333333 |37MA09 ,37MA10 ,37MA11 ,37MA12
000019074|000023019|NEWWWWWWWWWWWWWWWWW |444444 |37MA09 ,37MA10 ,37MA11 ,37MA12
000019085|000023019|NEWWWWWWWWWWWWWWWWW |555555 |37MA09 ,37MA10 ,37MA11 ,37MA12 |
In the output file look at the second record. Here
OUT-PDF-PROD-CODE should be 27MC11. Not 27MC11 ,27MA10 ,27MA11 ,27MA12.
Thanks,
Thiru |
|
| Back to top |
|
|
Thirumurgann
New User
Joined: 13 Feb 2008
Posts: 36
Location: India
|
|
|
|
Hi,
I could nt use the other two fileds as keys since thier record length exceeds 255.
Thanks,
Thiru |
|
| Back to top |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
| Thirumurgann wrote: |
Hi,
I could nt use the other two fileds as keys since thier record length exceeds 255.
Thanks,
Thiru |
Of course, overlooked that.
Beside in case of a match you only have to compare the last 2 fields
for a record match. But because you cannot sort on the large field
how can you guarantee that there will be any match. |
|
| Back to top |
|
|
Thirumurgann
New User
Joined: 13 Feb 2008
Posts: 36
Location: India
|
|
|
|
This could be implemented in easytrieve???
Pls let me knw. If this cant be implemented then pls suggest how to implement this requirement.
Thanks,
Thiru |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| I will take care going forward. |
The Code tag should also be used for data and jcl. I'm not sure exactly how your most recent post is supposed to line up and i deleted the duplicate. . .
| Quote: |
| This could be implemented in easytrieve??? |
Unless i've misunderstood the requirement this should be doable with Easytrieve.
If you actually write the match code (rather than using the builtin feature) you may have more success. Done properly, this looks like a normal 2-file match/merge exercise. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Are you expecting duplicate keys to be possible on the files?
We need to see the sorted data that is going into the match, and I suspect you do too. |
|
| Back to top |
|
|
Thirumurgann
New User
Joined: 13 Feb 2008
Posts: 36
Location: India
|
|
|
|
Hi Dick,
The input and the output files have record length greater than 30,000.
Thats what made me complex to handle. since i could nt sort all the 5 fields present in the file. Just i am handling the record with first 3 fields as key. Then what happen's is the record's fectched in to the host variables are being overlapped in the second fetch. I tried to initialize the host variable's
ST-PDF-PRI-PROD-CD1 = ' '
ST-PDF-PROD-CODE1 = ' '
ST-PDF-PRI-PROD-CD2 = ' '
ST-PDF-PROD-CODE2 = ' '
But it dint work. I am getting
A010 INVALID FILE REFERENCE - SORT3
Then i tried with initializing the output fields after every record fetch. I dint get any compilation errors and still i get the records overridden.
Thanks,
Thiru |
|
| Back to top |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
A010 INVALID FILE REFERENCE - SORT3
That occurs if there is no match and SORT4 is the active file.
So you can do the following :
| Code: |
IF MATCHED
compare the 2 fields not belonging to the keys
do some processing if equal
do some processing if not equal
END-IF
IF SORT3
do some processing with the SORT3 fields
END-IF
IF SORT4
do some processing with the SORT4 fields
END-IF
|
|
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Thirumurgann wrote: |
Hi Dick,
The input and the output files have record length greater than 30,000.
Thats what made me complex to handle. since i could nt sort all the 5 fields present in the file. Just i am handling the record with first 3 fields as key. Then what happen's is the record's fectched in to the host variables are being overlapped in the second fetch. I tried to initialize the host variable's
ST-PDF-PRI-PROD-CD1 = ' '
ST-PDF-PROD-CODE1 = ' '
ST-PDF-PRI-PROD-CD2 = ' '
ST-PDF-PROD-CODE2 = ' '
But it dint work. I am getting
A010 INVALID FILE REFERENCE - SORT3
Then i tried with initializing the output fields after every record fetch. I dint get any compilation errors and still i get the records overridden.
Thanks,
Thiru |
Thiru,
There is no sign of DB2 anywhere in your program. You are using non-database files. Files have records. You do not use "host variables". You do not "fetch".
The length of the record is giving you problems, and you are using your DASD very inefficiently. What is the "design" reason behind this?
All the initialisation you show above is unnecessary. The fields you initiliase are the target of an assignment, so previous contents are absolutely irrelevant. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Can you show us how your input files are defined (are they V, VB, F, FB or something else), please? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
How many records does each input file have?
It appears that the 30k fields are not part of the match, but rather are just along for the ride so they shouldnot add to the complexity of the match.
Is there some reason the choice was made to do all of the sorting within the Easytrieve code? This is is a very bad choice for other than small files. . . |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Hello Dick,
TS is comparing the whole record, for an "Update/Insert" extract.
May be a task for SORT.
TS will need to supply all the same answers already asked. |
|
| Back to top |
|
|
Thirumurgann
New User
Joined: 13 Feb 2008
Posts: 36
Location: India
|
|
|
|
| Bill Woodger wrote: |
| Can you show us how your input files are defined (are they V, VB, F, FB or something else), please? |
Hi,
I found the issue. I have defined my sort work files wrongly.
FILE SORT3 F 30082 VIRTUAL
ST-PDF-UPN1 01 09 N
ST-PDF-MEM-OF-UPN1 11 09 N
ST-PDF-BU1 21 40 A
ST-PDF-PRI-PROD-CD1 62 20 A
ST-PDF-PROD-CODE1 83 30000 A
FILE SORT4 F 30082 VIRTUAL
ST-PDF-UPN2 01 09 N
ST-PDF-MEM-OF-UPN2 11 09 N
ST-PDF-BU2 21 40 A
ST-PDF-PRI-PROD-CD2 62 20 A
ST-PDF-PROD-CODE2 83 30000 A
But this should be defined as variable block, records varying from 01 to 30082. Please suggest how to define the sort work file for this.
Thanks,
Thiru |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
This will get you a VIRTUAL file with variable length records. You don't need to specify blocking for a virtual file - if my memory is holding-up on this.
[code]FILE SORT3 V 30082 VIRTUAL[/cod]
That is not the end of your problem.
Now your file is variable, but your data definition is not, and if you use that definition you will be looking "beyond the end of the record".
What about your output files? Are they variable? If so, what about your update/insert indicator which is stuck way down the end of the record.
To either proceed with an Easytrieve solution or start with a SORT one, we need answers to the questions already asked. |
|
| Back to top |
|
|
Thirumurgann
New User
Joined: 13 Feb 2008
Posts: 36
Location: India
|
|
|
|
Thanks dude for making me understand.
My input files read from the proc is already variable one and the output files which has been written was also declared as variable.
So i could change my file definition in the easytrieve and i will try to execute.
Thanks to all,
Thiru |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Thiru,
You're not there yet. You need a method to match the variable-length part of your record. You need to relocate you update/insert marker. |
|
| Back to top |
|
|
Thirumurgann
New User
Joined: 13 Feb 2008
Posts: 36
Location: India
|
|
|
|
| Bill Woodger wrote: |
Thiru,
You're not there yet. You need a method to match the variable-length part of your record. You need to relocate you update/insert marker. |
Hi Bill,
Could you please elobrate on the solution further. I am very new to easytrieve.
Thanks,
Thiru |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
I can elaborate on the problem, at least. Sort of, because we are still short of answers....
You are matching two files.
If the records are identical, you ignore them.
If the keys are the same but the data not, you extract the "latest" with an indicator saying "Update".
If the keys do not match, and it is the "latest" you extract with an indicator saying "Insert".
The "data" part of your record is variable in length (making the who record variable in length).
Your first problem is what to do with the Update/Insert marker. You are putting it after the variable part of the record, either forcing the record to a maximum, or making you use a different method to ensure it is located after the existing data (and making your output record longer by two bytes).
To do your comparison, you must ensure that you only compare data that is actually within your record.
Easytrieve now supports variable-length fields. You'll have to look that up.
Easytrieve will also use the current record on a file, with the correct length, when you use the "filename" - like MOVE filename TO a data-name or another-filename. Or IF filename EQ another-filename.
Look at the manual for RECORD-LENGTH for how to set and extend your output lenghts for a variable-length record.
This will move current record of SORTF3 to record-area for OUTFIL2.
| Code: |
| MOVE SORTF3 TO OUTFIL2 |
This will rest the entire record on SORTF3 against the entire record on SORTF4.
| Code: |
| IF SORTF3 EQ SORFT4 |
For a variable output file you need to set the record-length.
It is easier than it looks once you've done it. |
|
| Back to top |
|
|
Thirumurgann
New User
Joined: 13 Feb 2008
Posts: 36
Location: India
|
|
|
|
BILL,
Thank you so much. I will try out this...
Thanks,
Thiru |
|
| Back to top |
|
|
Thirumurgann
New User
Joined: 13 Feb 2008
Posts: 36
Location: India
|
|
|
|
Hi BILL/DICK
Special thanks to you guys...When i am clu less BILL and Dick helped me a lot to complete this deliverable on the time. BILL patiently explained me. Hats off BILL.... You rock...
Thanks,
Thiru |
|
| Back to top |
|
|
seagull
New User
Joined: 28 May 2007
Posts: 24
Location: Dublin
|
|
|
|
| It looks to me like the best option is to put the SORT into a separate step ahead of the easytrieve, and then code the match logic yourself. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Thiru, I'm glad it helped.
Please take the time it has saved you to fully understand the code you have. Document as necessary. When one of your colleagues picks up the code next time, they need to know what is going on.
| seagull wrote: |
| It looks to me like the best option is to put the SORT into a separate step ahead of the easytrieve, and then code the match logic yourself. |
It would be possible, I'm sure, for the full solution to be done with a SORT/xxxTOOL.
With no duplicates on either file, the Easytrieve file matching is a simple process. The problem was in how to compare variable-length data.
Although I am one for KISS, and would not myself code an "internal SORT" in Cobol, Easytrieve handles things differently. Each SORT and JOB is a seperate entity, which can share file definitions and "working storage" definitions. The Easytrieve "Virtual File Manager" provides a convenient method of defining and using "temporary" files, and even tables for binary searches. If the VIRTUAL file can be fitted into memory, it will be. This means you can define a load of temporary files to simplify the processing without having to include them in the JCL.
The Easytrieve SORT just uses the installed SORT product. With no record selection, it will run fairly well, though I don't know who is handling the IO for the input file (for the VIRTUAL, it is obviously Easytrieve).
For the solution, either "as is" in Easytrieve, or one with SORT/xxxTOOL. If the input files are "big", I would do the sorts seperately for the Easytrieve. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
