RE: Sorting

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

IMO, RUNSQLSTM is not as flexible as QMQRYs. You could have a replacement 
variable for the key fields (comma-separated list, build with a loop in CL) 
in the CREATE INDEX or ORDER BY. You just need to remember that the SETVARs 
have to be 55-character variables, so you may need to split things up. Or 
get one of the generic EXECSQLSTM things that can do any statement.

I wonder about performance. If an index already exists, I think the RGZPFM 
over that index would be fastest. This is similar to CLRPFM being MUCH 
faster than DELETE FROM LIBRARY/FILE. If the index does not exist, it's a 
tossup.  A SELECT with ORDER BY that is run with STRQMQRY with 
OUTFILE(SAMEFILE) will likely do a sort, actually. This is faster than 
building an index, I believe. A test over a smaller file with STRDBG will 
give you optimizer messages. These will tell you what it does. If it has to 
create an index, anyway, then use STRQMQRY to build an index, then either 
RGZPFM on that index, or CPYF FILE(INDEX) to QTEMP.

NOTE NOTE NOTE
OUTPUT(*OUTFILE) of STRQMQRY will replace the contents with the result of 
the SELECT statement - in place - without any confirmation. This'd do what 
you want, but be sure to have a backup!!!

HTH

Vern

At 02:19 PM 4/30/2003 -0500, you wrote:
> I would probably use embedded SQL.  You could also update a source member
> and then use the RUNSQLSTM command to execute it.  That seems like extra
> work to me though.
>
> Is there any way to determine if particular fields are used in the sort more
> frequently than others?  Anything that could be done in the way of indexing
> will help.  Maybe you could log the selection requests to a file and look at
> it for patterns.  If you do decide to create any indexes I would suggest
> using SQL to create them.  SQL indexes use a larger buffer than DDS indexes.
>
> It's too bad you don't have a fixed number of key fields I liked the QMQRY
> suggestion earlier.
>
> Rick
>
> -----Original Message-----
> From: Metz, Zak [mailto:Zak_Metz@xxxxxx]
> Sent: Wednesday, April 30, 2003 2:06 PM
> To: Midrange Systems Technical Discussion
> Subject: RE: Sorting
> If I were to use SQL, what is the most straightforward way to build the
> SQL string and run it? Would that be embedded SQL with a prepare? If so,
> I have no objection, or enlighten me of a better way?