Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
lal_arun_272

New User


Joined: 09 Oct 2006
Posts: 31
Location: Bangalore

PostPosted: Sun Oct 07, 2007 4:28 pm    Post subject: How can i improve the execution time of my db2 cobol program
Reply with quote

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
Back to top
View user's profile Send private message

CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Sun Oct 07, 2007 4:40 pm    Post subject:
Reply with quote

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....
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Sun Oct 07, 2007 4:43 pm    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 149
Location: Ottawa Canada

PostPosted: Tue Oct 09, 2007 7:42 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
martin9

Active User


Joined: 01 Mar 2006
Posts: 288
Location: Basel, Switzerland

PostPosted: Tue Oct 09, 2007 8:01 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Cobol GUI (like Editor) Kala Mainframe COBOL Programming 14 Wed May 10, 2017 12:30 pm
No new posts IMS T-Pipe queue counts in a COBOL Pr... Siva NKK Kothamasu IMS DB/DC 0 Tue May 09, 2017 6:31 pm
No new posts DSNACCOX (can it be run on 1 db/ts, t... SRICOBSAS DB2 3 Sat May 06, 2017 12:59 am
No new posts COBOL Code Parsers and Lineage Establ... balimanja COBOL Programming 2 Tue May 02, 2017 3:30 am
No new posts Cobol upgrade - source code missing f... gthmrj IBM Tools 1 Wed Apr 26, 2017 6:04 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us