Re: how to sort a file in place?

"If a keyed file is identified in the Key file (KEYFILE) parameter,
the system reorganizes the member by changing the physical sequence
of the records in storage to either match the keyed sequence of the
physical file member's access path, or to match the access path of a
logical file member that is defined over the physical file.
Reorganization can decrease file processing time when a program is
reading sequentially through a keyed physical file or through a
keyed logical file."

So I'd say we know what it does. Sure, the individual pages won't be next
to each other, but within each page the records will be in order.

Charles


On Wed, Jan 23, 2013 at 1:42 PM, Dan Kimmel <dkimmel@xxxxxxxxxxxxxxx> wrote:

What does RGZPFM actually accomplish. We know only two things.
1) it eliminates deleted records
2) it changes the RRN to match the order of the index given in KEYFILE.

We don't know that it actually arranges the records of the table in
anything resembling sequential order, though it does give that appearance
when using DSPPFM.

We assume that it reorganizes data into some efficient structure. We know
this only because some sequential accesses have improved performance. How
that is actually accomplished probably has no relationship to any kind of
sequential organization of the physical records.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, January 23, 2013 12:30 PM
To: midrange-l@xxxxxxxxxxxx
Subject: 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.

--
Regards, Chuck
--
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: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.



--
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: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.