CODEWITHSUNDEEP
c#.netodbcibm-midrange
larryr
larryr

Reputation: 1588

AS/400 query response time

What I'm doing....

I am connecting to an AS400 via ODBC (currently I have to use ODBC) in a C#/.Net application. The queries are dog slow. I run DBmonitor & query the file that it returns where QQIDXA = 'Y' and I'm being told to create indexes, that already exist. The reason codes are I1 & I3.

I have rebuilt the indexes, in the same order that the where clauses are, with no luck.

What can I do to improve my AS400 queries???

AHIA, LarryR...

Upvotes: 1

Views: 1835

Answers (3)

Tracy Probst
Tracy Probst

Reputation: 1859

A common problem I get with DB2/400 queries and indexes has to do with field attributes. If, for example, you're joining two tables using fields that do not have the same attributes, then the database may have to create a special, on-the-fly index after deriving a temporary field. For example, a Numeric(8,0) is not the same as a Decimal(11,3). Even though you can join them together, a temporary index is often created. This is an annoyance, to be sure. The fix I often use is to cast my fields in the join statement so that I'm always joining the fields together so with the correct attributes. Here is an example:

LEFT JOIN DBRQRQH ON DBRQH_COMPANY = DBWHS_COMPANY AND DBRQH_REQ_NUMBER = CAST(DBWHS_DOC_NBR AS NUMERIC(7,0))

The same can sometimes--though not as often--be true for your WHERE clause. If casting the joins does not help, try using CASTs with your selection criteria.

Here's another tip for query performance optimization. Start your C# program in a debug session with a breakpoint after you connect via ODBC and before you execute your query. Then find your ODBC job on the AS/400 (iSeries) by using WRKACTJOB JOB(QZDASOINIT). Look for the job that has your user profile on it. Or at least the user profile used when connecting. When you find that job, put it in debug mode with these two commands (from a green screen):

STRSRVJOB JOB(258094/QUSER/QZDASOINIT)

(Note: the job number will change depending on the ODBC job you find).

STRDBG UPDPROD(*YES)

Then run your query from the C# program and examine the job log of the selected QZDASOINIT job on the AS/400. When an SQL is run in a job that is in debug mode, you get all sorts of helpful stuff in the job log. When you're finished, remember to run ENDDBG and then ENDSRVJOB.

Upvotes: 3

David G
David G

Reputation: 4014

You might also want to consider posting your question on the MIDRANGE-L mailing list.

Lots of DB2 experts there.

Upvotes: 2

Buck Calabro
Buck Calabro

Reputation: 7648

First, some nomenclature. AS/400 is a very, very, very old machine. Now some people insist on calling successor models 'AS/400' out of habit. This may work against you when searching the web. IBM made several generations of midrange machines after AS/400. Try iSeries, System i, and IBM i.

The database on these midrange machines is a variant of DB2, and as such is tuned pretty much like any other database. The proper indexes are usually the first solution to database performance problems. Don't look only at the WHERE clause; consider any JOIN ON and ORDER BY as well.

IBM has software called Client Access (iSeries Access, IBM i Access) which has a free component called Navigator. The Navigator has a very good DB tool called Visual Explain. Have the midrange admin install Navigator on your PC.

IBM published several Redbooks on database tuning that might help:

Upvotes: 3

Related Questions