COBOL DB2 Program tuning



IBM's flagship relational database management system

COBOL DB2 Program tuning

Postby hparthas1 » Wed Dec 15, 2010 11:49 pm

Hi,

I'm coding new COBOL DB2 program.

Could someone can let me know any tips which would improve the performance of the processing. I'm sure my program will process more than 10 millions of records everyday, and mostly its database insert/update code after validation process. I'm sorting the file based on the primary key in the file and its successors.

It would be great if there is any document to refer or and tips to code my program very effectively.

Thanks in advance for your valuable input.

Best Regards,
Harikrishnan
hparthas1
 
Posts: 7
Joined: Wed Dec 15, 2010 11:38 pm
Has thanked: 0 time
Been thanked: 0 time

Re: COBOL DB2 Program tuning

Postby dick scherrer » Thu Dec 16, 2010 2:00 am

Hello,

When tuning cobol/db2, improving the performance of the SELECTs used gains the most improvement. . .

How many rows are in the table to be updated? 10 million or some much larger number?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: COBOL DB2 Program tuning

Postby hparthas1 » Fri Dec 17, 2010 11:06 pm

Thanks for your input Dick scherrer.

Currently table contains 6 million records. From Mar, 2011 we will process 10 million records on monthly basis, also this might tale some time to get updated.

Is there any way to avoid other batch to fail when they try to access this database ? Also please advise about checkpoint frequency will impore in processing.

Thanks in advance....

Best Regards,
Harikrishnan
hparthas1
 
Posts: 7
Joined: Wed Dec 15, 2010 11:38 pm
Has thanked: 0 time
Been thanked: 0 time

Re: COBOL DB2 Program tuning

Postby BChat » Fri Dec 17, 2010 11:59 pm

Hi Harikrishnan,

Are you loading data from a file from a table ?

Good way to load table is to use the DB2 load utility, rather than INSERT statements.
Do all your validation from the file itself & then writing out the valid data to another dataset in the in the format required for loading (Do a sample download from the table & get the SYSPUNCH to know the format). You may use SORT OURTEC.
Next you use the load utility followed by a table repair (it will be in copy pending).

If you really need to do a lot of INSERT/UPDATE/DELETE to the tables, a common reason for failure is LOCK overflow; in most cases lock size is ROW.
You may need to change the lock size to ANY, do all your maintenace, and then put back the lock size to ROW.

Other reasons for bulk inserts can be the extents of the physical VSAM files reaching 255. Need to reallocate with bigger allocation / purge records from the table

Hope that helps,

Thanks,
Bchat
BChat
 
Posts: 19
Joined: Thu Jun 11, 2009 8:20 pm
Has thanked: 0 time
Been thanked: 0 time

Re: COBOL DB2 Program tuning

Postby dick scherrer » Sat Dec 18, 2010 2:56 am

Hello,

If a high % of the rows in the table are to be maintained (insert/update/delete), it is often much faster to unload the table, perform the maintenance on the sequential data, and reload the table. Proper scheduling will prevent other tasks from running while this is running.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post