MVSFORUMS.com

[siddharth]

Hello,

I am working on a task which has the requirements as mentioned below:

1. Read a DB2 Table.
2. Select only unique records from the table.
3. For every record selected call a subroutine which does some processing.

Note: Database contains 150,000 records

Assumptions:
1. Programs are written in COBOL 390.
2. DB2 Version is 7.0


I have thought of schemes to accomplish the above task. They are:

Scheme A:
1. Unload the DB2 table on a flat file.
2. Sort the file to eliminate duplicates.
3. Main program reads this unloaded file with unique records and for every record read, the main program calls the subroutine.
4. The subroutine processes the record and returns a value.
5. Repeat steps 3 & 4 until end of unloaded input file.

Scheme B:
1. Unload the DB2 table on a flat file.
2. Sort the file to eliminate duplicates.
3. Main program reads this unloaded file with unique records and loads an internal array with the entire input file.
4. For every record read from the internal array the main program calls the subroutine.
5. The subroutine processes the record and returns a value.
6. Repeat steps 3 through 5 until end of unloaded input file.

Scheme C:
1. Declare a cursor in the main program where the unique records are selected from the database.
2. For every record fetched from the cursor the subroutine is called by the main program.
3. The subroutine processes the record and returns a value.
4. Repeat steps 2 & 3 until there are no more records in the cursor.

Please suggest the most optimised way of doing the task assigned to me.
Also, if there is any other scheme which could be used in doing this?

Thank You.
Siddharth.

[kolusu]

Siddharth,

Personaly this is how I rate the 3 solutions listed above

Solution A: Very efficient and easy to maintain. You can try to unload unique keys with db2 utility itself there by eliminating the sort step. code an unload Job with the distinct verb and verify its runtime against the (normal unload + sort step to remove duplicates

Solution B: Bad design. If you are loading into internal table then it would be an extra process as well as storage.

Solution c: can be worth trying since the total no: of records are only 150,000.

Hope this helps...

cheers

kolusu

[slade]

Hi Sid,

Here's another approach that avoids the sort pass (assuming you can get the recs off the DB2 tbl in the desired order.

Write a COBOL pgm that drops the dups:

(pseudo code)

[sriramla]

IMHO, option A is what I choose if the program is relatively stable (not expecting any changes in near future). Reason being, for any change in the requirement, I need to change the JCL unload step, my record layout in the program among other things.

Option 3 is good if there is an index built on the columns in WHERE Clause (with regular runstats/reorg done). Also, the program is easy to maintain.

You can have these points also during your design phase.

[haatvedt]

I would use a variation of option 1. I would use an E35 sort exit program to call the subroutine when a unique record is encountered during the sort. This could be done very easily by saving the record and then comparing the current record to the previous record. This would eiliminate two passes of the data file.

In your case perhaps the savings would not be great, but I make it a point to save resources whenever possible. There is a complexity factor to take into consideration. However, a sort exit program is not complicated, so I would use the sort exit program.

good luck. ps... there are plenty of examples of sort exit programs in Cobol.

chuck.
_________________
Chuck Haatvedt

email --> clastnameatcharterdotnet

(replace lastname, at, dot with appropriate
characters)