Q

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 first published in September 2003

Dig deeper on IBM DB2 management

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSOA

SearchSQLServer

Close