Efficient Queries on DB2/400

[briby]

Hi, I am a PC programmer so pardon my lack of DB2/AS400 knowledge - I'm trying to learn. I am currently trying to increase the performance of a client/server application we have written in Delphi and running queries against the DB2 tables on our iSeries.

An example of a query used by the program:
select * from CVB7CPL2 where (V7BKSV<>'AC') AND (V7BMSV='N') and (V7CVSV='C') order by V7AINB

I have done some reading and the query is now:
select ABCDE, ..., FGHIH from CVB7CPL2 where (V7BKSV<>'AC') AND (V7BMSV='N') and (V7CVSV='C') order by V7AINB for read only

My main query is regarding indexes, the table CVB7CPL2 is actually a 'logical' table (I assume that's an index) of the physical table CVB7CPP. But this doesn't include the other fields that are in the WHERE clause. Would it be better to query against the physical table and create an index that encompasses all the fields in the where conditions. Or an index for each field used in the where conditions (sometimes it will run with more conditions).

If I query the logical does this means that the DB2 Optimizer will be cut out of the equation?
Any information/handy hints would be gratefully received.

 

[blom0344]

First of all a logical is NOT an index, it can be seen as the equivalent of a database view from purely relational databases like ORACLE, DB2 UDB, SQL server.
Secondly, using ORDER BY can be a real performance drag, since it requires massive resources, do not use it if you sort the data at the front end.
The use of indexes can be beneficial, but if you have just a few possible values for a specific field, a full scan can be faster, because the whole table will be accessed.
Logicals are primarily used for:
1. Showing just a portion of the records (like the active ones) from the underlying physical.
2. Creating sorted data to speed up queries for performance sake. We are using sometimes 30 different logicals for a given physical.
The for read only aims at the isolation level, the best performance is to use queries that will read anything, no matter if the data is already committed. This avoids performance-loss through locks.
Try to create a logical that is already sorted the way you want containing only the data you want......... T. Blom
Information analyst
tbl@shimano-eu.com