How can i improve the execution time of my db2 cobol program

lal_arun_272 · New User Joined: 09 Oct 2006 Posts: 31 Location: Bangalore

Hi

My job is getting timeout when i am executing my program . (I can't chage my job param)

I have to read one input file and need to check ithe ID from the file is present in the Table(it is having 900000 rows) if it is present i am updating child table ..

my input file is having 150 rows.

Is there anyway I can reduce the execution time of this query .. or program
(select id into : temp_id from table where id = :file_id )

I tryed the same using cursor (first table ID ...and compare each with the file id ) also ... it's does not given any time diff for execution ..

Thanks
Arun

CICS Guy · Posted: Sun Oct 07, 2007 4:40 pm

Only 150 rows for input?
It might be revealing to display to sysout a timestamp each time you retrieve one of those rows....That would show you if each row took the same amount of time of if the time kept getting longer and longer with each row....

dbzTHEdinosauer · Posted: Sun Oct 07, 2007 4:43 pm

Have you done an EXPLAIN on your singleton select? If not, perform one.

try not to mix attribute names in your problem description; makes it difficult to understand your problem.
Files have records which have fields which contain values.
tables have rows which have columns which contain values.

you have an input file, having 150 records, each has a field containing an ID.

You wish to determine if you primary db2 table has a row which has a column containing an id = to the file id. When yes, you need to update a second table.

900,000 rows is not very many.
Is the db2 column containing the ID defined in an index?
Can you have duplicate id's in the DB2 table?
Is the host variable containing the file id directly compatable as a predicate in your where clause or must db2 convert to an appropriate data type?

Using a cursor is not going to buy you anything except more time spent comparing.

A singleton select should be sufficient. If the column containing the id is not within a unique index, you could be table scanning. But you need to do an explain on your select statement.

And, why do you think you are not having good execution time?

TG Murphy · Posted: Tue Oct 09, 2007 7:42 pm

So with 150 input records you are launching your singleton select 150 times against a table that contains almost a million rows.

Questions:

How long does your process take?
What do you expect it should take?
Does your singleton select have index support? I suspect it does not because if it did your job would execute very fast. Without index support you are scanning 1 million rows and you are doing this scan 150 times.

If you do not have index support, then ask the DBAs whether it is appropriate to define an index.

The DBAs may say YES. But they may say NO. If they say NO then restructure your logic so that you only scan the DB2 table once. Load your input file into a COBOL table and search the table after each FETCH to see if you have a match.

martin9 · Posted: Tue Oct 09, 2007 8:01 pm

hy arun,

it seems to me that the id check, takes to much time.

make sure the id is a key (indexed), therefore there
will be no full table scan.

if this field is already indexed, then you have to check the
EXPLAIN output.

be also aware, if you do not use the same picture clause
to use in a query, this could also lead to a full table scan (i.e C++).
for cobol you can use the new COMP-5 in queries.

regards,

martin9