RTVSQLSRC is not an IBM command, so apparently it doesn't have to follow IBM's
syntax rules. CREATE INDEX syntax in V5R4 does not allow, CAST, SUBSTR, or
anything else except column names. See
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstxcindx.htm
Cf.
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzxcindx.htm where
ON table (column-name ...) has been replace with ON table (key-expression ...).

An index may show on green screens as a LF, but it is not one.  A DB2 INDEX is 
actually an ACCESS PATH.  As such it can utilize the new ENCODED VECTOR 
addressing for increased performance.

I am on V5R4M0. I ran a RTVSQLSRC to get the SQL source of a file that was
created with DDS and uses the SST. The SQL has all the fields and then the following

CAST (  SUBSTR ( AWCWO# , 1 , 6 ) AS CHAR(6) CCSID 37 ) ,
CAST (  SUBSTR ( AWCWO# , 7 , 1 ) AS CHAR(1) CCSID 37 )  

So according to this you can create an index using a substr in SQL.

One difference is that an SQL Index is better, performance wise.  On the other 
hand, if you ever need to change or recreate your SQL Index you will not have 
source code.

There may be internal differences, depending on what exactly you specify, but
your programs won't care. There are certain DDS logical files that SQL can't or
won't use (any query, actually). But if all you want is a key on the first 3
characters of a character field, and you create that with a DDS LF, SQL will use
it if the the query calls for it.

Forgive my ignorance but. What is the difference between an Index and a LF? When
an index is built using SQL on the iSeries it creates a LF object. Is the only
difference one is built with DDS and one with SQL?

Unless you're on V6R1, it can't be done as an SQL INDEX. The closest you can
come is an index on empname, and rely on your query pick out the first 3 characters.

Hi Dale,

Thanks for your response. I need to create only index not LF.
My requirement is initially a string on 3 length will be enterd on screen, i 
need to process those records only. But in my table a 10 length field is 
there, i need to substring that field for first 3 chars for all records and 
has to process baased on that substringed values. Please suggest me. We can 
use SQl quey with substr while fetching from table, but i need index only as 
client is insisting for that index.

If you're on V5R4 (or earlier), you can create the index with a DDS LF. See the
SST keyword in the "Programming DDS for physical and logical files" manual, SST
keyword. The example not only shows how to code SST, but uses the derived field
as a key field. The usage must be I (input only), which means the field will be
part of the input record.

In V5R4 you're stuck with column names. In V6R1 you're allowed to use
expressions for index keys, with certain restrictions. Basically, it has to be a
scalar function, and certain field types are excluded; SUBSTR is fine.

Hi All,

Good Morning!!
My requirement is to create an index over a Table with Substringing a value a 
field from Table.
For example i have a Table with 4 fileds say empid(5,0), empname(10A), empsal
(5,0) and edept(3A).No keys in table. 
I need to create an index on the above table, with key field as first three 
characters from empname(substringing empname from 1 to 3 positions). 
Is it possible if so please let me know how i can create.

Waiting for your responses
Pawan