Re: how to sort a file in place?

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.