From: sfc%tpghq@uunet.UU.NET (Steve Caswell)
Subject: Re: Sorting on multiple locations ?
Organization: National Radio Astronomy Observatory
Date: Sat, 25 Jul 1992 16:38:51 GMT

> 
> >From leath@atlas.cs.upei.ca Fri Jul 24 12:24:11 1992
> Newsgroups: comp.databases.ingres
> Path: tpghq!uunet!uunet!utcsri!torn!csd.unb.ca!atlas.cs.upei.ca!leath
> From: leath@atlas.cs.upei.ca (Stephen Leath)
> Subject: Sorting on multiple locations ?
> Message-ID: <1992Jul24.162411.22586@jupiter.sun.csd.unb.ca>
> Originator: leath@atlas.cs.upei.ca
> Sender: news@jupiter.sun.csd.unb.ca
> Organization: University of Prince Edward Island
> Date: Fri, 24 Jul 1992 16:24:11 GMT
> Lines: 7
> 
> We are trying to setup ingres so that sorts will sort over multiple
> locations, but, have had no luck so far. We having trouble understanding
> the use and setting of the ingres logical II_WORKDIR_XX_NN. Has anyone
> accomplished this and if so, is there anything else to do besides setting
> the logical ?
> 
> Thanks in advance 
> 
> 
The following is an article I wrote for the latest newsletter published by
The Palmer Group.  The information contained herein was derived from technical
information provided by Ingres.  If anyone would like to be added to our
mailing list to receive a coy of The Palmer Gropu newsletter, you can drop
me an e-mail and I'll get yopu set up.


Redirecting Server Sort Space

New to INGRES 6.4 is a set of environmental variables which allow
you to redirect the server sort space.  Sorting is performed by
INGRES when a query contains a DISTINCT, ORDER BY, or GROUP
BY clause, during joins, and as part of primary and secondary index
generation.  By default, sort space is directed to the location
containing a relation data file.  If the relation is split across multiple
locations, sorting is performed on the first file location.  This could
cause problems if the location is nearly full or is heavily used.

To create alternate sort locations, you must first create an INGRES
directory path on each disk that will serve as a sort location.  The
path to be defined is [INGRES.WORK] on VMS and
ingres/work/default on UNIX.  Permissions for the work directory on
VMS must be S:RWE,O:RWED,G:RE,W:RE, and on UNIX must be
read, write, and execute to the user id 'ingres'.

Next, create logical names to point to the new locations.  The logical
name should only point to the name of the device containing the
location, not the full directory path.  The naming convention for the
logical names is II_WORKDIR_XX_N, where XX is the two letter
identification code of the installation, and the N is a number between
1-16.  You can determine the two letter identification code by looking
at the name of your server.  You should omit the _XX if you are
running a production installation on VMS.

For example, to create a sort location on BIG$DISK in a production
VMS installation, create a directory BIG$DISK:[INGRES.WORK], then
define II_WORKDIR_1 BIG$DISK:.

To create a sort location on /usr1/temp for an installation with
identifier code of 'T1' on UNIX, create a directory
/usr1/temp/ingres/work/default, then ingsetenv II_WORKDIR_T1_1
/usr1/temp from the 'ingres' account.

INGRES begins choosing alternate sort locations by looking for the
first logical name in sequence (II_WORKDIR_XX_1) and proceeding
in numerical order.  It terminates the logical name search when it
finds an undefined or null logical name.

Translation of sort location logical names is performed at server
start-up.  If you need to change sort locations, you should first shut
down all INGRES DBMS servers, make the location changes, and
restart the servers.

Please do not confuse the server sort space with the front-end tools
sort space.  There is an environment variable called II_TEMPORARY
which is used to define an alternate sorting location for the front-end
tools.  This functionality was available in INGRES 6.3 and continues
with 6.4.

There is a new trace point DM1440 which will show any work locations being
used by the server.  You can use this trace point by connecting to a server
and issuing the set trace point command.  The following example shows a single
work location defined on a UNIX platform under the ingres installation with an
installation identifier of PG:

   $ ingprenv | grep WORK
   II_WORKDIR_PG_1=/usr1/ingsort1/sort_area
   $ sql iidbdb
   * set trace point dm1440;
   \p\g

   Executing . . .

   ********************************************************************

   === List of Work Locations for this Server ===

   /usr1/ingsort1/sort_area

   === End of List ===

   ********************************************************************
   \q

Ingres did a terrible job documenting how to use this new feature.  Ingres, if
you are listening, this level of documentation is unacceptable.  When new
features are added, you MUST tell us how to use them in the same release, not
one or two releases down the road.  New features without adequate documentation
are useless.


Steve Caswell           |   (404) 448-7727    |  "The opinions expressed are my
Principal Consultant    |   sfc@tpghq.com     |   own.  They may not be perfect,
The Palmer Group        |   uunet!tpghq!sfc   |   but they're all I've got."

Newsgroups: comp.databases.ingres