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 System
• 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 http://www.informix.com.

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:

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.

Washington Area Informix User Group Forum 96

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.

Surprise Presentation

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.

Client Server

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 VB.

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++.

Wrap-Up

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.

You Know My Methods, Dr. Watson

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.

Observe

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.

Hypothesize

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?"

Test

-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.

(Silver Blaze)

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.

Correction

...[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 one.

Prevention

...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.

John Ashmead

Ashmead Computers

139 Montrose Avenue

Rosemont, PA 19010

Phone:   215-527-9560

Testing an Informix Developer

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.

1. lack of knowledge in database concepts
2. unable to use right INFORMIX-4GL statement to accomplish a task
3. software reuse
4. 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 sequentially.

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 the courses.

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: ORACLE/INFORMIX/SYBASE/others.)
• 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 deletes?
• 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 = 123.

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 SCROLL list?
• 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?

Madhu Reddy  

American Computer Technology, Inc, 10816 ESTATE CT, FAIRFAX, VA-22030

Phone: (703) 385-3273  FAX: (703) 385-4969

Error Handling Functions in Informix Programs

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 to abort.

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 the error.

Error Handling

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 and processed.

Operator Notification

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.

Processing Clean-up

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.

Error Log

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.

Abort Processing

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 program design.

Error 

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 continue processing.

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:

Example 1:

	IF ( STATUS !=0 ) THEN

		ERROR "This is the programmers error message"

		CALL error_clean_up()

		EXIT PROGRAM

	END IF



Example 2:

	IF ( STATUS !=0 ) THEN

		CALL ERR_PRINT(STATUS)

		CALL error_clean_up()

		EXIT PROGRAM

	END IF

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.

Logging Functions

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 logging function.

Example A:	

Error logging function when checked out of SCCS for editing (get -e filename):

	call errorlog( code, "MOD:%M% REL:%I% LINE:%C% :",message )



Example B:

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 messages.

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.

Example C:

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")



Example D:

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 ")

	or 

	call errorlog(sqlca.sqlcode,"MOD:%M%REL:%I% LINE:%C%:","SQL Error")



Example E:

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")



Example F:

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

#################################################################

function errorlog(code,relid,msg)

define 	

	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

case

	when ( code = 1000 )# always write messages to the log

		let msgline = "MESSG: ",code using "------& ", relid ,

		msg clipped

		call errorlog(msgline)

		return

	when ( code < 0 )	# always write errors to the log

		let msgline = "ERROR: ",code using "------& ", relid ,

		msg clipped, "\n", err_get(code)

		call errorlog(msgline)

		return

	when ( code >= 0 and debug_flag = true ) # only when debugging

		let msgline = "DEBUG: ",code using "------& ", relid ,

		msg clipped

		call errorlog(msgline)

end case

end function

#################################################################









Example G: 

This is an example Informix 4GL program showing how you could use the error

logging functions.







main

	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 ")

	# or 

	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")

end main

Lester Knutsen Email:                                lester@access.digex.net

Advanced DataTools Corporation                       Phone:  703-256-0267

4216 Evergreen Lane, Suite 136, Annandale, VA 22003

INFORMIX ANNOUNCES RECORD TPC-C BENCHMARK

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

                                              CPUs       Memory



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)                             |