MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » January 2013

Re: how to sort a file in place?



fixed

On 23 Jan 2013 07:23, Stone, Joel wrote:
I would like to sort WORKFILE1 in place, preferably using SQL.

Something to consider is "What is the reason for wanting to do so?"; and having shared that, contributors might offer alternatives.

Is this possible?

In effect, yes, but not with SQL. The RGZPFM CL command provides a means to reorganize the physical data in a database physical file member.

Physical order of data is inconsequential to the relational model; i.e. where data is unordered sets. And so for SQL as a language to provide data access for the RDBMS, there is effectively nothing that the SQL provides to physically order the data within a TABLE. Collation is an attribute of the request to extract data [the run-time SELECT], rather than an attribute of the physical data. That is because there can be many different possible collations for the same set of data; most notably, DESCending vs ASCending, but also according to data encoding and language\locale preferences, and across any variety of expressions\columns.

Or must I create another table with SQL CREATE and then CPYF it back
to the original file.

That is one of a number of ways to effect a desired physical order. But the value from doing so is limited, except for a static TABLE and for the arrival sequence access method. My preference is to use the RGZPFM with the KEYFILE parameter naming an access path that defines my desired collation.






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact