Problem solve Get help with specific problems with your technologies, process and projects.

Making sure your I/O routine doesn't degrade performance

Our company supports and maintains mainframe financial systems. In order to maintain a common set of source code, we use a concept of I/O routines that are passed function codes by calling programs (batch and CICS) to perform all type of I/O to the database. We currently support three databases, ADABASE, DB2 and VSAM.

One of our clients is insisting that we break up our DB2 I/O routines into smaller executables because the number of key columns has a direct effect on the number of cursors we have for reading prior and next. Some of the larger I/O routines exceed 200K in size.

Is there any benefit (CPU utilization, storage consumption, response time, etc.) to breaking these I/O routines into smaller chunks?

Well, you probably won't like my answer. I/O routines and "black boxes" are detrimental to DB2 performance. By funneling all requests to a centralized I/O routine performance suffers for any number of reasons. Some include:

* Not using relational features like joins, table expressions, and subqueries

* Filtering data in the program instead of in SQL predicates

* Requiring additional instructions which increases CPU time

In general, putting as much work as possible into the SQL is the way to go for performance. So, your I/O routine may make it easier for you to support multiple DBMS products and file formats but at the expense of degraded performance.

Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our .VO7aaqqaAFk.0@/search390>discussion forums.

This was last published in September 2003

Dig Deeper on IBM DB2 management

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.