Re: Better SQL performance than SUBSTRING

Yes, I agree absolutely. Have it generate the object, retrieve the source
and put in a source member.

I strip out the library and use the Default collection DFTRDBCOL parameter
to specify the library to use to compile to.

If you use my iBuild command (www.sourceforge.net/projects/ibuild) you can
just put the build instructions in the header for how to build using
RUNSQLSTM.

The readme has an example of doing this.

On Fri, May 4, 2018 at 2:50 PM Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx> wrote:

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';

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD