SQL Optimise clause was( fast search or scan for words in selected field)

Well I see a leading % in the sample code I listed.

I believe IBM is constantly developing SQL and I reckon that Joe and
others will induce sufficient grit to get a pearl from this oyster.
IBM will not improve/develop CHAIN ops and I hope Joe  succeeds in
getting IBM to recognise the idiocy of having 2 different teams of
developers one for the RPG compiler and another for the RPG SQL
pre-compiler.
BTW

For fast SQL single record retreival I got this from the SQL ref
SC41-5612-02
---  copy starts  ----
Declare a cursor named UP_CUR, to be used in a PL/I program, that updates
the start date (PRSTDATE) and the end date (PRENDATE) columns in the
PROJECT table. The program must receive both of these values together with
the project number (PROJNO) value for each row. The declaration specifies
that the access path for the query be optimized for the retrieval of a
maximum of 2 rows. Even so, the program can retrieve more than 2 rows from
the result table. However, when more than 2 rows are retrieved,
performance could possibly degrade.

     EXEC SQL  DECLARE UP_CUR CURSOR FOR
                 SELECT PROJNO, PRSTDATE, PRENDATE
                   FROM PROJECT
                   FOR UPDATE OF PRSTDATE, PRENDATE
                   OPTIMIZE FOR 2 ROWS ;
----  Copy ends -----

This implies that one could say OPTIMIZE for 1 ROW.  Could not SQL be
programmed to recognise this clause and do an actual chain operation.
Another thing that slows repeated SQL single record access is the way
the RPG code is constructed to do repeated calls to SQL programs.
(Also a entry is written to the job msgq for each access, this is a
very slow operation)
These calls would be better off bound at compile time, I am sure
I am missing some details but thats what system programmers are for.
I still think that the way the SQL pre-compiler works in RPG is a
LAZY INEFFICIENT EXCUSE for SLOPPY S___ID CODERS.  IMO do away with
the pre-compiler and most of these performance issues will dissapear.

Frank Kolmann

>from: rob@xxxxxxxxx
>subject: RE: fast search or scan for words in selected field
>
>I think you missed the leading percent.
>
>But, since this retrieves a set versus a single record, I doubt if Joe
>would have much of an issue with it.
>
>Rob Berendt
--- snip----
>>
>>At 08:52 PM 9/11/2004, you wrote:
>>Dan to ignore case try this
>>SELECT * FROM cositm WHERE UCASE(ITDS) like '%CLIP%'
>>
>>
>>Frank Kolmann