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