IBM Mainframe Forum

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

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?

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

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

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.