Ingres Database Reference
| *** This document is formatted for a FIXED PITCH FONT and ***
| *** an 80 CHARACTER display ***
I N G R E S
F R E Q U E N T L Y A S K E D Q U E S T I O N S ( F A Q )
| This version: 1.8 08SEP97
| Last version: 1.7 21MAY95
| Archived at ftp.naiua.org in /pub/ingres
| Copyright (C) 1997 by Roy Hann
This document is COPYRIGHTED to control its distribution and to prevent
it from being used for profit. PLEASE DO NOT DISTRIBUTE IT WITHOUT THIS
DISCLAIMER OR THE COPYRIGHT NOTICE. Replication of this document by any
means is explicitly encouraged, provided that the content is not altered
in any way.
------------------------ D I S C L A I M E R --------------------------
| BKB ENGINEERING LTD., COMMON SENSE COMPUTING INC., RAYTHEON CO., AND THE
| NAIUA WILL TAKE ABSOLUTELY NO RESPONSIBILITY FOR THE CONTENT OF THIS FAQ.
| I PREPARED IT ON MY OWN TIME, WITHOUT THE PERMISSION, ENDORSEMENT OR
| KNOWLEDGE OF ANY REPRESENTATIVE OF BKB ENGINEERING. This is a
voluntary effort by many contributors, coordinated by me, who may or
may not be identified in "Contributors." Their respective employers are
in no way responsible for the content of this FAQ. Nothing in this FAQ
should be regarded as true. Any item of information may become
obsolete or out of date at any time. Information about undocumented
features should always be regarded as potentially harmful.
The coordinator, contributors, and distributors of this document make
no representations as to the correctness of the information contained
herein, and make no promises to correct any errors or ommissions. You
use this information at your own peril.
| Roy Hann rhann@tnc.com
| BKB Engineering Ltd., (comments on FAQ to: ingfaq@naiua.org)
11211-76th Avenue,
| Edmonton, Alberta Tel: (403)497-2409
| T6G 0K2 FAX: (403)436-6055
| Canada
-----------------------------------------------------------------------
If you don't see the '---- END OF FAQ ----' line, something is missing.
-----------------------------------------------------------------------
TABLE OF CONTENTS
00. Notes
00.001 Contributors
00.002 Notations
00.003 Acronyms and Abbreviations
00.004 Changes in this Version
00.005 Useful telephone numbers and E-mail addresses
| 01. Ingres
01.001 What is Ingres?
01.002 Where can I get PD Ingres?
| 01.003 Where can I get CA-Ingres?
| 01.004 How much does Ingres cost?
| 01.005 Does Ingres support SQL?
| 01.006 What are the features of Ingres?
| 01.007 What platform should I run Ingres on?
| 01.008 Can I run Ingres on machine ABC?
| 01.009 Can I run Ingres over network ABC?
| 01.010 Where is the Ingres FAQ?
| 01.011 Where can I get the Ingres TPC benchmark results?
| 01.012 Is Postgres anything to do with Ingres?
| 01.013 Is Ingres secure?
| 01.014 Does Ingres run on the PC under MS-DOS or MS-Windows?
| 01.015 Where can I subscribe to the Info-Ingres mailing list?
01.016 Can I get the FAQ by E-mail?
01.017 Can I get the FAQ on discette?
01.018 Is the FAQ available on WWW?
| 01.019 Where can I get a comparison of RDBMS products?
| 01.020 Can I use a RAID with Ingres?
|
| 02. Miscellaneous date questions
|
| 02.001 Is 2000 A.D. a leap year?
| 02.002 Are there any Y2K (Year 2000) problems with Ingres?
03. Contributed software
| 03.001 ftp site for Ingres tools
03.002 ingperl, sqlperl and DBperl
03.003 tclsql
03.004 $II_SYSTEM/ingres/sig
| 03.005 NAIUA tool set
03.006 onyx
04. Third-Party Software
04.002 Can I use ODBC (MS-Windows Open Data Base Connectivity)?
04.003 What is Grafsman?
04.004 What is GQL?
| 04.005 Can I use JDBC (Java connectivity)?
05. Books
| 05.001 What is a good book on Ingres?
05.002 What is a good book on relational databases?
05.003 Where can I get a description of the SQL standard?
05.004 Where can I get a (BNF) definition of SQL syntax?
06. Reviews/Articles
| 06.001 Are there any Ingres periodicals?
| 06.002 What are sources of reports on Ingres?
| 06.003 Where can I find recent articles about Ingres?
| 07. Ingres/Advisor and Tech support
| 07.001 What is Ingres/Advisor?
| 07.002 How can I access Ingres/Advisor?
07.003 What is ProAlert?
07.004 How do I log trouble calls?
07.006 How do I vote on a SIR?
07.007 Tech support is useless, what do I do now?
| 07.008 What is CA-TCC?
| 07.009 How do I find out about patches?
08. General Frequently-Asked-Questions
| 08.001 How can I store a large object in an Ingres database?
08.002 How can I suppress execution (I only want to see the QEP)?
08.003 Other than this FAQ, what is a good source of practical info?
| 08.005 Why is Ingres reporting an incorrect row count?
| 08.006 Can I do an outer join?
08.007 How can I add/delete/alter a column in a table?
08.008 How can I grant other people access to my tables?
08.009 How can I change the ownership of a table/report/form?
08.010 What is a QEP and how do I interpret it?
08.011 How can I ask for just the first N rows?
08.012 Can I override the optimizer with my own execution plan?
08.013 How can I tell why my search is taking so long?
08.014 What are TIDs?
08.015 How can I display the proper Fkey labels using an emulator?
| 08.016 Does Ingres support row-level locking?
08.017 How can I invoke emacs (or any other editor) within isql?
08.018 How can I change the displayed precision in isql?
08.019 How can I change the destructive behaviour of the Return key?
08.020 Does anyone at CA read comp.databases.ingres?
08.021 Is it possible to generate FRS forms dynamically at run-time?
08.022 Why are modifications allowed to the system catalogs...?
| 08.023 How can I bulk authorize Ingres users?
08.024 How can I load ASCII-delimited files created by PC `X' base?
08.025 How can I import dates from PC `X' base?
08.026 How can I create export files for a PC application?
| 08.027 What Ingres files can I delete (to recover space)?
08.028 How can I assign sequential record numbers?
08.029 How can I do a case-insensitive string match with wild-cards?
| 08.030 How can I generate surrogate keys for best performance?
08.031 How do I find rows that don't match a row in another table?
08.032 Why do I have a SWAP and a NOSWAP authorization string?
08.033 How can I automatically report the location of an ESQL error?
08.034 What are some good trace points?
| 08.035 Does Ingres have an API I can use?
08.036 How can I find duplicate rows in a table?
| 08.037 How can I create a WWW interface to an Ingres database?
| 08.038 Is there any point in doing REPEATED INSERT?
| 08.039 Can you get unloaddb to go directly to tape?
09. RDBMS
09.001 What processes do what?
09.002 How are extra iislave (disc) processes started?
09.003 How do I recover an inconsistent database?
09.004 What are "compressed" table structures?
09.005 Why might I configure more than 1 DBMS server process?
09.006 How can I direct batch or reports to a particular server?
| 09.007 How does Ingres use sort space?
| 09.008 When does Ingres switch to a new journal?
09.009 Should I use mirroring on my data locations?
09.010 Can I use mirroring as a super-fast checkpoint?
09.011 Can I mirror my log file?
09.012 Can I have more than 1 DBMS page cache?
09.013 Can I have more than 1 fast commit server?
| 09.014 What is the Ingres Search Accelerator?
| 09.015 When and how do I use the Ingres Search Accelerator?
| 09.016 How can I find out more about the Ingres Search Accelerator?
09.017 Why am I running out of QSF memory (E_OP0886 and E_QS0001)?
| 09.018 How can I assign particular jobs to particular servers?
| 09.019 Should I set II_DMFRCP_STOP_ON_INCONS_DB?
| 09.020 How do I recover after a fire/flood in the machine room?
10. QUEL
10.001 What is QUEL?
10.002 How different is QUEL from SQL?
10.003 Should I learn QUEL?
10.004 Should I convert my QUEL to SQL?
10.005 Are there any utilities for converting QUEL to SQL?
11. VIFRED
12. Report Writer
12.001 How can I put ASCII TABs in a report?
12.002 How do I put escape characters in a report?
12.003 How can I number pages as "Page n of m"
12.004 How do I submit a report to run at a given time?
13. QBF
14. ABF
14.002 What causes C compilation errors while building an ABF image?
15. RBF
| 16. OpenROAD/Windows4GL/W4GL
16.001 Why is Windows4GL so slow to react to database events?
16.002 How can I force a window to stay visible on top?
| 16.003 Is anyone working on an OpenROAD/W4GL FAQ?
| 16.004 How easily can I use the OpenROAD-Oracle driver?
| 16.005 Where can I get more information about OpenROAD?
17. VISION
18. NET
18.001 What do I do if I can't connect?
18.002 How do I configure NET?
18.003 What is the 'ingvalidpw' program for?
| 18.004 How can I restart NET without restarting Ingres?
18.005 How can I shut down the communications servers?
| 18.006 Which Ingres processes do I need on a client system?
18.007 How do I configure different protocols?
18.008 What is the address and listen information in netu?
18.009 How does NET figure out its TCP port numbers?
18.010 Can I have 2 installations running NET on the same system?
18.011 How can I use different protocol...to access the same server?
18.012 Can NET use all of my LAN boards?
18.013 Can I use NET over a LAN bridge?
18.014 What about NET over WANs?
18.015 What if I change my password on my NET server username?
18.016 What do communication servers do?
18.017 Will my applications run over NET unchanged?
18.018 Will my applications perform over NET unchanged?
18.019 What's the GCA protocol?
18.020 Does NET present any special security problems?
| 18.021 Can I run Ingres clients without NET?
19. STAR
20. Embedded
20.001 What C compilers are supported?
20.002 Can I use C++?
21. Database Procedures
21.001 What are database procedures?
21.002 Database Procedures and QEP's
21.003 Database Procedures and Locking
21.004 Can I flush out procedures without shutting down the server?
21.005 Database Procedures and Security
21.006 Database Procedures and Cacheing
30. errlog.log
30.001 My error log is full of E_SC022F_BAD_GCA_READ. What's wrong?
30.002 How can I trim errlog.log?
| 90. Ingres on Unix (Solaris, AIX, OSF/1 etc.)
90.001 How can I un-nice iidbms (Ultrix)?
| 90.002 Can Ingres make use of PrestoServe (Ultrix)?
90.003 What do the iislave processes do?
90.004 How many iislaves do I need?
90.005 Why is a raw log file faster on UNIX?
90.006 Should I stripe my raw log file on UNIX?
| 90.007 How do Ingres processes communicate on UNIX?
| 90.008 How does Ingres maintain database integrity on UNIX?
90.009 Should I stripe my data locations on UNIX?
90.010 Where do I start looking with a Unix performance problem?
90.011 Which filesystem type should I choose? (SVR4)
90.012 Will VxFS give me better performance? (SVR4)
| 90.013 What kernel resources does Ingres need? (SVR4)
| 90.014 What process limits could affect Ingres? (SVR4)
90.015 What CPU scheduler parameters should I set up? (SVR4)
90.016 Should I run the DBMS server as a real time process? (SVR4)
| 90.017 Why can't Ingres allocate shared memory? (SunOS/Solaris)
| 91. Ingres on VMS/OpenVMS
| 92. Ingres on OS/2
| 93. Ingres on Windows NT
| 93.001 How can I keep Ingres running after the user ingres logs off?
93.002 Do I need NT Server (formerly NTAS)?
99. User Groups
99.001 How can I contact the NAIUA? (North America)
99.002 How can I contact the UKIUA? (United Kingdom)
99.003 How can I contact my local users group? (US)
99.004 How can I contact my local users group? (Canada)
99.005 How can I contact my users group? (South Africa)
| 99.006 How can I contact my users group? (Europe)
99.007 How can I contact my users group? (Irish Republic)
99.008 How can I contact my local user group? (Australia)
| 99.009 How can I contact my local user group? (India)
| SECTION 0 -- NOTES
|
00.001 Contributors
This FAQ is the work of numerous individuals working on it on their
own time. (At least) the following people contributed:
Steve Caswell sfc@tpghq.com
Darrin Chaney
Laurie Comeau lcomeau@unb.ca
Michael Dorfman MDORFMAN@hamp.hampshire.edu
| Robert Griffith rcg@swl.msd.ray.com
Tracy Gulliver tmg@unb.ca
Roy Hann rhann@tnc.com
John Hascall john@iastate.edu
Tim Holmes timh@psammead.demon.co.uk
Jeffrey Horn horn@cs.wisc.edu
Bruce Horrocks bh@granby.demon.co.uk
Angela Lamb
| Mike Leo mal@visi.com
Brig McCoy brigc@world.std.com
Carole McMillan carole@cent1.lancs.ac.uk
Bob Salnick salnick@dejavu.spk.wa.us
| Please direct all corrections, suggestions and complaints to the following
| email address: ingfaq@naiua.org
| At this time, that will direct the messages to Bob Griffith and Roy Hann.
| Roy still hopes to be involved with the FAQ, but it will be to a lesser
| degree.
| New contributors are always welcome. General expressions of praise
| and gratitude can be directed to any and all of the above.
|
None of these people presently are employees of Computer Associates;
they may or may not have other official links to the company.
00.002 Notations
Any non-trivial changes made since the immediately preceding version of
the FAQ are identified by a `|' at the left of the page. (If anyone
misses some issues and wants a custom marked-up version, send mail to
rhann@tnc.com indicating the version number of the last version you saw.
Version 1.0 is the first version ever.)
There are certainly numerous references to trademarked terms in this
document. Failure to acknowledge the registered owner of the trademark
should not be construed as an attempt to contest or userp the trademark.
00.003 Acronyms and Abbreviations
| 3GL so-called 3rd generation language (eg C, Cobol, FORTRAN)
ANSI American National Standards Institute
API Application Programming Interface
BBS Bulletin Board System
| CA Computer Associates International, Inc.
DBA Data Base Administrator
DBMS Data Base Management System
DML Data Manipulation Language
FAQ Frequently Asked Questions (Answered)
FRS (INGRES) Forms Run-Time System
ISO International Standards Organization
ITSEC International Technology Security Evaluation Criteria
LAN Local Area Network
NAIUA North American INGRES Users Association
NCSC National Computer Security Center
OLTP On-Line Transaction Processing
PC (IBM-style) Personal Computer
PD Public Domain
RDBMS Relational Data Base Management System
SIR System Improvement Request
TPC Transaction Processing Council (and its benchmarks -A, -B, -C)
Dramatis Personae
RTI Relational Technology Inc.
ASK The ASK Group (f. A. Sandra Kurtzig, founder)
CA Computer Associates
Internet Shorthand (by popular demand)
AFAIK ...as far as I know...
BTW ...by the way...
FWIW ...for what it's worth...
IMHO ...in my [anything but] humble opinion...
IMO ...in my opinion...
RTFM Read the manual!
TIA ...thanks in advance...
| YMMV ...your mileage may vary--indicates uncertainty
:-) smiley face--indicates levity
;-) wink--indicates irony
:-( sad face--indicates regret
00.004 Changes in this Version
(All minor corrections and clarifications are indicated with a vertical
bar at the left of the page. This section lists only changes which
are not signalled sufficiently clearly with the bar at the the left.)
| ** All references to CA-OpenIngres and CA-OpenROAD have been changed to
| OpenIngres and OpenROAD respectively, consistent with current CA
| practice.
| 01 Sections 01.019 and 01.020 have been added.
| 02 Sections 02.001 and 02.002 have benn added.
| 03 Section 03.005 has been added.
| 04 Section 04.005 has been added.
| 07 Section 07.004 has been restored, Section 07.009 has been added.
| 08 Sections 08.030, 08.037, 08.038 and 08.039 have been added.
| 09 Mike Leo has replaced section 09.010
| 14 Section 14.001 has been deleted.
| 16 Section 16.005 has been added.
| 91 All sections deleted.
|
| 92 All sections deleted.
|
| 99 Major revisions to virtually all sections; section 99.009 has been added
|
|
00.005 Useful telephone numbers and E-mail addresses
Phone Numbers
===== =======
Computer Associates sales information . . . . . . . . . . . 1-800-CALL-CAI
| CA-World '97 conference and reservation information . . . . 1-800-CA-INFO97
| INGRES Technical Support . . . . . . . . . . . . . . . . . (516)342-5500
| E-Mail Addresses & URLs
| ====== ========= = ====
| mgmt@naiua.org . . . . . . . . . . . NAIUA info and membership enquiries
| ftp.naiua.org (/pub/ingres) . . . . . . . . . . . . . . . INGRES ftp site
| http://www.naiua.org . . . . . . . . . . . . . . . . . . . NAIUA Web site
| http://www.cai.com . . . . . . . . . . . . . Computer Associates Web site
| http://www.caworld.com. . . . . . . . . . . . . . . CA World '97 Web site
Also see section 99 of the FAQ for the address and telephone number of
IUA officers in various countries.
| SECTION 1 -- INGRES
| 01.001 What is INGRES? %Roy Hann
| INGRES is a relational database management system.
|
There are two distinct strains of INGRES; there is so-called
"University" Ingres, which is in the public domain, and there is a
| commercial version of INGRES developed and marketed by Computer
| Associates. Most of this FAQ is devoted to the commercial version
| which is called OpenIngres.
University Ingres is also known as Berkeley Ingres, Ingres89, and
"distributed" Ingres. (The latter term is unfortunate because it is
hard to know whether someone is talking about public domain
"University" Ingres, or the distributed database management
| capabilities of OpenIngres. The term "distributed" should probably be
| avoided.) University Ingres is the original version of Ingres
| developed at UC Berekely during the 1970s to demonstrate the concept of
| a relational database. It has a plausible claim to being the first
| implementation of a relational database management system since it
| pre-dates IBM's System R. This program eventually evolved into the
| first versions of the commercial product marketed by Relational
| Technology Inc. (eventually bought by The ASK Group and then by
| Computer Associates). It has some of the features of the currently
available commercial version, but is architecturally different; the DML
is QUEL, and the performance and reliability are only fair to
adequate. NB: NONE of this FAQ is related to University Ingres unless
EXPLICITLY noted.
| OpenIngres is a commercial product and costs money to buy. It is a
| robust, state-of-the-art RDBMS with a reputation for including
| first-class development tools. The database server component runs on
| all the major Unix derivatives, as well as VMS, Microsoft Windows NT,
| and even on Novell Netware as an NLM. The Ingres client components run
| on all of these too, and also on AXP VMS, Microsoft DOS and Microsoft
| Windows.
| As well as a database engine, the OpenIngres product suite includes a
| range of development and enquiry tools and components for implementing
| distributed databases. The database engine and tools are usually
| referred to collectively as "Ingres" without distinction.
| --------------------
| Trivium: INGRES is an acronym for INteractive Graphics REtrieval System
| (revealing the nature of the project out of which the experiments with
| relational databases arose). By happy accident, there was also a
| French artist by the same name: Jean Auguste Dominique Ingres
| (1780-1867). (A highly placed source who wishes to remain anonymous
| confirms that the selection of the name WAS an accident.) Warning to
| those with no social life: The book entitled "Ingres's Eroticized
| Bodies" by Carol Ockman, ISBN: 0300059612, has nothing to do with
| software.
01.002 Where can I get PD Ingres?
University Ingres 8.9 is in the public domain. It is available for
anonymous ftp from many sites around the world. It is also distributed
as unsupported or contributed software by some vendors, such as
Digital Equipment Corporation.
ftp Sites:
==========
s2k-ftp.cs.berkeley.edu pub/ingres
This site also has the `linux' port of Ingres 8.9. Many additional
sites can be located using "archie" and other mechanisms.
Contact: ingres@postgres.berkeley.edu
There are no known ports of University Ingres to Xenix, MS-DOS,
Windows NT, or any other non-Unix operating system. [If you are aware
of counter-examples please let me know at rhann@tnc.com] There have been
various bold announcements of efforts to do some of these other ports,
but so far none has come to fruit.
| 01.003 Where can I get OpenIngres?
| OpenIngres is a commercial product. For information, pricing and
authoritative availability information contact your local Computer
Associates office.
| Australia: (61)(2)923-2066
Austria: (43)(1)894-1913
Belgium: (32)(2)773 28 11
Canada: (905)676-6700
Denmark: (45)(42)95 86 00
France: (33)(1)40-97-50-50
Germany: (49)(6151)949-0
Holland: (31)(3402)483 45
Ireland: (353)(1)478 0800
New Zealand: (64)(4)801-7654
UK: (44)(753)5777 33
US: 1-800-225-5224
Or call International Operations at 1-516-342-5224 for the number of
your local office.
01.004 How much does INGRES cost?
| University Ingres is free. OpenIngres is not.
The cost varies a lot depending on the class of machine you intend
| to use, the number of users, and what components of OpenIngres you want.
| It can be a little or a lot. Contact your Computer Associates office.
| 01.005 Does OpenIngres support SQL?
| OpenIngres supports SQL. OpenIngres 1.x is compliant with the ANSI/ISO
| Entry-Level SQL92 (SQL2) standard and also has some of the
| Intermediate-Level features. Embedded SQL and embedded Dynamic SQL are
| also fully supported. (See section 05.003 below for SQL2 references.)
| Note that OpenIngres also supports QUEL and embedded QUEL, which it
inherits from University Ingres. Computer Associates de-emphasizes
QUEL for obvious commercial reasons. Because QUEL has considerable
| technical advantages over SQL, OpenIngres adopted SQL relatively late
| (c. 1986) and perhaps for this reason there is a lingering
| misapprehension that OpenIngres still does not support SQL.
University Ingres supports only QUEL and embedded QUEL. (See 03.006
for a description of `onyx' which provides an SQL to QUEL interface for
University Ingres.)
| 01.006 What are the features of OpenIngres?
DATATYPES
=========
| OpenIngres supports the following native datatypes:
i1 1-byte signed integers
i2 2-byte signed integers
i4 4-byte signed integers
f4 4-byte floating point
f8 8-byte floating point
cN N-byte fixed length PRINTING character strings (1<=N<=2000)
char(N) N-byte fixed length ASCII character strings (1<=N<=2000)
varchar(N) N-byte variable length ASCII strings (1<=N<=2000)
text(N) N-byte variable length ASCII, except NUL (1<=N<=2000)
date 12-byte date and/or time (absolute or interval)
money 8-byte money type (locally defined currency)
| byte 1-byte unsigned quantity
| decimal N-byte fixed decimal
| long varchar N-byte variable length ASCII strings (1<=N<=2Gb)
| long byte N-byte variable length binary data (1<=N<=2Gb)
Columns of the above types may be made nullable, or may be assigned a
| default value. OpenOpenIngres 1.x allows user definable default values.
table_key 8-byte surrogate key (optionally system maintained)
object_key 16-byte surrogate key (optionally system maintained)
| In addition, an optional OpenIngres component, the Object Management
Extension, allows the programmer to define abstract datatypes and
operators on those datatypes and the native datatypes. The new types
and operators can be used in any context without restriction.
TABLE ORGANIZATION
==================
| OpenIngres allows base tables (and secondary indices) to be organized as:
ISAM
compressed ISAM
hash tables
compressed hash tables
heaps
compressed heaps
sorted heaps
compressed sorted heaps
B-trees
compressed B-trees
Tables may have up to 300 columns, and a row may be up to 2000 bytes.
Tables may have up to 8 million pages of 2kb each (16Gb). The number
of rows that will fit in 8 million pages depends on the row size.
There is no practical limit on the number of tables in a database.
There is no practical limit on the number of databases on a system.
Tables may be reorganized, relocated and re-indexed on-line.
| A table may be distributed over multiple OpenIngres locations.
| OpenIngres locations can be defined on an unlimited number of physical
drives.
| OpenIngres uses the native file system of the platform on which it is
running. A "database" is (one or more) directories, and tables
are files within the directory.
OPERATORS
=========
| OpenIngres supports all the SQL92 operators, and QUEL.
DISTRIBUTED DATABASE CAPABILITIES
=========== ======== ============
| OpenIngres applications can connect over a network to remote databases
| using OpenIngres/Net. That is to say that the application does not
necessarily have to be running on the same machine as the database.
| An application can use OpenIngres/Net to connect to more than one remote
database simultaneously (BUT NOT INTEROPERATIVELY; ie. it can't join
together two tables, one from each database.) More importantly changes
made to the databases cannot be guaranteed to have referential
integrity. To achieve this, special coding is required, or the
| application should use OpenIngres/Star.
| Using OpenIngres/STAR databases can be distributed over multiple host
| systems. Using OpenIngres/STAR a table in database `A' CAN be joined with
a table in database `B'.
| OpenIngres also provides gateway products that allow OpenIngres to access
"foreign" databases. Gateways exist for a number of products such
as Rdb, DB2 and IMS.
| OpenIngres can act as an ODBC server.
SECURITY
========
| Using SQL OpenIngres provides the usual per-user security constraints.
Users can be selectively permitted to read, update, append and
delete, on a table by table basis.
| Using the optional OpenIngres/Knowledge Management Extensions it
is also possible to define and manage groups of users and user roles.
Both of these make security management much simpler.
QUEL users can additionally control access by time of day, day of
the week, terminal used, and the content of the record. These
extra QUEL constraints ARE NOT IMPOSED ON SQL USERS.
For users with special security needs, there is a secure version
| of OpenIngres called OpenIngres/Enhanced Security.
See section 01.013 for further information.
INTEGRITY
=========
Integrity constraints on the data are imposed at the level of the
database engine and cannot be subverted, except that integrity
constraints are not enforced during table load operations using the
`copy' command. Integrity constraints can also be imposed in form
definitions, but these are imposed only if the user happens to use
form which includes the constraints (or validations as they are
called in `vifred'.)
| Declarative referential integrity constraints are supported.
Checkpoints of the database can be done on-line and can be written
to disc or tape.
| 01.007 What platform should I run OpenIngres on?
| There is no single good answer to this question, although it pops up
regularly. It depends on dozens, maybe even hundreds of factors.
| The best you can probably hope for is some advice on the KIND of system
| to run. You can then contemplate factors like the vendor's quality of
| service, the operating costs, the kind of deal you can cut, and a host
| of intangibles--like do you feel comfortable dealing with the company?
| No one can help you with these.
| If you are going out to buy hardware to run OpenIngres, and you don't
have an installed base or any other factors affecting your decision
| other than just finding the "best" OpenIngres platform, then you should
| first decide how you want to run OpenIngres.
| You have three or four choices: (1) you can run the OpenIngres server
| and the OpenIngres applications on a central system in the traditional
| mainframe style. (2) You can run the OpenIngres server on a central
| database host and have one or more OpenIngres application servers
| accessing the database through OpenIngres/NET. (3) You can have the
| OpenIngres server run on a database host and have the applications run
| on desktop clients such as PCs or Unix workstations. (4) You can do a
| mix of all of the above, with X-terminals thrown in to cloud the issue
| still further.
The pros and cons of the various approaches are too numerous to go
| through in detail here now (a future version of the FAQ may attempt a
| discussion of this subject). For now, suffice it to say that a single
| central system and a number of "dumb" character-cell terminals will
| probably be the cheapest solution and will not raise any networking
| issues. A client/server solution with a dedicated central database
| host and intelligent desk-top clients will probably get the best
| performance out of any given database host but could be expensive if
| the clients aren't already in place.
Neither of these generalizations will always be true of course, but
| they may serve to help the hardware selection process. If the database
| server and the applications are going to run on a central system, you
| may need a more powerful machine. If you just run the database server
| on the central system and have smart clients, you may be able to get
| away with a more modest machine. Maybe. As always, job mix, work load
| peaks, access patterns and a myriad other things can influence what you
| need.
Having selected your strategy, you can move on to select specific
hardware. If the decision is unconstrained by any requirement for
compatibility with existing hardware or the need to exploit existing
expertise, you could look at benchmark results. The "benchmark"
benchmark is the suite of TPC benchmarks (TPC-A, -B and -C).
| Unfortunately OpenIngres is not submitted to the Transaction Processing
Council for evaluation. Furthermore, these are presently regarded with
some suspicion because -A and -B have been accused of being open to
manipulation. Indeed the same claim has been made about the whole TPC
organization. Eventually these issues will be resolved, but for now,
the benchmarks are probably still quite useful for comparing hardware
platforms running a specific DBMS product, even if they are not
reliable for comparing different DBMS products. Even if the benchmarks
were above suspicion, they should still only figure as one factor among
many.
| No matter what platform you choose, you must configure it correctly to
| get the best performance out ot it. A good hardware vendor will be
| willing to help you with this, indeed their willingness to help could
| reasonably be a selection criterion. Proper configuration can include
| tuning system software parameters, installing optional software
| designed to improve database performance, and choosing the right
| hardware components in the first place. It is possible to misconfigure
| any system. Note that workstations are generally designed and
| configured to optimize number crunching and sequential access to
| contiguous regions of disc--for signal and image processing
| applications. A database server has very different needs.
| A relational database application will be disc bound (usually), so it
| pays to buy fast discs. In most relational databases you tend to get
| lots of little records spread all over the disc so transfer time will
| usually be irrelevant--access time (rotational latency and seek-time)
| will dominate. Fast discs are currently able to achieve average seek
| times of 8-9msec and average rotational latency of 4.3msec.
| A fast disc is wasted without a controller that can take advantage of
| its speed. Choose a fast controller too. The controller should not
| only be fast with one disc, it should exhibit a linear increase in
| throughput as discs are added.
| No OpenIngres system should have fewer than 3 physical discs. To
| achieve the maximum level of security from disaster you will want to
| ensure that you can keep your database, your transaction log and your
| journal files physically separate. If this is done, then even in the
| event of a catastrophic failure of a disc drive you would be able to
| recover the database to the state in which it was at the precise
| instant of the failure. A RAID might be a very good alternative.
Some routine database operations require space equal to 3 times the
| space occupied by the largest table. Plan to have sufficient free disc
| space at ALL times to allow you to do a "modify" on your largest
| table.
| Extra RAM is never wasted.
A high-capacity tape back-up device would be convenient.
| 01.008 Can I run OpenIngres on machine ABC?
| It is believed that the entire OpenIngres product is presently available
for at least the following machines/operating systems:
Intel machines running Windows NT
| Intel machines running OS/2 (strictly Net) --OpenROAD/W4GL in beta
Bull DPX/20
Convex (** see below)
DEC Alpha OSF/1
DEC VAX BSD
DEC VAX Ultrix
DEC VAX VMS
DG AViiON DG/UX
HP 9000 HP/UX
IBM HACMP/6000
IBM RS/6000 AIX
ICL DRS 6000
ICL VME
MOTOROLA 88000 System V
NCR Series 3000
NCR Series 3600 (massively parallel)
PYRAMID OSx BSD
PYRAMID SYSTEM V
SCO Open Desktop
SCO UNIX
SEQUENT DYNIX/PTX
SIEMENS MX
Sun SPARCsystems Solaris 2.x
Sun SPARCsystems SunOS 4.1.x
TANDEM Integrity Nonstop-UX
| In addition, the OpenIngres clients are available for:
386/486 machines running MS-DOS
386/486 machines running MS-Windows
DEC AXP OpenVMS
There is NO server product available for MS-DOS or MS-Windows.
| There is NO OpenIngres product available for Silicon Graphics, but this
| is under development and should be available by late 97/early 98.
|
| [** OpenIngres 6.3 runs on the Convex C2 and C3 under Covex OS 10.1
but the current/future status of this product is unknown to me. Check
with your local Computer Associates office.]
| The NAIUA web site currently lists platforms where OpenIngres products were
| recently made generally available, as well as platforms which are
| currently in Beta release. This information is furnished to the NAIUA
| by Computer Associates representatives. You can find this information
| on the following web page:
|
| http://www.naiua.org/faqs.html
|
Be sure to contact your local Computer Associates office for
authoritative information.
| 01.009 Can I run OpenIngres over network ABC?
| There are OpenIngres/NET network adaptors for at least:
TCP/IP
DECnet
SNA LU0
SNA LU62
Wollongong TCP/IP
DEC UCX TCP/IP
IBM TCP/IP
KNET TCP/IP
Be sure to contact your Computer Associates office for authoritative
information.
01.010 Where is the INGRES FAQ?
| The INGRES FAQ (~300kb) is archived at:
| ftp.naiua.org in /pub/ingres
| Updates to the FAQ seem to get posted very infrequently at the moment,
| especially when I am distracted by actual work.
The following is an example of the dialogue with the FTP server; lines
that include user input are marked with a `->' symbol:
-> ftp ftp.naiua.org
| Connected to ftp.naiua.org.
...
-> Name (ftp.naiua.org:): anonymous
331 Guest login ok, send your complete e-mail address as password.
-> Password: juser@somewhere.org
...
230 Guest login ok, access restrictions apply.
-> ftp> cd /pub/ingres
...
250 CWD command successful.
-> ftp> get Ingres-FAQ
200 PORT command successful.
150 Opening ASCII mode data connection for Ingres-FAQ (234847 bytes).
226 Transfer complete.
local: Ingres-FAQ remote: Ingres-FAQ
240460 bytes received in 45 seconds (5.2 Kbytes/s)
-> ftp> quit
221 Goodbye.
| 01.011 Where can I get the OpenIngres TPC benchmark results?
| Ingres is not submitted for TPC benchmarking (or at least the
| results are not submitted to TPC for publication). This may soon change;
| see below.
| You can get the TPC bylaws and the specifications for TPC-A, TPC-B, and
| TPC-C, as well as the results for systems that ARE tested from the
| Transaction Processing Council web site:
| http://www.tpc.org/bench.descrip.html
| The Transaction Processing Council (TPC) can be contacted c/o
| Shanley Public Relations
| 777 N. First Street, Suite 600
San Jose, CA 95112-6311
|
| Tel (408) 295-8894
| FAX (408) 295-9768
shanley@cup.portal.com
| The Transaction Processing Council is a non-profit organization formed
| in 1988 to develop standard benchmarks for transaction processing
systems. The council has more than 40 members consisting of RDBMS
vendors and hardware vendors.
Be aware that there is presently considerable controversy surrounding
the validity of the TPC-A and TPC-B benchmarks. Some commentators have
asserted that certain vendors were able to exploit unrealistic features
of the benchmark specifications to achieve results that are better than
they would have obtained with a less imaginative interpretation of what
was required. So far the same criticism has not been made about the
more realistic TPC-C benchmark.
| The thing to keep in mind when looking at some of these results is that
| a system's performance is expressed (among other ways) as a ratio of
| the number of transactions per second per dollar (tps/$). This means
| that if you can get tps high enough, you can spend ridiculous amounts
| of money and still get a pretty good tps/$ ratio. For instance, if you
| have a really fast 64-bit CPU it may be advantageous to spend an
| astronomical sum to eliminate disc I/O entirely by buying enough RAM to
| load the entire database into memory--say 40Gb of RAM--because the tps
| will be so high that it will still yield a reasonable--even
| attractive--tps/$ ratio. It remains to be shown that this gives any
| insight into the performance to be expected from the same software
| running on more pedestrian hardware.
|
While this may make the TPC-A and -B benchmarks less useful for
comparing different database products, they may still be useful for
comparing different hardware platforms for the same database product,
| ie for helping with the question: what is the best hardware to run DBMS
| X on?
| Interestingly, CA management have twice committed to performing and
| publishing TPC benchmarks for OpenIngres 2.0, once at Ingres World
| '95, and again at Ingres World '96. They would probably only do this
| if they seriously expect to blow someone out of the water.
| As of Ingres World '97, CA management is now stating that they expect
| TPC benchmarks to be published by late this year (1997). Assuming they
| are as good as CA believes they will be, you should certainly hear about
| it when they come out.
|
01.012 Is postgres anything to do with INGRES?
Postgres is an experimental relational database management system
| developed at UC Berkeley. It is intended to demonstrate how object
| oriented data management can be accomplished without discarding the
| relational model. (See Stonebraker's writings on the subject,
| under the heading The Third Generation Database System Manifesto.)
| The project is directed by Professor Michael Stonebraker, one of
the originators of Ingres. Postgres is a spiritual descendent of
University Ingres but is not truly related to INGRES.
| Some very exciting news on the Postgres front is the release of
| PostgresSQL (or Postgres95 as it was initially known). PostgresSQL has
| many fascinating features, and it is the only public domain database
| software with a native SQL interface. (Which is not to suggest that
| having an SQL interface is in any way a good thing, but many people
| have been asking for a PD RDBMS that supports SQL.) PostgresSQL runs
| on AIX, Linux, and possibly NT.
| It is now available for ftp as
| ftp://ftp.postgreSQL.org/pub/postgresql-v6.1.1.tar.gz
| For full information, see:
| http://www.postgresql.org
| http://s2k-ftp.cs.berkeley.edu:8000/postgres95
| Potentially even more exciting is that OpenLink Software offers a native
| PostgresSQL JDBC driver to allow Java applications to run against
| PostgresSQL databases. See http://www.openlinksw.com for more information.
| Mailing Address Purpose
| ======= ======= =======
| pgsql-announce@postgresql.org Postgres discussion and announcements
| pgsql-bugs@postgresql.org Postgres bug reports
| pgsql-questions@postgresql.org Questions to the developers of Postgres
|
| More info, including how to subscribe-unsubscribe to various Postgres
| mailing lists, can be found on this web page:
|
| http://www.postgresql.org/supp-mlists.shtml
|
| [Thanks to Paul M. Aoki and Jolly Chen of UC Berkeley for some of this
| information.]
|
| There is a commercial version of postgres called `Illustra' (at one time
| called `Montage' and also `Miro'). Illustra was recently acquired by
| Informix. You can find more info at the Informix web site:
|
| http://www.illustra.com
|
-------------------
Trivium: Illustra was originally called `Miro'; evidently the joke of
naming products after artists lived on briefly--Miro was a student of
Ingres'.
|
01.013 Is INGRES secure?
| OpenINGRES claims to provide C2 levels of security functionality.
The multi-level secure variant of INGRES (INGRES/Enhanced Security) has
already been certified to provide F-B1 levels of functionality at the
E3 ITSEC assurance level. OpenINGRES/Enhanced Security (the next
release) will shortly be submitted to the US NCSC for B1 evaluation.
INGRES/Enhanced Security is NOT presently certified to be NCSC B1
secure.
To find out what the NCSC security designations mean, consult the
"Orange Book":
Department of Defense Trusted Computer System Evaluation Criteria
DoD Directive 5200.28-STD
National Computer Security Center,
Fort Meade,
MD 20755-6000,
USA
Attn: Chief, Computer Security Standards.
Office of Standards and Products
There is also a "Red Book" covering network security.
01.014 Does INGRES run on the PC under MS-DOS or MS-Windows?
The server does not run under DOS or Windows (but it DOES run under
Windows NT). The INGRES tools do run under DOS and Windows and use
INGRES/Net to access a remote database on a server.
Relational Technology did briefly market a DOS version of INGRES
Release 5 that included the "engine", ABF, QBF and Report Writer, but
that was discontinued.
01.015 Where can I subscribe to Info-Ingres mailing list?
http://planetingres.org/mailman/listinfo/info-ingres
|
|
01.016 Can I get the FAQ by E-mail?
| The FAQ is posted irregularly. Between postings it is
available from the archive site using ftp (see section 01.010). If
you do not have ftp access to the archive site, you can obtain the
| FAQ by mail using one of the many ftpmail servers.
[The following information is provided by Bruce Horrocks and Tom Northey.]
The ftpmail servers use different software and the commands required
will differ from server to server. Try sending a message consisting of
just the word "help".
For some systems, the body of the message should be something like this:
open address [username [password]]
mode ascii
cd /pub/directory/etc
get filename
where address is an IP address, username and password are the username
and password to log into that machine as. Normally these are omitted
and they default to "anonymous" and your own mail address as the
password. Default transfer mode seems to be binary with files coming
over uuencoded, hence the switch into ascii mode.
In reply you get a confirmation of receipt mail message with an ID
number that lets you cancel your request. Assuming all goes well you
get the file requested as a mail message sometime later. Large files
are blocked up into 64k messages and need to be reassembled before
uudecoding.
Here is a list of ftpmail servers:
USA
===
ftpmail@decwrl.dec.com
ftpmail@sunsite.unc.edu
bitftp@pucc.princeton.edu
Europe
======
bitftp@vm.gmd.de
ftpmail@ftp.uni-stuttgart.de
ftpmail@ftp.inf.tu-dresden.de
ftpmail@grasp.insa-lyon.fr
bitftp@plearn.edu.pl
ftpmail@ftp.inf.tu-dresden.de
ftpmail@ftp.uni-stuttgart.de
ftpmail@doc.ic.ak.uk
Australia
=========
ftpmail@cs.uow.edu.au
01.017 Can I get the FAQ on discette?
Yes. As of release 1.5 (13JUL94) it is available on discette at no
| charge from Quest Software or Common Sense Computing. To get a copy, write
| to: info@oz.quests.com or
| Europe Elsewhere
| ====== =========
| Common Sense Computing Quest Software Pty Ltd
| Canada House 10 illowa St
| 272 Field End Rd East Malvern Vic 3145
| Eastcote, Middx. HA4 9NA Australia
| UK
| Phone: 081 866 4400 Phone: 03 885 5829
| FAX: 081 429 2848 FAX: 03 885 0116
| NOTE: Common Sense Computing and Quest Software distribute the FAQ at no
| charge as a service to the user community and are not in any way responsible
| for the content of the FAQ. All corrections and flames should be
| directed to ingfaq@naiua.org
01.018 Is the FAQ available on WWW?
Yes, but not in a really convenient form.
You can try looking at
| http://www.naiua.org/faqs.html
| A much better implementation of the FAQ in hypertext format will
| be announced soon.
| 01.019 Where can I get a comparison of RDBMS products?
| In November of 1996 Martin Rennhackkamp published a comparison of Oracle,
| Informix, DB2, OpenIngres, Sybase, and Microsoft SQL Server in DBMS
| Magazine. See:
|
| http://www.dbms.mfi.com/9611d52.html
|
|
| 01.020 Can I use a RAID with Ingres?
|
| Yes. As far as Ingres is concerned it is just disk space.
|
| RAID stands for Redundant Array of Inexpensive Disks. Just what it means
| beyond that is up to you. There are generally held to be 6 non-trivial
| variants of RAID, each with different properties. All offer some degree
| fault-tolerance and redundancy, but there are performance and price
| tradeoffs.
|
| For many people RAID means RAID 5. RAID 5 has many virtues but write
| performance is not one of them. Write-intensive applications would do
| well to choose another configuration. Also, anecdotal evidence claims
| that RAID 5 is not as reliable as theory predicts--possibly just due to
| complexity.
|
| RAID 0+1 is comparitively expensive (duplicate drives) but it offers
| outstanding performance both reading and writing, and reliability is
| claimed to be very good. Those who advocate using large numbers of
| smaller drives instead of a few large drives, but who worry about the
| the short MTBF (Mean Time Between Failures) when using many drives,
| should find RAID 0+1 quite attractive.
|
| For more information on RAID consult:
|
| http://www.compumedia.com/~kkirk/raid.html or
| http://www.storage.com
|
|
|
| SECTION 2 -- MISCELLANEOUS DATE QUESTIONS
|
| 02.001 Is 2000 A.D. a leap year?
|
| [I cannot see any way to improve on this posting taken verbatim from
| comp.databases.ingres. --Roy Hann]
|
| From agnew@gems.vcu.edu
| Date: 18 Oct 96 16:11:02 -0400
| From: Brainwave Surfer
| Newsgroups: comp.databases.ingres
| Subject: Year 2000 is a LEAP Year!!
|
| well, guys about 2000 as the leap or non-leap year, here is the
| DEFNINITIVE response.... Jim
|
| X-News: gems comp.os.vms:29785
| From: Paul S Winalski
| Subject:Re: RE: YEAR 2000
| Date: 24 Feb 1996 05:31:01 GMT
| Message-ID:<4gm7ql$kkv@zk2nws.zko.dec.com>
|
| OBRIEN wrote:
| >
| >
| >On this issue, there is a wonderful SPR which I have seen copies on DECUS
| >collections and Compuserve .. and possibly was posted here at some time. I
| >cannot remember the references, but have kept my own local copy, gleaned from
| >one of these sources, and which I always give a copy of to anyone who argues
| >that 2000 is not a leap year.
|
| You refer to the famous SPR response drafted by Stan Rabinowitz in response
| to an SPR filed against VMS V3.2 claiming that the LIB$DAY RTL function was
| incorrect in treating the year 2000 as a leap year. Here, published for
| the first time anywhere, is the unexpurgated first draft of the SPR
| response. DEC SPR Administration made him remove the bit at the end
| about WWV and atomic clocks, and the reference to VMS V4.0. I had the
| honor of being the technical reviewer for the answer.
|
| It is probably the only response to a software bug report ever to mention
| Sosigines, Regiomontanus, and the Council of Trent.
|
| --PSW
| =========================================================================
| D I G I T A L
|
| SPR ANSWER FORM
|
| SPR NO. 11-60903
|
|
| SYSTEM VERSION PRODUCT VERSION COMPONENT
| SOFTWARE: VAX/VMS V3.2 VAX/VMS V3.2 Run-Time Library
|
|
|
| PROBLEM:
|
| The LIB$DAY Run-Time Library service "incorrectly" assumes the year
| 2000 is a leap year.
|
|
| RESPONSE:
|
| Thank you for your forward-looking SPR.
|
| Various system services, such as SYS$ASCTIM assume that the year 2000
| will be a leap year. Although one can never be sure of what will
| happen at some future time, there is strong historical precedent for
| presuming that the present Gregorian calendar will still be in effect
| by the year 2000. Since we also hope that VMS will still be around by
| then, we have chosen to adhere to these precedents.
|
| The purpose of a calendar is to reckon time in advance, to show how
| many days have to elapse until a certain event takes place in the
| future, such as the harvest or the release of VMS V4. The earliest
| calendars, naturally, were crude and tended to be based upon the
| seasons or the lunar cycle.
|
| The calendar of the Assyrians, for example, was based upon the phases
| of the moon. They knew that a lunation (the time from one full moon
| to the next) was 29 1/2 days long, so their lunar year had a duration
| of 354 days. This fell short of the solar year by about 11 days.
| (The exact time for the solar year is approximately 365 days, 5 hours,
| 48 minutes, and 46 seconds.) After 3 years, such a lunar calendar
| would be off by a whole month, so the Assyrians added an extra month
| from time to time to keep their calendar in synchronization with the
| seasons.
|
| The best approximation that was possible in antiquity was a 19-year
| period, with 7 of these 19 years having 13 months (leap months). This
| scheme was adopted as the basis for the religious calendar used by the
| Jews. (The Arabs also used this calendar until Mohammed forbade
| shifting from 12 months to 13 months.)
|
| When Rome emerged as a world power, the difficulties of making a
| calendar were well known, but the Romans complicated their lives
| because of their superstition that even numbers were unlucky. Hence
| their months were 29 or 31 days long, with the exception of February,
| which had 28 days. Every second year, the Roman calendar included an
| extra month called Mercedonius of 22 or 23 days to keep up with the
| solar year.
|
| Even this algorithm was very poor, so that in 45 BC, Caesar, advised
| by the astronomer Sosigenes, ordered a sweeping reform. By imperial
| decree, one year was made 445 days long to bring the calendar back in
| step with the seasons. The new calendar, similar to the one we now
| use was called the Julian calendar (named after Julius Caesar). Its
| months were 30 or 31 days in length and every fourth year was made a
| leap year (having 366 days). Caesar also decreed that the year would
| start with the first of January, not the vernal equinox in late March.
|
| Caesar's year was 11 1/2 minutes short of the calculations recommended
| by Sosigenes and eventually the date of the vernal equinox began to
| drift. Roger Bacon became alarmed and sent a note to Pope Clement IV,
| who apparently was not impressed. Pope Sixtus IV later became
| convinced that another reform was needed and called the German
| astronomer, Regiomontanus, to Rome to advise him. Unfortunately,
| Regiomontanus died of the plague shortly thereafter and the plans died
| as well.
|
| In 1545, the Council of Trent authorized Pope Gregory XIII to reform
| the calendar once more. Most of the mathematical work was done by
| Father Christopher Clavius, S.J. The immediate correction that was
| adopted was that Thursday, October 4, 1582 was to be the last day of
| the Julian calendar. The next day was Friday, with the date of
| October 15. For long range accuracy, a formula suggested by the
| Vatican librarian Aloysius Giglio was adopted. It said that every
| fourth year is a leap year except for century years that are not
| divisible by 400. Thus 1700, 1800 and 1900 would not be leap years,
| but 2000 would be a leap year since 2000 is divisible by 400. This
| rule eliminates 3 leap years every 4 centuries, making the calendar
| sufficiently correct for most ordinary purposes. This calendar is
| known as the Gregorian calendar and is the one that we now use today.
| (It is interesting to note that in 1582, all the Protestant princes
| ignored the papal decree and so many countries continued to use the
| Julian calendar until either 1698 or 1752. In Russia, it needed the
| revolution to introduce the Gregorian calendar in 1918.)
|
| This explains why VMS chooses to treat the year 2000 as a leap year.
|
| Despite the great accuracy of the Gregorian calendar, it still falls
| behind very slightly every few years. If you are very concerned about
| this problem, we suggest that you tune in short wave radio station
| WWV, which broadcasts official time signals for use in the United
| States. About once every 3 years, they declare a leap second at which
| time you should be careful to adjust your system clock. If you have
| trouble picking up their signals, we suggest you purchase an atomic
| clock (not manufactured by Digital and not a VAX option at this time).
|
| END OF SPR RESPONSE
|
|
| 02.002 Are there any Y2K (Year 2000) problems with Ingres?
|
| The OpenIngres DATE data type handles dates in the range 01-jan-1582
| (the beginning of the Gregorian era) to 31-dec-2382 with a resolution
| of 1 second. Dates represented using the DATE datatype will therefore
| be unaffected by the turn of the century.
|
| However there may be problems displaying dates or entering dates in
| Ingres applications if the application programmer has used date
| templates that omit the century (eg: d"03FEB01" or d"02/03/01").
| As a temporary solution OpenIngres supports an environment variable
| called II_DATE_CENTURY_BOUNDARY that controls the interpretation of
| the year part by the front-end. If this environment variable is set
| to 50 (say), an input value of less than 50 will be interpreted as
| being in the 21st century, and a value of more than 50 will be
| interpreted as being in the 20th century. For example, an input of
| 03 will be interpreted as 2003, and an input of 99 will be interpreted
| as 1999. Note that this strictly a display issue; dates are stored
| correctly in the OpenIngres database.
|
| While not a Y2K problem, note that SELECT dbmsinfo('_bintim') returns
| the number of seconds since January 1st, 1970. Many programmers
| convert this to a 4 byte integer and use it as a compact time stamp.
| In mid-2035 this will roll over and cease to work.
|
|
|
| SECTION 3 -- CONTRIBUTED SOFTWARE
|
03.001 ftp site for INGRES tools
| A number of INGRES tools (including the NAIUA tools) are
| available to NAIUA members from the NAIUA web site:
| http://www.naiua.org
You are strongly encouraged to contribute tools. If you have useful
tools but are concerned about your employer's proprietary rights to
the tools you've developed: ask--you never know...
| Contact Bob Griffith (rcg@swl.msd.ray.com; see also section 99.001) to
arrange a donation.
03.002 ingperl, sqlperl and DBperl
There is a variant of Larry Wall's popular `perl' language that
provides a full-function (ie read/write) SQL interface to CA-INGRES
(and significantly NOT to University Ingres). It is referred to
by all of the above names by different people in different posts.
| The INGRES-friendly variant is available from the NAIUA tools archive,
| or for anonymous ftp from:
ftp.demon.co.uk (/pub/perl/db/perl4/ingperl)
As mentioned in the README file at that location, and it bears
repeating here, there is an effort underway to define a standard API
that will allow perl to access ALL the popular RDBMSs and it would be
well to keep an eye on that process so that you don't end up being
surprised that you've written a lot of non-standard code.
| When a DBD::Ingres module for Perl5 arrives it will be supplied with
| an Ingperl emulation layer so old code will run without changes.
| The current rev with Ingres support is perl 4. perl 5 has been
| waiting for the OpenIngres 1.1 API.
| Once you load this software, you will probably want to buy the two
| excellent O'Reilly books on perl (although the Ingres extensions
| are not mentioned in them):
|
| Learning perl ISBN 1-56592-042-2
| Programming perl ISBN 0-937175-64-1
|
|
|
03.003 tclsql
tcl is an acronym for "Tool Command Language". The version
extended to talk to INGRES is `tclsql' and it is thought to
| be CA-Ingres 6.4 specific.
It is available for anonymous ftp from:
harbor.ecn.purdue.edu (/pub/tcl/extensions/tclsql-1.0.tar.Z)
There have been reports that some sites have been unable to uncompress
the files from this archive. It seems likely that the problems were
local though. [Further information on this is solicited.]
03.004 $II_SYSTEM/ingres/sig
There are a number of unsupported utilities in the sig directory
| that is part of the standard distribution.
Some of these tools may not be compiled and may require either or both
of ABF and ESQL/C to create an executable.
| 03.005 NAIUA tool set
| The NAUIA tools committee now makes its collection available to NAIUA
| members only via the NAIUA's web site. A password is required to
| access the Members-only area of the web site where the tools archive
| is stored (the password is available from the NAIUA Management office,
| which can be contacted by email at mgmt@naiua.org). Start at www.naiua.org
| to enter the Members Only section and access the Tools Archive.
| Ingres users anywhere in the world are welcome to join the NAIUA.
| See section 99.001 for details or write to mgmt@naiua.org
| New contributions to the NAIUA tool set are always welcome.
| The archive is maintained by Bob Griffith; see section 99.001 for how
| to contact Bob.
|
|
| 03.006 onyx
|
| Onyx is a PD 4GL/terminal monitor for linux that provides SQL access to
| a variety of data repositories. University Ingres is one of the
| engines supported by Onyx; SQL is translated into QUEL.
|
Onyx contains an SQL compiler, a distributed transaction manager and a
4GL.
ftp Sites:
==========
wowbagger.pc-labor.uni-bremen.de pub/unix/databases
incoming/databases
ftp.germany.eu.net pub/comp/i386/Linux/Local.EUnet/Applications/Database
Contact Michael Koehne at:
kraehe@bakunin.north.de
for further and better information.
| NOTE: If you are interested in Onyx as a way to get a public domain SQL
| database interface, you should probably also look at PostgresSQL. See
| section 01.012 for more information on Postgres95 and where to get it.
| Also consider SOLID. See http://www.solidtech.com for more information.
| SECTION 4 -- THIRD-PARTY SOFTWARE
|
04.002 Can I use ODBC?
ODBC is Microsoft's implementation of the Call Level Interface
specification defined by the SQL Access Group. INGRES can act as
an ODBC server.
The INGRES driver is a Level 1 ODBC driver. This provides MS-Windows
applications with transparent access to INGRES databases. The ODBC
driver and the MS Driver Manager are available at no charge for the
INGRES/Net product for MS-Windows but must be EXPLICITLY requested .
| The CA-INGRES ODBC driver is NOT bundled with INGRES/NET.
| Refer to Jon Machtynger's INGRES ODBC FAQ at ftp.naiua.org /pub/ingres
| for full information on using ODBC with INGRES.
| See also the ODBC FAQ maintained by Microsoft, which you can FTP from
| ftp.microsoft.com in /bussys/sql_odbc/odbc/faq.doc
| Suggested site for a compendium of information about ODBC (performance
| info, ODBC Hall of Fame, links to articles, ODBC vendor info, benchmarks,
| myths and facts, database conference presentations and workshops, etc.):
| http://ourworld.compuserve.com/homepages/Ken_North
| Follow the Data Access Roadmap to the ODBC-related pages. This site also
| includes JDBC and OLE DB info.
|
|
|
04.003 What is Grafsman?
| GRAFSMAN, an OpenIngres third-party application, lets developers add
graphics to their applications on any of 35 platforms, ranging from
DOS, to Windows, OS/2, UNIX, and VMS. Output can be directed to
character terminals, X, DOS E/VGA, DEC REGis, Tektronix mono/color,
Epson dot matrix, HP LaserJet, or PostScript, among many others.
| GRAFSMAN can be built into OpenIngres/ABF, OpenIngres/Vision, and
| OpenROAD applications. On UNIX and VMS platforms, it is also
possible to embed calls to GRAFSMAN in text files (such as reports
produced with Report Writer) which can be interpreted by a suppplied
filter before printing.
| There is also a version of Grafsman for the web.
| See http://www.soft-tek.com for more information.
|
04.004 What is GQL?
GQL is a third-party graphical query language that has support for
| OpenIngres (among many others). There is a read-only version called
GQL/User and a read/write version called GQL/Update.
Each site also needs at least a single copy of the GQL/Admin module
to control the client GQLs.
GQL/Design assists with database design and table definition.
GQL is capable of accessing all the popular database management systems
| as well as OpenIngres, and a future version is expected to be able to
join tables from disparate systems.
The vendor has established a Gopher/FTP site for product support and
information.
The relevant info:
FTP: bbs.andyne.on.ca (IP:198.96.20.209)
Gopher: bbs.andyne.on.ca
BBS: (613)548-1032 2400bps/14.4kbps/N,8,1
WWW: http://bbs.andyne.on.ca/
| 04.005 Can I use JDBC (Java connectivity)?
|
| JDBC provides Java applications and applets with access to databases
| in much the same way that ODBC does. For information on JDBC consult:
|
| http://splash.javasoft.com/jdbc
|
| At the time of writing there are three vendors of JDBC drivers for
| OpenIngres.
|
| Caribou Lake Software, Inc.
|
| http://www.cariboulake.com
|
| Intersolv (DataDirect Java JDBC solutions)
|
| http://www.intersolv.com
|
| Openlink Software
|
| http://www.openlinksw.com
|
| All these sites offer downloadable demo versions of the respective
| vendor's products. Caribou Lake specializes in Java/OpenIngres
| solutions.
|
|
|
| SECTION 5 -- BOOKS
|
05.001 What is a good book on INGRES?
A Guide to Ingres
= ===== == ======
Chris Date
Addison-Wesley
ISBN-0-201-06006-X
1987
This book is getting badly out of date but it does give a nice
introduction to the older INGRES tools. It concentrates on QUEL (it
treats SQL only in passing and with barely suppressed scorn). It has a
good description of QUEL aggregate functions. University Ingres users
may find this book marginally more useful than users of CA-INGRES
would, especially because of the emphasis on QUEL.
Ingres: Tools for Building an Information Architecture
======= ===== === ======== == =========== ============
Carl Malamud
Van Nostrand Reinhold
ISBN 0-442-31800-6
1988
According to Pamela Barker's review in the February 1993 NAUIA
Newsletter, since 6.4 came out this book is a little stale too.
It is recommended for readers who already have a basic knowledge
of INGRES.
INGRES SQL Developer's Guide
====== === =========== =====
Carolyn and Jack Hursch
Windcrest/TAB/MacGraw Hill
ISBN 0-8306-2564-X (harcover)
ISBN 0-8306-2528-3 (paperback)
1992
Covers the INGRES implementation of SQL and embedded SQL. Examples
of embedded SQL use C.
Programming in SQL with Oracle, INGRES, and dBASE IV
=========== == === ==== ====== ====== === ===== ==
John Carter
Blackwell Scientific Publications,
ISBN 0-632-03136-0
1992
| Roger Hill says of this book:
| "It has proven invaluable to me...lots of examples of code for eg. doing
| outer joins etc. The tuturial stuff on setting up databases I found to
| be superfluous, but the SQL stuff was pretty good. Basic and recommended
| for new DBA's (like me!)."
|
Introduction to INGRES
============ == ======
Margaret A. Zinky, James W. Everett, Linda J. Hawbaker
PWS-Kent Pub. Co.
ISBN 0-534-92869-2
1992
This book has examples from a complete ABF application. Students like
it, and after working through the application should gain a basic
understanding of ABF. The book is good value for money.
INGRES & Relational Databases
====== = ========== =========
D. Rothwell
McGraw-Hill
ISBN 0-07-707482-3
1992
[No first-hand reviews available.]
INGRES User Guide: Visual Programming Tools
====== ==== ====== ====== =========== =====
Peter Mathews
Prentice-Hall
ISBN 0-13-463720-8
1991
[No first-hand reviews available.]
Database Tuning: A Principled Approach
======== ======= = ========== ========
Dennis E. Shasha
Prentice Hall
ISBN: 0-13-205246-6
1992
$32.00US
Discusses INGRES (amongst other products).
The INGRES papers: anatomy of a relational database system
=== ====== ====== ======= == = ========== ======== ======
Michael Stonebraker, editor.
Addison-Wesley
ISBN 0-201-07185-1
1986
A collection of scholarly papers on University Ingres, edited by
one of the chief architects, Professor Stonebraker. These papers
are literally only of academic interest (in the best sense of the
term).
05.002 What is a good book on relational databases?
An Introduction to Database Systems, Vols 1 and 2
== ============ == ======== ======== ==== = === =
C. J. Date
Addison-Wesley
ISBN 0-201-14201-5
Now venerable, this still seems to be the standard work. Volume 1 is
| in its 6th edition. This is a two volume set. The essential one is
Volume 1. This book covers all the major data management models, not
just the relational model. These books have very nice annotated
bibliographies. Fairly rigorous and academic.
Fundamentals of Database Systems
============ == ======== =======
R. Elmasri and S. B. Navathe
The Benjamin/Cummings Publishing Company
ISBN 0-8053-0145-3
This book compares favorably with the Date books above. It covers
all the major data models, not just the relational model. Extremely
rigorous and academic.
SQL and Relational Basics
=== === ========== ======
Fabian Pascal
M&T Books
501 Galveston Drive
Redwood City, CA 94063
ISBN 1-55851-063-X
$28.95
This is a superb book for the practitioner and novice alike. It
cannot be recommended too strongly. It is nominally aimed at the
PC database market, but there is little or nothing in it that is
really specific to PCs. The three striking features of this book
are: (1) it describes the database software selection process
using tests for relational fidelity; (2) it describes in terms
even your management can understand WHY relational fidelity is
important and why you can't get away with playing fast and loose
with Codd's rules forever, and (3) it describes how to live with
SQL's manifold defects, in a refreshingly candid way. Highly
recommended.
A Guide To The SQL Standard (Third Edition)
= ===== == === === ========
C. J. Date with Hugh Darwen
Addison-Wesley
ISBN 0-201-55822-X
Covers SQL2 (SQL/92) with an overview of SQL3. Good thorough book that
does exactly what its title says: interprets the (sometimes
impenetrable) language of the official standard in a straightforward
and lucid manner. Recommended if you wish to fully understand every
aspect of the SQL language.
05.003 Where can I get a description of the SQL standard?
There are two practically identical version of the SQL2 standard
description: one from ANSI (ANSI X3.135-1992) and one from ISO
(ISO/IEC 9075:1992(E)). However, because the sale of standards
documents is a major revenue source for standards organizations,
neither version is available on-line nor in any other machine-readable
form.
A printed copy may be purchased from:
Customer Service
American National Standards Institute
1430 Broadway,
New York
NY 10018
Tel: (212)642-4900
The cost is about $230US each, plus shipping and handling. Outside
the US, the ISO document will be available from your own national
standards body.
The latest working draft [in, I believe, encapsulated Postscript] of
the SQL3 standard (X3H2-93-091/YOK-003) is available for anonymous ftp
from:
gatekeeper.dec.com (16.1.0.2) /pub/standards/sql
There is also WWW site with information on SQL and conformance testing:
http://speckle.ncsl.nist.gov/
The Validated Products List for SQL can be accessed as:
http://speckle.ncsl.nist.gov/~kailey/sql.htm
Finally, ANSI has granted NIST a non-transferable license to make an
HTML translation of SQL-86, the most obsolete of the SQL standards,
available for demonstration purposes. It will be reachable at
http://case50.ncsl.nist.gov/sql-86/
It is not a funded project and is not expected to be completed soon.
PS: ISO now has a home page at:
http://www.iso.ch/welcome.html
The ordering information for the ISO version should be there.
05.004 Where can I get a (BNF) definition of SQL syntax?
The book "lex & yacc" in the wonderful O'Reilly and Associates
Nutshell series has an SQL grammar.
lex & yacc
O'Reilly and Associates
ISBN 1-56592-000-7
$29.95
Source code examples from the book can be anonymously ftp'd from:
ftp.uu.net (192.48.96.9) /published/oreilly/nutshell/lexyacc
| SECTION 6 -- REVIEWS/ARTICLES
|
06.001 Are there any INGRES periodicals?
Software vendors, consultants and others who publish INGRES related
newsletters which can be made available at no charge are invited to
submit information about their publication(s) for inclusion here.
| Send details to ingfaq@naiua.org
NAIUA Newsletter
===== ==========
The North American INGRES Users Association publishes the NAIUA
| Newsletter 3 times a year for all OpenIngres customers in North
| America. You do not need to be an NAIUA member to receive this
| publication unless you are outside North America.
The newsletter is largely filled with the comings-and-goings of
the executive; useful advertising (sometimes including Help-Wanted);
helpful hints, and information about imminently forthcoming new features
and products.
Articles can be submitted to:
| Mike Greene
| Bowater Mersey Paper Co., Ltd.
| PO Box 1150
| Liverpool, Nova Scotia B0T 1K0
| Canada
| merseyis@atcon.com
| FAX 902-354-7480
|
| NAIUA SELECT
| ===== ======
|
| The North American INGRES Users Association publishes a bi-monthly
| newsletter exclusively for members called NAIUA SELECT. This
| newsletter includes information about the NAIUA and the OpenIngres
| product set. Because it is sent only to NAIUA members it treats
| sensitive issues somewhat more frankly than the newsletter above can.
| It also includes late-breaking news, the latest information on upgrade
| availability, and even relevant juicy rumours. Some of the information
| contained in the NAIUA SELECT would not find its way onto the Internet,
| so it is a valuable additional source of information about developments
| that probably affect you.
|
| Membership in the NAIUA is NOT automatic any more; to receive the NAIUA
| SELECT you must join. See section 99.001 for details and instructions
| for making a membership application.
|
|
comp.databases.ingres
=====================
There are two Usenet newsgroups of interest to INGRES users:
comp.databases and comp.databases.ingres
comp.databases carries postings on miscellaneous database products
that may be of interest to database programmers and DBAs in
general, though there are only infrequently postings relating
specifically to INGRES.
comp.databases.ingres is specific to INGRES, and is devoted almost
| exclusively to the commercial OpenIngres variant. On the whole this
seems to be a fairly responsive group with a moderate amount of
traffic.
| See also section 01.015 for the INFO-INGRES mailing list, which carries
| the same material for people who do not have access to an NNTP news
| server.
| DBA reQuest Newsletter
| === ======= ==========
A newsletter providing updated information on Common Sense Computing's
INGRES tools, with comments on current INGRES DBA and development
issues.
| This newsletter is now available to the public on the World Wide Web.
| You can find it at the following URL:
| http://www.comsense.com/comsense/newslet.html
|
| If you would like to receive a free copy of the newsletter by mail,
| you can request it by sending:
|
Your Name
Company Name & Address
Contact Phone or E-Mail (in case of problems with address)
E-mail to:
| subscribe@oz.quests.com
06.002 What are sources of reports on INGRES?
Independent, in-depth reports on commercial software are very
| expensive. Your local CA sales office will probably be able to
provide free reprints of the relevant sections of favorable reports
such as the ones by The Aberdeen Group and ButlerBloor.
Aberdeen Group, Inc.
92 State Street
Boston, Massachusetts 02109
Tel (617) 723 7890
FAX (617) 723 7897
ButlerBloor Ltd
Challenge House
Sherwood Drive
Bletchley, Milton Keynes
MK3 6DP, England
Tel +44 (0)908 373311
FAX +44 (0)908 377470
| 06.003 Where can I find recent articles about OpenIngres?
Please send the title, periodical name, date and page number of any
| interesting OpenIngres articles appearing in the trade press (or other
| publications not exclusively devoted to OpenIngres) to
| ingfaq@naiua.org
for inclusion here. "Articles" which are merely Computer Associates
press releases will not be listed here. These are readily available
| from CA's web site (www.cai.com).
| In addition to the articles listed below, a good column to check out is
| "Server Side" by Martin Rennhackkamp in DBMS Magazine. This is a monthly
| column by Martin, and usually his topic-of-the-month includes
| OpenIngres in the discussion.
| Publication Issue Article
| ----------- ----- -------
| Data Management Review Feb-97 Inside Ingres: Storage Options for
| Peak Performance
| Data Management Review Mar-97 Inside Ingres: Data Storage Options
|
| Computer Reseller News 12-May-97 CA Launch Pushes DBMS to workgroup
|
| Information Week 9-Jun-97 CA Expands Database Management
|
| Computerworld 9-Jun-97 CA Adds Internet Features to OpenIngres
|
| Infoworld 9-Jun-97 CA hopes to gain market share with an
| update to OpenIngres
|
| PC Week 7-Jul-97 OpenIngres Starts Catching Up
|
| Computer Reseller News 7-Jul-97 CA Takes Wraps Off OpenIngres Database
| Version 2.0
|
| Data Management Review Jul/Aug 97 Inside Ingres: Data Storage Options
|
| Computerworld Canada 1-Aug-97 Jasmine, OpenIngres play supporting
| roles at CA World
|
| Government Computer 4-Aug-97 Computer Associates Releases Version
| News 2 of OpenIngres
|
| Database Programming Sep-97 CA Takes OpenIngres to the Web
| & Design
|
|
| SECTION 7 -- INGRES/ADVISOR AND TECH SUPPORT
|
07.001 What is INGRES/Advisor?
| INGRES/Advisor is an on-line technical support service. It is
| gradually being replaced by the web-based tool CA-TCC (see item 07.008),
| available from the Computer Associates web site (www.cai.com).
| CA-TCC is now the primary on-line technical support service.
| INGRES/Advisor includes tools for accessing the
| so-called Knowledge Base, for viewing technical notes, for
| accessing the comp.databases.ingres newsgroup, and for monitoring
| the progress of a technical support call. Virtually all of the above
| functions are now set up in read-only mode; no updates or new items
| can be created.
| See the Advisor User Guide for detailed descriptions and instructions.
|
07.002 How can I access INGRES/Advisor?
It is available to users on the Internet by
| telnet advisor.cai.com
| and (at no charge) through CompuServe (host name is ADVISOR).
| Note that if you post to the comp.databases.ingres newsgroup using
INGRES/Advisor, your posting really will get out into the Usenet.
You must be a CA-INGRES customer with a support agreement in order
| to access the system. You will be prompted for your site ID and PIN
| number, and a password. Initially, until you change it, the password
| will be your last name in lowercase; eg hann.
| INGRES/Advisor is available to all North American INGRES customers,
| but it may not be available to customers elsewhere. Contact your
| local CA office or your CA-Ingres distributor if you have trouble
| accessing Advisor.
07.003 What is ProAlert?
| ProAlert is a service of INGRES/Advisor which provides access to documents
| describing SIRs and known INGRES bugs and anomalies. Where possible, the
documents describe work-arounds. [IMO some of the workarounds sound
| not quite right though--use your own good judgement, or call tech
| support to confirm. --Roy Hann]
A VERY valuable but underused feature of ProAlert is the ability to
locate and vote on System Improvement Requests (SIRs). This allows you
to see a logged request for an enhancement to the product and to see
how much support there is for it.
On the basis that there are some SIRs for features which repeatedly
crop up as requests on comp.databases.ingres but which have only a
handful of votes, it seems reasonable to say that we don't make as much
use of this valuable opportunity as we should.
| Note: Since CA-TCC has replaced INGRES/Advisor as the current online
| tech support tool, there are no new documents being added to ProAlert.
| If you wish to see recent documents from the Knowledge Base, you must
| use CA-TCC and select the "Search CA Knowledge Base" link.
07.004 How do I log trouble calls?
| OpenIngres users who have support agreements will have designated
technical support contacts who are authorized to place trouble calls.
Trouble calls can be placed by calling Technical Support at
| (516)342-5500, or through the "Open A New Issue" link on CA-TCC (see
| item 07.008).
| At this time it is not possible to start a call via email, but Technical
| Support hopes to restore this capability at some time in the future.
07.006 How do I vote on a SIR?
| Until a few months ago, it was possible to do this via the ProAlert
| function of INGRES/Advisor. This is no longer possible.
| At the moment, the best mechanism available is to contact your local
| Ingres users group, or even better, your national users group.
| They should have access to the current SIRs, and on occasion they
| will solicit votes on the most popular/needed SIRs and forward that
| vote on to Computer Associates management. A request from a users
| group to implement a SIR has considerable clout, since the users
| group represents many customers.
| The NAIUA hopes to establish a page on their web site where SIRs can
| be listed and voted on. This may be limited to NAIUA members only.
| Watch the web site or comp.databases.ingres for any news on if and
| when this capability is implemented.
| If you have any questions concerning SIRs, you can contact the chair
| of the NAIUA Product Directions Committee. See section 99.001 for details.
|
07.007 Tech support is useless, what do I do now?
| This seems to be a favorite carp, and to be sure, there have been some
problems with tech support in the past, but CA and the NAIUA are
making great strides in this area.
Here are a few tips to help get the best out of tech support (if you
haven't tried all of these then the problem is not just with tech
support...):
1. If you have a production system down (Rank 1 call), MAKE THAT
CLEAR TO THE DISPATCHER so that your call goes straight to the
level 2 support team.
2. If you think you have identified a bug, try to find the simplest
recipe that re-creates it before calling tech support.
3. If you don't think your problem is being resolved quickly enough,
| or you don't think the analyst handling the call is knowledgeable
| enough, or if you can't make the suggested remedies work, take the
| initiative and TELL the support analyst to escalate the call.
| It will be promoted to a Rank 1 call, no questions asked. This
| is your most powerful option--use it.
4. If you want to escalate a call, or change support analyst for ANY
| reason, feel free to call Brian Dempster, Level 1 Support Manager,
| at (516) 342-2192.
5. Don't close the call till you're satisfied. Especially don't
close the call and immediately open it again as a new one; that
| just skews the response time statistics and conceals the problem.
| Use option 3 above.
6. If all else fails, and you are an NAIUA member, contact one of the
NAIUA Members-at-Large (see section 99.001 for details), who will
attempt to get some sort of resolution for you. Be ready to
provide your call reference number and a history of the call.
7. If you are not satisfied with the handling of your call, make
sure someone at CA knows about it. Complete your satisfaction
survey that is sent to you when you close the call, and tell them
| EXACTLY what wasn't right. It is in everyone's interests to
| make sure problems are identified and resolved. For the sake of
| all of us, don't just go away angry.
|
| 07.008 What is CA-TCC?
|
| CA-TCC (Total Client Care) is now the on-line technical support service
| for OpenIngres. It provides access to the Computer Associates
| centralized client support database via the World Wide Web. This
| service allows the user to: open new issues; browse existing issues to
| check on current status and send in additional information; search through
| CA's "Knowledge Base" for problems, solutions, issues.
|
| In order to use CA-TCC you must use a web browser that supports the HTML
| specification 2.0 or above, such as Netscape Navigator 2.0 or higher or
| Microsoft Internet Explorer 3.0 or higher. This is necessary because
| the CA-TCC web pages support several advanced functions: secure sockets
| layer (SSL) for encrypting transaction traffic (you may need to make
| changes to your browser's options before accessing CA-TCC for the first
| time); "Cookies", or encrypted data records requred by CA-TCC; and
| HTML tables.
|
| All first-time users of CA-TCC must register. Begin by going to the
| following web page - http://www.cai.com/catotalclientcare.htm .
| Click on the link for CA-TCC Registration, then enter all requested
| information, including your site id, pin number, and desired password.
| Once you have submitted the form, your registration will be verified
| and you should be notified of this by CA (by email) within 24 hours.
|
| Issues opened via CA-TCC should get a response just as fast, if not
| faster, than one opened via phone. Also, once an issue is open, use of
| CA-TCC to check on progress and send messages can help alleviate the
| problems of time-zone difference and phone tag that frequently occurs
| when dealing with Tech Support.
|
| While CA-TCC is working pretty well so far, it is still a young product,
| and is missing features that users have grown accustomed to with
| INGRES/Advisor. CA personnel have stated that they have plans to make
| a number of improvements to CA-TCC. If you have suggestions, there is
| a mechanism within CA-TCC for submitting these. Select the link to
| Open a New Issue, then select the option for "Enhancement Request" rather
| than the usual "Issue", and within the Product pull-down select "WEBTRK".
| Fill in all other required fields, put your suggestion in the Issue
| Activity Text area, and then click on the "Create Issue" button.
|
| 07.009 How do I find out about patches?
|
| The best and most reliable way to find out what patches apply for the
| OS platform and Ingres version you are using is to talk to Tech Support,
| either by phone (516-342-5500) or via CA-TCC on CA's web site. If you
| need a patch sent to you, normally they can arrange to have it arrive
| at your site within a day or two.
|
| There is now another method available, via the Internet. CA has an ftp
| site where you can check for (and download, if needed) patches which
| may apply to your installation. This site is still a work in progress:
| currently there are only a few patches available there, and only for
| Windows operating systems. Hopefully more patches, and more platforms,
| will be available soon.
|
| The address of CA's ftp site is: mf.cai.com
|
| If you are accessing this site via the web, you can go straight to the
| start of the Ingres patches by entering the following URL:
|
| ftp://mf.cai.com/CAproducts/ingres
|
| If you are accessing this site via anonymous ftp, then move to the start
| of the Ingres patches by typing "cd CAproducts/ingres" once you have
| logged on.
|
------------------------ END OF PART 1 ------------------------------
| SECTION 8 -- GENERAL FREQUENTLY-ASKED-QUESTIONS
| 08.001 How can I store a large object in an Ingres database?
| (The Fall 1993 issue of inquire_ingres has an article starting on p.6
| that covers this issue at length.)
| OpenIngres 1.x supports BLOb columns up to 2Gb wide called LONG VARCHAR
| and LONG BYTE. Before this, the biggest object Ingres 6.4 could cope
| with was an object that could be crammed into a char(2000) or
| varchar(2000). If you are using OpenIngres and embedded SQL in a host
| 3GL you can use the native BLOb types; see the documentation for more
| details. If you are using Ingres 6.4 or you are not using a 3GL some
| alternate strategies are described here.
| OpenROAD has some methods for manipulating objects of up to 64kb, but
| if it isn't possible to use OpenROAD or if the objects in question are
| bigger than 64kb, then other techniques are required.
| There are three approaches that can be used. The first is to write a
| procedure that takes the large object and encodes it in the ASCII
| character set, and then stores it in (multiple, numbered) rows in a
| varchar(1996) column. Another procedure is needed to extract the rows
| in the proper order and to decode the ASCII to recover the original
| object.
| Another approach with MUCH more overhead, but which does at least work,
| is to create a separate table for EACH instance of an object. The
| table is defined as a heap with a single i1 column. Each byte of the
| object is written to its own row. The advantage of this approach is
| that there is no need to write software for encoding and decoding the
| object.
|
A third technique is "horizontal decomposition" which involves encoding
the object in ASCII, then splitting it into chunks of 2000 characters
or less, and storing the chunks in two or more tables. If you imagine
the tables pasted up side by side then the large object appears as one
long row spanning the separate tables. This is preferable to the first
| technique above because there are no worries about numbering and
| sorting the rows to ensure that the rows comprising the object are
| retrieved in the correct order, you just need a common key.
08.002 How can I suppress execution (I only want to see the QEP)?
Use the 'set optimizeonly' command.
To turn this feature off use the command 'set nooptimizeonly'. This
can be useful when you may wish to execute the SQL and display the QEPs
in just the section of code being debugged, not the whole program up to
that point.
08.003 Other than this FAQ, what is a good source of practical info?
| If you still have an Ingres 6.4 database (that is 6.4/02 or later)
| then you will find a number of useful notes located in
$II_SYSTEM/ingres/advisor
The drift of some of these notes is repeated in some of the
| FAQs answered here. Usually the Ingres note is referenced in that
case.
| NB: Prior to release 6.4/02, the notes were supplied in the
$II_SYSTEM/ingres/notes
directory and were numbered differently.
| In OpenIngres installations, the advisor directory no longer exists.
| The best current source of notes is available in either INGRES/Advisor
| (see sections 07.001 and 07.002 for more information) or in CA's web
| utility CA-TCC. If you want the most recent notes, your best bet is
| in CA-TCC, however you may need some patience: CA has a large backlog
| of notes that they plan to install here, but they cannot be made available
| until they have been reviewed and approved.
| 08.005 Why is Ingres reporting an incorrect row count?
The rowcount reported by the 'help table' command is only an
| ESTIMATE of the number of rows in the table. Ingres updates this
information opportunistically. It updates the information when
a `modify' is done on the table, or when a sufficient number of
rows in a table are updated by a single transaction, but it will quickly
become stale again.
It is slow, but you can ALWAYS get an accurate row count by doing:
select count(*) from
| 08.006 Can I do an outer join?
| Outer joins (left-, right-, and full-) are available in OpenINGRES 1.x
| using the ANSI SQL92 syntax.
| Outer joins are not directly supported in Ingres 6.4 but they can be
| coded by hand. If you are still running 6.4, as an interim solution a
| sufficient number of unions may achieve the required effect. Note
| us_13272.doc in $II_SYSTEM/ingres/advisor (or r6032.dbms in
| $II_SYSTEM/ingres/notes) suggests three other possible solutions as
| well.
08.007 How can I add/delete/alter a column in a table?
| With the recent release of OpenIngres 2.0, the ALTER TABLE capability
| is now available. The documentation for this release should describe
| how to perform these functions.
| If you do not yet have OpenIngres 2.0, then there is no supported tool
| for this operation. What are your alternatives? Read on:
|
One common approach is to replicate the table using `create table XXX
as select'; drop the table, and then create it again using `create
table YYY as select' from the copy.
Another (better) approach is to copy the table out of the database
using `copydb -c ' to generate a pair of SQL
scripts called copy.out and copy.in (NOTE the -c argument to copydb.)
copy.out is executed by `sql':
sql < copy.out
The copy.in file contains commands to recreate and load the table and
can be edited to drop the original table and re-declare it as required.
The copy.in script is executed the same way:
sql < copy.in
The advantage of the latter approach is that any grants, integrity
constraints and secondary indices on the table are re-created too.
NOTE that views ARE NOT re-created. If the deleted table is involved
in any views the views are automatically and silently destroyed when the
table is destroyed. They must be identified and preserved manually
before starting.
| The third, and probably best, approach is to use the 'tblmnt' utility
| from the NAIUA tool archive (see section 03.001).
08.008 How can I grant other people access to my tables?
| OpenIngres 1.x allows users to grant access rights to their tables,
| which other users can then access by referring to the table as
| username.tablename.
|
| Ingres releases prior to and including 6.4 do not allow users other
than the database DBA (usually the user `ingres') to grant access
to tables. Only tables owned by the DBA may have permissions
granted on them. Tables created by ordinary users are private for
all time.
If you want to make a private table accessible, the only remedy
presently available is to unload the table and reload it logged in as
the DBA. The following procedure will serve:
1. login in as tha DBA for the database (usually ingres)
2. copydb -u
3. sql -u < copy.out
4. sql < copy.in
5. go into isql and grant permissions as required
******************************* NB *********************************
This procedure will NOT copy any associated views, integrity
constraints, or rules. If you intend to destroy the original table,
BE SURE to preserve/copy the views etc. first. Destroying a table
silently destroys all the associated views etc.
********************************************************************
| OpenIngres 1.1 makes it possible for users to grant permissions on
| their tables and for other users to address those tables as
| `owner_name.table_name'
08.009 How can I change the ownership of a table/report/form?
| At present the only way to change the ownership of Ingres
objects is to copy them out of the database with the appropriate
tool and then reload them. Generally this question arises when a
private object must be shared, therefore the procedure described
here is to change the owner to the DBA so that the object can
be visible to everyone.
Changing the ownership of tables is covered in section 08.008 above.
An analogous procedure is used for forms.
1. Log in as the DBA (usually `ingres')
2. copyform -u
2. copyform -i
And for reports:
1. Log in as the DBA
2. copyrep -u
3. sreport
08.010 What is a QEP and how do I interpret it?
| A QEP is a "query execution plan" and is the algorithm that the
| Ingres optimizer selects for satisfying a "query." In this context
a query is anything that requires locating rows the database, whether it
be to select, update, or delete them.
The term is also used to refer to the displayed representation of
the query plan produced when the `set qep' command has been given.
A thorough explanation of how to interpret a QEP is beyond the scope
of the FAQ. Note US-38697 supplied in the $II_SYSTEM/ingres/advisor
directory provides an explanation (formerly r6004.dbms in
$II_SYSTEM/ingres/notes). This document is also available from
| Ingres/Advisor.
A careful study of the QEP is a good way to identify problems in a
badly behaved query. Tech support may ask for a QEP to help isolate a
problem.
08.011 How can I ask for the first N rows?
It can't be done. The request is meaningless within the relational
model. All operations in a relational database are described in terms
of mathematical set operations. Sets do not incorporate the notion of
ordering.
What is meant by the "first" N rows? Are these the first N rows
according to the order they were entered? Or the order in which they
appear in the file where the base table resides? (What happens when
the table is re-organized?) Or are the rows to be ordered according to
the value of some explicit sort key on the data?
Only the latter ordering makes any sense in a relational database.
However, to ask for anything less than all the rows described by
the WHERE clause violates the relational principle of atomicity,
which has it that all operations are executed completely or not at
all.
Extracting a subset of data at random, ie without an EXPLICIT
WHERE clause and an EXPLICIT ordering, is very bad practice and
is a sure sign that you are up to no good or that you are about to
find out the hard way why the model includes the requirement for
atomicity.
If it is not possible to contrive a suitable restriction (WHERE
clause) then one may have to suspect that the data model OR THE
INTENTION is defective.
Unfortunately, embedded SQL does provide a means of violating
atomicity using cursors. Cursors return rows one at a time and they
can be closed at any time. In a select loop atomicity can be violated
by issuing EXEC SQL ENDSELECT. There are no doubt any number of
pragmatic reasons for wanting to do this in an application, but
careful reflection on the possible resulting modes of failure is
advisable.
You can't use the resource limiter to select just the first N rows.
The resource limiter is pre-emptive. If it thinks you are going to
get more than the allowed number of rows you get no rows at all.
It IS reasonable and possible to ask a similar kind of question, such
as: "Who are the three highest paid members of staff?" The following
query does this:
select *
from staff_table a
where 3 > (select count(*)
from staff_table b
where b.salary > a.salary);
This does not violate the atomicity principle mentioned above because
the result set is complete. Achieving the same effect by aborting an
ESQL/C query after reading the first three rows is invalid because the
result set is not complete. This is not just a matter of technical
dogma. There are inescapable real-world reasons why aborting the select
loop after a fixed number of rows is invalid. To see why, consider
that the above query is capable of returning almost any number of
rows--not 3. If that is the case, but the query correctly states the
question, then arbitrarily choosing 3 rows from the result set is just
plain wrong.
Finally, even from a purely mechanical point of view, it is just not
possible to abort the delivery of the result set within Report Writer
and similar tools, so it is good to know the "proper" way to do this.
08.012 Can I override the optimizer with my own execution plan?
No. The programmer will almost certainly not do a better job than the
| Ingres optimizer can, and even if he could, changes to the table
location, structure or key distribution would ruin any execution plan
the programmer dreamed up.
If all the tables are properly organized, properly indexed and have
little or no overflow, and if a query is not performing well, study it
carefully. Poor performance is almost always a result of one of two
possibilities: there is an error in the WHERE clause--usually failing
to make a necessary equi-join, or the search is intrinsically slow.
| In many cases generating statistics on the distribution of key (and
non-key) values using optimizedb can make a marked improvement.
(It may be well to do a sysmod after collecting statistics. That will
keep the statistics table in tip-top shape.)
Other reasons for poor performance are: (1) one or more tables has
no keys defined; (2) no defined key is being involved, or (3) the
table has a lot of overflow pages. Problem 1 is fixed by modifying
the table to a suitable structure with a suitable key or keys.
Problem 2 is solved by defining a suitable secondary index on the
table. The index should be defined with an appropriate structure too.
The default is to organize it as ISAM. (You can define any
number of secondary indices on a table, but because they are maintained
dynamically, having too many can affect the performance of OLTP update
and insert operations.) Problem 3 is corrected by periodically
modifying the table to its nominal structure. Hash tables and ISAM
tables are especially vulnerable to overflow problems in a busy
database.
Having said all this, the optimizer is the most arcane and complex
part of a relational database engine--which is why so few RDBMSs even
bother, and it is not surprising that it does VERY infrequently goof.
See section 08.013 "How can I tell why my search is so slow" for ideas
| on determining if Ingres really has blundered.
In the rare case where the optimizer chooses a bad plan (and that is
probably by definition a `bug' in the optimizer), then it is sometimes
possible to "coerce" it into choosing a better plan by explicitly
involving the secondary indices in the WHERE clause.
08.013 How can I tell why my search is taking so long?
If the search has been used in production for some time, ie it is not
under development, and there is every reason to expect that it SHOULD
execute rapidly, begin by suspecting one of the tables is locked. The
| Ingres ipm monitor will help to identify these kinds of access
contention problems.
If the search is under development, and it is not known for sure
that it should execute quickly, the first step must always be to
examine the WHERE clause to see if a missing condition is causing
| Ingres to generate a Cartesian product instead of a much more
restricted equi-join. An unqualified join on a table of M rows
with one of N rows contains M*N rows. Even quite modest tables
will have products with MILLIONS of rows.
Check that the tables are indexed appropriately for the search.
It may be necessary to introduce a secondary index to prevent
| Ingres from scanning a table exhaustively when no keys are
involved. The 'help table ' command in isql will
report the keys on the base table and the existence of any
secondary indices.
Be aware that searches for non-existence are almost always slow
because they almost always require an exhaustive search. You
can't (in general) know it's not there till you've looked everywhere.
Check for an excessive number of overflow pages on the tables in
use. Just how many pages is too many must be determined by
experience, but overflow pages are scanned sequentially regardless
of the nominal table structure and the mere existence of overflow
pages requires that they be scanned. The 'help table '
will report overflow pages. Modify the table to its nominal
structure to eliminate overflow pages. For example if the parts table
is nominally a hash table keyed on part_nr, then
modify parts to hash on part_nr
will restore it to optimal condition.
| If none of these measures identify a significant problem, Ingres
can be asked to dump its query execution plan (QEP) for inspection.
| This is done by issuing the 'set qep' command. Ingres will execute
the search as usual, and then print out the plan it used. See
advisor note US-38697 for details of interpreting QEPs. If the search
is taking too long for this to be feasible, then the execution
phase can be suppressed by issuing the 'set optimizeonly' command
as well.
In extreme cases the optimizer may be fooled into choosing an
unsuitable QEP because of out-of-date or non-existent statistics
on key value distribution. To see if the actual effort required
to execute a search differs wildly from the predicted effort, use
the `set trace point qe90' command to dump statistics about the
resources consumed. The display produced by this tracepoint is
analogous to the display of the QEP. (Trace point qe90 is an
undocumented feature and Computer Associates may change it at any time.)
A divergence between predicted and actual costs may signal a need
for better key value distribution statistics.
If statistics have not been collected for some time, or if there is
reason to suppose that the distribution of values has changed then
optimizedb must be run to generate new statistics. Make sure that ALL
of the columns referenced in the WHERE clause, not just those used to
join tables, have had statistics gathered for them. Once the
statistics have been collected, it may be necessary to shut down the
server and re-start it to force it to re-read the statistics.
In ONE well documented case, generating key distribution statistics
can actually make a search SLOWER. A defect in optimizedb causes
it to examine only the left-most 8 bytes of a large text key. If
the values are indistinguishable in those 8 bytes, then the optimizer
will decide that the index is insufficiently selective and will
ignore it. Normally, without statistics, it assumes a 1% hit-rate
for each key value (10% if the relationship is an inequality).
Deleting the defective statistics will allow the optimizer to use its
usual assumption and performance will improve. Setting trace point
op165 will cause statistics to be ignored, so that they need not be
deleted.
| Avoid using functions on key columns that appear in the where clause
| as they prevent the optimiser from making use of the key. Thus
| select employee_no
| from employee
| where uppercase(name) = 'FRED';
| will result in a slow table scan even though there is an index on
| column "name".
|
| Beware of implicit function joins that can also prevent the use of an
| index. Consider the following example:
|
| select emp.name
| from employee emp, department dept
| where emp.dept_code = dept.dept_code
| and dept.dept_name = 'SALES';
|
| Normally this would be fine. However if, for whatever reason,
| dept.dept_code were defined as varchar but emp.dept_code were defined
| as char then the optimiser would have to include an implicit
| conversion function so that the where clause effectively becomes:
|
| where emp.dept_code = char(dept.dept_code)
|
| which prevents the use of any index on dept_code in the department
| table.
|
|
08.014 What are TIDs?
TIDs are "tuple identifiers" or row addresses. The TID contains the
page number and the index of the offset to the row relative to the
page boundary. TIDs are presently implemented as 4-byte integers.
The TID uniquely identifies each row in a table. Every row has a
TID. The high-order 23 bits of the TID are the page number of the page
in which the row occurs. The TID can be addressed in SQL by the name
`tid.'
It is inadvisable to exploit any knowledge of TIDs in an application.
In the documentation Computer Associates quite explicitly reserves the
right to change the form and use of TIDs without warning. In any case
the TID of a row can change at any time, just in the course of the
| operation of Ingres.
The temptation to use the TID in an application usually arises when it
is necessary to distinguish two "identical" rows, or when it is
necessary to locate a row for update when all the potential key values
have been changed. (Identical rows are logically meaningless, so this
should only arise in a trouble-shooting situation--otherwise there is
an error in the database design.) Normally it is preferable to define
and assign a "surrogate" key instead of using TIDs. The system
maintained table_key data type is intended for this purpose. Manually
maintained surrogate keys are also appropriate. The only requirement
of a surrogate key is that it be guaranteed to be unique in the table.
Surrogate keys need not be assigned sequentially or even monotonically.
(See section 08.028 for further information about generating surrogate
keys.)
Because surrogate keys are completely artificial and are assigned
automatically there is no legitimate reason ever to update one. It is
this property that makes them suitable for locating a row for update
when all the potential composite keys in the row are changed.
08.015 How can I display the proper Fkey labels using an emulator?
It is common for many sites to have a number of personal computers
which are also used as terminals to run INGRES applications which
were originally designed to run on a terminal such as a DEC VT220.
To make the PC support the INGRES forms interface correctly
these PCs will be running one of the many terminal emulator packages
which are available.
Since the PC keyboard does not correspond very closely to the
VT220 keyboard these emulator packages generally remap some of the
PC keys to VT220 functions, and even where there are keys with
corresponding functions on the two keyboards the key-cap labels will
usually be different. For instance, one terminal emulator program
uses the PC F1 key for the VT220 PF1 key; control-F3 for F13,
control-F6 for Do, and PgDn for Next.
These are fairly sensible mappings but a casual user is unlikely to
guess that in order to obtain the function in the menu apparently
assigned to the Do key (according to the display) he must hold down
control and press F6!
Therefore, in order to obtain the maximum benefit from the
almost fool-proof INGRES menu system it is necessary to display
the correct key-cap designations in the menu line when an INGRES tool
is used with a PC running a terminal emulator program.
Three things must be done to display the correct key-cap labels.
First, the PCs running a terminal emulator must be given their own
assign the INGRES FRSkey designations to function keys on the emulated
terminal's keyboard and to define the actual key-cap labels used on
notify the INGRES tools and local application programs to use the
new mapfile. All three steps are easily accomplished.
The first task is to choose a terminal designation which is not yet in
use. For instance, to use `zs220' to identify a PC running KEA
has been used already then choose another (or delete the existing
one).
------------------------------------------------------------------------
NOTE: When the user logs in it will be necessary to assign this terminal
designation to the TERM_INGRES environment variable. How this can
be done is really beyond the scope of an INGRES FAQ; the local system
administrator should be asked to take care of this. However, for
completeness one technique is described here (for a UNIX system).
At some sites the terminal type can be determined by knowing that
a specific port is hardwired to a PC. In other cases, such as
connections via modem or through a terminal server the terminal type
may need to be indicated by the user. The following example shows how
this can be done in the .login file on a BSD UNIX system:
set term = `tset -I -Q - -m 'dialup:?zs220'`
if ($TERM == zs220 ) then
setenv TERM_INGRES zs220
set term="vt200"
endif
In this example, when a dial-up connection is made, the system
takes zs220 as the terminal designation, unless the user specifically
chooses something else. Note that as far as UNIX is concerned the
file too).
-----------------------------------------------------------------------
The file that defines what key-cap label is displayed in parentheses
after each menu item in the INGRES menu must be edited to correspond
to the PC keyboard. In this example of a VT220 emulator, the supplied
vt220.map file in $II_SYSTEM/ingres/files should be copied to zs220.map
and edited. The following segment illustrates what to do:
/* FRS Mapping file for KEA ZSTEM VT220 emulator */
/* Menu Key */
menu = pf1 (F1) /* formerly PF1 */
/* Help facility */
frskey1 = pf15 (Ctl-F5) /* formerly F15 */
/* End current screen and return to previous screen */
frskey3 = pf3 (F3) /* formerly PF3 */
/* Go or execute function */
frskey4 = pf16 (Ctl-F6) /* formerly Do */
...
/* Previous screen or set of rows in table field */
scrolldown = pf25 (PgUp) /* formerly Prev Screen */
/* Next screen or set of rows in table field */
scrollup = pf26 (PgDn) /* formerly Next Screen */
...
Using this file the `Go' item in the typical INGRES menu will be
followed with (Ctl-F6) instead of (Do), which will be correct for the
PC.
Having identified the terminal and having supplied a corrected function
key map, it remains to instruct the INGRES tools to use the new map.
for the emulator. In the case a VT220 emulator, duplicate the entry for
the VT220. The only changes which are necessary are to the first line
to insert the new terminal designation, and to the `mf' item to point
this:
Z2|zs220|KEA ZSTEMpc-220 emulator:\
:co#80:li#24:bs:cd=50\E[0J:ce=3\E[0K:cl=20\E[01;01H\E[2J:\
...
:Ge=ansi:Gp=ansi:Gh:Go:Gw:GC#68:GR#22:Gr#1:mf=zs220.map:
Note that although this example has assumed a VT220 terminal, a
VT220 terminal emulator, and a UNIX system, the same procedure is
applicable to any terminal, its emulator, or to VMS.
08.016 Does INGRES support row-level locking?
No. There is not much consensus on whether that is good or bad. There
are certainly many people who would like to see it; it was the 4th most
frequently requested enhancement in the SIR survey done by the NAIUA
User Request Committee. On the other hand, Computer Associates has not
| so far provided the facility, although it is rumoured to be an enhancement
| that will be supplied in a future version.
There is a plausible argument in an internal ASK Group memo that the
value of row-level locking is over-rated and that the overhead required
to implement it is significant enough that it could not be regarded as
just a throw-away item. It appears that it would be necessary to have
a serious access contention problem before row-level locking would be
| advantageous. Thoughtful design of the application program can reduce
| lock contention. A future version of the FAQ will discuss this further.
| See section 08.030 for comments on generating surrogate key values that
| minimize contention in ISAM and B-trees.
Note that if one is satisfied to run the risk of having a table updated
while it is being read, read-locks can be turned off. This may be
worth doing in the case of very static catalogues. Look up the `set
lockmode' command for details.
Note also that row-level locking can be achieved by adding a large dummy
char column to the table definition. This extends the row-width to such
an extent that Ingres is only able to store one row per page. It can
sometimes be useful to do this to specific tables that have a serious
contention problem at the cost of wasted disk space. Be aware though
that contention can easily be increased by poor application or database
design and that these should be re-examined before deciding to force
row-level locking.
08.017 How can I invoke emacs (or any other editor) from isql?
Set the environment variable ING_EDIT to the pathname of the
editor. For example:
setenv ING_EDIT /usr/local/emacs
Note that at present the environment variables VISUAL and EDITOR
override ING_EDIT.
In some environments is also necessary to set VISUAL and EDITOR to the
full path of the required editor; ING_EDIT alone is unsufficient.
(This is a bug and may have been fixed by the time you read this.)
VMS users should either:
define ing_edit "+TPU" -or-
define ing_edit "+EDT"
which uses the callable version of these editors rather than
spawning a subprocess each time the editor is invoked. With regard
to TPU use the logical name TPU$SECTION to specify a customised
section file.
08.018 How can I change the displayed precision in isql?
The default display format is n10.3 for float4 and float8 columns; i6
for integer1 and integer2 columns and i13 for integer4. These can
be overridden with arguments on the command line.
The general syntax of the format argument for floating point is:
-fkxM.N
The -f is literal and signals a floating point format. The k is the
datatype selector and can be 4 or 8 for float4 or float8 respectively.
x is the format specifier, and can be one of E, F, G, or N. M is the
total field width in characters. N is the number of decimal places.
Example: to print float4 values in 9 character field with 2 decimal
places of precision, the flag would be -f4N9.2
The syntax for integer columns is:
-ikN
Again, -i is literal. k identifies the datatype and can be 1, 2 or
4 for integer1, integer2 or integer4. N is the field width.
Example: to print integer1 values in a 3 character field, the flag
would be -i13
08.019 How can I change the destructive behaviour of the Return key?
| The default behaviour of the Ingres FRS when the RETURN key is
pressed is to delete everything to the right of the cursor before
advancing to the next field. Sometimes it may be preferable if
it just advanced to the next field without deleting, as it does when
the TAB key is pressed.
In an embedded SQL application using C, the following statement will
make the Return key behave more like the TAB key:
exec frs set_frs frs ( map(nextitem) = controlM );
To make a global change so that the RETURN key is non-destructive in
programs like QBF, the appropriate *.map file in $II_SYSTEM/ingres/files
must be edited. (There is a .map file for each supported terminal
type, as well as an frs.map file.) Change:
clearrest = controlM (Return)
to:
nextitem = controlM (Return)
Note that control-X will still clear the entire field, so there will
still be a quick way to clear a large field even if the behaviour of
the Return key is tamed.
08.020 Does anyone at Computer Associates read comp.databases.ingres?
| Yes, a great many do.
Although their participation is officially described as "informal", it
has been reported that some CA employees are encouraged to
monitor the group. (It may therefore be productive to post SIRs for
comment.) However, the limited number of responses posted even to
questions explicitly directed to CA personnel makes it clear
that their's is a passive role. This is not a free alternative to
Technical Support.
08.021 Is it possible to generate FRS forms dynamically at run-time?
There is no documented way of doing this.
08.022 Why are modifications allowed to the system catalogs when it is
so dangerous?
One of the principal tenets of relational database theory is that a
relational database must store system data in the same structure that
it uses to store user data. If a database does not do this then it
| cannot truly be called a relational database. Note that Ingres
provides protection against inadvertent updates by restricting
modifications to super-users only and then only if the -S flag has been
specified on the command line.
| 08.023 How can I bulk authorize Ingres users?
| OpenIngres 1.x supports the SQL92 CREATE USER command which makes bulk
| authorization scripts easy to write. See the DBA guide for more
| details.
| Ingres 6.4 users should read Advisor note us_38622.doc (filed as
| us_18622.doc in $II_SYSTEM) which describes how to bulk authorize
| users. This is also described in Chapter 2 of the 6.4 Database
| Administrator's Guide. Since the Advisor note is so clear and
| detailed, and since it is included as part of the standard
| distribution, there seems little point in either paraphrasing or
| quoting it verbatim here.
08.024 How can I load ASCII-delimited files created by PC `X' base?
All of the popular PC database/record managers can export data in a
| number of formats. Usually the Ingres user will have to use the
ASCII-delimited format--which will usually be making the best of a bad
choice. The main problem is that such ASCII-delimited files tend to
have commas between the fields, and the string fields tend to have
double quotes around them as well. This is probably thought to be
necessary because the string field might very easily contain commas as
data. (It is not clear that double quotes are any less likely to occur
in data, but that is the way it is.)
| To get such a doubly delimited dataset into an Ingres table requires
a little extra work than usual. Usually the COPY command would be
used, and the command would be written on the assumption that
there would be exactly one delimter at the end of each field, that the
data fields would be of variable length, and the delimeters would be
completely unambiguous. A command like
copy some_table ( field1=c0tab, field2=c0tab, ... fieldN=c0nl )...
would be usual. The syntax of the copy command describes the data
format as an unknown number of characters up to, but excluding,
the next instance of a specified character which is to be treated as a
delimiter. The delimiter is assumed to have no purpose other than to
mark the end of the data field and it is discarded.
The limitation is that the delimiter is assumed to be a single
character. Somehow, the additional (spurious) delimiter must be
discarded also when the data file is an ASCII-delimited file from a
PC record manager.
The trick is to use the COPY command's `d' format to discard the
additional character(s). The syntax of the `d' format is the same
as any other format, so `d1' directs COPY to discard a single character.
`d0tab' directs it to discard all characters up to and including the
next ASCII TAB. The following example shows how to use the `d' format
and the COPY command's normal behaviour of discarding delimiters to
import a doubly delimited data file.
| Suppose that the Ingres table was created by
create table parts
(
seq_nr i2,
part_nr c10,
description vchar(40),
ref_nr i4
)
and suppose that the data file (export.txt) created by the ASCII-
delimited export function of the PC record manager looks like
3451,"BC-1324","BELL-CRANK, STEEL",21323
| The Ingres COPY command would need to be something like this
copy parts
(
seq_nr='c0,', /* read up to the first comma */
x=d1, /* throw away the first " */
part_nr='c0"', /* read up to the second " */
x=d2, /* throw away the second comma AND the " */
description = 'c0"', /* read up to the fourth "*/
x=d1, /* throw away the comma */
ref_nr=c0nl /* read up to the end-of-record */
)
from 'export.txt'
08.025 How can I import dates from PC `X' base?
Dates are often represented in some of the popular PC database
managers as integers or strings in the form YYMMDD. That is, a date
in the exported file might look something like:
...,"920512",...
where "920512" is intended to be interpreted as 12th of May, 1992.
| This is generally done to simplify sorting into date order (at least
| for this century).
| When trying to import this data into an Ingres database DATE type
column there are two obvious problems: the string values are surrounded
by pairs of double-quotes (if it was stored as a string), and the date
| is not in the usual Ingres form of dd-mmm-yyyy.
The paired quotes problem is handled as described in 08.024 above.
There are two ways to handle the date problem. The obvious (and
probably bad) solution is to edit the file, laboriously re-arranging
the numbers and mapping 01 to -jan- and 02 to -feb- and so on, and
exchanging the day and year positions. Even using regular expressions
and some ingenuity this is a significant chore and an opportunity to
corrupt the data.
A far better solution is to simply set the environment variable
II_DATE_FORMAT to "iso" like so:
setenv II_DATE_FORMAT iso (for UNIX)
define II_DATE_FORMAT iso (for VMS)
The COPY command will then interpret the string "920512" as 12-may-1992.
| (Because Ingres will also display dates in the ISO format, this feature
can also be used to prepare export files with dates in the YYMMDD form.
NB: if you do this, make sure you are not corrupting the stored dates
by truncating the century--eg turning geriatrics into newborns!)
| Note that Ingres will display dates in ISO format until the
environment variable is unset (unsetenv II_DATE_FORMAT or deassign
II_DATE_FORMAT) or is restored to its original value. (See the
| documentation on the II_DATE_FORMAT environment variable in Appendix D
| of the DBA Guide for other possible date formats.)
| See sections 02.001 and 02.002 for other date topics.
|
08.026 How can I create export files for a PC application?
Many PC and Macintosh applications such as Microsoft Excel like to
receive data files with TABs delimiting the fields. As noted below in
section 12.001 the Report Writer has a notorious bug in the way it
handles TABs so it may not be convenient for this job. Although not as
versatile as the Report Writer, the COPY command can be used, and it is
probably more efficient (of machine resources) anyway.
To create an export file (/tmp/parts) of the table `parts' described in
section 08.024 above, with the fields delimited by TABs, and with an NL
at the end of the record, use a command such as:
copy parts
(
seq_nr = c0tab,
part_nr = c0tab,
description = c0tab,
ref_nr = c0nl
)
into '/tmp/parts'
Using the c0 format, integer and floating point values will be written
| out right-justified in a field of the usual Ingres default width for
the type (or as selected with the appropriate flags as described in
section 08.018 above) padded on the left with ASCII blanks. vchar
fields will be written as fields of the maximum declared size, padded
on the left with ASCII blanks.
It will usually be necessary to adjust the display format for floating
point columns so that precision is preserved (the default for f4 columns
is f10.3 which will not be sufficient in general).
If string values must be enclosed in double quotes (or if any other
character must be inserted) use the `d' format:
copy parts
(
seq_nr = c0tab,
x = 'd0"',
part_nr = 'c0"',
x = d0tab,
x = 'd0"',
description = 'c0"',
x = d0tab,
ref_nr = c0nl
)
into '/tmp/parts'
Note that the COPY command operates only on entire base tables. It is
not possible to COPY views nor is it possible to restrict the COPY with
a WHERE clause. If a view or a restriction of a table is to be
exported an intermediate table must be created with CREATE TABLE...AS
SELECT.
Finally, consider whether it is necessary to export the data at all.
| Ingres can act as an ODBC server, allowing most of the popular PC
packages to extract the data directly from the database at run time.
| 08.027 What Ingres files can I delete (to recover space)?
[The Spring '91 issue of inquire_ingres carried this topic.]
This section is in the form of a shell script that UNIX users can edit
to suit their local needs. All commands are commented out; delete the
# as required. (grep for #FAQ-rm# to extract this script.) Expect to
recover at least 2.5Mb (and potentially a lot more) with this procedure.
# To delete the technical notes (print them off first?): #FAQ-rm#
#rm -r $II_SYSTEM/ingres/notes #FAQ-rm#
#rm -r $II_SYSTEM/ingres/advisor #FAQ-rm#
#FAQ-rm#
# To delete the release notes: #FAQ-rm#
#rm $II_SYSTEM/ingres/release.doc #FAQ-rm#
#FAQ-rm#
# To delete the ABF demo: #FAQ-rm#
#rm -r $II_SYSTEM/ingres/bin/abfdemo #FAQ-rm#
#rm -r $II_SYSTEM/ingres/bin/deldemo #FAQ-rm#
#FAQ-rm#
# To delete VIGRAPH #FAQ-rm#
#rm -r $II_SYSTEM/ingres/vec #FAQ-rm#
#rm $II_SYSTEM/ingres/bin/vigraph #FAQ-rm#
#rm $II_SYSTEM/ingres/files/*.gr #FAQ-rm#
#rm $II_SYSTEM/ingres/files/english/vg*.hlp #FAQ-rm#
#FAQ-rm#
# To delete miscellaneous other executables: #FAQ-rm#
#rm $II_SYSTEM/ingres/bin/pclink #FAQ-rm#
#rm $II_SYSTEM/ingres/bin/hstpclink #FAQ-rm#
#rm $II_SYSTEM/ingres/bin/iistar #FAQ-rm#
#rm $II_SYSTEM/ingres/bin/starview #FAQ-rm#
#rm $II_SYSTEM/ingres/bin/eqf #FAQ-rm#
#rm $II_SYSTEM/ingres/bin/esqlf #FAQ-rm#
#rm $II_SYSTEM/ingres/bin/eqc #FAQ-rm#
#rm $II_SYSTEM/ingres/bin/esqlc #FAQ-rm#
#FAQ-rm#
# To delete Release 5 files #FAQ-rm#
#rm $II_SYSTEM/ingres/bin/conv* #FAQ-rm#
#rm $II_SYSTEM/ingres/bin/*60* #FAQ-rm#
#rm $II_SYSTEM/ingres/bin/cvsync #FAQ-rm#
#rm $II_SYSTEM/ingres/bin/appchk #FAQ-rm#
#rm $II_SYSTEM/ingres/bin/iifsgw #FAQ-rm#
#rm $II_SYSTEM/ingres/bin/dupdb #FAQ-rm#
#rm -r $II_SYSTEM/ingres/convto60 #FAQ-rm#
#FAQ-rm#
# To delete the contents of the sig directory #FAQ-rm#
#rm -r $II_SYSTEM/ingres/sig #FAQ-rm#
#FAQ-rm#
# To delete extraneous terminal mapping files #FAQ-rm#
#rm $II_SYSTEM/ingres/files/97801f.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/ansif.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/ansinf.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/at386.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/bull10.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/bull24.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/bullvtu10.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/bullwv.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/dg100em.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/dg220em.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/dgxterm.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/h19f.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/h19nk.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/hp2392.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/hp70092.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/ibm5151f.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/icl12.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/icl34.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/mac2.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/mws00.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/mws01.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/mws02.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/mws03.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/mws04.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/mws05.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/mws06.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/pckermit.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/pt35.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/suncmdf.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/sunf.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/sunk.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/sunm.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/tk4105.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/vt100f.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/vt100i.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/vt100nk.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/vt200i.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/vt220.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/vt220ak.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/wview.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/wy60at.map #FAQ-rm#
#rm $II_SYSTEM/ingres/files/xsun.map #FAQ-rm#
In addition to deleting these files, the few necessary terminal
and the rest deleted. This can also improve start-up performance
if the required terminal descriptions appear far down in the file.
Also, be sure to run ckpdb with the -d option from time to time to
delete stale journal and checkpoint files. ckpdb cannot address
journal files older than the 16 most recent ones, so they must be
deleted manually if they exist. Since auditdb can't address them
either, there is no point in keeping any more than the 16 most recent
ones anyway.
See also section 30.002 on recovering space used by the error log.
08.028 How can I assign sequential record numbers?
System-generated sequential numbers are useful in a number of
applications such as work order numbers, cheque numbers,
invoice numbers, or just as a surrogate key to maintain the order of
user entered records. Some database products offer a "serial" datatype
that the system automatically increments in order to simplify numbering
| rows sequentially. Ingres does not have such a datatype so you must
create your own.
| Note that non-sequential numbers may be preferred in some situations.
| See section 08.030 for more details.
|
There are many ways to assign sequential numbers, but the basic idea
is to create a table in which you store the last number assigned
and then access the table in a way that guarantees that no two users
will ever be able to get the same number. The example shown here will
use the `generator' table defined by:
CREATE TABLE generator ( next_nr INTEGER NOT NULL ) WITH JOURNALING;
To get the next sequential number, first turn off autocommit if it
is on, and then:
EXEC SQL UPDATE generator SET next_nr = next_nr+1;
EXEC SQL SELECT next_nr INTO :next_nr FROM generator;
...
EXEC SQL COMMIT;
By doing the UPDATE first you will be blocked until any lock held
by another user is released, and once you get access to the table
you will hold an exclusive lock on it until you COMMIT. No one else
will ever be able to get the same next_nr. (If the order of the UPDATE
and the SELECT is reversed, then two users could easily get the same
number.)
Because the sequential number generator table will often be a very
busy table it can easily become a system bottle-neck. To maximize the
availability of the generator table, you should wait until as late as
possible in the transaction before accessing it so that you can COMMIT
as soon as possible. Alternatively, you could use a second session
to access it; you could then COMMIT immediately--that would probably
give the very best performance of all.
If you want to maintain multiple series of numbers independently, like
work-order numbers, employee numbers and inventory numbers, you can
either use one table with an extra column to identify the number series,
or use separate tables for each series. If you opt for a single table
and if all the rows will fit in one or two pages--which they almost
certainly will, it would be best just to keep it as a heap for maximum
performance. On the other hand, one table per series will maximize
concurrency.
Once you have a basic sequential number generator you can invent many
variations on the theme. For instance, if you don't mind having gaps
in your numbers (perhaps you are just generating a surrogate sort key
and all that matters is that the numbers be strictly increasing), then
you can increment the last assigned number by an increment greater than
1--say by 10 or 100 or more. By noting the first number in the range
and the last number in the range you can "reserve" a block of numbers
to use without having to access the database repeatedly. This can cut
your disc I/O in half in some transactions. This only works if you
don't mind reserving numbers and then not using all of them. (But it
can reduce I/O enough that you may reconsider how much you mind having
gaps--maybe you can stand them after all!)
Another useful variation on the theme is sequential numbers with
check-digits. Check-digits are a practical necessity when the number
you are generating will later be re-keyed manually to identify a
record. For example: inventory tag numbers, employee numbers, part
numbers, account numbers, and so on. If you simply assign sequential
numbers for these and an operator subsequently miskeys a number, but
the incorrect number corresponds to a valid number assigned to another
item, the problem may go undetected. Check-digits can all but
eliminate this problem by providing a way that the computer can tell
"by inspection" that an incorrect number was entered. The following
view, based on the `generator' table above, assigns a 6 digit number
with a 7th check-digit that will catch all errors involving wrong
digits and transpositions:
CREATE VIEW cd_generator AS SELECT
right(squeeze('000000'+ascii(next_nr)+ ascii( mod(10-mod(
(mod(next_nr,10000000)/1000000)+
mod((mod(next_nr,100000)/10000)*2,10)+
((mod(next_nr,100000)/10000)*2/10)+
(mod(next_nr,10000)/1000)+
mod((mod(next_nr,1000)/100)*2,10)+
((mod(next_nr,1000)/100)*2/10)+
(mod(next_nr,100)/10)+
mod(mod(next_nr,10)*2,10)+
(mod(next_nr,10)*2/10) ,10),10) )),7)
AS next_cd_nr
FROM generator
(As defined here, next_cd_nr is a 7-character string padded on the
left with 0s, just as you would probably want to print it on a label
or a form. If you need a larger number range the extension is
straightforward once you see the pattern.)
An application subsequently trying to determine the validity of an
input just needs to compute the expected 7th digit from the first 6
that were entered, and if it does not agree with the supplied 7th
digit, there is an error. For example, using the computation above,
the number 314159 has the check digit 5 (ie 3141595). If the operator
inadvertantly reverses the 5 and the 9 and enters 3141955, then the
computed check digit would be 9, which would not match the supplied
check digit and an error could be flagged. Similarly, if the 9 were
miskeyed as 0 (3141505), then the computed check digit would be 4 and
once again an error would be recognized. Pretty obviously if the
error is made in keying the check digit itself an error will be flagged
too--there is no way to tell where the error is located without
reference to the intended input.
The computation used in the application is the same as in the view
but the precise implementation will vary with the language used so it
| is not shown here.
08.029 How can I do a case-insensitive string match with wild-cards?
| Versions of Ingres up to and including Release 6.4 implement the
Entry-Level definition of the standard `like' predicate. This insists
on having a column name immediately to the left of the LIKE keyword;
in particular, functions are not allowed. Therefore it is not possible
to write a WHERE clause such as:
WHERE lowercase(col1) LIKE 'figseeds'...
It must instead be written as
WHERE lowercase(col1) = 'figseeds'...
This is an irritating quirk, but not much more. A worse problem is
when wildcard characters are necessary. How can the search which is
naturally expressed as
WHERE lowercase(col1) LIKE '%figseeds%'...
be handled? The obvious (albeit grotesque) solution is to create a
view of the table which returns the column strictly in lower case.
Happily, John Morgan provides a somewhat less awful solution that
handles this particular situation without creating yet another view.
His solution is neither concise nor natural, but it is by far the best
the net has seen to date:
WHERE locate(lowercase(col1),'figseeds') < size(col1)...
The somewhat simpler case, looking for 'figseeds%', is expressed as:
WHERE locate(lowercase(col1),'figseeds') = 1...
Unfortunately this does not generalize much further very easily. For
example, it is not trivial to adapt this trick to cases such as:
'%figseeds'
'%fig%seeds'
'% ___seeds'
'\[fp\]ig\[fs\]eeds' escape '\'
The long-term solution to this problem is provided by the
Intermediate-Level definition of the LIKE predicate, which behaves
| more naturally, and which allows functions on the left. This is
| available in OpenIngres 1.1.
| [Steve Caswell reports that Ingres 6.4/05 has the Intermediate-Level
LIKE predicate--even though it is not mentioned in the release
notes. It just works. Further feedback on this is welcome.]
[Further feedback from a source in CA tells me that the intermediate
level LIKE is available in 6.4/05 through dynamic SQL (and hence
through isql, Report Writer, etc), but NOT through "static" SQL (ie
ESQL/C).]
| 08.030 How can I generate surrogate keys for best performance?
|
| [This is a continuation of section 08.028 above.]
|
| Ingres provides two logical key types: TABLE_KEY and OBJECT_KEY, but
| these have some serious drawbacks that require elaborate work-arounds.
|
| There are some performance pitfalls to consider when generating your own
| surrogate keys. The obvious one is that the table containing the
| last value assigned can easily become a system bottle-neck. Another
| possibly less obvious problem is the that sequential values, if used
| for a primary key, can cause some table structures to perform poorly
| (especially ISAM and B-tree).
|
| Access contention problems over the last-value table (`generator') can
| be handled in a number of different ways which can be more or less
| effective. Three possible techniques were mentioned briefly in
| 08.028: (1) deferring the update of `generator' until the last
| possible moment so that the exclusive lock is held for as short a time
| as possible. (2) Using a second session to access `generator' so that
| the update to it can be commited without commiting the "real" work, and
| (3) pre-allocating blocks of numbers to use without accessing the
| database at all.
|
| The last two can only be used effectively when you don't mind having
| gaps in your numbers; for instance when it is important only that the
| numbers be non-repeating and strictly increasing. This would be
| acceptable for labeling records as they are entered to allow you to
| reconstruct the order of entry when the records are retrieved. Such
| numbers are also useful when combined with the table's natural key if
| the application is allowed to alter all of the other attributes in the
| row. Usually, without a composite key constructed from the natural
| primary key and some arbitrary discriminating number it is necessary
| keep a before-image of the row, or else succumb to the temptation to
| use the TID in order to be able to find the original row to update it
| in the database.
|
| Sequential numbers may be fine in many cases, especially if you are
| just assigning ordinal numbers to use to sort rows later, but they are
| not ideal in all applications. Sequential record numbering can have
| undesirable effects on the performance of the database even if the
| numbers can be allocated rapidly and with a minimum of locking. If the
| generated number is used as a primary key, it can cause two problems.
| First of all, it can tend to cause all database activity to be
| concentrated on the same page of a table--if many users are adding rows
| with a key that causes them all to attempt to update the same page at
| once they will end up blocking each other and performance will rapidly
| deteriorate. Hash tables don't suffer from this especially, but all
| the other table structures do (in general anyway; there are odd-ball
| single-user cases where performance actually improves, but unless you
| know for sure that you are in one of those situations, assume
| you aren't.) If the generated number only needs to be unique, then it
| is far better to have consecutively generated numbers that are "far
| apart" or maybe pseudo-random. (Note that the Ingres system
| maintained logical key types TABLE_KEY and OBJECT_KEY are
| pseudo-random, presumably for this reason.) In theory, these "far
| apart" surrogate keys should vector simultaneous users to update data
| pages that are "far apart" too, eliminating or greatly reducing
| blocking.
|
| The second problem with sequential primary keys is that they force
| chronic B-tree index splitting and lop-sidedness in ISAM tables,
| which affects performance.
|
| Unique non-sequential keys can be generated using an additive
| congruential algorithm such as the following:
|
| UPDATE generator
| SET keyval=keyval/2+mod(mod(keyval,2)+mod(keyval/8,2),2)*2**30;
| SELECT keyval INTO :keyval FROM generator;
| COMMIT;
|
| (For a full explanation of this algorithm consult Roy Hann's paper
| "Key Points About Surrogate Keys", session number NT020S, presented
| at CA World '96.)
|
|
|
08.031 How do I find rows that don't match a row in another table?
This is a pretty basic question that is dealt with in almost any
relational database textbook and in the manuals. However it comes
up regularly, so here is the basic form again:
SELECT *
FROM table1 t1
WHERE NOT EXISTS
(
SELECT *
FROM table2 t2
WHERE t1.keyfld = t2.keyfld
)
You could also use the NOT IN construct if that makes more sense
to you, but they both end up the same query plan, which is what you
would hope and expect.
08.032 Why do I have a SWAP and a NOSWAP authorization string?
CA-Ingres uses SWAP and NOSWAP keys because of different machine
architectures (big-endian vs little-endian). You only need to use the
one which is appropriate for your machine--the other one would give
you an error like:
E_CL2604_CI_BADCHKSUM The authorization string (II_AUTHORIZATION)
checksums incorrectly. This is probably a typographical input error.
Please consult the CA-Ingres system manager.
To see which key you need for your machine, choose from the list
below:
SWAP Machines are as follows:
AT&T, ALLIANT, APOLLO, AMDAHL, BULL, CCI, CONVEX, CRAY, DG, ELXSI,
GOULD, HP, IBM, NCR System V Tower 32/850, PYRAMID, SILICON GRAPHICS,
SPERRY, SUN, TANDEM
NOSWAP Machines are as follows:
DEC (VAX VMS, Alpha OSF, Open VMS), SEQUENT, 386, 486 and Pentium
MACHINES, Siemens MX, IBM PS/2, NCR System 3000, NCR Tower 32/300, 500,
700
This information should be provided with the keys.
08.033 How can I automatically report the location of an ESQL error?
The syntax of the SQL WHENEVER statement does not allow any arguments
(which might be useful in tracking down the the actual location and
cause of any errors). John Hascall suggests the following neat little
trick for getting the cpp pre-processor and ESQL/C to work together
to insert the ANSI standard manifest constants __LINE__ and __FILE__
into an error message:
EXEC SQL INCLUDE SQLCA;
#define SQLERR() sqlerr(__LINE__,__FILE__)
EXEC SQL WHENEVER SQLERROR CALL SQLERR;
main()
{
EXEC SQL CONNECT somedb;
/* ... */
}
sqlerr(line, file)
int line;
char * file;
{
/* ... */
}
08.034 What are some good trace points?
Trace points are mostly undocumented, and are mostly useless or
dangerous if invoked by us customers. However a few are exceedingly
useful and it is a great pity that those few are not fully documented
or made available in a more friendly form. (Actually some have
appeared as new SET options like OPTIMIZEONLY, formerly trace point
op160.)
| Here are a few that are useful:
dm420 - dump DMF cache statistics
| dm421 - flush the DMF cache (essential when benchmarking)
| dm1305 - force a consistency point
dm1440 - display sort locations
op132 - turn off query flattening
op165 - disable use of statistics
| qe90 - display ACTUAL resources consumed (in same format as QEP)
| qs506 - purge the QSF pool and force new QEPs to be generated
| sc906 - log sessions in errlog.log
A trace point is turned on and off with the SET command. eg:
SET TRACE POINT qe90;
SET NOTRACE POINT qe90;
Needless to say, these trace points are unsupported, and that any
unfortunate consequences of using them is entirely YOUR responsibility.
| (For instance qs506 above will temporarily degrade the performance of
| your system.) Furthermore they might disappear or change behaviour in
| future releases.
| Several months ago, an Ingres Trace Point Reference List was created and
| made available on the Web by Robert Morey. This list can be found at the
| following URL:
| http://www.well.com/user/ideamen/trace.html
| As stated on the web page, use these at your own risk.
| 08.035 Does Ingres have an API I can use?
| CA-OpenIngres 1.x has an API. Ingres 6.4 and earlier does not. Before
| describing the API though, it needs to be pointed out that a great deal
| of rubbish is talked about the merits of APIs versus embedded SQL.
| This entry in the FAQ hopes to set the record straight.
| The OpenIngres 1.x API is mainly intended to satisfy the need for
| performing asynchronous database operations, and to make it easier for
| developers of third-party software to create Ingres ports of their
| products. The API is not functionally very different than the existing
| dynamic SQL capability provided by Ingres, except that it does not
| require the ESQL precompiler (instead it requires the API library).
|
| For pretty well all ordinary application programming an API is neither
| required nor desirable. Application programming is usually most
| productively and reliably done using embedded SQL and the ESQL
| pre-processor (regardless of its manifold defects). APIs are hard to
| use; they are unique to each product, and they are very likely to
| change from release to release. They should be avoided if possible
| even when using products that do support them.
|
| If dynamic SQL is required, then the EXECUTE IMMEDIATE statement is the
| first and simplest option. This will be adequate to handle the usual
| problem of wanting to supply the table name at run-time. It can be
| used to supply the rest of the statement at run-time too, provided that
| the existence and form of the target list is unvarying.
|
For more elaborate queries, where even the target list might vary, then
| there is the PREPARE...FROM, DESCRIBE...INTO, and FETCH...USING
| DESCRIPTOR statements. These are not easy to use, but are not any
| harder to use than an API. One potential short-coming with these is
that the query plan cannot be cached for future re-use, but the kinds
| of applications that would use these statements probably wouldn't
benefit much from cached query plans anyway.
For really exotic requirements, such as asynchronously executing
| transactions against the database, then you were on your own prior to
| OpenIngres 1.x. For that problem you would fork independent "query
| servers" built with ESQL. Anyone capable of dealing with the
| labyrinthine complexity of an API would have no trouble doing this.
| There is a widely held view that using an API to access the database
| will result in better performance than using ESQL for the same purpose.
| Until a credible benchmark proves this, we really need to be very skeptical
| of this claim. There is no reason to expect this to be the case.
| Wanting to use an API in order to use a favoured compiler that is not
| otherwise supported by ESQL may indicate somewhat confused priorities.
| Rather than petitioning for an API, it may be better to petition for
| better ESQL support for more compilers, especially in the Microsoft
| world. (Contact Jane Frazer (Chair of the NAIUA Product Directions
| committee) at prod_dir@naiua.org, or log a SIR through Technical
| Support.)
|
08.036 How can I find duplicate rows in a table?
Finding duplicates is harder than just getting rid of them. To get
rid of them, just create a new table by:
CREATE TABLE newtab AS
SELECT DISTINCT *
FROM oldtab;
Then use this new table to replace the original table, or use it for
whatever other purpose you had in mind.
To actually find the duplicates is a bit more work. For that you
will need something like:
SELECT col1, col2, ..., colN, count(*)
FROM oldtable
GROUP BY col1, col2, ..., colN
HAVING count(*) > 1;
You name as many columns for col1 to colN as are important to you.
To find wholly duplicate rows you must name ALL the columns in the
table.
| 08.037 How can I create a WWW interface to an Ingres database?
|
| If you are using OpenIngres 1.2 or later, you can use OpenIngres/WEB or
| OpenIngres/ICE. OpenIngres/WEB is a standard component of OpenIngres
| and is not an extra-cost option. It is intended for supporting
| so-called "intranets". OpenIngres/ICE is basically the same product,
| but licenced for unlimited users, and it intended for supporting
| applications on the Internet. (ICE stands for Internet Commerce
| Enabled.) Contact your local CA office for authoritative information.
|
| Users running Ingres 6.4 or OpenIngres 1.1 can use a couple of tools
| (such as webintool) from the NAIUA Tool Archive
| /pub/ingres/utilities/NAIUA on ftp.naiua.org (198.53.152.12).
| WebinTool is a generic WWW to SQL-database interface building tool
| developed at the BBSRC Roslin Institute, UK, which is freely
| available. The tool was originally designed for the Ingres DBMS, but
| it can be ported to support other DBMSs (e.g. Sybase, Unify2000)
| without too much effort.
|
| WebinTool allows the user to create USER-CUSTOMIZED WWW interfaces to
| SQL-databases through a series of user-defined WebinTool forms. A
| WebinTool form is a document written in HTML and a set of webin
| statements.
|
| For more information, access
|
| http://www.ri.bbsrc.ac.uk/webintool.html
|
| Questions and comments can be sent via email jianhu@bbsrc.ac.uk or via
| WWW http://www.ri.bbsrc.ac.uk/cgi-bin/webintool_cmmt.sh
|
| Another increasingly popular option for deploying WWW applications
| against an Ingres database is Java. See section 04.005 for a list of
| JDBC driver vendors.
|
| Note: you should probably read the fine print in your Ingres
| licence agreement before you decide to use webintool or any similar
| program to make your Ingres installation available over the Internet.
|
|
|
| 08.038 Is there any point in doing REPEATED INSERT?
|
| Possibly. Flagging an embedded SQL statement with REPEATED causes the
| query plan to be cached for future re-use, thus improving performance.
| However there is no query plan for a simple INSERT INTO...VALUES...
| statement so it is not obvious that there is any point in marking it
| REPEATED. However a less often recognized effect of REPEATED is that it
| also causes the parser and relation descriptor facility to be bypassed
| too. If you are doing a large number of INSERTs (say thousands) then
| it may well be worth marking them as REPEATED.
|
|
|
| 08.039 Can you get unloaddb to go directly to tape?
|
| To unload very large Ingres tables is easy under UNIX. First make a
| named pipe under Unix using the mknod -p command
|
| mknod -p /tmp/fifo
|
| then use this file as the file name in the COPY command.
| As as separate process you then copy the information to tape.
| A sample Bourne Shell script might look like this:
|
| (
| sql database << EOF
| copy blah() to '/tmp/fifo
| \\p\\g
| EOF
| ) &
| dd if=/tmp/fifo of=/dev/rmt/1
|
|
------------------------ END OF PART 2 ------------------------------
| SECTION 9 -- RDBMS
|
09.001 What processes do what?
dmfacp archiver process
wakes up at intervals and copies committed transactions
on journalled tables from the log file to the journal file.
| dmfrcp recovery process (recovery server in OpenIngres)
The dmfrcp process moves log records from log buffers
in memory and writes these records to the logging
system file. In case of server or system failure, the
dmfrcp recovers pending transactions by reading the
logging system file and performing appropriate recovery
actions. This is done during start-up. While the recovery
process is running the database is locked and users attempting
to connect see the message "The database is currently
unavailable."
iidbms INGRES RDBMS server process
| the actual data management component. There may be multiple
| servers per installation. OpenIngres allows customer defined
| server classes.
iigcc communication server process
provides the network communication function of INGRES/Net.
There may be multiple instances of iigcc in an installation.
iigcn name server process
monitors and identifies all the INGRES servers in an
| installation (eg iidbms and iigcc). There will be one per
| installation, on each client and server.
iislave disc I/O process (Unix machines only)
actually performs disc I/O operations for iidbms. To
| maximize concurrency, it is advisable to have at least one
| iislave per disc drive. (See note 09.002 below.)
09.002 How are extra iislave (disc) processes started?
| It is generally advisable to have at least one iislave process per disc.
The required number of iislaves is requested by the user `ingres' with:
ingsetenv II_NUM_SLAVES=
where is the number of iislaves to fork when iistartup is
executed. This assignment is non-volatile and needs to be made only
| once, or as new discs are added. If multiple servers are to be started
| the value of II_NUM_SLAVES can be adjusted for each server prior to
| starting it.
09.003 How do I recover an inconsistent database?
| Contact Computer Associates Ingres Technical Support. This answer is
not deliberately intended to be facetious, it is just that an
| inconsistent database can be caused by a number of things. Technical
Support are best able to determine the cause of the inconsistency and
advise on the appropriate recovery procedure.
| Some situations which cause a database to be marked inconsistent can
| be pre-empted. See section 09.019 for more information.
|
| It would be very foolish to do as some contributors suggest and just
use verifydb to force the database consistent. That option of verifydb
| is a last-resort salvage tool, not a recovery mechanism. It fixes
| nothing and leaves a mess in the database.
| 09.004 What are "compressed" table structures?
In ordinary table structures space is allocated for data rows according
| to the maximum width of the row. With compressed table structures
| Ingres applies a compression algorithm in order to reduce the amount of
| space required. The advantage is a reduction in storage requirements;
| the disadvantages are twofold:
Firstly there is a small performance penalty whenever a row is stored
| or retrieved, as it has to be compressed or uncompressed. Secondly
| when a row in a compressed table is updated, it is unlikely to remain
| the same size as the old row. If it is bigger then it won't fit in the
| hole left by the old row, and so it is added somewhere else in the
| table. Thus an UPDATE is effectively turned into a DELETE followed by
| an INSERT, and a hole, ie unused space, is left in the table.
Eventually these holes occupy more and more of the table until a MODIFY
is required to reclaim the space.
For these reasons compressed structures are not recommended for tables
that are updated heavily. For other operations, ie: SELECT, INSERT and
| DELETE there is a performance penalty because OpenIngres 1.x uses LZW
| compression. (Ingres 6.4 just eliminates "blank" space at the end of
| strings, which is not very intensive.)
Note that for CISAM and CBTREE structures only the data is compressed.
Data stored as part of the key is never compressed in the index.
09.005 Why might I configure more than 1 DBMS server process?
The DBMS server is multithreading: it handles SQL requests on behalf of
many INGRES clients. Yet INGRES allows me to set up more than 1 DBMS
server. What reasons might I have to do this?
There are quite a number of reasons you might decide to do this:
o Multiprocessors. Although a DBMS server is multithreading, it can
only run a single thread at a time. If you have more than 1 CPU it
may be beneficial to have more than 1 DBMS server. As the number
of CPUs increases then you are likely to need more DBMS server
processes to exploit the extra power.
The optimal number depends on average SQL arrival rate and service
time. You could build a model using queueing theory. Alternatively
do what everyone else does: try different numbers and measure
which is best for your workload and configuration.
o Keep DBMS size manageable. You can get a lot of users on 1 DBMS
server. But you need to configure more and more DBMS resources as
the number of users grows. There comes a time when the DBMS just
gets too *enormous* to be manageable.
Splitting the workload across multiple servers allows you to run
without setting all the Kernel process limits up to ridiculous
values, negating their original purpose.
o Separate conflicting work types. Some types of work don't run well
together in the same DBMS server, e.g. OLTP and heavy batch, OLTP
and heavy scans (reports or MIS). There is no internal
mechanism for assigning and managing thread priorities within
the DBMS server, which can lead to undesirable interactions between
the different parts of the workload.
A very common scenario is to have a separate server for daytime
batch and reports. This prevents the batch/reports interfering
with OLTP throughput and response times.
Another possible scenario is to separate 2 OLTP systems. This
separation may help with performance analysis and tuning, and
prevent interactions between them. They each have their own set of
resources.
o Error containment. When a DBMS server crashes or decides to shut
down, all users of that server are affected. It might make sense
to partition your workload into multiple DBMS servers, so that
only part of the user population is affected by a DBMS failure.
This can be particularly useful if your workload is unstable, even
as a temporary measure while you find the real problem.
Note that currently if a DBMS connected to a shared cache fails,
then all other DBMS servers using that shared cache will fail too.
That is, the shared cache is the unit of failure.
09.006 How can I direct batch or reports to a particular server?
| OpenIngres supports customer-defined server classes. See section
| 09.018 for more information.
|
| Ingres 6.4 does not offer such an elegant solution as OpenIngres.
At the moment, you have to bypass the Name Server to do this.
There are a number of steps:
o Set up a *private* DBMS server, that is: one the Name Server doesn't
know about. Do this by setting the DBMS startup flag 'nonames'.
No connections will be routed to this server by the Name Server.
o Store the private DBMS server ID in a Well-Known place, e.g. an
O/S file, an installation-level environment variable.
Remember this changes when INGRES is restarted. You need to
find out what it is and store it on every restart.
o Arrange that the II_DBMS_SERVER variable is set locally for batch
and reports which you want to run in the private DBMS server:
$ II_DBMS_SERVER=1234
$ export II_DBMS_SERVER
Where '1234' is the private server ID remembered earlier. This
routes the batch/report to the private server.
You will need to write some simple shell scripts to store and retrieve
the private DBMS server id. The iishutdown script looks for private
servers: you don't need to shut them down manually.
09.007 How does INGRES use sort space?
If you don't do anything, INGRES will take sort space to modify tables
from the table location, and other sort space from the database home
location. For production databases this is probably not what you want.
You can tell INGRES to use sort work locations which can be set up
anywhere. See the DBA Guide for details.
There are some things the DBA Guide doesn't tell you:
o The sort workfiles are striped across the available sort locations
by the sorter. The size of the stripe is determined by the sorter
at run time.
o It follows that the input and output files of the sort phases are
striped across the same disks. So you're guaranteed disk
contention--and there's nothing you can do about it.
o Keep the sort locations about the same size. When one location
fills that work file is *full*. There may be Gbs free in the other
sort locations, that doesn't matter. The sorter just gives up on
the sort in progress and returns an error.
o Unlike some other system locations, sort locations can be changed
by resetting the relevant installation environment variables. The
sort locations are dereferenced by the DBMS server when it
starts up, so restart INGRES or at least the DBMS servers to pick
up the new sort configuration.
o Check your sort locations have been picked up like this:
$ isql iidbdb
SET TRACE POINT DM1440;
This gives a list of sort location pathnames known to the
DBMS server at the moment.
o There must be no gaps in your sequence of sort location numbers.
When the DBMS server gets to a gap it stops looking for any more.
o It follows that if sort location #1 is not defined then sort space
is allocated using the default scheme described earlier.
Note: space for temp tables is always taken from the database home
location, not from the sort locations.
09.008 When does INGRES switch to a new journal?
Journals are switched automatically when you take a checkpoint. But
sometimes INGRES will switch to a new journal between checkpoints.
| The journals are written by the Archiver Process (ACP), dmfacp. By
| default this writes the journal in 8 kb `pages'. When it has written
| 1024 of these pages it will look to switch journals, i.e. after 8 Mb of
| journal.
The ACP is woken up periodically to copy information from the log file
to the individual database journals. When it has completed its log
file scan and copied all it can to the journals, it then checks if the
journals need switching. So in practise the switch happens at 8 Mb + a
little bit.
| In OpenIngres the journal switch point and page size are configurable.
09.009 Should I use mirroring on my data locations?
Maybe.
There's no standard virtual disk package: each is implemented
differently. You need to ask your vendor this question: does your
virtual disk implementation guarantee that mirrors are in sync after a
crash or power-off?
o If the answer is yes, then it is probably safe to mirror your
data locations. The choice is then a trade-off of resilience
against performance.
| o If the answer is no, then Ingres fast commit recovery will probably
leave the mirrors out of step. The same SQL query may give different
answers at different times. Avoid, or if you want to take the risk
then invest in an uninterruptable power supply. This will give you
some protection against the most common failure case.
o If the answer is fudged, it's probably best to assume a `no'.
If you do mirror your data locations, the disk I/O load on the total
system will increase. You need to configure more disks and controllers
to handle the load.
Note: the problem is that fast commit recovery algorithms have to probe
the database after a crash to see which updates made it to disk. If the
mirrors are out of step, different things will happen depending on which
mirror returns the data. There are a number of scenarios, some of which
are not very nice: duplicate rows on one mirror, missing rows on one
mirror, corrupt index on one mirror, etc.
09.010 Can I use mirroring as a super-fast checkpoint?
| Yes, but ONLY if you do so by modifying the checkpoint template and
| using "ckpdb". This method will work for either on-line or off-line
| checkpoints, and inherits all the respective limitations.
| To implement this, your "mirroring" solution must be controllable from
| the command line. You need to be able to reliably run a command that:
| 1) determines if the mirrors are all on-line and synchonized
| 2) disengages one of the mirror disks so it is no longer updated
| 3) re-engages the mirror disks
| The general checkpoint template algorithms are:
| 1) In the BEGIN phase, assure all mirrors are on-line and
| synchronized. If not, fail.
| 2) In the WORK phase, disengage one of the mirror disks for each
| data location.
| 3) In the END phase, do nothing.
| After the "ckpdb" command completes, you have a set of disks containing
| a "copy" of your database. If you performed an off-line checkpoint,
| this "copy" is perfect. If you performed an on-line checkpoint, this
| copy is not perfect, but will be made perfect when you recover by
| "rollforwarddb" properly applying the dump file records.
| Since you probably want to re-engage your mirror disks to prepare for
| the next checkpoint, you must now copy the data from the off-line
| mirror set to tape. Then issue the proper command to "resync" the
| mirrors. Remember this may or may not have adverse performance
| implications. Some solutions will resync faster if the time spent
| "off-line" is minimized.
|
| If you use this solution, remember that from the time the ckpdb begins
| until you resynchronize the mirrors, you are protected by one less
| mirror set. You may want to consider a minimum of three mirror sets if
| you choose to implement this strategy.
|
| Make sure you properly modify the checkpoint template so you can
| recover properly.
|
| PLEASE NOTE! Simply using the OS or hardware to "break" a mirror
| set for later backup to tape WILL NOT WORK unless Ingres
| has been shut down normally. If there are any
| transactions in the log file, or any unflushed cache
| pages in an active DBMS server, this method will NOT
| provide a "recoverable" backup.
|
|
09.011 Can I mirror my log file?
| In installations using FAST COMMIT (i.e. almost all Ingres
| installations) committed transactions are not written through the DMF
| cache to disc right away. Committed updates can exist in the cache and
| the transaction log for quite some time without being written to disc.
| The transaction log file is therefore a `critical single point of
| failure' for an Ingres system. If fast commit is enabled and you lose
| the log file, you WILL lose committed transactions. A mirrored log
| file could protect against this situation.
| OpenIngres supports a dual logging option to mirror the log. This
| option should be exploited. See the System Reference Guide for
| instructions. (Note that dual logging will only be effective if the
| mirror is on an entirely separate drive.) The rest of this note
| applies to Ingres 6.4 which does not have this valuable feature.
|
| Ingres 6.4 does not support dual logging as an Ingres feature, but
| mirroring can still be done. As always, there is a price: performance
in this case. If your workload is update- and commit-intensive you
will notice it most. On an extreme workload of this type (TP1) a
reduction in throughput of some 40% was measured.
But you can choose between performance and resilience on this single
point of failure. An alternative approach is to disable fast-commit,
| but that will also have a severe performance penalty.
Some virtual disk packages don't support mirrors on raw disk. Check
the manuals.
| If you don't mirror your transaction log and you do want to run
| fast commit, you can reduce the amount of data that would be lost
| in the event of a log failure by making the archiver run after
| every consistency point. The default is to run after every fourth
| consistency point.
09.012 Can I have more than 1 DBMS page cache?
This is quite easy to arrange, but be sure it's really what you want to
do.
Simply starting multiple servers with the runrundbms utility will, by
default, start each server with its own private DMF page cache in its
| internal data space. The Ingres locking system is used to maintain the
coherency of multiple caches, using a value block locking technique.
| You may therefore need to configure additional lock resources.
If you want to have a number of DBMS servers using fast commit on the
| same database, then you can use the shared_cache option. This
creates the DMF page cache in a shared memory space, which is then
connected to by all of the servers. There is a system of latches and
semaphores to safely coordinate their access to the common DMF page
| cache. (Note that if several servers share a cache and one server
| dies, all the servers attached to the same cache will immediately
| die too.)
It is also possible to have several shared caches, but you must give
them different names. See the I & O Guide for details.
09.013 Can I have more than 1 fast commit server?
Yes. Set -fast_commit and -shared_cache and DON'T set -sole_server.
Refer to Chapter 2 of the I&O Guide, "Using a Shared Buffer Cache" for
more information.
| 09.014 What is the Ingres Search Accelerator?
| The Ingres Search Accelerator is a hardware device that speeds
up certain types of database operation and is based on ICL's
SCAFS technology. (CAFS stands for Content Addressable File
Store and was developed by ICL for their VME mainframes. SCAFS
| is the SCSI bus based version.) It is available for the ICL DRS6000
| and DRS3000, the IBM RS/6000, and probably any Fujitsu box that both
| it and Ingres run on.
The system works by passing a query, or part of a query, to an
intelligent disk controller. The disk controller applies this
query to the data that it has been asked to read from the disk
and only passes back the rows that match. This results in a
significant reduction in the amount of data being passed back to
the main processor giving the following benefits:
- there is reduced bus activity, thereby allowing other
system activities to run more quickly
- the CPU has fewer rows to process and therefore less work
to do, so queries execute more quickly and more CPU
cycles are available for other activities
| - there is less data coming back to the CPU so Ingres data
caches are flushed less frequently
The following operators are supported by the Accelerator and
queries containing them are eligible to be "accelerated":
Operator Meaning
= equal
!=, <> not equal
< less than
> greater than
<= less than or equal, not greater
than
>= greater than or equal, not less
than
IS NULL TRUE if the column value is null
IS NOT NULL TRUE if the column value is not
null
LIKE TRUE if the column value conforms
to a match string
NOT LIKE TRUE if the column value does
not conform to a match string
Note: A character string containing a character range (eg
[A-H]) cannot be handled by the Accelerator.
The Accelerator can handle absolute dates but not date
intervals.
09.015 When and how do I use the INGRES Search Accelerator?
Use of the Accelerator is completely transparent to the
application. The Query Optimizer decides whether or not to
invoke the Accelerator and there is no way, as with normal
queries, of hinting at or suggesting a plan to the Optimizer.
The Accelerator hardware can be fitted "retrospectively" and any
existing applications will benefit from performance increases
automatically.
The Accelerator is used to best effect when there are tables
that would normally be scanned rather than accessed through an
| index. Typically this occurs in MIS systems where ad hoc
queries are issued by the users and the application designer
cannot determine the most effective indexing in advance.
| 09.016 How can I find out more about the Ingres Search Accelerator?
| See the "Ingres Search Accelerator Workload Trials Report"
which gives detailed performance figures for five different
real-life applications. The results presented in the report
were independently verified by management consultants KPMG, and
ranged from a worst case of 2.5 times more to a best case of 45
times more in terms of transaction throughput.
| Details on the product can be had by pointing your Web browser to:
| http://www.icl.co.uk/cguide/cg500009.html
|
09.017 Why am I running out of QSF memory (E_OP0886 and E_QS0001)?
There are two possibilities: the pool is too small for your local
requirements, or the pool is too fragmented (or equivalently: your
queries are too complicated).
Unless otherwise specified, the size of the QSF (Query Storage
Facility) memory pool is estimated from the number of connected sessions
declared when the server is started. By default, the pool size is set
to 60kb+N*40kb, where N is the number of connected sessions allowed.
This formula is just a rule-of-thumb for sizing the pool. In any given
installation the type of work being done may mean this estimate is too
small.
| The allowed number of connected sessions is defined using CBF in
| OpenIngres, and in Ingres 6.4 by -connected_sessions in
| $II_SYSTEM/ingres/files/rundbms.opt. It is possible to increase the
size of the QSF pool by adjusting the allowed number of connected
sessions upward.
A better solution might be to increase the size of the pool directly by
specifying a pool size using the -qsf.pool_size variable in the same
file (this line will usually have to be added to the file). To find
out the current pool size:
SET TRACE POINT QS501
After making the necessary changes, shut down and restart the server.
One suggestion seen on the net is to simply double the pool size;
in the absence of any better advice that suggestion is repeated here.
Note that increasing the size of the pool may not solve the problem, or
not for long anyway. Objects in the pool require contiguous chunks of
space. The pool may have enough total free space, but it may be so
fragmented that no one region is big enough. If enlarging the pool
size provides no lasting relief, there are three possible courses of
action: (i) review and simplify your searches if they are hand coded;
(ii) SET TRACE POINT QS506 to clear the pool completely, or (iii) start
another server, de-register the first one, and register the new one.
Breaking up a complex search with lots of 'ORs' into unions may help.
The rundbms.opt file and its contents are described in the Installation
and Operations manual. The behaviour of the QSF pool is described in
more detail in Advisor note US-16503.
| 09.018 How can I assign particular jobs to particular servers?
The goal here is to have two or more database servers eg: 'LIVE_SERVER'
and 'REPORT_SERVER' running and to be able to select one or the other
when connecting to the database, eg:
sql report_database/REPORT_SERVER < report.sql &
sql live_database/LIVE_SERVER < live.sql &
The REPORT_SERVER could be running at a lower process priority than the
LIVE_SERVER (NB not a good idea to do this on the SAME database).
Existing server types exist in the following file:
II_SYSTEM/ingres/files/name/iiname.all (Unix)
II_SYSTEM:[ingres.files.name]iiname.all (VMS)
It contains something like:
INGRES local transient
COMSVR local transient
STAR local transient
NODE global
LOGIN global
LTICKET local
RTICKET local
To add your own server type add the lines:
LIVE_SERVER local transient
REPORT_SERVER local transient
Servers must be registrated with the name server manually, eg:
$ runrundbms rundbms_noname.opt
Checking INGRES installation environment...
INGRES installation is setup correctly
Starting a DBMS server (iidbms) ...
II_DBMS_SERVER = 2264
$ iinamu add REPORT_SERVER * 2264
It is possible to automate this with some judicious shell/DCL
programming. After this it will be possible to connect to the new
server as described above.
This has the distinct advantage over using II_DBMS_SERVER that the name
server is not bypassed, and so connections to remote hosts are
possible.
OpenINGRES supports the manipulation of server classes through the new
CBF (Configuration By Forms) and no manual jiggerypokery is required.
| 09.019 Should I set II_DMFRCP_STOP_ON_INCONS_DB?
|
| [This environment variable is apparently documented only in some
| release notes for some platforms, although it is thought that this
| feature is supported by all versions of OpenIngres. The following is a
| posting taken nearly verbatim from comp.databases.ingres, explaining
| the purpose of this environment variable. If your release notes do not
| describe it then obviously you use it AT YOUR OWN PERIL. --Roy Hann]
|
| [The] recovery algorithms are predicated on the notion that at any point
| the RCP can be asked to step in and recover either a single transaction
| or all transactions for a failed server. For example, if a server runs
| out of resources or otherwise fails to recover a transaction, it can
| ask the RCP to take over the transaction (this is known as a "pass
| abort"). If the RCP fails to recover a transaction, it has nowhere
| else to go for help: it is the court of last resort. If the RCP
| encounters errors performing recovery, it marks the affected
| database(s) inconsistent. Once a database has been marked
| inconsistent, recovery from a checkpoint is necessary to ensure data
| integrity.
|
| Therefore it is critically important that we ensure that the RCP has
| sufficient resources to be able to abort any transaction at any time
| and to be able to recover all transactions for a server at any time.
|
| In general, very few additional locking system resources are needed
| during recovery, due to the effects of some of the Ingres recovery
| protocols. Certain rare situations exist where many locks are required
| to perform recovery. One such is UNDO recovery of a transaction which
| has deleted rows in a btree table with overflow chains on the leaf
| pages.
|
| Currently, the locking system cannot guarantee that resources will be
| available for the RCP during recovery. In the case of a pass abort, it
| is possible for the RCP to deadlock with other transactions running in
| the server. Either of these conditions can elicit a variety of error
| messages, depending on the state of the RCP when the condition
| manifests itself. The problem at Qantek seems to have been a failed
| pass abort.
|
| When a transaction is selected for UNDO recovery, it by definition has
| not committed, and so it still holds all the normal transaction page
| locks for data pages which the transaction updated. Since Before Image
| recovery operates by replacing data pages with their before images from
| the log, the data pages which are updated by Before Image recovery are
| still locked by the transaction. Thus no new lock resources are needed
| to restore data pages from their before images. The problem arises
| when processing a pass abort which involves changes to btree leaf and
| index pages. If the pass abort happened when the lock list was very
| nearly full, the additional locks required to recover the leaf and
| index pages may not be available. In fact, if the btree table has a
| highly duplicative key, an arbitrary number of locks may be required to
| lock the leaf page overflow chain. The attempt to escalate to a table
| level lock may result in deadlock with transactions still running in
| the server. Deadlock is also possible when attempting to lock the leaf
| or index pages, even if sufficient locks are available. Any of these
| situations will result in the database being marked inconsistent,
| necessitating recovery from a checkpoint.
|
| Recovery from checkpoint may not be acceptable in all situations, so an
| alternate processing scheme which forces the system to shut down rather
| than mark a database inconsistent is provided. To use this alternate
| processing scheme, the INGRES environment variable
| II_DMFRCP_STOP_ON_INCONS_DB must be set to "YES" with the ingsetenv
| utiltiy. When INGRES shuts down, the open databases are left in a
| state which will require the RCP to operate in REDO mode on restart.
|
| When a transaction is selected for REDO recovery, the database
| requiring REDO recovery is taken offline and no access is allowed to
| the database except by the RCP (or CSP in the cluster node failure
| case). REDO recovery takes advantage of this by forgoing normal
| database, table, and page locking during REDO recovery. This
| dramatically reduces the locking resources required to process any
| transactions which must be REDOne or UNDOne. If the locking system has
| been underconfigured, it is possible to reconfigure it before
| restarting INGRES.
|
| With II_DMFRCP_STOP_ON_INCONS_DB set to "YES", almost all conditions
| which would normally result in a database being marked inconsistent can
| be handled without rolling forward from a checkpoint. Since the RCP is
| started before the DBMS servers, it has an opportunity to clean up the
| open databases without risk of lock conflict.
|
| Shutting down INGRES is a drastic measure. It should be avoided, but
| in this case the alternative is worse. The best that can be done is to
| minimize the frequency. To do this, avoid lock escalation. Design
| applications so that table level locks, if required, are taken
| initially, rather than after some number of pages of the table have
| been locked. In particular, avoid escalations due to running out of
| configured locks per transaction. Either configure the INGRES locking
| system with enough locks per transaction to meet the needs of the
| application, or modify the application to require fewer locks. Highly
| duplicative keys should also be avoided.
|
|
|
| 09.020 How do I recover after a fire/flood in the machine room?
|
| 1) Regularly take a valid checkpoint (on or off line) on your
| production machine. Do this checkpoint to either tape or disk, but
| copy it to tape so that it can be stored off-site. When disaster
| strikes call tech support. They will talk you through step 2 and up.
|
| 2) Make sure your replacement system has a database by the same name
| with the same locations and disk paths.
|
| 3) Shutdown Ingres on the replacement box.
|
| 4) Wipe out ALL the data for the replacement machine's default database
| directory.
|
| 5) Copy the destroyed database's aaaaaaaa.cnf file from the checkpoint
| tape to the default database location on the replacement system.
|
| 6) Make the jnl and dmp directories on the replacement box identical to
| the destroyed machine.
|
| 7) Bring Ingres up on the replacement box.
|
| 8) Use "rollforward" with or without journals to "recover" the database
| from the checkpoint.
|
| The only thing to worry about is users, groups, and roles, that are
| stored in the iidbdb.
|
|
|
| SECTION 10 -- QUEL
|
10.001 What is QUEL?
QUEL is the original INGRES DML, now supplanted by SQL for commercial
reasons. There is a small number of very well regarded authorities,
| as well as long-time Ingres users who believe that QUEL is superior
| to SQL.
QUEL is available as part of the INGRES base system. The interfaces
are `quel' (a terminal monitor equivalent and similar to `sql') and
`iquel' (similar to `isql'). Report Writer is able to determine
automatically whether SQL or QUEL is used in the .QUERY section.
The embedded pre-processor is `eqc' for C.
QUEL is based on the relational calculus; SQL is based on the
relational algebra. There are many operations that are straight forward
in QUEL that are not readily reproduced in SQL.
Although it is de-emphasized, QUEL _IS_ supported and Computer Associates
has not yet announced that it will be dropped.
10.002 How different is QUEL from SQL?
VERY different, although in embedded applications, where the two DMLs
are used only for the basic operations of fetching, updating and
inserting, the differences are not very apparent. A few of the
important differences in embedded applications are:
(1) In EQUEL the INGRES identifiers are flagged (viz #part_no =
part_no), while ESQL the host-language identifiers are
flagged (viz part_no = :part_no).
(3) QUEL allows range variables (analogous to SQL correlated table
names) to remain defined between statements. A range variable
declaration need not appear in the source code anywhere near
the reference to the range variable.
(4) The EQUEL `APPEND' statement (analogous to SQL `INSERT') insists
that column be identified by name--columns cannot be addressed by
position.
(5) EQUEL allows the WHERE clause to be defined dynamically, without
recourse to a "dynamic" EQUEL.
As well, some of the other difference that apply to both the embedded
and non-embedded version of QUEL, but which are perhaps of great
importance when converting an application to SQL are:
(1) Some of the more exotic QUEL access controls are not imposed on
SQL accessors--this creates a potential security breach.
(2) Outwardly identical SQL and QUEL views can produce different
results because of semantic differences. (QUEL implements
views so that they act like modifications to the WHERE clause, SQL
implements them so that they act like virtual tables--with
duplicates permitted.)
(3) QUEL auto-commits by default; multi-statement transactions
must be explicitly encapsulated between a BEGIN TRANSACTION
and an END TRANSACTION or an ABORT.
In non-embedded uses, such as `iquel' and Report Writer, the differences
can be very great. A future version of this FAQ may deal with this more
thoroughly.
10.003 Should I learn QUEL?
| Users of OpenIngres have the choice, but QUEL is the only DML available
to users of University Ingres.
If the need arises, QUEL is certainly easy to learn. It is more
regular and orthogonal than SQL. However, mastery of the more subtle
features of QUEL (such as aggregate functions) requires considerable
sophistication.
QUEL may be useful within Report Writer where its capabilities can
allow certain reports to be defined very concisely, particularly those
that require aggregation over many different columns and particularly
those that require aggregation over a function of a column. There are
many operations that can be expressed concisely and non-procedurally
that have no equivalent concise or non-procedural expression in SQL.
Unfortunately, in spite of its manifest superiority to SQL, QUEL is
unique to Ingres now. Proficiency in QUEL may be satisfying
personally, but it will not be transferable to other systems. QUEL is
not under active development any longer, therefore it is not able to
access the user defined datatypes that can be created using the Object
Management Extension. Any reports or applications developed using QUEL
may be hard to maintain in future.
Employers would probably counsel against learning QUEL, academics would
probably argue the opposite.
10.004 Should I convert my QUEL to SQL?
The answer to this question will depend on circumstances. The best
answer that can be offered is a list of the pros and cons of
conversion.
The implicit reason for wanting to do this will usually be
a desire to be able to hire SQL programmers off the street, or
| to take advantage of recent Ingres enhancements that are available
only in SQL. The advantage of being able to hire SQL programmers
| off the street is probably deceptive. SQL as such constitutes less
than 3% of the source code (by one estimate), so familiarity with
| Ingres' forms run-time system and Ingres utilities will always be
the main obstacle to recruiting experienced programmers.
| Wanting to take advantage of new Ingres facilities is a compelling
reason to convert to SQL.
In embedded applications, the conversion of QUEL to SQL is fairly
mechanical, and can probably be undertaken at minimal cost. If
necessary it is possible to do the conversion piecemeal, mixing SQL and
QUEL in the same application. See Advisor note US_13270 for details.
In the case of Report Writer, the conversion will in general be much
more difficult. It is possible to do some very sophisticated data
reduction in QUEL, and if a report makes use of some of the more
powerful capabilities of QUEL, conversion to SQL may well be so painful
that it should be avoided if at all possible. If it cannot be avoided,
plan to spend a long time on the conversion and on testing. It is not
unknown for a relatively modest report of under a 100 lines of code
using QUEL to balloon to over 2000 lines of code when switching to
SQL.
10.005 Are there any utilities for converting QUEL to SQL?
Q2 is a commercial QUEL to SQL converter which automatically converts a
high percentage of QUEL to SQL, and identifies and isolates the
remainder. It is available through Common Sense Computing, who offer a
trial service which allows you to e-mail or send sample QUEL programs
for conversion so you can judge for yourself the effectiveness of the
conversion. Contact details for Common Sense Computing are given in
section 01.017 of this document.
| SECTION 12 -- REPORT WRITER
|
12.001 How can I put ASCII TABs in a report?
There is no easy way to do this. The first way that springs to mind is
simply to print literal TABs enclosed by quotes. However the Report
Writer replaces TABs (and all other carriage control characters (BS,
LF, VT, FF, CR) with spaces so this doesn't work. For the same reason,
neither does assigning the TAB to a variable and using the variable
instead.
The next way that comes to mind is to try:
.QUERY select tab = char(x'09') , x , y , ...
...
.HEADER REPORT
.FORMAT tab(q0)
...
.DETAIL
.PLN x,tab,y,...
But this doesn't work either (although it should; executing the select
statement alone in `isql' shows that it is returning the expected TAB
character). In this case the Report Writer lexical analyzer seems to be
defective--setting printqry shows that it is not interpreting x'09'
properly.
Another possible solution is to create a table with one row and one
column, containing the TAB. This can be selected in the report, but
(as documented) all of the normal formats (c, cf, cj and t) translate
the TAB on output in an unwanted way. The q0 format just doesn't
work--the TAB character disappears.
In the end, the best solution that some of the most inventive
contributors to comp.databases.ingres have come up with is to just
print some unusual character (one which doesn't occur in the data or
the trim) and convert it to a TAB with some kind of output filter.
Ghastly, but there it is.
In fact this may not be as awful as it seems. In a real printed report
it is almost never necessary to use TABs; some number of spaces is
better, and Report Writer does that well. Generally when this question
comes up it is from someone who wants to write a TAB delimited file to
be imported by a PC or Macintosh application. In this case the `copy'
command is a more appropriate tool and it can be directed to delimit
fields with TABs (or anything else) very easily. See 08.026 above.
12.002 How do I put escape characters in a report?
The `q0' format allows you to insert characters in a report without
changing the value of `position_number' (ie: so that the string
logically uses 0 space). This allows arbitrary variable length
strings to be inserted at any point without disturbing the regular
appearance of the report with unnecessary line-breaks and wrap-arounds,
provided that the output device consumes the additional characters.
Example:
.PLN '^[&34p'(q0),string_var
.PLN string_var
will print string_var in the same physical position on both lines if
^[&34p is meaningful to the output device.
12.003 How can I number pages as "Page n of m"
There is no way to do this within Report Writer. The only thing
to do is write a two-pass output filter to count the number of pages
in the report and fix it up afterward. To simplify this process it
would be advisable to have Report Writer number the pages with a
distinctive series of place-holder characters (eg #XX#) to search for
and replace. For example:
.FOOTER PAGE
.CE .PR 'Page ', page_number(i4), ' of #XX#'
12.004 How do I submit a report to run at a given time?
| There is no Ingres facility for job control when running reports. The
"call report" statement executes the report immediately and the user is
forced to wait until it completes. The normal work-around for this is
to use the "call system" command instead and execute the appropriate
operating system command to submit the job to a batch queue. The
syntax of such a command is operating system specific and therefore
beyond the scope of this FAQ.
Some users have written report dispatchers that can be used to run
reports both asynchronously and at specific times.
| SECTION 14 -- ABF
|
14.002 What causes C compilation errors while building an ABF image?
Check that the CFLAGS environment variable is not set to cause the
compiler to reject non-ANSI C. See question 20.001 for a further
explanation.
| SECTION 16 -- OpenROAD/Windows4GL/W4GL
| 16.001 Why is OpenROAD so slow to react to database events?
| OpenROAD polls for database events at a rate defined by
|
SessionObject.DBEventPollrate
which is set at 30 seconds by default. Some users recommend
setting this to as little as 1 or 2 seconds. See the "Language
| Reference Manual for OpenROAD" for further information.
16.002 How can I force a window to stay visible on top?
| This is possible in OpenROAD 3.0 by defining the frame to be a "popup", but
not in versions prior to that. It is a hugely irritating problem for
users of older versions of W4GL.
For older versions of W4GL there are a couple of workarounds that have
been suggested--all fairly hacky. David Tondreau recommends having a
frame send itself a user event at short intervals, with an event
handler that brings the window back to the top.
| 16.003 Is anyone working on an OpenROAD/W4GL FAQ?
|
| Yes. Pat McGibbon is working on one over in the UK, and it is almost
| ready as of this writing.
|
| When it is publicly available, it should either be directly available
| on the NAIUA web site (www.naiua.org) or there will be a link over
| to the OpenROAD FAQ.
|
|
|
| 16.004 How easily can I use the OpenROAD-Oracle driver?
|
| The release notes suggest that the driver works well and there are no
| major bugs to be aware of. Several early adopters agree that this is
| substantially true. However, a few points should be considered before
| agreeing to convert a major Ingres-based application to run on top of
| Oracle (or any other non-Ingres RDBMS) using the non-Ingres drivers.
|
| The OpenROAD-Oracle driver only works with OpenROAD. If the
| application includes Report Writer reports, ABF applications, or
| components written in a 3GL with embedded SQL, they will not be able
| to exploit the OpenROAD Oracle driver, and they will have to be
| rewritten (pretty much from scratch) or you will have to use an Oracle
| gateway (which will only get you started).
|
| Unless you adhered rigidly to the OpenSQL standard when writing the
| OpenROAD application for Ingres (highly unlikely), you must review all
| the SQL code in the application and expect to replace all the
| Ingres-only extensions you have used. (You may be surprised at how
| many you do use.) Fortunately SELECT loops WILL work with Oracle--you
| will not be reduced to using cursors everywhere instead of SELECT loops.
|
| The Oracle optimizer is getting better (so I am told), but it is not as
| good as Ingres at distilling the logical essence out of all the
| equivalent ways of expressing the same query. SQL which works well
| with Ingres may need to be rewritten to suit Oracle's delicate
| stomach, and much trial-and-error may be required to find just the
| right formula.
|
| It boils down to this: the Oracle driver is there to allow Oracle users
| to buy and use OpenROAD, it is not there to help Ingres users defect to
| Oracle and you will have a big job on your hands if you try.
|
|
|
| 16.005 Where can I get more information about OpenROAD?
|
| One source is at Computer Associate's web site. You will find information
| on OpenROAD at the following URL:
|
| http://www.cai.com/products/or.htm
|
| If you want more information, the page includes a form which you can use
| to have CA send you material or contact you.
|
| There is also an OpenROAD Special Interest Group which has been organized
| for OpenROAD users. It is currently headed by Elena Yatzeck of GSB
| Computing Services, University of Chicago. You can contact Elena at the
| following email address: emy@gsbpop.uchicago.edu
|
| A very valuable resource that the OpenROAD SIG started up is a mailing list
| exclusively devoted to OpenROAD issues. This list is managed by the
| Majordomo program. If you would like to subscribe, send an email message
| to the following address:
|
| Majordomo@gsbpop.uchicago.edu
|
| Leave your "subject" line empty, and in the body of your message enter the
| following command:
|
| subscribe openroad-users [address]
|
| This will subscribe yourself at the address you sent the message from,
| or you can specify an email address. If the message is successfully
| received and processed, you will receive a welcome message which includes
| more details on how to utilize the mailing list, and how to unsubscribe
| if necessary. If you have any questions or problems, you can contact the
| list owner at: Majordomo-Owner@gsbpop.uchicago.edu
|
| The OpenROAD SIG, in cooperation with the NAIUA, maintains a Web page that
| includes info on the latest status of OpenROAD, and access to a few utilities.
| The URL of this page is:
|
| http://www.naiua.org/open_road.html
|
| Another source of information should be coming soon. The OpenROAD FAQ,
| mentioned in section 16.003, is expected to be available in a few short
| weeks. Once released, you should be able to access it from the OpenROAD
| web page listed just above (or possibly from the FAQ web page, which is
| at www.naiua.org/faqs.html).
|
|
| SECTION 18 -- NET
|
18.001 What do I do if I can't connect?
This is the question every NET DBA dreads. There can be so many reasons.
First, try a remote login to the server system. If this fails then the
problem is in the underlying communications.
Some other things to check:
o Are the communications servers running on the client system?
o Did they get a successful listen on the protocol you want to use?
Check in errlog.log in the INGRES client installation.
o Are the authorisation details for the user correct in netu?
o Has the password been changed on the remote user? Then you must
update the netu information to match.
o Has the remote user got a password? NET insists on passwords.
Remember a password of 'return' is not the same as no password.
o Are the vnode details correct in netu?
o Has the user got a private vnode of the same name as a global one?
This will be used, the global one is 'hidden' from the user.
o If there is any doubt about any of the netu information, delete and
re-enter it. Note that netu accepts unprintable characters but
won't display them when you 'show' details.
o Are the communications servers running on the server system?
o Did they get a successful listen on the protocol you want to use?
Check in errlog.log in the INGRES server installation.
o Is the 'ingvalidpw' program correctly installed on the server
system?
o Are the listen details in the iirungcc.opt file correct? They must
match the netu information on the client system.
If you can't work it out turn on the NET diagnostics at both the client
and server systems. Set II_GCCxx_LOGLVL to a higher value, see the NET
User's and Administrator's Guide for details. You need to restart the
communications servers for this to take effect.
At least this will tell you how far the connect request got. You should
be able to work out if it left the client and if it arrived at the
server. This will usually isolate the problem to either the client
system or the server system.
If it looks like the communications server on the client sent it OK,
but it never arrived at the remote communications server, you are going
to need help from a network person. Plan on working late.
18.002 How do I configure NET?
There are 2 ends to think about: the client end and the server end.
The tools are the `mkrungcc' script at the server end, and the `netu'
utility at the client end.
At the server end, you have to set up information for the NET
comminication servers using the mkrungcc script. This asks a lot of
questions and encodes the answers in the file iirungcc.opt in
$II_SYSTEM/ingres/files. It also sets up some INGRES environment
variables at the installation level.
Using mkrungcc you set up things like: (i) the number of communication
server processes; (ii) which protocol(s) each will use; (iii) the
listen addresses for each server and protocol, and (iv) the number of
inbound and outbound connections per server. When this has been done,
iistartup will automatically use these details to start your
communication servers at startup time. To change the details just run
mkrungcc again and the new details will be picked up at the next
restart.
At the client end, you also need to set up information using the
mkrungcc script since you need communication servers at both ends. An
annoying feature with the later versions of NET is that a communication
server won't let you make outgoing connections unless it can set up a
successful listen for incoming connections. So if you have a system
where it's used purely as a client and nothing else--where there are
never any incoming connections--you still need functional listen
addresses for the communication servers to use. If a listen fails then
the communication server `blacks' that protocol and won't allow
outgoing connections on it.
Assuming you have the communication servers running on the client
system, you also need to set up client information using the netu
utility. This is a command line question-and-answer tool. You set up
2 kinds of information in netu: (i) vnodes and (ii) authorisations.
The netu information is stored away in $II_SYSTEM/ingres/files/name and
changes should be visible right away. You don't need to do a restart.
"vnodes" are virtual names for target server systems, and the vnode
data you are asked for includes: (i) the real node name of the target
machinel (ii) the protocol to get there, and (iii) the communication
server listen address to aim for. The vnode name is what goes on the
command line to tell INGRES the database is remote, e.g.:
$ isql gnu::mydb
Vnodes can be global or private, commonly the DBA sets up global vnodes
which everyone uses. In theory users could set up their own private
vnodes if they didn't like the global ones but they usually don't
bother.
Authorisations are the information used to actually log you in on the
server system: (i) user name on the target system and (ii) password for
that user. These can also be global or private. Global authorisations
are only useful if everyone wants to be seen as the same user on the
target system, not often the case. Users need to set up their own
private authorisations or (more likely) the DBA does it for them.
The netu utility has an poor method of error reporting. Look out for
the message: `0 rows changed'. This means it didn't do anything and
it isn't going to tell you why.
Hint: set up global vnodes and private authorisations. This is the
only way that makes any sense 99% of the time.
18.003 What is the 'ingvalidpw' program for?
The communications server needs to get at your Unix password
information. On some Unix variants this is not so easy, the encrypted
passwords are stored in the protected file /etc/shadow which only
superuser can read.
The ingvalidpw program does this. It is installed with `setuid root' so
it can read the shadow file. The name server then uses it to validate
username/password combinations for incoming connection requests.
18.004 How can I restart NET without restarting INGRES?
It can be a nuisance to do a full restart of INGRES just to pick up a
minor change to the NET configuration.
Use the iirungcc utility. If there are communications servers running
this will ask if you want to shut them down, then it will start up the
communications servers again using the new configuration.
18.005 How can I shut down the communications servers?
They get shut down automatically by the iishutdown script.
If you need to shut them down manually, use iinamu to get a list of
communications servers like this:
$ iinamu
> show comsvr
> quit
Then use netu to shut down each communications server using the
server_id numbers listed by iinamu.
18.006 Which INGRES processes do I need on a client system?
If you are using a system purely as an INGRES client, and never want to
create or access local databases, then you don't need a full INGRES
service running. You only need a name server and communications
server(s).
One way to do this is to install using iibuild -c. Then iistartup will
only start the processes you need. Alternatively:
$ iirungcn; iirungcc
Whichever way you do it, you save a lot of startup time and main memory
as you don't need DBMS, archiver, or recovery processes. This makes
room for lots more users on the client.
18.007 How do I configure different protocols?
The mkrungcc script tries to figure out which protocols are available
when you run it. For each available one it prompts for server listen
information.
Sometimes it needs a little encouragement. If it doesn't prompt when a
protocol is clearly available and should be supported, you'll have to
tinker with the script a bit. On Unix it may use the pkginfo
command to probe for the presence of OSI and X.25, but if the package
names are slightly different than when the script was written it will
get confused.
A single communications server can listen on multiple protocols. You
don't need a server for each protocol unless you particularly want to
do it that way.
18.008 What is the address and listen information in netu?
This depends on the protocol. Some examples:
For TCP/IP, the address is usually the node name of the target system
as in /etc/hosts (or equivalent). The listen information is used to
calculate port numbers to use. It must match the listen information set
up with mkrungcc on the target system.
For OSI, the address is a communications initiator path set up using
your communications configurer. This will also contain TSAP numbers, so
the listen information in netu is not used. You can set it to
anything.
With DECnet, the address is either a DECnet address or the name of
a node in the NCP database. The listen address is the name of the
network object in the remote node's NCP database, usually II_GCC_0. The
remote machine will translate this object name into a process id and
form a logical link between the two GCC servers. Whereas all
Unix installations have a 2 character code for II_INSTALLATION (see
below) on VMS this only occurs when more than one instance of Ingres
is installed on a machine. Normally the installation code is left
blank. To connect to a different installation the listen address should
be II_GCCxx_0 where xx is the installation code. It is also possible
(but usually unnecessary) to have more than one GCC server, in which
case the listen address is II_GCC_n where n=0,1...
Other protocols do it differently. Check in the NET User's and
Administrator's Guide.
18.009 How does NET figure out its TCP port numbers?
When NET is using the TCP/IP protocol, it uses the listen information
to work out a port number for the communications server to listen on.
The listen information is in the form 'ab' or 'abN', a 2-character
identifier followed optionally by a number. When you give this to
mkrungcc, it's put through an algorithm to generate a unique port
number. When you give it to netu it's put through the same algorithm,
so the client can figure out the TCP port numbers on the target
system.
There's usually no good reason not to use the default, which is the
2-character INGRES installation id with a number on the end for multiple
communications servers.
Note: you can also give numeric TCP port numbers directly and bypass
this mechanism.
18.010 Can I have 2 installations running NET on the same system?
Yes, provided you use different listen information. How to do this
depends on the protocol.
For TCP/IP you can use listen information based on the unique
2-character INGRES installation ID. This guarantees unique port
numbers for the communications servers in each installation.
For OSI you need to use different TSAP numbers in the communications
responder paths for the 2 different installations.
For DECnet specify the listen address as the name of the DECnet object
as described in 18.008 above.
For other protocols the principle is the same. See the NET User's and
Administrator's Guide for details.
At the client end you need to tell netu about the different listen
information, e.g. for TCP/IP:
vnode protocol address listen
gnu_test tcp_ip gnu xx
gnu_prod tcp_ip gnu yy
Or for OSI:
vnode protocol address listen
gnu_test iso_oslan gnutest notused
gnu_prod iso_oslan gnuprod notused
In this case the 2 communications initiator paths 'gnutest' and
'gnuprod' contain the TSAP numbers of the different INGRES
installations on the remote server system.
18.011 How can I use different protocol routes to access the same server?
Suppose a client and a server are connected by both TCP and OSI, which
is not that uncommon in some environments. How do you tell NET which
to use for your connection?
Actually this is quite easy. Just set up 2 global vnodes, e.g.:
vnode protocol address listen
gnu_tcp tcp_ip gnu II gnu_osi
iso_oslan gnupath notused
Then you can choose which protocol route by using the appropriate
vnode:
$ isql gnu_tcp::mydb # goes via TCP/IP route
Or: $ isql gnu_osi::mydb # goes via OSI route
18.012 Can NET use all of my LAN boards?
It's common for larger systems to have several LAN boards, and each of
these will have its own network address. Can NET handle this?
This isn't too difficult. The netu utility has a 'merge' option for
vnodes to cater for this. It allows you to give additional network
addresses for a vnode. These are used in a round-robin fashion as
connections are made by the client, which spreads the connections
evenly across the communications boards on the remote server system.
E.g. for TCP/IP:
vnode protocol address listen
gnu tcp_ip gnu1 II
gnu tcp_ip gnu2 II
In this case gnu1 and gnu2 are entries in /etc/hosts (or equivalent)
which contain the different Internet addresses for the remote server.
Or for OSI:
vnode protocol address listen
gnu iso_oslan gnupath1 notused
gnu iso_oslan gnupath2 notused
In this case gnupath1 and gnupath2 are 2 communications initiator paths
set up with the communications configurer, they contain 2 different
network addresses for the remote server.
18.013 Can I use NET over a LAN bridge?
Yes, no problem.
But watch out for performance. LAN bridges typically run at speeds like
64 kbaud, which is a whole lot slower than the LANs at either end. They
can become a bottleneck as the traffic levels rise.
18.014 What about NET over WANs?
NET supports X.25 on some ports. This allows WAN connection over
long-haul X.25 networks.
However you must consider whether you really want to run NET traffic
over a slow network. NET was designed for LANs where message size is
not an issue. And each SQL interaction will pick up multiple network
turnrounds. In practice it is very, very hard to get reasonable
performance. (Don't even think about 9600 baud lines.)
If you want to do this, accept that the whole application design has to
be geared around the network bandwidth. Reasonable response times
dictate 1-2 SQLs only in many cases. You may also be forced into
(possibly nasty) compromises: shorter table and column names, heavy use
of database procedures, shorter names for these and their parameters,
all queries made repeat. Anything to cut down the network load.
18.015 What if I change my password on my NET server username?
This password is also known to netu on each client that connects via
NET to that server. It's stored in the local authorisations on each
client.
You have to go round the network and change the password on all the
clients too (assuming you can remember where they all are).
18.016 What do communication servers do?
When a communication server starts up, it gets its listen information
from the iirungcc.opt file in $II_SYSTEM/ingres/files. This has a line
for each communications server that gets started by the iirungcc
utility. It will look something like this:
II_GCC_TCP_IP=ab;export
II_GCC_TCP_IP II_GCC_TCP_IP=ab1;export
II_GCC_TCP_IP;II_GCC_ISO_OSLAN=gnu_ab;export II_GCC_ISO_OSLAN
This information starts 2 communications servers. Server 1 listens on
TCP/IP with a port number calculated from 'ab'. Server 2 listens on
TCP/IP with a port number calculated from 'ab1', and also on OSI using
the responder path 'gnu_ab'. This path has already been set up using
the communications configurer, and contains the TSAP numbers which are
used to listen on.
The main functions of a communications server are:
o It attempts a listen on each protocol defined by its line in
iirungcc.opt. The result is logged in errlog.log whether successful
or not. If a listen fails then the communications server won't let
you use that protocol for outgoing connections as well.
o It listens for incoming connection requests from the network. When
one arrives passes it's passed to the name server for validation,
and then gets a local DBMS connection for it.
o It listens for outgoing connection requests from local client
processes for the network. When one arrives it gets a network
connection for it.
o Incoming messages from the network are converted from network
format to local data format, then forwarded to the corresponding
local DBMS connection.
o Outgoing messages for the network from local client processes are
converted from local data format to network format, then forwarded
to the corresponding network connection.
The communications server stays in the message path. On the client your
SQL is passed to the local communications server, which forwards it to
the remote communications server. This then forwards it to the DBMS
server. The results go back down the same route.
If the client and server systems have the same hardware format then the
data isn't converted to/from network format. Conversion is only done
for heterogenuous connections.
18.017 Will my applications run over NET unchanged?
Yes, the SQL will run across NET exactly as it runs locally. If the
client and server systems are the same type of hardware, and run the
same version of INGRES, you should not even need to recompile.
For a 4GL application use the -d flag to redirect the connection:
$ my4glapp -dgnu::mydb
You may have problems if you use a local filesystem from the application
Also check out things like hard-coded path names. Small differences in
the environment are another problem area.
If your application fires off background activities, you need to think
whether these are best run on the client or the server. Use something
like uux to start remote batch or reports. Most environments/protocols
will provide some kind of remote execution facility.
DBA things are usually better done on the server system. A number of
DBA-type functions won't run over NET, e.g. ckpdb.
18.018 Will my applications perform over NET unchanged?
If your application is very well designed and built, and the SQL
technique is good, then yes.
Unfortunately, many real world applications aren't like this. They
break the golden rules:
1. Omit Needless SQL
2. Only Get The Data You Need
The cost of extra SQL or fetching large data sets will slow down a
local application. Over NET it can make an application unusable. In
practice most applications need to be modified to get the right
performance over NET. The network changes the trade-off between design
and programming ease, and performance.
There are some kinds of things that are inherently unsuitable for NET:
o Heavy batch processing which brings a lot of data to the program
for processing. Run these on the database server system.
o Long reports which again must by their nature fetch large amounts
of data. Run these on the database server system and use remote
printing or file transfer to get the results to the right place.
o Database browsing where large data sets may be fetched. Impose
limits in the browsing function so this can't happen. Or initiate
a batch process on the database server system.
o Tablefields which bring back many rows to the client. Don't.
18.019 What's the GCA protocol?
This is the application-level dialogue rules for a conversation between
an INGRES client and server process. It's the same whether the
connection is local, or remote via NET.
It's not the same as a communications protocol. You still need TCP/IP
or OSI or whatever, GCA is a level above these and runs over them. They
are used to transport the GCA conversation.
In some ways it's not a compact protocol. For example: a simple SELECT
will cause 4 or 5 GCA messages to be exchanged. This might cause up to
10 network packets. You can start to see why careless SQL can cause
heavy NET traffic.
18.020 Does NET present any special security problems?
An INGRES/NET client stores the passwords needed to access a remote
system in a local encrypted file. It must decrypt the password to
transmit it to the server to authenticate access. It is therefore
conceivable that a sufficiently well informed and industrious
"intruder" could analyse the executable code and determine how to
decrypt the local password file.
Usually the effort and expertise required will be sufficient
discouragement, but if a system contains especially sensitive or
valuable data then it will naturally be attractive to precisely the
kind of people who have the skills and inclination to penetrate it.
Some precautions can be taken:
o The first precaution is to check that the local password
file on Unix clients is secured so that only `ingres' processes
can read it. If it is not, then the following will fix it:
chown ingres $II_SYSTEM/ingres/files/name
chmod 700 $II_SYSTEM/ingres/files/name
o If the user has no need to access the remote host by any
means other than NET then that user's login shell can be set to
something harmless like /etc/date. NET will still be able
to access the system but it will be impossible to actually
log in as that user.
o If the user DOES need to log into the remote system, assign the
user a separate ID to be used just with NET and give it an unusable
shell like /etc/date. Such additional IDs would not require a
home directory or other significant resources. The password of
just the NET ID would need to be stored on the client.
18.021 Can I run INGRES clients without NET?
There's an undocumented facility which may allow you to do this,
sometimes referred to as INGRES/LAN or direct connect.
You set II_DBMS_SERVER to the address of a remote DBMS server process:
$ II_DBMS_SERVER='gnu::1234'
$ export II_DBMS_SERVER
Now all INGRES connections from this shell will go to server id 1234 on
the remote server system with the TCP node name 'gnu'.
Performance will often be better as you are bypassing the NET
Communication Servers at both ends, reducing path lengths and process
switching.
It has a number of limitations. Some of these are:
o It only works with TCP/IP protocol, because it's a side-effect of
INGRES processes communicating via TCP loopback sockets.
o It probably only works for UNIX clients and servers.
o Since you bypass the NET Communication Servers you don't get data
format conversions. So both the client and server systems must have
the same hardware format: same byte order, same float format,
same char set, etc.
o You have to give the DBMS server port numbers directly, and these
change every time the server is reloaded or a new DBMS server
starts. You will need to set up a mechanism to propogate the
server ids to all the client systems.
o It follows that there's no load balancing since you bypass the
Name Server on the remote server system. You have to arrange
the mapping of client connections to DBMS server processes
yourself.
o This is an undocumented facility and it may cease to work at any
time in the future without any warning or apology from Computer
| Associates.
If you decide to use this for real work you'll need to check your
support status with your supplier's Technical Support.
------------------------ END OF PART 3 ------------------------------
| SECTION 20 -- EMBEDDED
|
20.001 What C compilers are supported?
The native C compiler of all the supported systems, as described in
section 01.008 above, plus Microsoft C (versions 5.1, 6.0a and 7.0)
| on the PC, and Whitesmiths on the VAX under VMS, though 6.4/05 will
| be the last release to support the Whitesmiths compiler according to
| the Release Notes Supplement, Release6.4/05 (vax.vms/02), 21-nov-94,
| p 2.
Note that building an executable within ABF, or by using "abfimage"
causes INGRES to convert the ABF source into C code which is then
compiled into an executable using the native C compiler. (This happens
even if the installation licence does not permit ESQL/C to be used.)
The C code that INGRES generates is K&R C, not ANSI C. Any compiler
options should, therefore, be set to not flag errors or warnings if
non-ANSI C is encountered, otherwise the ABF compilations will fail.
20.002 Can I use C++?
In the current release there is no SUPPORTED way to use C++. However
numerous people have done it, going to greater or lesser lengths.
The simplest approach, suggested by Holger Pampel, is to use the
following definitions for the INGRES library functions:
extern "C" {
int IItbinit (...);
int IItbact (...);
int IItbsetio (...);
void IItbsmode (...);
int IItscroll (...);
int IItdelrow (...);
void IItfill (...);
int IItcogetio (...);
void IItcoputio (...);
int IIdispfrm (...);
int IIrunform (...);
void IIforminit (...);
int IIchkfrm (...);
void IIendfrm (...);
void IIforms (...);
int IIfsetio (...);
void IIputfldio (...);
void IIgetfldio (...);
int IIactfld (...);
void IIredisp (...);
void IIresnext (...);
void IIclrscr (...);
void IIresfld (...);
void IIclrflds (...);
void IIendforms (...);
void IIprmptio (...);
void IIresmu (...);
int IIiqset (...);
void IIiqfsio (...);
void IIstfsio (...);
void IIfrshelp (...);
int IInfrskact (...);
int IIactscrl (...);
void IIutsys (...);
void IIcsOpen (...);
void IIcsQuery (...);
int IIcsRetrieve (...);
void IIcsERetrieve (...);
void IIcsERplace (...);
void IIcsGetio (...);
void IIcsClose (...);
int IIcsDaGet (...);
int IIretval (...);
void IIretinit (...);
int IInextget (...);
void IIgetdomio (...);
void IIputdomio (...);
void IIwritio (...);
void IIsyncup (...);
void IIxact (...);
void IIgetoper (...);
int IIinitmu (...);
int IInmuact (...);
int IInestmu (...);
int IIrunnest (...);
void IIendnest (...);
void IIrunmu (...);
int IImuonly (...);
int IIendmu (...);
void IIsqInit (...);
void IIsqConnect (...);
void IIsqDisconnect (...);
void IIsqStop (...);
void IIsqMods (...);
void IIsqFlush (...);
void IIsqPrint (...);
int IIsqPrepare (...);
void IIsqDescribe (...);
void IIsqExStmt (...);
int IIsqExImmed(...);
void IIFRgpcontrol (...);
void IIFRgpsetio (...);
int IIFRitIsTimeout (...);
int IIFRafActFld (...);
void IISmessage (...);
void IImessage (...);
void IITBceColEnd (...);
char * IILQint (...);
int IIsexec (...);
int IInexec (...);
int IIexExec (...);
int IIexDefine (...);
int IIerrtest (...);
int IIbreak (...);
int IIflush (...);
int IILQpriProcInit (...);
int IILQprsProcStatus (...);
int IIeqiqio (...);
int IILQprvProcValio (...);
int IILQsidSessID (...);
int IILQssSetSqlio (...);
void IILQisInqSqlio (...);
int IILQshSetHandler (...);
}
If this list is incomplete or new functions are encountered, it is
simple to add them; it appears the only two choices for type are
`void' and `int'.
| SECTION 21 -- DATABASE PROCEDURES
|
21.001 What are database procedures?
Database procedures are pieces of code that reside and execute within
the database server. There are two main advantages to this approach:
- common pieces of code can be shared among a number of applications
thereby ensuring that each application remains consistent with the
others
- transactions consisting of a number of SQL statements can be
batched up into a single database procedure called only once. This
provides a significant saving in inter-process communication
between the front- and back-ends. This saving is especially
noticeable in client-server applications where the network
delay is relatively large.
21.002 Database Procedures and QEP's
The QEP for a database procedure is calculated whenever the procedure
is loaded into the database server's cache. This happens either when the
procedure is first created or when it is first executed by a user after
having been flushed out of the cache, which can happen through lack of
use or the server having been shut down and restarted. Thereafter the
stored QEP is used for successive invocations of the same procedure by
both the original and other users.
The advantage of storing the QEP is that when most users come to
execute a database procedure the QEP has already been calculated so
the procedure starts to execute immediately. The effect is as if every
SQL statement within the procedure were "repeated". To facilitate
| this, Ingres allows five times longer than normal to calculate the
initial QEP before timing out to help ensure that the QEP used is
optimal.
The disadvantage is that the QEP does not take account of any
statistics that might have been gathered for tables referenced by the
| procedure. Ingres always uses the defaults, which are 1% of rows hit
for exact matches and 10% for range matches. Therefore the QEPs
generated may not be the best possible.
The best work-around for this is to try and design database procedures
to only use queries that join and restrict using key fields. This
gives the Query Optimizer less scope for getting it wrong.
21.003 Database Procedures and Locking
| Ingres uses the QEP to estimate how many rows any particular query
will affect. It is this estimated row count that is used to determine
what type of locks will be taken out when the query is actually
executed. The key words here are "QEP" and "estimated row count".
| If Ingres estimates (from the QEP) that an update will affect 10 or
| more pages in a table then it will automatically take out a table level
lock before starting to do the updates. It does this assuming that
starting with page level locks and then escalating will be a waste of
time, which is quite reasonable under normal circumstances.
The QEP for a database procedure is calculated when the procedure is
first created. If that procedure contains an update statement, that
| would affect more than 10 pages, then Ingres takes out a table level
lock as soon as the procedure starts to execute EVEN IF THE UPDATE
STATEMENT IS WITHIN AN IF..THEN BLOCK AND IS NEVER EXECUTED.
Indeed, even if the database procedure returns without performing any
database access at all the locks that might possibly be required are
| still taken out.
21.004 Can I flush out procedures without shutting down the server?
There are only two ways to flush database procedures out of the server
cache. The first is to shut the server down and the second is to drop
and re-create the database procedure. Neither of these are terribly
practical for a live system. However, it is possible to *refresh* a
database procedure.
| Database procedures are timestamped automatically by Ingres so that
it can tell whether or not they are out of date with respect to the
tables referenced within them. Thus if you modify one of these tables
| Ingres has to regenerate the QEP for the database procedure (because
you might have taken away an index column that it was relying on).
However you don't need to add or remove any columns to update the
timestamp of the table - just simply modify it to the same structure
as it was originally and this does the job.
The database procedure is marked as out of date and the next time that
a user tries to run the procedure it is reloaded into cache and a new
QEP generated.
21.005 Database Procedures and Security
Permission to run database procedures can be granted to individual
users by use of the SQL "grant" command. The advantage of doing this
is that it provides an additional level of security and database
integrity over that provided by granting access to tables alone.
A typical example is with a master-detail relationship. If inserts to
the detail table are denied to all users then they can only insert
rows via a specially written database procedure. This procedure can
ensure that a master record exists before allowing a detail record to
be inserted thereby ensuring referential integrity.
Note that this example is slightly contrived because the same could be
| achieved more flexibly by the use of a rule to invoke the database
procedure.
21.006 Database Procedures and Cacheing
| Ingres caches database procedures in the server on a least recently
used (LRU) basis. Thus if a user tries to run a database procedure and
there is no room for it in the cache then an "old" procedure is
cleared out to make room for it. Normally this is not a problem and is
generally an effective solution to the problem of finite cache space.
However it does mean that designers should avoid having one or two
very large database procedures as these will need to flush out a large
number of small procedures in order to make sufficient space
available. The effect of this is that the procedures flushed out are
not necessarily "least recenty used" but can in fact be frequently used.
The advantages of cacheing are therefore destroyed.
The solution is to code these large procedures into the application
rather than use database procedures. Ideally all database procedures
| would be roughly of the same size.
| SECTION 30 -- errlog.log
|
30.001 My error log is full of E_SC022F_BAD_GCA_READ. What's wrong?
| This error is logged whenever the connection between the Ingres
server and a front-end application is broken unexpectedly. Usually
this just means that the user terminated the application abruptly
without allowing it to disconnect first. Segmentation faults in the
application, pressing the BREAK key, and a host of other occurrences
can cause this. It is almost certainly benign--or is not a DBA problem
anyway.
The error "E_CLFE06_BS_WRITE_ERR Write to peer process failed" is
indicative of the same problem.
30.002 How can I trim errlog.log?
errlog.log can grow to enormous size quite quickly. To truncate it,
first shut down the servers, then
rm $II_SYSTEM/ingres/files/errlog.log
The file will be re-created with suitable permissions and ownership
| when Ingres is re-started.
To keep some recent history in the log (say 500 lines), shut down the
servers and then:
cd $II_SYSTEM/ingres/files
tail -500 errlog.log > /tmp/errlog.log
rm errlog.log
mv /tmp/errlog.log .
chown ingres errlog.log
chgrp ingres errlog.log
chmod u+rw,g+r,o+r errlog.log
No matter how you do it, it is HIGHLY inadvisable to truncate the
error log without first shutting down the servers.
| SECTION 90 -- INGRES ON UNIX (Solaris, AIX, OSF/1, etc.)
|
90.001 How can I un-nice iidbms (Ultrix)?
Once a process accumulates 10 minutes of user CPU time, Ultrix and
some other BSD derivatives will automatically "nice" that process
so that it executes at a deferred priority. Because iidbms is quite
intensive it tends to be "niced" pretty quickly. To overcome this,
give the process negative niceness (ie an elevated priority).
Giving iidbms a niceness of -1 will give it only a very slightly
elevated priority but its priority will never be lowered. iidms can
be given a niceness of -1 by user `root' with:
renice -1 -p
The execution priority of iidbms under Ultrix may in fact not be all
that important. The time delay required for any amount of disc I/O
will usually swamp any delay due to reduced execution priority.
| 90.002 Can Ingres make use of PrestoServe?
Yes it can. PrestoServe is a write-through disc cache built into some
models of DECSystem that can speed up write operations. It makes no
difference to read performance.
A word of warning though: if PrestoServe is enabled on ALL disc drives
| it will become a critical single-point-of-failure for Ingres because
writes to the journal file as well as to the database and to the
transaction log will be buffered. If PrestoServe fails in such a way
that it scribbles on the disc farm (and there is a precendent) all the
data written since the last checkpoint to TAPE may be lost, no matter
how you journal. It would be prudent to disable PrestoServe for at
least the drive where the journal files reside. Since the journal
file is written asynchronously there will be no performance penalty
for this.
90.003 What do the iislave processes do?
| The Ingres DBMS server processes work on behalf of many users. It must
be able to fire off an I/O request and then continue with other work,
picking up the I/O when it has finished. The DBMS server cannot afford
to block waiting for I/O requests to terminate. This is accomplished
under VMS through the SYS$QIO system service, which provides an
asynchronous I/O subsystem.
In UNIX there is no standard asynchronous I/O interface. A few vendors
| provide their own, but they are all different. Ingres must provide a
simulation of asynchronous I/O itself.
The iislave processes provide this simulation. All database I/O
requests are handed off to a pool of iislave processes. Their function
is to take an I/O request and block waiting for it to complete. This
allows the DBMS server to continue with other work while I/O requests
are processed.
Note: each DBMS server process has a separate pool of I/O slave
processes.
90.004 How many iislaves do I need?
| There is an Easy Answer: one per database disk. Like most Easy Answers,
there's more to it than that:
o A *busy* iislave spends 99%+ of it's time blocked waiting for I/O
requests to terminate. It has a finite throughput limit. So the
more I/O slaves, the higher your potential database I/O
throughput.
o On the other hand, the more iislaves you have then the more process
switching you incur. This can lower throughput, especially with
some RISC CPUs which don't tolerate high process switch rates very
well.
o The end result is that for any particular workload and hardware
configuration, there is an optimal number of iislave processes.
Fewer or more will reduce the throughput.
This model was tested by running an implementation of the TP1
benchmark, and establishing the optimum number of iislave processes by
measurement of different runs. Then the CPU board was replaced by a
faster model and the process repeated. No other changes were made.
The results showed clearly that the optimal number of iislave processes
was higher with the faster CPU. But how do you calculate the optimal
number for your workload and configuration? You just have to try it and
see...sorry!
To muddy the waters yet further, there also obscure occassions when you
can get better performance with no slaves at all! These would occur
when you have a single user performing a single task (such as a bulk
copy for example), in which case you can eliminate the interprocess
communication overhead by letting iidbms do its own I/O.
90.005 Why is a raw log file faster on UNIX?
On most flavours of UNIX a raw log file is recommended for better OLTP
performance.
There are some known reasons for this:
o UNIX will update the inode timestamp after each write to the log
file. This not only doubles the write activity, it introduces head
| movement for *every* write. With a big log file the average head
movement, and hence delay, can be large.
A raw log file does not incur *double writes* and so the disk
heads can sit on EOF (nearly) all the time. Writes can be very
fast with only a rotational delay and little or no head movement.
o The effect of log file traffic on the UNIX FS cache is disruptive.
The *flow-thru* nature of log file activity is a worst case for
the FS cache, pushing other things out to make way for log file
blocks which are never hit again.
A raw log file bypasses the UNIX FS cache completely and so avoids
disrupting cache discards.
Remember that the log file is your 'final limit' on OLTP throughput. An
I/O bottleneck on the database can be addressed by relocating tables or
using multi-location tables. An I/O bottleneck on the log file is
final.
There is no concept of a raw log file in VMS.
90.006 Should I stripe my raw log file on UNIX?
Probably not. There is little to be gained, even if your virtual disk
implementation allows it.
Almost all log file activity is writes to EOF. This is where the
bottleneck occurs. Using a striped disk would just switch the
bottleneck periodically from one device to another. There'd be very
little overlap, potentially only when the switches occurred.
This could look very confusing in your system activity stats. These
are usually averaged over a relatively long period, so it would appear
that the devices for the log file striped disk were sharing the load.
A true picture would only emerge if stats were obtained for very short
timeslices.
Note: some virtual disk packages have a special type of striping which
is designed for raw log situations, e.g. 'interleave' disk on the
Pyramid implementation. These guarantee that successive writes go to
different physical devices. These have the potential to increase log
file I/O throughput and may be worth experimenting with.
| 90.007 How do Ingres processes communicate on UNIX?
| Ingres inter-process communication (IPC) uses available O/S mechanisms
| in each environment. On UNIX, Ingres uses loopback sockets for IPC.
This is normally done using the TCP loopback device driver in the UNIX
kernel, although UNIX domain sockets are an alternative in some UNIX
environments.
| The Ingres client opens a socket when it connects to the DBMS server.
The DBMS server also opens a socket at its end. The pair of sockets
are connected together by the kernel, as they are opened with an
address which indicates *loopback*.
Because you are using TCP loopback sockets it follows that the TCP
| package must be installed to use Ingres. However, there's no need for
a LAN board as the sockets get looped back together at a higher level
in the kernel.
| Note: some of Ingres' own processes communicate through shared memory
and semaphores for speed: e.g. DBMS server and iislaves, DBMS server
and RCP.
| 90.008 How does Ingres maintain database integrity on UNIX?
It's well known that UNIX I/O is deferred: a successful write system
call just means that the data will be written to disk *sometime* when
| UNIX sees fit. As Ingres uses UNIX filestore files, how can it control
what is written to the database disks and when?
| Ingres uses a special flag on the open system call to do this.
Database files are always opened with the OSYNC flag, this forces all
writes to that file to be done synchronously. Now a successful write
system call means that the data is *definitely* on the disk.
| So Ingres can be sure what is written to the database and when, and
database integrity can be maintained.
90.009 Should I stripe my data locations on UNIX?
| It's not usually a good idea. Ingres provides multi-location tables
which are striped by the DBMS. In most situations these are the better
choice.
Another effect of striping your data locations is to make performance
analysis more difficult. It's often necessary to undo the striping to
isolate an I/O bottleneck to a particular table.
Note: if you do use disk striping, the information which defines the
striped disk becomes vital to the integrity of your database. If you
lose this the striped data locations are just meaningless bit
patterns. You need to back it up with as much care as the rest of your
data.
90.010 Where do I start looking with a Unix performance problem?
Let's assume this is a workload problem, rather than a particular
query. With those you can jump straight into QEP analysis.
This is a big subject, and the best we can do is give a few guidelines
that may help get you started.
The basic tool is the Unix system activity reporter, the sar utility.
The options on sar vary somewhat according to your flavour of Unix (and
in fact, sar does not exist in all flavours of Unix), but these are
pretty universal:
o The -u flag. This gives CPU usage, and also I/O delay. If %wio is
low and %idle is low or zero then the CPU is saturated. If %wio is
high for long periods (say >10%) then you have an I/O bottleneck
somewhere.
o The -d flag. This gives disk loading for each device. If %busy is
high for long periods (say >50%) then a disk may be saturated.
Also look at the disk balance, and think about disk controllers.
There are no direct activity stats for these, but try to work out
if there are too many busy disks on each controller.
The avwait and avq figures should pick out disks with slow service
times. This could be due to filesystem fragmentation, or bad disk
layouts with active spots far apart on the disk.
o The -wq flags. If %swpocc is non-zero or swpq-sz is non-zero then
swapping has occurred. Memory is oversubscribed, a serious sin on
a Unix system.
This should at least direct you to an area for further investigation.
In general you should sort out system resource problems before you
| start fiddling with QEPs or Ingres tuneables.
| After sar, the next step is often to look at Ingres logging and locking
using IPM.
90.011 Which filesystem type should I choose? (SVR4)
On SVR4 Unix the virtual filesystem concept gives you a choice of
filesystem types, typically: System 5 1/2 Kb, System 5 1 Kb, System 5 2
Kb, Berkeley UFS. Later SVR4 versions also have Veritas as supplied.
Experience has shown that the System 5 types are best avoided. They
suffer badly from free list fragmentation and require regular
maintenance to maintain performance. In you decide to use System 5, go
| for the 2 Kb blocksize which matches the Ingres page size.
Berkeley UFS is a good general purpose choice. They do not fragment
readily, and offer a range of tuning options to maximise filesystem
performance.
Veritas (VxFS) is a possible alternative. It does not suffer badly
from fragmentation and has an on-line defragmenter. It also offers
fast restart after a crash compared to the other types, and is more
resilient to power-off situations.
90.012 Will VxFS give me better performance? (SVR4)
VxFS offers a number of advantages over other filesystem types:
resilience, fast restart, on-line defragmenter. But as always, there
is a price to pay.
A VxFS filesystem has an intent log beside the inodes on the disk. It
| has a similar function to the Ingres log file: changes to the
filesystem are logged here before the change is applied for real. Only
structural changes are recorded in the intent log, e.g.:
o create/destroy directory
o create/destroy file
o add block(s) to a file
After a crash or power-off, the intent log is used to restore the
integrity of the filesystem structure. This avoids the lengthy fsck
checks needed for other filesystem types, giving a faster restart.
The disadvantage of the intent log is extra I/O and head movement.
Every structural change requires the disk heads to skip to the intent
log and write to it, then skip to the required position to do the
change. It's a double write with often a big head skip. If a
filesystem is volatile and has continual structural change, VxFS can
give poor performance.
It's not always obvious in an application when structural changes will
occur to the underlying filesystem:
o Use of temporary tables is an obvious case. It's probably best to
avoid VxFS for data locations which are used for temporary tables.
o Adding rows to the end of a table is less obvious. From time to
| time extra blocks have to be added to the underlying file. Ingres
does this synchronously, writing the changes through the page cache
to ensure EOF integrity. This will incur an extra cost with VxFS.
o Changing the primary key of some rows in a table is even less
| obvious. The rows are physically moved by Ingres, and extra blocks
| must be added to the underlying file. Again Ingres does this
synchronously to ensure data integrity. VxFS adds to this cost
with intent logging, causing doubled writes and a lot of head
movement.
o Changing the secondary key of some rows in a table will cause rows
to be moved in the secondary that is changed. Same as the previous
case.
You have to know your physical database well to forecast if an SQL will
result in structural change to the underlying filesystem. Such SQLs
will tend to perform poorly with VxFS, particularly in cases where a
lot of blocks have to be added synchronously. VxFS is not a 'magic
button' for performance!
It's good for: non-volatile filesystem with little structural change,
fast restart requirements, on-line filesystem maintenance. It's not so
good for: temporary tables, bulk load, anything volatile at the
structural filesystem level. It can be hard to predict if a particular
workload will run better on VxFS - without trying it to see.
Note: never use VxFS with intent logging enabled for things like /tmp,
mail partitions, II_TEMPORARY partition, etc.
| 90.013 What kernel resources does Ingres need? (SVR4)
| You must configure sufficient resources in the Kernel for Ingres.
Here is a summary of experience with SVR4 Kernel tuneables:
o Shared memory resources. The tuneable SHMMAX limits the size of a
shared memory segment. It must be set large enough for the size of
| your Ingres logging and logging segment. If you use a shared cache
then it must also be set large enough for this.
o Semaphore resources. The tuneables SEMMNS and SEMMSL limit the
maximum number of semaphores and the maximum semaphore set size.
| See the I & O Guide for details of how many semaphores your Ingres
service needs. There is a basic set, then a set per DBMS server.
The SEMMNS tuneable must be set large enough for the total.
Currently the DBMS server requests a set of 30 semaphores, for a
potential 30 I/O slaves. Even if you have the default 2 I/O
slaves, the server still asks for the full set of 30 semaphores.
The tuneable SEMMSL must be set large enough to allow this, the
default of 25 is not enough.
| o TCP resources. Since Ingres uses TCP loopback sockets for inter-
process communication, there must be enough for the number of users
to be supported. The name of the Kernel tuneable may be NTCP.
| Allow 2 sockets per Ingres user, or 3 per TCP/IP NET user. Add an
allowance for listen sockets and other overheads, and other users
of TCP/IP networking.
o Swap resources. On SVR4, swap space of 2 x memory is usually
enough. Allow a little more if you want to be extra safe.
The iistartup utility does a system resource check before actually
| starting the Ingres processes. It will usually refuse to continue if
there is a serious resource problem.
In this case you need to supply the resource, by changing the Kernel
tuneable as indicated above, and rebuilding the Kernel. This must be
followed by a system reload.
The exception is swap space, which can be added dynamically using the
/etc/swap command.
| 90.014 What process limits could affect Ingres? (SVR4)
The SVR4 Kernel provides a system of hard and soft limits on some of
the resources available to each process. The shell builtin command
ulimit (or limit/unlimit for csh) is used to set and display their
current values within the process.
| The process limits used for Ingres processes like the DBMS server are
inherited from the environment where the iistartup utility is run.
Here is a summary of experience with these limits:
| o The CPU time limit H/SCPULIM applies to all Ingres processes,
including the DBMS server process. It will need to be set to a
high value or 'unlimited' for server processes. These are shared
processes and do processing on behalf of many users.
| o The file size limit H/SFSZLIM applies to all Ingres files: table
files, journal files, dump files, disk checkpoint files, non-raw
log files. Set it high enough for the biggest table fragment, or
the biggest location in a disk checkpoint. It is not applied to
raw log files.
o The data heap size limit H/SDATLIM applies to the data structures
created at startup by a DBMS server in its own private data areas,
including the (non-shared) DMF page cache and the QSF memory pool.
For a large DBMS server with large connected_users value, or if you
configure a large non-shared DMF page cache, it can be necessary to
raise this limit.
o The open file descriptor limit H/SFNOLIM applies to all files opened
by a DBMS server process or by an I/O slave process. Don't confuse
the process file descriptor limit with the system limit on open
files. At SVR4 new system file table entries are allocated
dynamically; the system limit on open files is effectively
eliminated. (However, each filesystem may have its own tuneable
Kernel limits.)
Each connected user is a socket connection to the DBMS server pro-
cess, so each is a file descriptor. If you want to support a lot
of users in 1 DBMS server then you may need to raise this limit.
The actual database files are opened by each I/O slave process.
Again, if there are many database files then it may be useful to
raise this limit, although the I/O slaves will close and reopen
files as required when the limit is hit.
o The process size limit H/SVMMLIM applies to the total virtual size
of the DBMS server process. As for the data heap size limit, this
may need to be raised for large DBMS server processes.
The data heap size limit applies to the heap segment of the
process, where dynamic memory is allocated. The process size limit
applies to the total of all the segment sizes in the process, where
the data heap is only one of these segments. Usually the heap data
size limit and the process size limit are raised in a coordinated
way. It makes no sense to have a heap data limit the same or
larger than the process size limit.
Note that this limit applies to the process's address space. It is
a limit on virtual memory size, *not* on real memory usage of the
process. There is no way to 'ration' real memory at the process
level.
Note: the ULIMIT (file size limit) in /etc/default/login is only for
login shells. It is not applied to purely background processes (e.g.
cron jobs) or to system startup scripts, because in neither case is a
| login shell invoked. So it will *not* be used when Ingres is started
| from the system startup scripts, neither will it be used for Ingres
cron jobs. Be careful!
| 90.015 What CPU scheduler parameters should I set up for Ingres? (SVR4)
The SVR4 CPU scheduler is a complete rewrite from previous versions.
It supports real time processes, and is very tuneable using the
| priocntl command. Surely this can be exploited to tune Ingres
performance?
| The Ingres `system' comprises a number of cooperating processes:
clients, DBMS servers, I/O slaves, recovery process, archiver process.
The interactions between these are complex, and the effect of raising
or lowering CPU priorities or changing other scheduling parameters is
difficult to predict or understand.
Currently there is not enough experience in this area to offer reliable
advice. It's probably best to leave it alone for now.
90.016 Should I run the DBMS server as a real time process? (SVR4)
The DBMS server is the most important process on my system. Why don't
I run it as a real-time process so it never has to wait for the CPU?
The simplicity of this argument is appealing. Unfortunately the real
situation is more complex. For example:
o The DBMS server hands off I/O requests to its I/O slave processes.
If these are held up it will slow down the processing of SQL in the
DBMS server. So should these run at real-time priority too?
The I/O requests would get queued up but not serviced until the
DBMS server gave up the CPU. The slave processes would have to
wait, possibly for quite a long time, to get the small slice of CPU
they need to pick up a request and fire it off. The I/O would tend
to happen in 'bursts'.
But it's not clear that running the slave processes at real-time
priority would help. Would they be able to pre-empt the DBMS
server to get their CPU slice?
o The DBMS is multithreading. In a busy system it will have a lot of
work in hand on behalf of its many clients. Perhaps it will tend
to 'run and run' producing results while the clients can't get CPU
to use their results?
The DBMS server would pick up all of the incoming SQL requests, and
work on all of them until they have to be suspended for I/O or some
other reason, or there are results ready for the client to read
off. During this processing, all client processsing would stop.
Then, when the DBMS server gave up the CPU, they would burst into
life until the DBMS server had more work to do. System response
might appear jerky or erratic.
This effect has been observed in experiments, TP1 response times
become more erratic if the DBMS server is run as a real-time
process.
The observed results in (admittedly limited) experiments are not
promising, and the behaviour of a complex workload with a real-time
DBMS server is currently difficult to predict. For these reasons, it's
not recommended.
| Note: a 'runaway' DBMS server at real-time priority could lock the
entire system, including the console. It would probably not be
possible to stop the system cleanly, a forced system reload would be
needed.
| 90.017 Why can't Ingres allocate shared memory? (SunOS/Solaris)
| When attempting to start up Ingres on SunOS, and the following error
occurs:
Allocating bytes of shared memory...
!shmat for id at address F4000000 fails
!errno = 22
!Can't map locking segment into memory at address FFFFFFFF
!Check or adjust the value of II_LG_MAP_ADDR
Could not create the system segment, does your kernel have enough
shared memory or has this program already been run?
the remedy is to
ingsetenv II_LG_MAP_ADDR E0000000
and run iistartup again.
Solaris 2.3 requires II_LG_MAP_ADDR to be set to C0000000.
| SECTION 93 -- INGRES ON Windows NT
| 93.001 How can I keep Ingres running after the user ingres logs off NT?
| Ingres can be installed as an NT service and thus configured to start
automatically on booting, and close down cleanly on shutting down. This
is accomplished using the command
crserver install
(the opposite of this is crserver remove). Select the Services icon
| from the control panel, find the line "Ingres Intelligent Server",
click on "Startup..." and select Automatic. This method should also be
| used to manually shutdown (through the "Stop" button) Ingres if the
| service method was used to startup Ingres. IISTARTU.BAT and
| IISHUTDO.BAT exist as an alternative way to start/shutdown Ingres. Note
| that if you do use IISTARTU.BAT to start Ingres, then subsequently log
| off then Ingres will be uncleanly stopped - and may even corrupt your
| log file. Using the service method will leave Ingres running even
though no one is logged into the machine.
| 93.002 Do I need NT Server (formerly NTAS) to run Ingres?
No.
Windows NT comes in two flavours: Workstation and Server. These terms
are highly misleading, suggesting as they do that a machine with
server functions should run NT Server. That is not at all the case.
The distinction between Workstation and Server is only important to
NT itself. The Server product includes the NT domain administration
tools and services. If you have more than one NT machine in a
network you will probably want one of them to be an NT Server machine
to act as the domain controller, but you probably DON'T want to run
| the Ingres server on that machine. (If you have only one NT machine
you probably can't even install NT Server. You will have to use
the so-called Workstation version--even if it is a top-of-the-line
Pentium SMP box with multiple RAIDs serving hundreds of users.)
| The Ingres servers and tools will run equally well on an NT
"Workstation" machine or on an NT "Server" machine, unless the Server
machine is the Domain Controller for a busy domain, in which case it
would be a poor choice of host for a busy database server as well.
| SECTION 99 -- USER GROUPS
|
99.001 How can I contact the NAIUA? (North America)
NAIUA
2107 Del Monte Avenue,
Monterey, CA 93940
USA
Tel: (408)649-0644
FAX: (408)649-4124
| E-mail: mgmt@naiua.org
Officers:
=========
| President George Merritt gmerritt@mentora.com (404)843-9308
| Vice President Mike Meyer mmeyer@ (815)266-3030
| micro.honeywell.com
| Past President Steve Caswell sfc@tpghq.com (770)448-7727
| Member-at-Large Amy Ludlam aludlam@concord.com (508)460-4646
| Member-at-Large Karl Schendel wiz@telesismfg.com (412)963-8844
| Committee Chairs & Other Board Members
| ========= ====== = ===== ===== =======
| CA Member-at-Large Kirsten Kenny (516)342-2587
| Program Committee Karen Bishop karen.bishop@imb.org (804)219-1550
Product Directions Jane Frazer jzfrazer@aol.com (510)883-0175
Communications Mike Greene greenemj@bowinc.com (902)354-3411 x359
Membership Allan Reid shjg90a@prodigy.com (303)236-0330 x292
Technology Bob Griffith rcg@swl.msd.ray.com (508)858-5892
| Membership in the NAIUA is NOT automatic (as it was several years ago).
| Note the NAIUA is an autonomous organization that is not funded or
| operated by Computer Associates in any way. The NAIUA relies on membership
| dues to support its activities (such as the newsletters, the tool archive,
| the WEB site, and the membership directory).
| A membership application form is printed in/on the back of the NAIUA
| Newsletter. It is also available by anonymous FTP from ftp.naiua.org
| in /pub/ingres/IUA/NAIUA/application, or you can e-mail to
| mgmt@naiua.org for a membership application. Or fax (408)649-4124.
| (Because of chronic E-mail problems, it is recommended that you FAX
| or snail-mail the completed form back.)
|
| Individual memberships are $50US per year. A corporate membership rate
| may now be available too. Copies of the Ingres World proceedings for just
| the cost of shipping is worth the price of a membership.
|
************************************************
INTERNATIONAL MEMBERS ARE WELCOME. Ingres users
anywhere in the world will find there are
numerous benefits to membership in the NAIUA,
including easy access to the Members-at-Large who
have direct access to every level of CA
management, up to and including the president;
the two newsletters, one of which is for members
only and gives "insider" news, and more.
************************************************
99.002 How can I contact the UKIUA? (United Kingdom)
Ingres User Association
| c/o Susie Jones
Computer Associates House
183-187 Batch Road
Slough
Berkshire
SL1 4AA
Chairman, Ingres User Association
Mike Prollins
Apertus Systems Limited
| Technico House
| Richardshaw Lane
Leeds
| LS28 6AA
| Vice Chairman, Ingres User Association
| Liz Toher
| Barclays Bank
99.003 How can I contact my local users group? (US)
AZ
Arizona IUA
Joel Munter
Intel Corporation
5000 West Chandler Blvd.
Chandler, AZ 85226
Voice:(602) 554-5078
FAX:(602) 554-5241
Pat Keegan
US Bureau of Reclamation
23636 N. 7th Street
Mail Stop 1100
Phoenix, AZ 85024
Voice:(602) 870-2553
CA
Bay Area IUA
Tess Kowalczyk
LLNL
5379 Windflower Street
Livermore, CA 94550
Voice:(510) 422-0044
FAX:(510) 423-2209
Shelley Alonso
Apple Computers
20525 Mariani Ave., MS/43-IM
Cupertino, CA 95014
Voice:(408) 974-0097
San Diego IUA
Tom Unger - President
Seneca Systems, Inc.
2119 La Mesa Court
Spring Valley, CA 91977
Voice: (619) 697-0500
FAX: (619) 697-3718
e-mail: unger@sdsc.edu
Southern California IUA
Richard Oyekan
Calscience Engineering Laboratories
17870 Castleton St., Suite 395
City of Industry, CA 91748
Voice:(818) 912-2292
FAX:(818) 913-3474
CO
Colorado IUA
| Allen Reid - President
| Office of Surface Mining
| Bldg 20 - Rm B2005
| Denver Federal Center
| Denver, CO 80225-0065
| Voice:(303) 236-0330 ext. 292
| FAX:(303) 236-0340
| E-mail: reid@osmer.gov
| Marilee Posavec - Past President & Secretary
| 10744 West Marlowe Place
| Littleton, CO
| Voice:(303) 236-0330 ext. 277
| FAX:(303) 236-8263
| E-mail: mposavec@aol.com
GA
Georgia IUA
Tom Stone - President
Network Publishing
P.O. Box 100001
Lawrenceville, GA 30245
Voice:(404) 962-7220
FAX:(404) 822-4301
| IL
|
| Midwest IUG
|
| Michael E. Meyer - President
| Honeywell MICRO SWITCH
| 11 W. Springs Street
| Freeport, IL 61032
| Voice: (815) 266-3030
| FAX: (815) 235-5574
| E-mail: mmeyer@micro.honeywell.com
|
|
KS
Alan Sargent
DMT
112 West 9th Street, Ste. 500
Kansas City, KS 64105-1712
Voice:(816) 421-3500
FAX:(816) 421-6328
| E-mail: sargent@dmt.com
LA
Deep South IUA
| Darryl J. Owen - President
Chevron Information Technology Corp.
| E-mail: djow@chevron.com
MA
New England IUA
| Alan Spillert - President
| Mass General Hospital
| Cardiac Computing Center, BUL-457
| 55 Fruit St.
| Boston, MA 02114-2696
| Voice:(617) 724-5327
| FAX:(617) 726-5866
| E-mail: spillert.alan@mgh.harvard.edu
MD
Mid-Atlantic IUA
William J. Koster
Computer Sciences Corporation
| 7700 Hubble Drive
Lanham, MD 20706
| Voice:(301) 794-2329
| E-mail: koster@cdhf2.gsfc.nasa.gov
MI
| Michigan EDS IUA
| Stephanie Fink - President
| 300 East Big Beaver Road
| Troy, MI
| Voice: (810) 524-8647
| FAX: (810) 524-4153
|
MN, ND, SD, IO
North Central IUG
| Phil Carroll - President
| ANATEC
| Voice: (612) 458-2106
| Michael Leo - Past President
| Caribou Lake Software
| E-mail: mal@visi.com
|
NC
Triangle Area IUA
| Jim Stivers - President
| E-mail: 76001.331@CompuServe.Com
|
NJ
NJIUA
Stephanie Lyon
SDL Consulting, Inc.
629 Scotch Road
Pennington, NJ 08534
Voice:(609) 737-0425
NM
New Mexico IUA
| Dick McGuire - President
| MetaTech Corporation
| 2309 Renard Place SE Suite 401
| Albuquerque, NM 87106
| Voice:(505) 243-0681
| FAX:(505) 243-0683
| E-mail: 71031.1207@compuserve.com
|
| Dwight Coles - Vice President
| Sandia National Laboratories
| MS 0763
| PO Box 5800
| Albuquerque NM 87185
| Voice:(505) 844-8434
E-mail: drcoles@sandia.gov
|
Los Alamos IUA
Leslie Morgeson - President
Los Alamos National Laboratory
P.O. Box 1663/MS: D417
Los Alamos, NM
Voice:(505) 665-1847
FAX:(505) 665-5234
NY
| Metropolitan New York IUA
| Carl Jones - President
| 225 Broadway
| New York, NY, 1007
| Voice:(212) 766-4150
| FAX:(212) 766-4172
|
PA
Western Penn. IUA
| Rita Motor - President
Carnegie Mellon University
| 4910 Forbes Avenue - CYH 154
| Pittsburgh, PA 15213-3890
| Voice:(412) 268-8071
| FAX:(412) 268-6868
| E-mail: rita@cmu.edu
|
| Laura McGinnis - Past President
Voice:(412) 268-5642
FAX:(412) 268-5832
| E-mail: lfm@psc.edu
| Mike Bennett - Treasurer
| Voice:(412) 733-0200 ext 5561
| E-mail: mbennett@nauticom.net
|
|
TX
| Austin IUA
|
| Wanda Vidrine - President
| ALUMINUM COMPANY OF AMERICA (ALCOA)
| PO Box 472
| Rockdale, TX 76567
| Voice:(512) 446-8862
| FAX:(512) 446-8721
| E-mail: wanda.vidrine@alcoa.com
|
| Houston IUA
|
| Glen Ball - Chairman
| 13011 Green Rainbow
| Cypress, TX
| Voice:(713) 370-0025
|
North Texas IUA
Anne Dollar - President
Mobile E&P Telchnical Center
| 3000 Pegasus Park Drive
| Dallas, TX 75265-0232
Voice:(214) 951-3510
| FAX:(214) 951-3598
| E-mail: aldollar@dal.mobil.com
UT
Salt Lake IUA
| Tyler McGraw - President (likely obsolete: Tyler is working in England now)
| E-mail: Tyler.McGraw@psemail.ps.net
| Jerry Harper - Vice President
| Thiokol DLV
| P.O.Box 689 M/S-W22
| Brigham City UT, 84302
| Voice: (801) 863-8947
| FAX: (801) 863-6430
| E-mail: harperj@tc.thiokol.com
| Gary Barlow - Past President
University of Utah
Cardiovascular Genetics
410 Chipeta Way, Room 161
Salt Lake City, UT 84108
Voice:(801) 581-3888
FAX:(801) 581-6862
| E-mail: gary@ucvg.med.utah.edu
99.004 How can I contact my local users group? (Canada)
Atlantic Canada IUA
| Richard Ward - Treasurer
| E-mail: rward@gov.nb.ca
|
| Lorne Wilson - Secretary
| E-mail: merseyis@atcon.com(MIME)
|
| Tracy Gulliver - Past President
University of New Brunswick
Computing Services Dept.
P.O. Box 4400
Fredericton, New Brunswick
E3B 5A3
Voice:(506) 453-4564
FAX:(506) 453-3590
|
| Ontario
|
National Capitol IUA
Lawrence Hercz - Acting President
Algonquin College
Computer Service
1385 Woodroffe
Naepan, ON K2G 1V8
Voice:(613) 727-4723 Ext. 5175
FAX:(613) 727-7674
|
Toronto Area IUA
| Rob McKenzie - Chairman
| Independent Ingres Consultant
| 40 Camrose Drive
| Keswick, Ontario
| Canada
| Voice:(905) 476-2532
| E-mail: robm@ilap.com
|
| Quebec
|
AFUIAN (Francophone)
| Bernard Olivier - President
| Industries James Maclaren Inc.
|
Luc Valliere - Treasurer
University de Quebec
C.P. 8888, Station A
Quebec, P.Q.
| Canada H3C 3P8
Voice:(514) 987-4338
| FAX:(514) 987-6503
|
|
| Saskatchewan
|
Prairies IUG
Adeline Skwara - President
Saskatchewan Energy & Mines
1914 Hamilton Street
Regina, Saskatchewan
Voice:(306) 787-2548
FAX:(306) 787-7338
Alberta -- There is no Alberta LUG at present.
Contact Roy Hann
BKB Engineering Ltd.
11211 76th Ave.
Edmonton, Alberta
T6G 0K2
Tel (403)438-2531
FAX (403)437-3367
rhann@tnc.com
|
| British Columbia
|
West Coast IUG
Kapil Lohia - President
Arrakis Consulting Group, Inc.
6865 Neoliani Place
Burnaby, British Columbia
Voice:(604) 654-4151
FAX:(604) 654-4667
99.005 How can I contact my local users group? (South Africa)
| Martin Rennhackkamp
| The Data Base Approach
PO Box 5165, Helderberg
Somerset West
7135, South Africa
Tel: 0(+27) 21 881 3043
Fax: 0(+27) 21 881 3318
E-mail: MR@SUNVAX.SUN.AC.ZA (Martin Rennhackkamp)
| 99.006 How can I contact my local users group? (Europe)
| BELGIUM/LUXEMBOURG
| CA-Ingres User Group Belgium/Luxembourg
| c/o Philippe BRAIBANT
| BELFOX - Beurspaleis
| Rue Henri Maus 2
| B-1000 Brussels
| Voice: (32) 2 512.80.40
| FAX: (32) 2 513.83.42
| CA-contact: Andre De Locht @ CA Belgium
| Voice : (32) 2 773.28.11
| E-mail: andrel@ingres.com for internet or delan07 for CA-NET
| DENMARK
| Danish IUA
|
| Christian Olsen - Chairman
| theklavel 46
| DK-2400 Kobenhavn NV
| Denmark
| Voice: 45 383 304 88
| FAX: 453 186 5106
|
|
| Scandinavian IUA
|
| Ole Thomasen - President
| Jorgen Knudesensvej
| DK-352-Farum
| Denmark
| Voice: 45 449 922 44
| FAX: 454 499 2220
|
|
|
| FINLAND
|
| Finnish IUA
|
| Ms. Leena Ahvonen - President
| PL105
| Fin-02631
| Finland
| Voice: 358 050 275 1
| FAX: 358 050 27359
|
|
|
| GERMANY
|
| German IUA
|
| Axel Schwanke - President
| c/o Lucent Technologies
| Dept. OEPS-PSE
| Thurn-und-Taxis-Str. 10
| 90411 Nuernberg
| Germany
| Voice: +49 911 526 2691
| FAX: +49 911 526 6299
| E-mail: axelschwanke@lucent.com
|
| Bodo Bergmann - Vice President
| Client/Server-Consulting & Training
| Ruedesheimer Str. 17
| 63110 Rodgau
| Germany
| Voice: +49 6106 876 115
| FAX: +49 6106 876 116
| E-mail: bodob@ibm.net
|
|
|
| The NETHERLANDS
|
| Ingres User Group the Netherlands
|
| Tjerk Post - Chairman
| Wim van der Maas - Secretary
| Martin Verdonk - Treasurer
| Mark Luijendijk - Workgroups and SIGs
| Eric Nooter - Activities
| E-mail: info@iugn.nl
| WWW: http://www.iugn.nl
|
|
|
| SWEDEN
|
| Swedish IUA
|
| Mr. Sven-Bertil Wallin
| Kungsgatan 53
| S-111 22
| Stockholm
| Sweden
| Voice: +46 8 24 34 20
| FAX: +46 8 24 27 68
|
|
|
| SWITZERLAND
|
| RUBIS (Rencontre des Utilisateurs de Base Ingres) Switzerland
| INGRES User Group Switzerland (Swiss french)
|
| c/o Monsieur Dominique CENTENO
| Institut de Pathologie
| 25, rue du Bugnon
| CH - 1011 LAUSANNE
| Voice: (41) 21 314 71 17
| FAX: (41) 21 652 08 80
| E-mail: rubis@itu.ch
| WWW: http://www.itu.ch/RUBIS
|
|
| SWINGRES (Swiss German)
|
| Ewald M. Mund - President
| Mund-Software
| Buchholzstrasse 13
| 3066 Stettlen
| Switzerland
| Voice: 0041 31 9340841
| FAX: 0041 31 9340842
| E-mail: mundsw@dial.eunet.ch
|
|
99.007 How can I contact my local users group? (Irish Republic)
Irish Ingres Users Association
c/o Europa House
Harcourt Street
Dublin 2
IRELAND
Phone: +353-1-4780800
FAX: +353-1-4780775
Chairman: Alan Daly
Superquinn Ltd.
PO Box 99
Sutton
Dublin 13
IRELAND
Phone: +353-1-8325700
99.008 How can I contact my local user group? (Australia)
AUSTRALIAN INGRES USER GROUP PRESIDENTS
| National IUG
| Phil James - President
| Voice: 041 11 99 441
| Fax: 07 3224 2390
| E-mail: jamesp@pib3.pib.dpi.qld.gov.au
| WWW: http://www.ozemail.com.au/~ingresug
| Australian Capitol Territory IUG
| Neil Telfer - President
| Stuart Spencer - Vice President
| E-mail: codrad31@aec.gov.au
| WWW: http://www.ozemail.com.au/~ingresug/act
| New South Wales IUG
| David Orr - President
| COMALCO Aluminium
| PO Box 85
| Fairfield, NSW, 2161
| Australia
| Voice: 612 681 1968
| Fax: 612 892 1770
|
| Queensland IUG
|
| Malcolm Thatcher - President
| Thentec Pty. Limited
| Voice: 61 7 3371 6611
| Fax: 61 7 3371 6633
| E-mail: malt@thentec.com.au
| WWW: http://www.ozemail.com.au/~ingresug/qld/index.html
|
|
| South Australian CA-Ingres IUG
|
| Fred Dolen - President
| E-mail: fdol1@anr.gov.au
|
|
| Tasmania IUG
| Frank van Ravels - President
| Australia
| E-mail: Frank_van_Ravels@umail.toursr.tas.gov.au
|
|
| Victorian IUA
|
| Gavin Trigg - President
| Information Technology Services (ASIS)
| The University of Melbourne
| Grattan Street
| Parkville 3052
| Australia
| Voice: 613 344 6003
| Fax: 613 347 4803
| E-mail: gavin@asis.unimelb.edu.au or
| Trigg@its.unimelb.edu.au
| WWW: http://www.ozemail.com.au/~ingresug/vic
|
|
| Western Australian IUG
|
| Andrew Tierney - President
| Legal Aid Western Australia
| 55 St Georges Tce
| Perth, WA, 6000
| Australia
| Voice: 09 261 6280
| Fax: 09 325 5430
| E-mail: atierney@acslink.net.au
|
| 99.009 How can I contact my local user group? (India)
|
|
| India IUA
|
| T S Ananthaswami Row - Chairman, Organising Committee
| Mastek Ltd.
| III Floor
| Wellington Plaza
| 90 Anna Salai
| Chennai - 600 002
| Voice: 044-8527289/ 8555369
| Fax: 044-831736
|
------------------------ END OF FAQ -----------------------------
Ingres Database Reference
To William's Home Page
© William Yuan 2000
Email William