MVSFORUMS.com

[nishantrk]

I have to read a files which has around 10 million records.Then query a DB2 tableemployee category table to get his dept ,based on the indicator is get from the DB2 table do a write for the records.
Now the table has only 200 rows , does it make sense to have the DB2 table data loaded into a COBOL table ,then search the COBOL table .Instead of querying the DB2 table again and again?
Which methods is more efficient in general and is there any limitation on the how big the COBOL table is ,for it to be efficient ??

[dbzTHEdinosauer]

considering the 10 million 'look-ups' that you have to make,
which means 10 million suspensions of your program,
means that the cobol table look-up will finish
a day or two before the one with db2 for each record.
(the day difference is in jest!!)

there are no fast figures -

with only 200 rows, there is a good chance that db2
will have the complete table in memory
so, what we are avoiding is program suspension.
(as well as db2 code other than the actual code used for row acquisition).

a lot has to do with your site set-up. how big a machine you have,
how busy is it.

the larger the cobol table, the more working-storage you need,
the more resource you need to start your job.
if you exceed 4k, you may encounter paging.

I have had good results with 3,000 item internal cobol table and 1 million input records.

keep the item of your cobol internal table small,
the item fields the same datatype (PIC) as the file fields used in the comparison -
---convert as you load the table if necessary -
load from an ordered cursor -
make the table ODO -
use Binary Search -
and always check the last found against current file value before searching -
up the buffer count for the input and output files
(start with BUFNO=10 or 20 and experiment

I assume the program is batch and amode any, rmode 31.

any table properly designed will be efficient.
as to where is the cut-off point between when to load an internal cobol table
and when to do db2 access each time,
depends - and I am not going to suggest other than what I already have.
_________________
Dick Brenholtz
American living in Varel, Germany

[papadi]

Whichever way you choose to implement, something that can save considerable time is checking if "this" record has the same dept as the previous lookup.

If the input is already in sequence by employee or dept/employee the number of lookups could nearly be eliminated completely. . .
_________________
All the best,

di

[dbzTHEdinosauer]

sorting the input file (10 million records) by dept no,
would potentially reduce the db2 selects by the number of dept_no not present in the file
and you would not need the cobol internal table.

but, how long does it take to sort 10 million records
once for this process
and then again for the next?
_________________
Dick Brenholtz
American living in Varel, Germany

[kolusu]

IMHO it is a good idea to load the table into internal table. Cobol has a limitation for internal tables of 16 MB which is a lot. Even in future if the table increases drastically you can still outperform 10 million SQL selects. The binary search is much more efficient/cheaper than a sql call.

Alternatively just dump the table into a flat file and load that it into an internal table there by eliminating the DB2 table access from the table.

Kolusu

[nishantrk]

Thanks all for your views and suggestion...
Actually i had only mentioned part of code...the actual code does a lot more processing and has been optimized to do that..my requirement is just a small enhancement to that code...But since the code didn't have any SQL statements before this..wanted to add the solution that will have as little impacted on performance as possible..
So i guess as everyone suggested since its a small table..i will load it into a Cobol table and do a binary search...

[papadi]

[Terry_Heinze]

[Terry_Heinze]

Both Dick and papadi have pointed out a very important fact. Checking your current search argument against your previous can save a lot of time and many programmers overlook this.
_________________
....Terry