April 1996 Newsletter
Volume 6, No. 2
Highlights of This Issue
Informix 1996 Worldwide User Conference
Washington Area Informix User Group Forum 96, by John Ashmead
You Know My Methods, Dr. Watson, by John Ashmead
Testing an Informix Developer, by Madhu Reddy
Error Handling Functions in Informix Programs, by Lester Knutsen
Informix Press Releases
- Informix Announces Record TPC-C Benchmark on Digital Alphaserver 64-Bit
- Informix's Illustra Database Powers Academy Awards Web Site with Interactive
Guide to Oscar-Winning Films and Stars
Next Meeting - SQR Workbench from MITI
Come hear about SQR Workbench, the production reporting system for Informix. If your
current reporting solution cannot meet the needs of your mission critical applications, then you
won't want to miss this presentation about SQR. The next meeting is sponsored by MITI.
Date and Time: June 5, 1996 at 9:00 AM to 12:00
Location: Hyatt Regency Washington, One Capitol Hill,
400 New Jersey Avenue, NW, Washington, DC
Hotel Phone: 202-737-1234
Discounts for Members
Attending the Informix Worldwide User Conference
Through a special arrangement with Informix Software, Inc., members are eligible to receive
a $200.00 (US) discount off of their registration fee for the Informix Worldwide User
Conference. The amount of the discount will depend on the attendance package selected and
the date of registration. To take advantage of the excellent opportunity, you must request the
International Informix User Group (IIUG) Member Discount when you contact Informix
Software, Inc., to register for the Conference. All WAIUG members as of March 1, 1996, were
enrolled in the IIUG. All attendees requesting the discount will be verified for IIUG
membership, so you must join the user group prior to registering for the Conference.
Information about the conference is available by contacting (800) 784-6580 or on-line at
See page 4 for more information about the Informix Worldwide User Conference
The user group has been supported by many companies over the years. If your company
would like to sponsor the newsletter please call for more information. We would like to thank
the following companies for sponsoring this issue of the newsletter:
Advanced DataTools Corporation
Business Data Services, Inc.
Business Systems Support Group, Inc.
Summit Data Group
Elections to the Board of Directors
At our September meeting we will be holding elections to the WAIUG Board of Directors. The
Board is composed of volunteers who plan our activities and work hard at putting them
together. If you are interested in volunteering to be on the Board, or would like to nominate
a member, please contact the Secretary of the WAIUG, Sam Hazelett at 703-277-6882 (W) or
Benefits for Members
In addition to this newsletter and our local activities, there is a new reason to be a member of
the Washington Area Informix Users Group. All current members will automatically become
members of the International Informix Users group for one year. Some of the benefits this
includes are discounts to the Informix World Wide User Conference in Chicago in July, and
full access to the members-only section of the IIUG Web Pages. Other discount programs are
being worked on as well. Have you renewed your membership for 1996? Membership dues
are $20.00. We also have a Corporate Membership Program. For membership information,
please call our Membership Director, John Petruzzi, at 703-490-4598.
Informix Software, Inc., announced its fifth-annual worldwide user conference and exhibition
to be held July 9-12 at the Navy Pier Convention Center in Chicago, Illinois. This year's
conference, entitled "Extend Your Horizons," will feature a keynote address by Paul Saffo,
director at the Institute for the Future, a management consulting foundation that provides
long-term planning and forecasting services to Fortune 100 companies and government
agencies. Saffo specializes in long-term social and commercial impacts of new information
technologies and is recognized as a leading futurist.
The conference will also offer plenary sessions featuring members of Informix's "Dream Team"
of database visionaries including Dr. Michael Stonebraker, co-founder and chief technology
officer, Illustra, and Gary Kelley, vice president of product development, Informix Software,
Inc. Each will share his perspective on the future of IT in these special plenary sessions that
are new to the conference this year.
Informix will also host a panel of three leading analysts--- Gene De Rose, president, Jupiter
Communications; John McCarthy, director of research, new customer connection, Forrester
Research; and J. Neil Weintraut, managing director of technology research, Hambrecht &
Quist, who will address the future of the Internet in a roundtable discussion.
"We are extremely excited about this year's conference. With our recent acquisition of Illustra
Information Technologies, Inc., we are able to extend our proven core technology with
non-traditional data types while maintaining our scalability and high performance," said Phil
White, Informix chairman and CEO. "We will demonstrate these enabling technologies and
the solutions provided by our strategic partners, as well as how our customers are benefiting
from them today and their plans to grow with Informix in the future."
The conference will feature more than 100 conference sessions, tutorials, and other breakouts
designed to help Informix's customers and partners effectively use Informix database
technology now and into the future. In-depth tutorials will focus on specific how-to training
requested most by IT professionals. Attendees will be able to choose from five conference
tracks: Trends in Technology; Industry Spotlights; Business Management; Advanced
Technology for Application Developers; and Advanced Technology for DBAs. Key topics to
be covered include interactive media, data warehousing, world wide web and the Internet,
mobile computing, business process re-engineering, OLTP, and systems management.
The conference will also feature a 100,000 square-foot-exhibition hall where over 110 Informix
hardware partners, independent software vendors, and application providers, will showcase
Informix-based solutions. Demonstrations of Informix's latest products, solutions and services,
including the company's newly-acquired technologies in multimedia content management and
data warehousing, will be featured at the Informix booth during the exhibition. More
information about the conference is available by contacting (800) 784-6580 in the U.S., (617)
736-1779 outside the U.S. or on-line at http://www.informix.com.
by John Ashmead
The Washington Area Informix User's Group held its third annual forum on Friday, March 1st at the
Sheraton Premier in Vienna, Virginia. Attendance was excellent: many of the talks were standing room
only. Informix was much in evidence: they are taking their support of user groups very seriously.
The first talk was a surprise presentation by Jim Hendricks, in charge of technical support at Informix.
His big story was a focus on systematic improvements in the quality of technical support. Informix has
achieved ISO9000 compliance for a number of their centers and is taking some of that systematic
approach to quality to heart. Jim discussed the "911" number (dial 911 when you on the hotline and get
patched right through to emergency support), statistics for response time and problem resolution (they
took a dip down when version 7 rolled out but are headed up), and ongoing efforts to improve regression
testing and the like. He takes some of the calls himself, by way of monitoring the quality of tech support.
I think the key point is that they are tracking the quality of customer support in a systematic way: metrics
of response time, commissioned surveys by polling firms of the Informix client base, and the like.
Keynote: "Future Directions for Informix"
Bob Macdonald, VP for Corporate Marketing, gave what was possibly the most energetic talk of the day.
Informix is hoping to eat Oracle's lunch by keeping a tight focus on database and closely related products
while letting Oracle try to be everything to everybody (a nice trick if you can pull it off). The Illustra
merger is a good example: there is a synergy between Illustra's advanced handling of new types and the
strengths of the Informix engine. But the acquisition doesn't involve Informix in head-to-head competition
with the third parties who use Informix.
What I took away from Bob's talk was BIG ENERGY: Bob bounds around the stage like a tiger on
steroids. He likes to use BIG FONTS to make his BIG POINTS. Informix has always been strong
technically; it is a pleasure to see them show some marketing PIZAZZ as well.
The bulk of the day split up into two tracks: client/server and OnLine administration. Unfortunately,
while we can mirror disks and databases, there doesn't yet to seem to be any way to mirror consultants.
Forced to make a choice, I stayed with the Client/Server track.
Anne Buzbee (Informix) presented a tool which does automatic conversion of 4GL to NewERA.
Unfortunately, this only runs on some SUNS in Germany. But one of the beta testers had 3.5 million lines
of code to convert, a code stream which can justify the trip. (There are apparently some intellectual
property issues to work out before the tool can leave Germany.)
Wayne Beekman (Information Concepts) discussed developing Visual Basic applications to run against
Informix databases: he has clients with thousands of VB stations hanging off Informix servers. He
discussed the advantages/disadvantages of the various methods of connecting VB to Informix: record sets,
ODBC, etc. Record sets offer less development time but lower performance, ODBC the reverse. He
emphasized the importance of discipline - source code control, code standards, etc. - when developing with
John Woolsoncroft (CDI) gave a presentation on migrating to NewERA, with particular emphasis on
three-tiered client/server architecture.
Tips and Techniques
The final talk (back to one track) was tips and techniques by Jonathan Leffler. Anyone who follows the
Informix news group or mailing list is familiar with Jonathan's work: he seems to bat cleanup on many
of the technical issues that come up. He explained the DBCENTURY variable for handling the year 2000
problem (i.e. is 01 really 2001 or 1901). He also went through some fairly elaborate header files and
techniques for getting Informix ESQL/C to compile under C++.
The day finished with wine, cheese, and a raffle in which a great deal of valuable software (NewERA v2
and NewERA v3 for instance) was given away. Not everyone won in the raffle, but at least everyone went
away with a diskette of public domain Informix tools supplied by the WAIUG.
What I think was the big news of the conference was the way Informix is really driving hard on its core
business, focusing on databases and emphasizing the things that make them work better: technical
support, user groups, better support for data types, support for replication, and data analysis tools. And
the way Informix is finally pushing hard on the marketing of all this.
Lester Knutsen, John Petruzzi, and the rest of the board at the WAIUG have done a very impressive job
with this third Forum: we are all looking forward to the next.
by John Ashmead
I've recently agreed to do a book on debugging Informix (and databases in general) for Prentice-Hall. Given that
perhaps half a programmer's time is spent debugging, it is amazing that I can find fewer than ten books on
debugging, and none specifically on debugging databases. Clearly it is about time to put one together. (In fact, I
would very much like to get ideas and suggestions from anyone who has them.)
The hard part with any project like this is getting started. I figured I'd begin by leafing through a few of the
Sherlock Holmes stories looking for an appropriate quote or two for the chapter headings. Debugging is after all
a specialized form of detection, and two excuses to review the Sherlock Holmes canon I don't need: the stories are
still fine reads.
Certainly, if Mr. Sherlock Holmes of 221B Baker street were still in practice, he would be in hot pursuit of computer
criminals: monitoring packet sniffers, de-spoofing spoofers, and tracing crackers back to their home machines. The
same ferocious energy he brought to footprints and bloodstains would be focused on hex dumps and logic analyzers.
In fact, Holmes himself comes across as the quintessential hacker: up late, careless in his personal habits, intense,
with no time for anything except work and his violin (although now of course it would be his CD collection).
But the surprising thing about the stories is how well the advice on detection holds up. Holmes is clear, first off, on
the need for having a system: "No, No: I never guess. It is a shocking habit-destructive to the logical facility"
(The Sign of Four). In fact, in debugging, the infallible sign of the amateur is repeated guessing. The professional
attacks the problem systematically: tracing the program from start to finish or comparing point by point a known
good to a known bad version; the novice tries one thing, then another, then the first thing again- generally without
using source code control or making backup copies-to the point where if the program was working at all at the start
it will not be by the end. (As a rule of thumb, I would allow one guess, maximum, and then say it's time to "do it
by the numbers.")
...my simple art, which is but systematized common sense... (The Adventure of the Blanched Soldier)
Holmes himself uses a definite system: one that can be applied almost verbatim to debugging. He is really using
the scientific method: observe, hypothesize, and test until you have a theory that explains the facts better than any
reasonable competing theory can. And once you have a working theory as to what is wrong, correct the problem
in the most economical, decisive fashion. I think most experienced Informix programmers do this instinctively, but
it is helpful to lay out the principles in black and white.
Data! Data! Data! I can't make bricks without clay. (The Adventure of the Copper Beeches)
It is a capital mistake to theorize before you have all the evidence. Insensibly one begins to twist facts to suit theories,
instead of theories to suit facts. (A Study in Scarlet)
Beginners also go astray in failing to collect all available information before arriving at a conclusion. It is essential
to get a clear account from the user (if the problem is being reported by a user and making appropriate allowance
for how knowledgeable the user is); to go over any console logs or error files which may have been left around; to
note any hardware or operating system problems which may have been happening at the same time. If you assume
you know where the problem is, you halve your chance of finding it (assuming that you are right about where it is
half the time-an optimistic view in my experience).
It is of the highest importance in the art of detection to be able to recognize out of a number of facts which are
incidental and which are vital. (Silver Blaze)
To do this you need to have a clear understanding of how the underlying software works, of what the pieces are and
how they fit together. In the case of Informix, you need to know what "prepare" and "execute" do; that
permissions problems can be at the table level or, in standard engine, on the database files themselves; that in the
compiled system 4GL turns into C code which can be inspected (and can also be incorrect). et cetera.
In fact, so important is a clear understanding of the underlying logical machine, that one effective debugging
technique is to create small test programs whose sole function is to explore a point of syntax or performance in
isolation. Often, the bug is a result of not understanding how the system actually works.
There is a strong family resemblance about misdeeds, and if you have all the details of a thousand at your finger ends,
it is odd if you can't unravel the thousand and first. (A Study in Scarlet)
Here Mr. Holmes would appear to be anticipating the advent of case-based reasoning systems. But I think most
experienced Informix programmers spend a fair amount of time asking "what similar problems have I seen in the
past." This is where membership in the Informix mailing list (see the article on Informix archive sites) can be
invaluable: many of the postings on that list are about recent or ongoing bugs.
It is an old maxim of mine that when you have excluded the impossible, whatever remains, however improbable, must
be the truth. (The Adventure of the Copper Beeches)
After a failure to take a systematic approach, making an incorrect assumption is the leading cause of marathon
debugging sessions. For instance, it is a good working hypothesis that the most recent change is most likely to be
the source of a new problem. But it has happened that the hardware coincidentally failed at exactly that time, or
the new feature exposed an old bug, or that new problems made the users more likely to report old ones ("ah ha!
now that we're getting some attention, let's report everything on our want list!"). Again, usually the Informix tools
are consistent and reliable, but every so often esqlc will mistranslate 4GL to C, the rapid development system
handles booleans a little differently than the compiled version, and so on.
See the value of imagination. It is the one quality which [Inspector] Gregory lacks. (Silver Blaze)
One of the best ways to come up with working hypotheses is to imagine how the code is working and how it might
respond to various user and other inputs. Experienced Informix programmers are always doing this: "if the input
array got an interrupt at this point, what would it do?"
-I would call your attention to the curious incident of the dog in the night-time. -The dog did nothing in the night-time.
-That was the curious incident.
A little imagination is invaluable in testing your hypothesis as well. You think you've nailed the little fellow, but
you might want to ask: if this is really the problem would we have seen exactly the symptoms reported? And are
there any other symptoms we should have seen but didn't?
I have devised seven separate explanations, each of which would cover the facts as far as we know them. But which
of those is correct can only be determined by...fresh information. (The Adventure of the Copper Beeches)
You will have observed that Mr. Holmes is always careful to test his hypothesis: the stories invariably include the
stalk, the lying-in-wait, the sudden confrontation which will verify (or refute) Holmes' theory.
The same policy holds good for bugs: even when you think you've finally made a clean kill, you should devise some
test which locks the matter away. =46irst, you may be wrong about the bug. And surprisingly often a second or
third bug has been concealed by the first: the test that was intended to show the bug was solved shows instead that
more work remains to be done.
...[sometimes] in my career I feel that I have done more real harm by my discovery of the criminal than ever he had
done by his crime. (The Adventure of the Abbey Grange)
It is commonly estimated that perhaps half of all bug fixes either fail to fix the original bug or introduce a new one.
Or both. It is not enough to solve the original problem; it is also desirable not to create new ones. In more serious
cases it can be more effective to completely rewrite a "bad patch" then to attempt to find and fix its bugs one by
...that highest value which anticipates and prevents rather than avenges crime. (The Valley of Fear)
This deserves a book (which I'm trying to write). To summarize that book in a sentence: bugs come from confusion
as to assumptions, objectives, or means. To minimize their likelihood, document (and test) your assumptions, state
your objective clearly, and employ the simplest and most direct means to get there.
The Whole Art of Detection
...but I propose to devote my declining years to the composition of a textbook, which shall focus the whole art of
detection into one volume. (The Adventure of the Abbey Grange)
Holmes himself was planning to spend his retirement keeping bees (he published a monograph on that subject) and
write his magnum opus, The Whole Art of Detection. Pending some descendent of Dr. Watson at last unearthing
that manuscript, we can do well by applying the scientific method: observe, hypothesize, test.
139 Montrose Avenue
Rosemont, PA 19010
by Madhu Reddy
This article provides two tests to evaluate an INFORMIX developer. In the next issue I will write an article to test
an INFORMIX DBA. For any company, recruiting good staff is always a difficult task. This article presents how
we came up with test questions to evaluate an INFORMIX developer. Recently, American Computer Technology,
Inc (ACT) has been awarded a contract by a large commercial company to evaluate their INFORMIX-4GL
developers and to recruit a new staff of INFORMIX developers.
The major goal for the company was to identify the problem areas of an individual developer and train them in
these specific areas. To accomplish the goal, first we decided to perform a code review. The code review process
helped us to identify the following four major areas where programmers need help.
- lack of knowledge in database concepts
- unable to use right INFORMIX-4GL statement to accomplish a task
- software reuse
- performance issues
In the first area (lack of knowledge in database concepts), major concepts programmers have difficulty
understanding are isolation levels, transaction processing, use of views, stored procedures and triggers,
concurrency, and locking mechanisms. For example, the application we reviewed was a transaction processing
application, and there were no BEGIN and END statements. In one program, when an update function needed to
be performed, the program read data with the isolation level Dirty Read, displayed data on screen for user
modifications, then accepted changes and updated the data.
In the second area (unable to use right INFORMIX-4GL statement to accomplish a task), many developers do not
know how to use the statements INPUT ARRAY, DISPLAY ARRAY, CONSTRUCT and INFORMIX Functions.
For example, in a program to display an array of rows, one programmer used an INPUT ARRAY statement instead
of a DISPLAY ARRAY statement and struggled a lot to control the changes. In one program to identify the login
user_id, a programmer wrote 16 lines of code. The programmers did not understand how to get an environment
variable value into a 4gl program. Some programmers write code sequentially in one function to accomplish a task
(20 pages). They don't understand the software reuse and writing common functions.
Many programmers were having problems in the performance area. For example, to read an array of rows, a
program should first execute a count statement and use FOR and FETCH to read array of records. This can be
directly achieved by using the FOREACH statement. Some programmers did not understand the difference
between the scroll cursor and regular cursor. Some programmers execute prepare and execute statements
After reviewing the code, we came up with two different tests, one to identify the level of knowledge in database
concepts, and second to identify the level of knowledge in INFORMIX-4GL. We used the test results to identify
individual problem areas. We then conducted a one-day course on DATABASE CONCEPTS and one-day course
INFORMIX-4GL CONCEPTS and discussed problem areas. Developers were found to be more productive after
1. TEST ON DATABASE CONCEPTS
- What is a transaction? What statements do you use to support a transaction?
- Write a SQL statement to find the number of rows in a table.
- Write a SQL statement to find the duplicate records and its count for a given key within a table?
- What is the purpose of creating a VIEW?
- Can you update a View? If Yes, what is the restriction(s)?
- When do you use outer join?
- What is self join?
- What is an isolation level? ( Define the isolation levels supported for the databases you worked with:
- Is isolation level for a Dirty Read acceptable all the time?
- Why do you need Version Management? Does INFORMIX support Version Management?
- What is a check point?
- What is referential integrity?
- Tell us something about cascading deletes - is there any situation where you should not use cascading
- What is the purpose of using stored procedures?
- When do you use triggers?
- Tell us a situation when deadlock occurs.
- When do you use the UNION statement? Does the union statement return duplicate records?
- What UNION statement is supported to return duplicate records?
- Is concurrency important for OLTP? What kind of locking will provide maximum concurrency?
- Is data integrity important for any database? What kind of mechanisms are supported with the
database(s) you worked?
- What is the purpose of INDEX?
- Is there a threshold to create an index?
- Write a statement to update a field EMPLOYEE_NAME to "CLINTON" of table EMP of key EMP_NO
2. TEST ON INFORMIX-4GL CONCEPTS
- What do you like about INFORMIX-4GL ?
- What do you not like about INFORMIX-4GL ?
- Did you ever receive formal or informal training in INFORMIX?
- Tell us of any experiences with INFORMIX-4GL problems that you have and how you handled them.
- What INFORMIX-4GL statement supports "QUERY BY EXAMPLE"?
- What INFORMIX function do you use to find the value of a environment variable?
- In order to use CONTROL-C, what command do you need in your program?
- What is the scope of an INFORMIX-4GL variable?
- What date types are supported by INFORMIX?
- What are the major differences between INFORMIX-SE and INFORMIX OnLine engines?
- What environment variable is used to identify the INFORMIX OnLine database engine Instances?
- What is the purpose of the SET EXPLAIN statement?
- To optimize SQL statements what kind of statements you use?
- When do you use dynamic SQL statement?
- How many times do you define GLOBAL variables?
- How do you execute a UNIX SHELL command within a INFORMIX-4GL program?
- If a select statement returns more than one row of data how do you handle it in INFORMIX-4GL?
- What function do you use to log error messages in a file?
- When do you use the statement WHENEVER ERROR CONTINUE?
- What is the purpose of the DISPLAY ARRAY statement?
- How do you communicate to a DISPLAY ARRAY statement the number of rows to be handled by the
- What does WITHOUT DEFAULTS mean when using the INPUT STATEMENT?
- What is the purpose of the INPUT ARRAY statement?
- What are some of the important functions used with the INPUT ARRAY statement?
- What is the purpose of the INPUT BY NAME statement?
- What are the keys used in an INPUT ARRAY statement to delete a row and add a row?
- What function do you use to get the buffer information?
- What function do you use to identify fields that are touched or not?
- What function do you use to get the current field name?
- What function do you use to get the argument values?
- If you have more than one 4gl program file where does the MAIN function ?
- How can you write a common function to share four global variables with different modules executables?
- Is it always advantageous to use PREPARE and EXECUTE statements?
American Computer Technology, Inc, 10816 ESTATE CT, FAIRFAX, VA-22030
Phone: (703) 385-3273 FAX: (703) 385-4969
by Lester Knutsen
This article defines some guidelines for error handling functions. An error handling function is the code that
responds to a user or system generated error. There are two classes of errors: fatal errors and non-fatal errors.
Events that prevent the continued operation of a program are fatal errors. An example can be a missing data file
required for input. No processing may take place without this file and the program must abort. Events that can
be corrected so that the program can continue are non-fatal. An example is a record locked by another user for
update. The current process can wait or continue processing other records. This event does not require the process
Informix documents all events which can cause an error in the publication "Informix Error Messages". This is
shipped with each product. Each error event is list in this document with its unique number and a description of
Computer programs need a process for dealing with unexpected events that cause system failures. This process is
the error handling function. There are two types or classes of errors; fatal errors and non-fatal alert messages. An
error that prevents the continued processing of a program is a fatal error. An error that can be corrected so that
the process can continue is a non-fatal error. Every program should have a logging process so that all errors are
logged and all critical information can be reviewed at a later date.
1. Fatal Errors
When a fatal error occurs, a program needs to perform the following actions: inform the user or operator of the
problem, perform any required post processing clean-up, log critical information about the error, and abort
processing. There are also circumstances when a program may not be able to perform these actions. A power
outage is an example of an error which causes all programs to completely stop without time for error handling to
be performed. However, every program should have these error handling functions for errors that can be trapped
The user or operator running the program needs to be notified that an error has occurred. The program should
stop and require the user to read an error message before scrolling or clearing any messages off the screen. The
error message should contain the Informix error number and a brief description of the error. Any information that
the operator requires to correct the error should also be displayed. If the process is run in the background, a fatal
error must be sent to a system assigned log.
The program must halt all processing and, as much as possible, return the environment to the state before the
program started. All temporary files or database tables must be removed. Any transactions that have not been
committed must be rolled back.
The program needs an error log. The log should contain some basic information about a successful run such as the
date and time, the operator id, and any options selected by the operator. When an error occurs, the program
should write an error message to the log. The error message needs to include the Informix error number and a one-line error text description. The log should contain the module name, line number of code, and any other
information that is required to correct the error.
A program needs to exit when it encounters an error event. The worst thing a program can do is to hang or
continue to attempt to process in an endless loop.
Abort without Error Handling
There may be cases when a program cannot continue processing to perform an error handling function. A power
or network outage where a program is killed will abort all error handling.
2. Non-Fatal Errors
Some error events are not fatal to a program. Every program needs an error handling processes to deal with non-fatal errors. The program requirements and design must identify what error events each program must be able
to handle, and how it will handle each event. An example of a non-fatal error is a locked record. Before a program
updates a record it must lock the record. If the record is already locked by another process, the new lock attempt
will fail. This will cause an Informix SQL error. The program can wait for the record to be released or continue
processing other records. The program must have a process to handle this type of error. The design specifications
must describe what non-fatal errors should be trapped and how the program will respond to the error.
Partial List of Non-Fatal Informix Errors
The following is a partial list of Informix error events that are usually not fatal and can be planned for in the
Number Error Description
100 Record Not Found.
-100 ISAM error: duplicate value for a record with unique key.
-107 ISAM error: record is locked.
-233 Cannot read record that is locked by another user.
-239 Could not insert new row - duplicate value in a UNIQUE INDEX column.
-250 Cannot read record from file for update.
-263 Could not lock row for UPDATE.
-271 Could not insert new row into the table.
-272 No SELECT permission.
-273 No UPDATE permission.
-274 No DELETE permission.
-275 No INSERT permission.
-288 Table table-name not locked by current user.
-289 Cannot lock table table-name in requested mode.
-329 Database not found or no system permission.
-378 Record currently locked by another user.
-387 No connect permission.
-908 Attempt to connect to database server failed.
Processing Non-fatal Errors
When an event occurs which is defined as a non-fatal error, the program should perform the following tasks: inform
the operator of the event, perform any required event processing clean-up, log information about the event, and
Informix SQL Errors
The Informix OnLine Database Engine maintains the status of every SQL statement in a data structure, SQLCA,
that is available to programs. A program must check the result of SQLCA.SQLCODE after every database
statement. A value of 0 indicates that there was no error. A value of 100 indicates that a record was not found.
Negative numbers indicate an SQL error occurred.
Informix 4GL Error Handling
Informix 4GL maintains error information in two global variables: STATUS and SQLCA.SQLCODE. A negative
number indicates that an error occurred. The 4GL program code will respond to an error depending on how the
statement "WHENEVER ERROR" has been set in the program. The programmer must check these global
variables after every critical statement to determine if an error event occurred and take the appropriate action.
The WHENEVER ERROR statement
The WHENEVER ERROR statement determines how a program will respond to an error. A program can have
multiple WHENEVER ERROR statements so that different sections of code can trap and respond to errors
differently. There are four options for this statement. The first option "WHENEVER ERROR GOTO" should
never be used. The option "WHENEVER ERROR CALL function_name" should be used when one error handling
function can meet all the requirements of a program. However, most programs require different error handling
functions for different sections of code. The option "WHENEVER ERROR CONTINUE" is the recommended
default for all programs. This can be defined once in each module. When this option is set the 4GL program must
check for errors after every critical statement and take appropriate action. The 4GL default is "WHENEVER
ERROR STOP". This causes the 4GL program to display an error message and abort. This is recommended for
sections of code where error handling has not been defined, as it prevents the program from hanging without
displaying any error message.
The Global Variables STATUS and SQLCA.SQLCODE
Informix 4GL sets the global variable STATUS to equal zero or the last error code after every statement. The
SQLCA.SQLCODE is set after every database statement. Informix 4GL programs must check these global
variables after every critical statement. Which variable is checked is very important. The SQLCA.SQLCODE
variable must be checked after every database statement.
The STATUS variable is very dynamic and its value changes after each statement. This can cause mis-informed
results. The successful display of a message after an earlier error will set status back to zero. This will lose the
original error number. The following is an example:
1 OPEN FORM new_form FROM "new_form.frm"
2 IF STATUS != 0 THEN
3 DISPLAY "ERROR OPENING FORM"
4 DISPLAY "ERROR NUMBER ", STATUS
5 END IF
When an error occurs, STATUS is set to a negative number. The "DISPLAY" statement in line three will reset
status to zero if it is successful. Line four will display zero and not the original error number. Every program must
check STATUS and save its value if it needs to be used later.
Displaying Error Messages in Informix 4GL
Informix 4GL provides two statements to display error messages. The "ERROR text" statement prints a
programmer defined text to the bottom line of the screen and rings the terminal bell. This statement is used when
the programmer has a planned error message. The "ERR_PRINT()" function takes the error number as an
argument and prints the Informix error message text on the bottom line of the screen and rings the terminal bell.
This function is used when an error has not been predetermined. The following are two examples:
IF ( STATUS !=0 ) THEN
ERROR "This is the programmers error message"
IF ( STATUS !=0 ) THEN
Informix 4GL Error Logging
Three levels of application error logging are required in Informix 4GL programming. The first level is to log what
the operator is doing so that when a user calls with a problem, the support group can identify and review the
functions. This is also a good way to find out what actually gets used in an application, and what does not get used.
The second level is to log all errors within the program. The third is to log more detailed debugging information
while a program is under development. The log could be to a file, to a network message system, or to a backup
device. The log needs to contain the 4GL module name, the version, the line number in the source code that caused
the error, and a message.
Informix 4GL has two functions to create and write to a log file. The function startlog("logname") creates a log
file. The function errorlog() writes to the log file. This can be used to save SQL error messages, informational
messages about the application, and debugging messages.
WHENEVER ERROR Statement and Logging
Informix 4GL uses a source statement "whenever error" to determine how to handle a fatal error in a program.
The default is "WHENEVER ERROR STOP", which causes the program to abort. This does not allow for any post
processing clean-up. The Informix database will rollback any uncommitted transactions. The recommended mode
is "WHENEVER ERROR CONTINUE". This allows the program to continue and perform any required clean-up
functions, but places the responsibility of planning for error handling on the designer.
When the state of the "WHENEVER ERROR" option in a program is to stop, Informix 4GL will automatically
write to an open error log the Informix error that causes a program to abort. The log will include the source code
line number and module name. However, because the program aborts, it does not allow you to do any clean-up that
may be required.
Informix 4GL Error Logging Example
This example Informix error logging function, includes the source code name, line number, error number and
message. By setting the option "whenever error continue" in a program, this function allows the program to handle
and recover from errors. The function uses features of the Unix Source Code Control System (SCCS) to write a
module name, version and line number to the log file.
The following is a description of an Informix error logging function and some examples. When the error log
function is called, a number (code), a string containing some SCCS information, and a programmer-supplied
message is passed to it. The SCCS information includes the module name, SCCS version, and line number in the
source file that generated the function call. Examples A and B show lines of Informix 4GL code calling the error
Error logging function when checked out of SCCS for editing (get -e filename):
call errorlog( code, "MOD:%M% REL:%I% LINE:%C% :",message )
Error logging function when checked out of SCCS for compiling (get filename):
call errorlog(code,"MOD:errorlog.4gl REL:1.5 LINE:52:",message)
The first argument is a variable named code which defines the type of message. This function has three types of
The first type of message is a program error. If the number is negative, then it is assumed it is an Informix error
message. By replacing the variable "code" in the above function with the SQL error code (sqlca.sqlcode) or status,
an error message will always print in the log if there is an error, and nothing will print if there is no error. The
second type of message is informational. The number 1000 is used for messages that need to be written to the log
to indicate what is happening in the program. The third type of message is for debugging only. The number 0 is
used to code messages that only get written to the log when debugging is turned on. To turn debugging on or off,
one variable in the function, debug_flag, needs to be changed.
The second argument is a string containing the SCCS module name, version and line number. When the source
code is checked out of SCCS using the get command, the %M% is replaced by the module file name, the %I% by
the SCCS version, and the %C% by the line number within the file. This allows the programmer to quickly find
the file and line number that caused an error. See the UNIX documentation for more information on SCCS.
The third argument is a programmer supplied message. This allows entry of messages like "preparing.." or "open
form new.frm" in the log file to track what the program is doing. The following are some examples of how this
function can be used.
To start and end a program with a message to the log saying that the program
started or ended, use a function call like:
call errorlog(1000,"MOD:%M% REL:%I% LINE:%C% :","Starting Program")
After every SQL statement, put a function call to check for errors in addition
to whatever error handling is in the program. If debugging is turned off, these
will only print if there is an error.
call errorlog(status,"MOD:%M% REL:%I% LINE:%C% :","SQL Error ")
call errorlog(sqlca.sqlcode,"MOD:%M%REL:%I% LINE:%C%:","SQL Error")
For debugging, the following statement can be entered anywhere in the program
that a message would be helpful. It will print only when debugging is turned on.
call errorlog(0,"MOD:%M% REL:%I% LINE:%C% :","Debugging Message")
This is the Informix 4GL code for the function:
# Copyright 1993-1996 Advanced DataTools Corporation
# Module: %W% Date: %D%
# Author: Lester B. Knutsen
# Description: General Informix 4GL Logging function
code integer, # message type
relid char(40), # SCCS filename, release and line number
msg char(60), # application message passed to function
msgline char(200),# message output to log
debug_flag integer # set level of error logging
whenever error continue # keep going if there is an error
# set the level of debugging for messages to appear in the log
# one of the following must be uncommented
let debug_flag = true # turn on debugging - all messages will
# appear in the log
#let debug_flag = false # turn off debugging - only sql error
# messages or messages when code is 1000
# will appear in the log
when ( code = 1000 )# always write messages to the log
let msgline = "MESSG: ",code using "------& ", relid ,
when ( code < 0 ) # always write errors to the log
let msgline = "ERROR: ",code using "------& ", relid ,
msg clipped, "\n", err_get(code)
when ( code >= 0 and debug_flag = true ) # only when debugging
let msgline = "DEBUG: ",code using "------& ", relid ,
This is an example Informix 4GL program showing how you could use the error
whenever error continue # keep going if there is an error
call startlog("program.log") # start the error log
# example that will always create a message to the log
call errorlog(1000,"MOD:%M% REL:%I% LINE:%C% :",
"Message 1 - Starting Program")
# example that will only create a message if debugging is true
call errorlog(0,"MOD:%M% REL:%I% LINE:%C% :",
"Message 2 - Debugging Message")
# example that will only create a message if their is a real
# error or debugging is on
call errorlog(status,"MOD:%M% REL:%I% LINE:%C% :",
"Message 3 - Error ")
call errorlog(sqlca.sqlcode,"MOD:%M% REL:%I% LINE:%C% :",
"Message 3 - Error ")
# example that will always create a message to the log
call errorlog(1000,"MOD:%M% REL:%I% LINE:%C% :",
"Message 4 - Tracking Message")
Lester Knutsen Email: email@example.com
Advanced DataTools Corporation Phone: 703-256-0267
4216 Evergreen Lane, Suite 136, Annandale, VA 22003
ON DIGITAL ALPHASERVER 64-BIT SYSTEM (Informix Press Release)
Comparison on Digital Platform Proves Informix Faster, Better, Cheaper Than Oracle.
Performance of INFORMIX-OnLine Dynamic Server on Digital AlphaServer Drives Joint Customer
Implementations For OLTP And Data Warehousing.
MENLO PARK, Calif. (March 25, 1996) -- Informix Software Inc. (NASDAQ:IFMX), the leader in parallel
processing database technology, today announced a record-breaking TPC-C benchmark result on Digital
Equipment Corporation's 64-bit AlphaServer 8400 5/350 system, beating Oracle's existing performance record on
the same platform. In a similarly configured system comparison against Oracle, Informix exceeded Oracle's version
7 transaction throughput by 19%. Informix also performed better than Sybase's System 11 benchmark on the
AlphaServer 8400 5/300 by over 23%.
INFORMIX-OnLine Dynamic Server version 7.2, delivered 13,646 tpmC at an outstanding price/performance
level of $277/tpmC, running the Digital AlphaServer Model 8400 5/350. By contrast, Oracle achieved only 11,456
tpmC, on a more expensive hardware configuration.
This benchmark quantifies the combined power of Informix's parallel processing database architecture with the
64-bit large memory addressability in the Digital Alpha VLM64 architecture. Optimized together, both products
deliver record performance capabilities for customers with requirements for business-critical applications such as
OLTP and data warehousing.
"Our early investment in core parallel processing proved to be an excellent decision," said Steve Sommer, vice
president, marketing, Informix. "These audited benchmark results substantiate that Informix is the leader in
performance. Informix, running on a single SMP system from Digital, has achieved the equivalent of 20 million
transactions per day. With performance of this magnitude, we believe the market will now shift focus to
extensibility and the ability to handle rich content and non-traditional datatypes. That's why we recently acquired
Illustra--the only company currently shipping a fully extensible relational database."
"OnLine Dynamic Server delivered faster transaction throughput than our closest competitor, Oracle, on this
platform," said David Watson, product marketing manager, Informix Server and Connectivity Products. "We
have out-performed the competition on a directly comparable benchmark because of DSAs multithreaded
architecture and core internal parallelism. We have also seen major performance advantages over Oracle for
complex query processing typical in a data warehouse environment. This is due to our parallel data query (PDQ)
technology which has been optimized for very large memory (VLM) environments."
The following comparison chart gives a closer look and explanation of database competitive TPC-C performance:
TPC Performance Server Hardware
tpmC $/tpmC # of Total
Informix-OnLine 13,646 $277 | 10 6GB
Dynamic Server |
(on Digital AlphaServer |
8400 5/350) |
Oracle 7 11,456 $286 | 8 8GB
(on Digital AlphaServer |
8400 5/350) |
The Digital AlphaServer 8400 has a total slot capacity of 8 (each slot supports a board containing either 2 CPUs
or 2 GB RAM). Memory is ar more expensive than CPUs. Since Informix is much more efficient at memory
utilization, customers can select less expensive systems (with more CPU boards and fewer memory boards), yet
achieve superior performance with Informix.
Digital and Informix: Strong Partnership, Technologies, Customer Base
Informix has worked closely with Digital to optimize OnLine Dynamic Server to take full advantage of Digital's
large memory address capability. OnLine Dynamic Server on Digital Alpha provides for I/O efficiency and query
optimization techniques for both OLTP and data warehousing applications with key features such as
multithreading, asynchronous read-ahead, row-level locking, database partitioning, hash joins, and parallel data
"Informix's record-breaking benchmark results demonstrate once again the superior performance, scalability and
affordability of Digital's 64-bit AlphaServer systems," said Pauline Nist, vice president of Digital's AlphaServer
Business Segment. "It is demonstrations such as this that unleash for our customers the superpower embodied in
the Alpha platform."
"The combination of Informix's 64-bit version of OnLine Dynamic Server and the AlphaServer 8400 system offers
performance levels unobtainable by 32-bit systems," said Jim O'Gara, Digital vice president of the Database
Market Development Group. "We are delighted to see Informix demonstrate such strong results which highlight
the strength of OnLine Dynamic Server and the AlphaServer platform."
The performance of INFORMIX-OnLine Dynamic Server on Digital has driven joint customer implementations of the
technologies for OLTP and data warehousing applications. Some of these customers include: 3Com Corporation, a pioneer
of data networking solutions; The legislative Service Center for the State of Washington, which provides information
technology services to the Legislature and other organizations requiring access to legislative information; Nash Finch, the
third largest wholesaler in the U.S.; Sumitomo Bank, worlds fourth largest bank; Telecharge, the second largest provider
of electronic ticketing services in the U.S., and the United Kingdom's Royal Navy.
Informix and Digital have extended their partnership to include a joint data warehousing initiative with systems integrator
KPMG. The initiative exploits the capabilities of the three companies' technologies and services for the data warehouse
market. A competency center and lab where customers may test their applications, as well as cross-training for technical
staff to increase their knowledge about each company's products, has been established at KPMGs Radnor, PA. facility.
INFORMIX'S ILLUSTRA DATABASE POWERS ACADEMY AWARDS
WEB SITE WITH INTERACTIVE GUIDE TO OSCAR-WINNING FILMS
AND STARS (Informix Press Release)
Illustra Technology Enables Customers To Develop Dynamic, Next-generation Web
Applications Using Images, Sound, Text and Video
Virtualize Taps Into New Business Opportunities on the Web with Informix's Illustra Rich
MENLO PARK, Calif., (March 19, 1996) -- Informix Software, Inc. (NASDAQ:IFMX), the leader
in parallel processing database technology, today announced the selection and implementation of
Informix's Illustra database software for "The Envelope Please," the official interactive guide to the
Academy Awards World Wide Web site. The Web site, developed by Informix customer Virtualize,
allows movie buffs around the world to access information from the Academy Awards dating from
1927, using market-leading, rich content management database software developed by Illustra
Information Technologies, Inc., a company acquired by Informix in February 1996.
Informix's complex data-management database software from Illustra allows on-line developers, such
as Virtualize, to create new-age, World-Wide Web applications, expanding the kind of information
and business opportunities available on the Web today. Using Illustra technology, developers now
have the ability to dynamically create pages, through which users can query content-rich information.
"The Internet is fueling the demand to manage and query new kinds of information, in a new, more
interactive, flexible and personal way,=94 said Dick Williams, Illustra's president. "The Envelope
Please Web site is an excellent example of how the Illustra technology can be applied to manage new
data types, providing people across the world with access to previously untapped information in a
compelling, interactive experience."
"The Envelope Please," (http://oscars.guide.com) features 1995's Oscar nominees by category,
number and picture; the history of the Academy Awards, dating from 1927; an Oscar contest; a
calendar of live events and interviews; a behind-the-scenes page; special features; and daily updates.
The site was launched concurrently with the 1995 Academy Awards nominee list this February. Since
then, it has received over 1.7 million hits.
The Web site project began over a year-and-a-half ago, just after the commercial release of the
Illustra extensible database software. Today, "The Envelope Please," is powered by an Illustra
database, running on three Intergraph Interserve machines with WindowsNT and Microsoft's Internet
"Early into the project, we realized the need for a database with the ability to handle not just text, but
visuals, audio, stills, clips and the ability to access that information quickly," said Dave Mangone,
executive producer of The Envelope Please Web site. "Informix's Illustra technology allows us to
manage that kind of information easily, while allowing users to ask for specific information in the
order and of the type that they want."
Building New Opportunities on the Internet
The ability to provide new kinds of information via the Internet is sparking new business
opportunities. Informix recognized that businesses want to expand their existing, flat-file Web pages
into a more dynamic, rich source of information -- one which may generate incremental revenue.
Virtualize, for example, is using Web-page sponsorships and advertising to increase its bottom line,
and plans to develop a subscription service to allow users to access historical Academy Awards
About the Academy Awards
Each year, a billion people, about a fifth of the world's population, watch the Academy Awards
television broadcast. This year's Academy Awards for outstanding film achievements will be
presented March 25, televised live by the ABC Television Network starting at 6 p.m. (PST).
Information about the 68th Academy Awards presentation is available via the World Wide Web at
Virtualize is a multimedia publishing company that develops and produces interactive concepts and
programming; consumer entertainment and edutainment titles; advertiser-driven programming; on-line
content; point-of-purchase and kiosk campaigns; and other interactive presentations. Located in
Santa Monica, California, Virtualize offers full-service digital production and post-production.
Informix Software, Inc., is the leading supplier of high performance, parallel processing database
technology for open systems. Informix products also include application development tools for
creating client/server production applications, decision support systems, and ad-hoc query interfaces,
and connectivity software that allows information to be shared transparently from PCs to mainframes
within the corporate computing environment. Informix's corporate headquarters is based in Menlo
Park, California. More information about Informix is available via the World Wide:
INFORMIX is a trademark of Informix Software, Inc. All other company or product names may be
trademarks of their respective owners.
The Academy Award(s) and Oscar(s) are registered trademarks and service marks of A.M.P.A.S.
A.M.P.A.S. is a copyrighted and registered trademark of the Academy of Motion Picture Arts and
This Newsletter is published by the Washington Area Informix Users Group.
Lester Knutsen, President/Editor
Washington Area Informix Users Group
4216 Evergreen Lane, Suite 136, Annandale, VA 22003