|
View previous topic :: View next topic
|
| Author |
Message |
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
Hi,
My reqt is
Read a record from DB2 table (having Branch no, Acct ID) and then check whether the Acct ID from table is there in another flat file. Normally there are 5 or 6 occurances of the Acct ID in the flat file. Select the one which has the largest Effective date for that Acct ID.
Example data
DB2 Table
| Code: |
Branch # Acct ID
01 200
02 200
01 300 |
[b]Flat File[/b}
| Code: |
Branch Acct ID Eff Date
01 100 2004-01-01
01 100 2007-05-01
01 100 2006-01-01
00 100 2003-01-01
01 200 2010-01-01
01 200 2005-01-01
01 200 2008-01-01
01 300 2008-01-01
01 300 2010-01-01 |
Logic
1. Fetch the row from DB2 table
2. Then read the flat file and if the Acct ID from table matches with that in flat file, store that value in array and check whether the Eff date is greatest for each read of flat file for that Acct ID.
3. Store the Acct ID and corresponding greatst Exp date to output variable
4. Fetch the next record from DB2 table.
Concern
The concern here is I can start reading the flat file and store the values once it matches the Acct ID 200. Then check for greatest sys date for each read of flat file. But how to stop reading the flat file once it reaches next changed Acct number 300.
I should not lose reading the Acct number 300, because if the next DB2 acct id is 300, I may not be able to read 300 from flat file, since it is already read.
Since the flat file has millions of records, I can't make it VSAM.
Please help?
Thanks
Vinu |
|
| Back to top |
|
 |
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
I didnt make use of Array initially.
| Code: |
PERFORM 0000-MAIN-PARA UNTIL SQLCODE = 100
0000-MAIN-PARA
FETCH a record from DB2
MOVE 'N' TO WS-FLAG
MOVE 00010101 TO WS-SAVE-EFF-DT
PERFORM 1000-READ-FILE-PARA UNTIL EOF-FILE OR
(WS-FLAG= 'Y' AND WS-ACCT NOT= WS-DB2-ACCT-ID)
1000-READ-FILE-PARA.
READ FILEA INTO WS-FILEA
AT END SET EOF-FILE TO TRUE
GO TO 1000-EXIT
NOT AT END
IF WS-ACCT = WS-DB2-ACCT
CONTINUE
ELSE
GO TO 1000-EXIT
END-IF
END-READ.
IF WS-ACCT = WS-DB2-ACCT
MOVE 'Y' TO WS-FLAG
IF WS-EFF-DT > WS-SAVE-EFF-DT
MOVE WS-EFF-DT TO WS-SAVE-EFF-DT
ELSE
CONTINUE
END-IF |
END-IF.
In this code, if I am reading the changed record, I end up losing it in the next read for the different Acct ID from DB2.
Experts please help.
Thanks
Vinu |
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8697
Location: Dubuque, Iowa, USA
|
|
|
|
| Quote: |
| Read a record from DB2 table |
This cannot be done. DB2 tables have rows, not record. Terminology is critical to IT and so far you've proved you do not know the terminology, which raises the question about just how much you DO know.
Is the flat file sorted? Is it sorted by the Acct ID? If not, none of the logic you've mentioned will work.
Why not read the flat file and build a VSAM file of the highest dates; you can then use the VSAM file to directly read the desired data? This may require a sort of the flat file.
| Code: |
READ FILEA INTO WS-FILEA
AT END SET EOF-FILE TO TRUE
MOVE WS-ACCT TO WS-HOLD-ACCT.
PERFORM 0000-MAIN-PARA UNTIL SQLCODE = 100
0000-MAIN-PARA
FETCH a record from DB2
MOVE 'N' TO WS-FLAG
MOVE 00010101 TO WS-SAVE-EFF-DT
PERFORM 1000-READ-FILE-PARA
UNTIL EOF-FILE
OR WS-HOLD-ACCT NOT = WS-DB2-ACCT-ID |
Use a hold field to keep track of what the current value of the flat file acct id is. |
|
| Back to top |
|
|
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
Robert,
Sorry. as you said it is a row in DB2 table.
The flat file is already sorted based on Acct ID.
Since I was told not to VSAM, I thought of stiring all the matchuing records from flat file to array.
I will use a hold field as per ur suggestion.
Thanks
Vinu |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
Sort on Acct ID and Date
then you would not need to store anything.
this is simple match merge logic - db2 cursor and file.
look at the programs button at top of page and read thru the logic
provided for different types of merges.
you are making this much more difficult than necessary. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
after a few minutes of thought:
sort on ACCT ID and EFFECTIVE DATE,
dropping dups on ACCT ID and retaining largest Effective date for that Acct ID.
This is a simple sort exercise, many examples of which can be found in the sort forums.
or do what everyone else does and just post a new thread asking for the solution.
then, you match merge is even easier because you do not have to deal with dups during your program. |
|
| Back to top |
|
|
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
Thanks Dick. It worked well.
I have used ICETOOL for sorting and retaining the first record which has duplicates.
Thanks
Vinu |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
Vinu,
am very glad my suggestion helped.
and congratulations on the implementation |
|
| Back to top |
|
|
|
|
