[WEB400] DB2 & Visual Basic

["Varnadoe, Chris" <cvarnadoe@xxxxxxxxxx>]

Hi! I'm new to this list and fairly new to the iSeries. My company is a small, 
specialized insurance company and we use a policy management system (POINT) 
from Computer Sciences Corporation (CSC, formerly PMSC). We are an outsourcing 
customer and access their iSeries system via a T1 line. Their operating system 
is V5R1 and here we are using Client Access Express V5R1M0 with the latest 
service pack installed.

Here is my problem: I have written a Visual Basic 6.0 program that goes through 
ODBC to pull certain data out of the database and then store that data into a 
local SQL Server database. The query on one of the tables is excruciatingly 
slow. In working with Access and SQL Server my first thought was to add an 
index, but evidently the DB2 database uses "logical" files as indexes. In 
talking with the systems people at CSC, they felt like it was the "order by" 
clause on my SQL query that was causing the delay. This logical file has 13 
fields as the key and I only have 6 of them in my "order by" clause. I am used 
to the indexes being formed from the fields being used in the "where" clause. 
Below I have listed the SQL query that is taking so long (the questions marks 
just indicate where VB will put data). I am thinking about asking them to 
create a new logical file with the fields from my "where" clause, but since 
that costs me money I thought I would see if anyone here could give me some 
insight first.

select * from ASBYCPL1 
where BYASTX = ? and BYADNB = ? and BYAGTX = ? and BYBRNB = ? and BYEGNB = ? 
order by BYAACD, BYABCD, BYARTX, BYASTX, BYADNB, BYAOTX

Thanks,

Chris Varnadoe
Information Systems Manager
Southern Mutual Church Insurance Co.