Iidbdb locations and updates

> One Question, two versions:
>
> Short Version:
>
> How can the master database, iidbdb, be queried for something like
> available location names?
>
> Long Version:
>
> How does one go about trying to provide a turnkey environment for
> naive end users to install and configure an application database?
> We don't want to perform miracles or rocket science.
> The default locations for user tables, log files etc. just aren't going to
> cut it. There doesn't seem to be a simple way of configuring things
> from within a shell scripts; it appears we must guide the installer through a
> maze of technical screens.


>Short Version:

The best way is to use Ingres's accessdb utility.  Run accessdb and
choose option Catalog(1), then choose option LocationNames(2).

You can also query iidbdb directly.  Say SQL IIDBDB, then do a
select * from iilocations.  To get a list of all the system catalogs,
do a   select table_name from iitables where system='S'.  There's
information on SOME of the system catalogs -- excluding underlying
tables and including views -- in the SQL manuals.

  Seth, sethg@zeus.dot.gov, grimes_s@eisner.decus.org
In article <1992Apr28.011416.21418@rossinc.com>, mickan@rossinc.com
(Michael J. Andrew) writesShort answer:

Connect to the iidbdb the same way you would to any database.  For basic
database information, the relevant system catalogs are iidatabase,
iiuser, iilocation, iidbaccess, and iiextend.  You can use "sql" or
"isql" to get help on these tables, and query them with normal SQL
statements or with "qbf".

If you want a list of _all_ the special system catalogs in the iidbdb,
create an empty database and query its "iitables" system catalog to get
a list of the tables, then compare this list to a similar list taken
from the iidbdb.  Restrict attention to tables owned by user "$ingres".

There is limited documentation on these special tables in Appendix D in
the INGRES/SQL Reference Manual (release 6.4).  Look at the last pages
of this appendix.

Long answer:

If I understand you correctly, you want to create a friendly application
that will take a naive user through _all_ of the steps needed to create
a new database.  For the most part, you _can_ write an application to do
this yourself, but there are significant technical and security issues.

Creating a database involves creation of objects that are privileged for
the OS (logicals, directories, user accounts), as well as operations
that are privileged from INGRES's point of view (creating users,
creating locations, creating a database, extending a database,
authorizing a user to access a private database, updating system
catalogs).

These are the steps I know about, and the standard method for doing
them.  We use VMS, so if you use Unix, you're on your own for the OS
steps.

1. Create the OS account for the dba account (the owner of the
database).  Use VMS's "authorize".

2. Create the INGRES authorization for the dba account.  Use INGRES's
"accessdb".

3. Create the areas for the database.  In VMS, these are logicals that
must be added to sys$manager:sylogical.com and defined by hand as well
(use VMS's "sysman" to do the latter). In addition, you may have to
create directories for data, checkpoint, journal, dump, and sort
locations using INGRES's utility ii_system:[ingres.utility]ingdefdev.com.

4. Create the locations for the database (data, checkpoint, journal,
dump, and sort(?)).  Use INGRES's "accessdb".

5. Create the database.  Use INGRES's "createdb" command.

6. Extend the database.  Use INGRES's "accessdb".

7. Authorize other users to use the database.  Use INGRES's "accessdb".

For the steps that involve OS operations, various system privileges are
needed.  You could write an application to do them, and install the
application with the necessary privileges.  In Unix, I think you can do
this with setuid (?).

For the steps that are done with "accessdb", you can do the same steps
in your own application, except for extending the database (since this
involves writing to the aaaaaaaa.cnf configuration file).  Try doing the
operations by hand yourself with "accessdb", but define the "ing_set" or
"ii_embed_set" logical to capture the queries.  You can put the same
queries into your own application.

Here are some of the technical problems you will face.  You won't have
any guarantee that your application will work from one version of INGRES
to the next, since Ingres doesn't guarantee that the system catalogs
won't change.  The application must connect to the iidbdb database with
the "+U" switch in order to update the system catalogs (iiuser,
iilocation, iidbaccess, iiextend), and any user of this application will
have to have the special switch on their INGRES account that authorizes
them to use the "+U" switch.  Ability to use the "+U" switch gives them
the privilege to mess up the system catalogs in _any_ database to which
they have access, and it gives them the privilege to make themselves an
INGRES superuser.  Also, the "+U" switch takes an exclusive lock on the
database, so while your application is connected to the iidbdb, no other
users will be able to make new connections to _any_ databases.

Good luck, and let us know how it goes.

--Mark Jaeger
University of Chicago   phone: (312) 702-0328
Graduate School of Business    fax: (312) 702-0233
1101 East 58th Street, W309  email: cs_mj@gsbvax.uchicago.edu
Chicago, IL  60637-1511, USA         (internet)


In response to the question about querying IIDBDB for available locations --

I have a program that does just that.  A connection is made to IIDBDB just
like any other data base, and the following query is issued:

SELECT lname, area
FROM iilocations ;

lname is the INGRES location name (such as II_DATABASE), area is the computer
system's representation of that area (for example, on a VAX, the logical
name INGRES$DB).

I'm sorry I didn't include the text of the original question.

Bill Koster
KOSTER@CDHF1.GSFC.NASA.GOV
Ingres Q & A
To William's Home Page
Email William