Re: Better SQL performance than SUBSTRING

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

That's a very helpful comment, Alan. I'd also add that it's critical 
that you take that SQL statement and put it in a source member, which 
you can then later run with RUNSQLSTM.  This way, you have a means of 
recreating an object should it accidentally be deleted.

On 5/4/2018 2:56 PM, Alan Campin wrote:
> Always use Operations Navigator or the new Data base support in ACS to
> build any SQL object. It will give you the proper syntax and everything you
> need. Never create any SQL object by hand.
>
> Here is the output of Ops Nav for an index it built for me.
>
> /* Creating index DEVAGC549.MY_PERFORMANCE */
> CREATE OR REPLACE INDEX DEVAGC549.MY_PERFORMANCE ON
> DEVAGC549.PLANT_PERFORMANCE_MASTER_TEST_RESULTS (MASTER_TEST_NUMBER  ASC)
> WHERE Substring( ENVIRONMENT_RAN_IN, 1, 3) = 'CP1';
>
> /* Setting system index name to PLPERM_I01 for DEVAGC549.MY_PERFORMANCE */
> RENAME INDEX DEVAGC549.MY_PERFORMANCE TO SYSTEM NAME PLPERM_I01;
>
> /* Setting label text for DEVAGC549.MY_PERFORMANCE */
> LABEL ON INDEX DEVAGC549.MY_PERFORMANCE IS 'My Performance Index';