Ingres FAQs
From Ingres Community Wiki
This page contains a list of Frequently Asked Questions (FAQs) Surrounding Ingres.
Ingres General FAQ
What is Ingres?
Ingres Corporation provides both an Open Source DBMS, Ingres, and an OpenSource development platform, OpenROAD. The Ingres DBMS Distribution contains a JDBC driver, an ODBC Driver, a .NET Data Provider, a number of character based tools (ABF, QBF, etc.), pre-compilers, Ingres/STAR, and Ingres/NET.
Does Ingres have a community?
Yes, Ingres has an active community at Ingres Community.
Does Ingres have a wiki?
Yes, Ingres has an evolving wiki here.
Where can I find articles to help me get started with using Ingres and OpenROAD?
Check out Tech Tips
Where can I find code samples to help me get started with using Ingres and OpenROAD?
Check out Coding Samples
I have a question about Ingres, where can I go for help?
There are several places you can go to get help:
- Ask questions in the forums
- Ask questions at Comp.Databases.Ingres
- Ask questions at IRC Channel
- If you have an active support contract, an issue can be logged at Ingres Service Desk
How can I get involved in the Ingres Community?
To get involved in contributing articles, coding samples, answer questions, or contribute to projects, check out Get Involved.
I've found a bug with Ingres or OpenROAD, how do I notify Ingres?
Bugs can be reported via the following channels:
- Ask questions in the Bugs
- If you have an active support contract, an issue can be logged at Ingres Service Desk
I've heard Ingres has a number of projects in the pipeline, where can I get more information about these projects?
The Ingres Projects page contains information about projects being developed internally as well as externally.
I'm interested in browsing the Ingres source code, is there an online repository where I can view the code?
Yes, the Open Source Ingres code is stored in a readily available subversion repository.
- Ingres Community Distribution code is available at Ingres DBMS Source Code
- Ingres Python and Ruby Code
- Ingres JDBC Driver Source Code
- Ingres .NET Data Provider Source Code
Is there a source code indexer and cross-referencer line index repository of the Ingres RDBMS Source Code?
Yes, see Ingres LXR Cross Referencer
I've heard about Open Source Bootcamp, what is it exactly?
Open Source Boot Camp (OSBOOTCAMP) is an outreach/training, mini-conference series dedicated to open source. In addition to providing skills which provide career differentiation, OSBOOTCAMP participants have the opportunity to mingle and to learn about interesting opportunities with Open Source Software. Supported by Talent First Network. For more details, see Open Source Bootcamp
Does Ingres participate in the Google Summer of Code?
Yes, see Ingres Google Summer of Code Projects
I would like to make a contribution to the Ingres Source Code, how do I go about doing that?
See Getting Started with Development
DBMS FAQ
There are a lot of processes that start when Ingres is started. 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 Ingres) |
| 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. | |
| 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. | |
| iigcd | data access server process |
| provides the network communication function for the JDBC Driver and .NET Data Provider. There may be multiple instances of iigcd in an installation. | |
| iistar | distributed database derver process (present only on sites with Ingres Star) |
| iigcb | bridge server process (present only on sites with Ingres Bridge) |
How do I change the storage structure of a table?
How do I create a secondary index?
How do I partition a table?
Should I use ckpdb or unloaddb to backup my database?
How do I recover an inconsistent database?
Contact Ingres 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 Should I set II_DMFRCP_STOP_ON_INCONS_DB? 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.
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 Ingres 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.
How can I direct batch or reports to a particular server?
Ingres supports customer-defined server classes. See section How can I assign particular jobs to particular servers? for more information for doing this with post Ingres 6.4 releases.
Ingres 6.4 does not offer such an elegant solution as later Ingres releases. With 6.4, you have to bypass the Name Server to do this.
There are a number of steps:
- 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.
- 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.
- 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.
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:
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
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 written1024 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 logfile 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 Ingres releases post 6.4, the journal switch point and page size are configurable.
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 acrash or power-off?
- 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.
- 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.
- 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.
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:
- determines if the mirrors are all on-line and synchonized
- disengages one of the mirror disks so it is no longer updated
- re-engages the mirror disks
The general checkpoint template algorithms are:
- In the BEGIN phase, assure all mirrors are on-line and synchronized. If not, fail.
- In the WORK phase, disengage one of the mirror disks for each data location.
- 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.
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.
Ingres 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.
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 System Reference Guide for details.
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 StorageFacility) memory pool is estimated from the number of connected sessionsdeclared when the server is started. By default, the pool size is setto 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 current Ingres releases, 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.
For Ingres 6.4, 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 Ingres Knowledge Document US-16503.
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 (Windows)
- 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.
Ingres now supports the manipulation of server classes through the new CBF (Configuration By Forms) and no manual jiggerypokery is required.
Should I set II_DMFRCP_STOP_ON_INCONS_DB?
Short answer: almost certainly yes, in a production system.
Short reason: it is possible, if rare, that an inconsistent database can be caused by environmental issues. (The data disk array being "fenced" and going read-only, for instance.) If the recovery server (RCP) is allowed to mark databases inconsistent, it is in essence giving up on those databases and you have no future options other than restoration from backup -- even after the environmental condition is cured. Stop-on-incons-db prevents this, at the cost of requiring administrator intervention.
Longer answer and reason:
[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. These resources may be internal, such as locking resources, or they may be external, such as the ability to write to the table data. Ingres is reasonably careful in assuring sufficient internal resources. It cannot control external resources. Regardless of the reason, if some resource isn't available to the recovery server, it will mark the database(s) involved inconsistent.
An inconsistent database is not just inaccessible (short of forcing it). The recovery server has given up on that database. The log records needed to retry recovery are no longer accessible. The only options are to force consistent (which does not cure any physical or logical damage), or to rollforward 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 utility.
With II_DMFRCP_STOP_ON_INCONS_DB set to YES, Ingres will refuse to mark a database inconsistent. Instead, the installation will shut down (or refuse to start up, if starting up). If the problem was an internal resource issue, offline (startup) recovery is likely to succeed, as it uses almost no lock resources and is starting from a clean slate. If the problem was an external resource issue, recovery will probably fail again, but the RCP will abort itself instead of losing the log records. Once the environmental problem is correct, recovery should succeed.
The main downside of II_DMFRCP_STOP_ON_INCONS_DB YES is that it does require administrator intervention in the case where the database is truly unrecoverable, whether due to bugs or hardware errors. The administrator will have to unset the variable to get Ingres up and running. Also, in an installation with many databases, an inconsistency on just one will take down the entire installation. (But if that one is the most critical one, so much the better.)
II_DMFRCP_STOP_ON_INCONS_DB is indisputably a blunt instrument. In the newest releases, the recovery algorithms are moving in the direction of more fine-grained inconsistency, table level inconsistency, and optional recovery interaction with an administrator. Perhaps in time, II_DMFRCP_STOP_ON_INCONS_DB will become obsolete.
How do I recover after a fire/flood in the machine room?
- 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 Ingres Support. They will talk you through step 2 and up.
- Make sure your replacement system has a database by the same name with the same locations and disk paths.
- Shutdown Ingres on the replacement box.
- Wipe out ALL the data for the replacement machine's default database directory.
- Copy the destroyed database's aaaaaaaa.cnf file from the checkpoint tape to the default database location on the replacement system.
- Make the jnl and dmp directories on the replacement box identical to the destroyed machine.
- Bring Ingres up on the replacement box.
- 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.
Ingres Tools
What are Ingres Tools ?
Ingres Tools refers loosely to the Character Based Tools such as:
- isql
- sql
- reportwriter
- abf
- etc
I can't issue queries in the command line SQL tool! How do I issues queries?
In the command line tool "sql" (aka tm or Terminal Monitor). Issue a backslash g after the command, that is, "\g".
Slash q is used to quit and slash p to print the SQL (useful for batch jobs)
Example:
C:\>sql iidbdb INGRES TERMINAL MONITOR Copyright 2007 Ingres Corporation Ingres 2006 Release 2 Microsoft Windows Version II 9.1.1 (int.w32/103) login Thu Jul 03 13:49:54 2008
continue * select _version();\p\g /* SQL Startup File */ select _version(); Executing . . . +--------------------------------+ |col1 | +--------------------------------+ |II 9.1.1 (int.w32/103) | +--------------------------------+ (1 row) continue * \q Ingres 2006 Release 2 Version II 9.1.1 (int.w32/103) logout Thu Jul 03 13:50:05 2008
How do I issue sql in the isql tool?
isql uses the Ingres Forms Runtime System (aka FRS). The key to press to "run" the SQL depends on the terminal type in use. Ingres needs to be told what terminal is in use with TERM_INGRES. Under Windows this defaults to IBMPCD, and the "Go" (run SQL) is the F9 (Function Key 9), hit the Escape key to scroll the menu (bottom of the screen) and hit F6 to quit. For Unix with VT100 use the Enter key on the numeric key pad (not the Return key on the main part of the keyboard). See IngresCharacterBasedToolsAndFunctionKeys for more information
QUEL
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 Ingres Corp has not yet announced that it will be dropped.
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:
- 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).
- 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.
- The EQUEL `APPEND' statement (analogous to SQL `INSERT') insists that column be identified by name--columns cannot be addressed by position.
- 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:
- Some of the more exotic QUEL access controls are not imposed on SQL accessors--this creates a potential security breach.
- 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.)
- 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.
Should I learn QUEL?
Users of Ingres have the choice, but QUEL is the only DML available to users of the very old University Ingres release (predates Ingres 6.4, which predates Ingres 2006).
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.
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.
Ingres OpenROAD FAQ
Ingres DBA Tools FAQ
What is happening with these tools?
Since the Ingres Development Summit in late April, there has been a concerted effort on several fronts:
- Fixing bugs in existing tools in preparation for the next major release of Ingres.
- Understanding the internals of the current tool set.
- Identifying the "first-cut" tool set that needs to be implemented.
- Working on the detailed Tools API.
- Studying Java and Web application security to fill in some missing pieces in the whole scenario.
There should be a document available soon describing the Tools API, for comment.
When can we expect to see some real deliverables?
Good question. Once the API document has been reviewed and approved then implementation can begin. This will take some months to complete (including test suites). Work on the UI design of the new tools will occur in parallel with that. Once the UI designs and API implementation are complete, then and only then can actual coding begin in earnest. (This doesn't mean that there won't be any coding before that, especially for proof-of-concept in certain areas.) Any time estimates for this coding effort would be pure speculation at this point. But, as hard as it is to say, it will be some time before we can see this first deliverables.
But, one of the overall design goals is simplicity of implementation, so it may be rather sooner than it might be. ;)
Stay tuned to this page as things progress. (You can put a "Watch" on this page to be informed of changes.)
What can I see so far?
The project description at Cross Platform Tool Set is where you can take a look into the future.
JDBC FAQ
Can I use JDBC (Java connectivity)?
Yes, JDBC provides Java applications and applets with access to databases in much the same way that ODBC does.
Where can I find more information on the Ingres JDBC Driver?
See here for more information on the Ingres JDBC Driver.
ODBC FAQ
Can I use ODBC (Open Database Connectivity)?
Yes, ODBC is Microsoft's implementation of the Call Level Interface specification defined by the SQL Access Group. Ingres can act as an ODBC server.
Where can I find more information on the Ingres ODBC Driver?
See here for more information on the Ingres ODBC Driver.
.NET FAQ
Can I use .NET Connectivity?
Yes, the Ingres .NET Data Provider is a Microsoft .NET component that provides native .NET connectivity to Ingres databases to interface with the Microsoft .NET Framework.
Where can I find more information on the Ingres .NET Data Provider?
See here for more information on the Ingres .NET Data Provider.
Which Ingres manual has the documentation on the Ingres .NET Data Provider?
See the Connectivity Guide and the chapter on Understanding .NET Data Provider Connectivity.
My .NET application issues a myIngresConnection.Close() statement, but DBMS monitors see that the connection is not closed until a minute later. Why does the connection stay open?
The physical closing of the connection approximately 60 seconds after the logical closing of the IngresConnection.Close() is correct and normal. The Ingres .NET Data Provider offers connection pooling by default, similar to the ODBC Driver Manager. Delaying the physical closing of the connection by a minute and putting the unused connection into a connection pool allows other sessions in the process with the same connection string to get the inactive connection from the pool and reuse the connection. This greatly improves performance and response time since a physical Open is not needed to satisfy the logical Open. If no other session grabs the physical connection after about a minute of idling in the pool as unused, the connection is physically closed to release system resources on client and servers machines. If the Application Domain is unloading (i.e. the application is shutting down) the connection is physically closed immediately.
PHP FAQ
Does Ingres have a PHP Driver?
Yes, has a PHP Driver.
Where can I find more information on the Ingres PHP Driver?
See here for more information on the Ingres PHP Driver.
Python FAQ
Does Ingres have a Python Driver?
Yes, Ingres has a Python Driver.
Where can I find more information on the Ingres Python Driver?
See here for more information on the Ingres Python Driver.
Ruby FAQ
Does Ingres have a Ruby Driver/Ruby on Rails Adapter?
Yes, Ingres has a Ruby Driver/Ruby on Rails Adapter.
Where can I find more information on the Ingres Ruby Driver/Ruby on Rails Adapter?
See here for more information on the Ingres Ruby Driver/Ruby on Rails Adapter.
CAFÉ FAQ
I've heard about Ingres CAFÉ, what is it exactly?
Please see the CAFÉ wiki for FAQ information about CAFÉ. Thank you.
Categories: Articles | FAQs | CAFÉ
