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.
