SQL Question

[Jeff Young <cooljeff913@xxxxxxxxx>]

I have an application that needs to search a physical file based on the value 
entered by the user appearing anywhere in either of 2 fields, each of which is 
50 bytes.
The SQL that I have set up for this is as follows:
Declare data Cursor for
Select * from OEHISHED where TRIM(hhorn1) || TRIM(hhorn2) like('%user data%) 
order by Hhorn1.
The statement works fine, but the file in question has a very large number of 
records and the initial OPEN of the cursor takes over 30 seconds.  This is an 
interactive program.
Running the program in DEBUG mode, I do not get any recommendation for indexes, 
just a statement that the optimizer selected arrival mode.
Does anyone have any suggestions for improving the time to open the cursor?
Once I have opened the cursor, the select takes almost no time.
I am processing a maximum of 70 records per selection, and am using the feature 
FOR 70 ROWS on the FETCH.

There is a logical file keyed by HHORN1 with a selection criteria that HHORN1 
must not be blank, but the optimizer rejects this file due to the static select.
When I remove the select, the optimizer rejects this file due to performance 
reasons.
It produces a recommendation that I create an index using '0' of the leftmost 
order by fields.

TIA,
 
Jeff Young 
Sr. Programmer Analyst
Dynax Solutions, Inc.
A wholly owned subsidiary of enherent Corp.
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2 
IBM  Certified Specialist- e(logo) server i5Series Technical Solutions Designer 
V5R3
IBM  Certified Specialist- e(logo)server i5Series Technical Solutions 
Implementer V5R3


 
____________________________________________________________________________________
Don't pick lemons.
See all the new 2007 cars at Yahoo! Autos.
http://autos.yahoo.com/new_cars.html