Re: SORT on the iSeries... -- MIDRANGE-L

   I am way behind on parsing some threads, so appologize if this already
   satisfactorily answered.  Your informant is both wrong and misleading
   dated.  The AS/400 world both supports earlier philosophies and supports
   new ways of doing things.  There are multiple opportunities for sorted
   data, and multiple opportunities for abuse.  Like this list is so
   overwhelming in traffic volume, some newbies might get turned off.

   Decades ago on systems that the AS/400 grew out of, such as S/34 & S/36,
   the way we sorted records was a choice of:

   * Physically rearrange large masses of records, which tended to eat a lot
   of disk space, and take a while to get done.  This harkens back to a
   philosophy long before current computers, of physically sorting the
   records before feeding them into a program that processed one record at a
   time.  There may be business applications where that makes sense today,
   but I do not have much experience with them.  More often, the records that
   need to be accessed are dependent on the records processed so far.  The
   400 supports this today via FMTDTA & QSORT.

   * Create an address index, in which the index records contain the basis of
   the sort, and the addresses of the detail records, then sort this index,
   which tended to go extremely fast, and not eat so much disk work space ...
   this was called an ADDROUT sort.  In today's programming world, we can
   create and uncreate this kind of index on the fly ... a software job
   stream creates the logical index needed, the main program processes the
   data in the specified sorted sequence, then the index is uncreated, or
   rather cleared is the modern approach.  Even though some software may
   explicitly call for a particular logical index, the 400 OS might use a
   different one that seems more efficient for the current task, so the inner
   logic needs to be explicit what records to include exclude, not rely
   totally on specified logic path.

   * Create a logical path accessing the records in the sequence desired.  In
   ancient S/36 days this was called an alternate index, but today it is
   called a logical file.  This was like a permanent ADDROUT file, kept
   current with any updates to the physical records.  There is some overhead
   maintaining both the physical file and lots of logicals, offset by the
   sheer speed of accessing the physical records any time we need them listed
   in whatever sorted sequence, and low overhead for the system compared to
   actually rearranging the records.

   You need to be careful in how you name & store logical files, to avoid
   hassles when restoring from backup.

   Since the logical data may jump around a lot relative to how the data is
   organized, you want to be very careful about whether you want to use the
   design feature of blocking where you read in chunks of records at a time. 
   Now we can do a periodic reorganization of the data in a physical file,
   such as to get rid of deleted records (or records coded for deletion),
   where we specify a particular logical to be used as the sequence for the
   physical.  Generally you can't have other people accessing a physical file
   during the reorganization, and this can be a burden on maintaining the
   contents of various logicals over the file being rearranged.

   Some files deliberately add records to the end, as opposed to dynamically
   removing deleted, then looking for a hole to add new records, in the
   interests of access speed during peak loads.

   At program compile time, one set of files can be designated, then at
   program execution time, the CL can use "data base override file" to use
   different logicals than those used in the compile, which works so long as
   they have the same layout.  That applies to normal access, but it is
   potentially a different reality for sub-programs & embedded SQL.

   Since the AS/400 replaced S/36 & S/38, and as the AS/400 advanced to new
   names, there has been an explosion of other programming languages, such as
   SQL/400 supporting the equivalent of ADDROUT logical sequences on the fly,
   of selected portions of a mass of physical files, and sub-sets of joins
   between selected portions of multiple files.

   Logical files are not only sequencing.  They are also include omit.  They
   can be joins of multiple physical files, but they do have some
   limitations.

   For example, I recently needed to modify a program to sort the data by a
   field that is not in the primary input file & I discovered that when you
   do a join, all the data used for sequencing has to be in whichever file
   you designate as the primary, so I used OPNQRYF ("open query file")
   technique to build the work file then sued as the primary input.  I do not
   advise a newbie trying to start out with "open query file", you need to
   learn CL first, and get pretty good in CL, which is unlike any control
   language on prior platforms.  However, "open query file" does have a lot
   of parallels with embedded parameters of S/36 which is what I was on
   before AS/400.  I think SQL is a much cleaner programming approach than
   "open query file", but my access to good documentation influences which
   tools I rely on the most.

   I use DDS in the design of all my logical files, but SQL can also be used
   for this.  Most of the SQL that I write is embedded inside RPG programs. 
   I assume it can also be embedded in COBOL.  Basically in RPG / COBOL we
   are reading one record of a file at a time, all column fields of the file
   record, but with embedded SQL we can process some join of multiple files,
   selected records, entire columns, at a time.  I use both the DO IT in some
   subroutine, and the CURSOR approach of setting up a pattern of wading
   through a set of data.

   I have heard of API's, not used many of them, need to learn more, since I
   can see they are very powerful.  I have not yet had occasion to explore
   the QSHELL so there may be opportunities there I am missing out on.

   I also use Query/400 a lot for data mining, quickie and simple reports.

     List,

     This is an iSeries newbie question so please bare with me. We are
     getting
     started on the project of migrating applications (all batch) from VSE
     (mainframe) to iSeries. I was "told" by someone that is a long-time
     AS/400
     person that "there is no system sort on the iSeries". I find that very
     hard
     to believe. There must be some system provided sorting mechanism. I see
     that
     ILE/COBOL fully supports the SORT verb so that must be interfacing with
     a
     system-provided utility.

     I searched for "sort" in the iSeries information center and could not
     find
     any explicit references to a sort program or utility.

     Perhaps the person meant that there is no "sort program" that you can
     explicitly execute in batch (as you can in the VSE world). Could someone
     authoratively clear this up for me please? TIA

     --
     Regards,

     Michael Rosinger
     Systems Programmer / DBA
     Computer Credit, Inc.
     640 West Fourth Street
     Winston-Salem, NC  27101
     336-761-1524
     m rosinger at cciws dot com

     --
     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.

   -
   Al Macintyre
   http://en.wikipedia.org/wiki/User:AlMac
   http://www.ryze.com/go/Al9Mac
   BPCS/400 Computer Janitor ... see
   http://radio.weblogs.com/0107846/stories/2002/11/08/bpcsDocSources.html