Informix Universal Server

formerly "Tips for Using the Illustra Relational Database" by Philip Greenspun for the Web Tools Review

This used to be where I'd let off steam after uncovering a nasty bug in the Illustra object-relational database management system. And, in fact, sometimes I reached such poetic heights of vitriol that I'm leaving the old stuff at the bottom (also, it might be useful if you are still running Illustra for some reason).

However, there really aren't any good reasons to pick on Illustra anymore. The company was bought by Informix, one of the "big three" traditional RDBMS vendors (Oracle and Sybase being the other two). Informix basically folded the interesting features of the old Illustra system into their industrial-strength enterprise-scale RDBMS and calls the result "Informix Universal Server" (IUS). To the extent that IUS is based on old code, it is based on Informix's tried and true Online Server, which has been keeping banks and insurance companies with thousands of simultaneous users up and running for many years.

I plan to be experimenting with IUS in some heavily accessed sites during the latter portion of 1997. I'm going to record my experiences here and hope to have lots of tips and source code to distribute.

Another good place to look is the on-line edition of my book and the accompanying virtual CD-ROM.


Things I learned porting from Illustra to IUS


Here then, more or less untarnished, is my old mean Illustra Tips article...

My Favorite Illustra Features

The PLS Blade provides a really convenient way of indexing character strings. You can only have one list of stop words per installation (see Doc Blade blurb below), though I think this limitation may be removed in a future release. Make sure you have the latest release of Illustra and PLS and that you've messed with the file descriptors parameter of your Solaris operating system. Otherwise you should remove all sharp and/or sledge-hammer shaped objects from your computer room.

One of the first horrors that confronts the Oracle SQL or ANSI SQL programmer is the lack of a boolean data type. Illustra has one. It is much cleaner than using CHAR(1) filled with 'f' or 't'.

Note: Informix Universal Server has at least two and maybe three full-text search Blades, one of which is PLS and none of which is the Doc Blade which I revile below. Also, IUS has the nice clean boolean data type.

Text Searching Caveats

The Doc Blade is the worst conceived and worst realized of the Illustra products I've used. It is supposed to index text. The first problem is its programmer interface. To get anything into a doc type, you first have to write it to a Unix filesystem file. This is especially painful if you are getting text input from Web users. If you go successfully go through these gymnastics, you'll then find that the Doc Blade returns completely random results when documents are short (e.g., classified ads or other smallish chunks you'd be likely to deal with on the Web). Finally, the Doc Blade provides for a list of stop words, i.e., words to not index. Unfortunately, the list has to be the same for every table and database in your installation. However, the appropriate set of stop words is completely different for documents of different types and lengths.

[Note: the Oracle text searching products provide a custom list of stopwords per index.]

The Web DataBlade

Rule 1 (general): don't depend on any Web software tool from a company that hasn't figured out how to put its documentation on-line.

Rule 2 (particular case of Rule 1): don't depend on the Illustra/Informix Web Blade.

The Web DataBlade is yet another server-side extension to HTML. The best of these extensions is Meta-HTML, a carefully designed programming language with HTML syntax. The simplest are the basic server-parsed HTML facilities that you get with standard Web servers (.shtml files).

The Web DataBlade is an ad-hoc design that lets you embed SQL queries in HTML. You are supposed to edit that HTML/SQL hybrid in a Netscape TEXTAREA form input and then submit it to be stored in a database table. Then you can link to Web Blade-backed pages and the WebDaemon will pull the relevant piece of extended HTML out of the database, parse it, do the SQL queries spec'd by your HTML, and then return standard HTML to the requesting client.

Bad Web Blade Item 1: Development Cycle

If you spent the last 20 years learning Emacs, the world's best text editor, then you'll be sorry to learn that the only way to edit a Web Blade page is via an HTML form. Netscape may be the best Web browser, but it is a terrible text editor compared to Emacs. The Netscape "Find" command does not even search through text in a TEXTAREA. So if you want to search for a part of a complicated page, you have to do it with your eyes only.

Compare this to AOLserver Tcl or CGI. If you make a change in either a CGI script or an AOLserver Tcl API program, you can do it in Emacs and the change is immediately reflected next time the page is loaded by a user. Since the CGI and Tcl scripts are stored in the Unix or NT file systems, you can use Emacs, grep, or perl to quickly find and replace pieces of your programs.

Databases are better than file systems, but not if the database vendor hasn't put any thought into what happens to the data once it goes into the RDBMS. If Informix had written a little bit of Emacs Lisp code so that you could edit your Web Blade pages from a standard editor, then having the database storage for code might be nice. But because they only implemented half of a solution, the RDBMS becomes a prison for your code.

Bad Web Blade Item 2: Ad hoc/Baroque syntax

It is obvious that nobody with experience in programming language design thought about the Web Blade. That's a shame. Because any kind of extension to HTML is a programming language. Here's an example of grabbing the email address and subject line for a particular message from a database table of bboard messages:

<?MISQL SQL = "SELECT email, subject FROM bboard where msg_id = 37;">
$2 (from $1)
<?/MISQL>

The business end of this code is the $2 (from $1). This is horrible data abstraction. It might not look that bad now, but what if you had a complicated query and then produced 30 lines of HTML. Do you really want to see $13 on line 28 and try to figure out which column it is? In Oraperl, AOLserver Tcl, or PL/SQL for Oracle WebServer 2.0, you'd instead be able to say $subject (from $email).

Conditionals and control structure appear to be even worse. The heart and soul of this thing is declarative but a lot of times you need to do procedural stuff. Feast your eyes on this..

cond=$(OR,$(NXST,$email),$(NXST,$name),$(NXST,$subject))
I thought nothing could be worse than Tcl or Perl but I was wrong...

Bad Web Blade Item 3: No Real Facility For Checking Input

You'd think that the whole point of a tool like the Web DataBlade would be that you could say "make sure this input is an integer not null and, if not, give the user a nice error message." But you can't. You have to put in all these gnarly conditionals yourself and write your own error messages. It isn't any worse than using raw Perl but the authors of Perl never made any claim that theirs was a Web/db integration tool.

Bad Web Blade Item 4: Bad Database Integration

Yes, bad database integration. Oracle WebServer 2.0 sucks in its own ways, but at least the form variables come to you the programmer as PL/SQL variables. They can contain any ASCII and you can insert them without further worry. Oracle understands RDBMS. Perl and Tcl don't. So you have to manually double the apostrophes (the SQL quoting character) before attempting a string insert, i.e., change 'don't' to 'don''t'. For AOLserver, I wrote a magic little Tcl function that lets me say $QQsubject when I want the double quoted version. The Web DataBlade isn't even as powerful a language as Tcl and it doesn't understand RDBMS like PL/SQL so you have to do this...
<?mivar delimit="'" replace="''" name=subject>$subject<?/mivar>
for every variable that you insert into the database. Ugh!

[Note: you could define a function call to do this a little more cleanly but it would still be much more painful than my Tcl hack.]

Bad Web Blade Item 5: It Works Slowly

Most of the Web Blade-backed sites that I've seen, including the old www.illustra.com (which they took down after being acquired by Informix) are unusable slow. Most people seem to use Web Blade as CGI and that is necessarily about 1/10th of the throughput of AOLserver or Netscape LiveWire or any other technology that holds persistent database connections open. Users wait while the Web server forks the CGI process. Then they wait while the CGI process connects to the RDBMS. Then and only then does the Web server as a whole begin to start trying to retrieve any data for them.

There is a way to mush the Web Blade code into the Netscape server family using the Netscape API. This should substantially increase performance but I've seen a few tales of woe about reliability and I'm not sure that I'd trust the overall system (since a bug in Illustra's code could bring down services for all Web users; that's the downside of a C API and that's why I only use safe APIs like Tcl, Java, and JavaScript).

Note: The Informix Universal Server version of the Web Blade allegedly has the NSAPI and ISAPI interface under control. There are production sites running this (efficient) way, seemingly quite reliably.

Bad Web Blade Item 6: It Doesn't Work At All

I learned about the Web Blade at an installation where they were getting deadlocks. It turns out that each Web Blade page is an SQL transaction. So if you SELECT FROM a table at the top of the page and then try to UPDATE or INSERT into the same table lower down, you will deadlock. That's because the SELECT grabs a read lock on the table. Another copy of the same page can be running simultaneously. It also grabs a read lock on the same table. Then the UPDATE or INSERT tries to get the write lock, which only one RDBMS connection can have at once. It waits for all read locks to be freed up. The simultaneously running copy, holding one of those read locks, is also waiting for the write lock. So two copies of the same page will wait for each other forever (or until the deadlock timer kills them both).

This isn't a problem in Oraperl or AOLserver Tcl. You probably don't need the whole page to run as a transaction and if you don't do anything special then the SELECT up top and the UPDATE down below are separate. You cannot possibly lose in this way.

But it turns out to be a serious problem at this Web Blade-backed site. Call this Bug 1: every page should not have to be a transaction.

So I turned on MI_TRACE planner.1 in the MiParams file so that I'd see all the queries in the miserver.log and could figure out which pages were causing the deadlock. Nothing showed up. It turned out that this was a known bug, let's call it Bug 2: tracing doesn't work for WebBlade queries. This greatly compounds Bug 1.

Finally, it turns out that being deadlocked triggers a bug in the Illustra 3.2 database code whereby miserver processes will grow to consume all the virtual memory on the machine. Now you can't fork processes even to see what is going on. Call this Bug 3, greatly compounding Bug 1 and Bug 2.

How would this work on AOLserver? First, you'd not deadlock unless you explicitly opened a transaction yourself and then did the classically stupid thing of reading before writing. Second, you'd see the queries that were sent to Illustra, in order, written in the AOLserver's own log file (and also probably in Illustra's miserver.log if you configured the RDBMS properly). You wouldn't have to grope around in the dark.

Note: The Informix Universal Server version of the Web Blade lets you choose whether to have each page execute as a transaction or not (I would choose "not", duh!). Also, even if you do have piles of transactions, IUS is about 10 times better at handling locks than Illustra ever was so deadlocks shouldn't be a problem. Finally, the newest Web Blade has much better tracing facilities.

Web Blade Workaround 1

The Web Blade code that I inspected had been written by an expensive highly regarded New York Web site development firm. They apparently didn't like the Web Blade's limited procedural language facilities. So when they want to check an input against a regular expression, they just EXEC a Perl script. Yes, that means that the Unix box has to fork right in the middle of a database transaction. Worse yet, they often fork off a Perl script that then needs to look up some information in the database. They could have installed MiPerl (Perl linked with the Illustra C library; sort of like Oraperl) but instead they had the Perl script fork MSQL, the Ilustra shell-level tool. The Perl script forks MSQL, collects the human-readable output, REGEXPs like crazy to pull the data out into Perl variables, and then may fork MSQL a few more times!

Web Blade Workaround 2

I installed AOLserver and wrote a Tcl script to pull the Web Blade templates out of the RDBMS and stick them into ordinary Unix files. Now they were accessible to grep, Perl, and Emacs. I then wrote a Perl script to batch convert these files into pidgin AOLserver Tcl API. I could have done this in my original AOLserver Tcl script but it turns out that Perl is a much better tool for the job than Tcl. Why? Perl offers the option of non-greedy REGEXP matching. This means that you can easily match "from to the first ".

I was then able to touch up the Perl output in Emacs at the rate of about 20 db-backed URLs/day. End result? The site was about 10 times more responsive at times of light load and was no longer plagued with deadlocks. The site went from being able to perform no more than 1 query at a time to being able to easily handle 10 or more queries/second.

[Note: If you have a suffering Web Blade site and would like a copy of my Tcl and Perl scripts to assist in a conversion to AOLserver, you can download them.]

Bottom Line on the Web Blade

The Illustra Web Blade is the worst-conceived worst-performing piece of Web/RDBMS integration software that I have used. I would rather go back to 1994 when I was using Oraperl and an ancient Oracle 6 installation (to play around with interfacing the Boston Children's Hospital RDBMS to the Web). Tools like AOLserver, Meta-HTML with its database enhancements, Netscape LiveWire, and CGI/Perl hacks are all head and shoulders above the Web DataBlade in terms of

IUS Note: The above paragraph seems harsh on a second reading. It is probably no more harsh than the Illustra Web Blade deserves. However, the new Informix Universal Server Web Blade 3.3 fixes all of the site-killing bugs/features in the original Web Blade. So I wouldn't tell someone with a working site to switch.

"Object-Orientation"

Dave Moon, a pioneer in modern object systems, was one of the world's best programmers but he was not a patient man. He was a founder of Symbolics and I was working there in 1984 when the company moved into a new building next to MIT. The facilities manager sent around some email telling people not to tape posters to their office walls because we'd be moving to bigger quarters in a few years and didn't want the landlord to charge us for excessive wear. That night, the Foonly crashed. A Foonly was a clone of the PDP-10, a mainframe computer designed by Digital in the 1960s. MIT and Stanford people loved the PDP-10 but couldn't afford DEC's million dollar price tags. So there were these guys in a basement in California smoking dope and wirewrapping clones that were 1/3 the speed and 1/20th the cost. Nobody ever figured out why they called the machines "Foonlies".

Moon was a superb hardware engineer and nobody doubted that he would get the Foonly up and running. Still, people were a bit surprised when a huge steel cylinder came crashing through the machine room wall. The cause of the crash had been one of those washing-machine-sized Control Data T-300 disk packs. The cylindrical missile had been the spindle holding together the bad 12" platters. Moon had hurled it through the wall after determining its guilt in the crime of the Foonly crash. I went back to my office and taped up a poster.

The point of this story is to illustrate the prevailing lack of patience among early developers and users of object systems during their first decades. One of the things that drove them crazy about the object systems of the 1970s (SmallTalk, Lisp Machine Flavors) was that if you changed a class definition, the existing instances of that class did not get modified. You'd have to restart your program, maybe even reboot your computer, if you changed your mind about how to represent something. You could lose 20 minutes or even more.

So the object systems of the 1980s, e.g., Common Lisp Object System, were designed to touch up running instances of classes if the class definition changed.

When I read the Illustra User's Guide, I came to it with 15 years of experience with object systems. It took me about an hour to figure out how to take all the columns that were common to a bunch of tables and put them in a supertable, then make subtables inherit from them. In a Web site that sold magazines, videos, books, and CDs, I made a products table with columns product_id, short_name, description, etc. Then the magazines table would inherit from products plus add a column for issues_per_year.

Everything was great until six months later when I wanted to facilitate integration with the MIS department by adding a foreign_key column to the products supertable. You can't change a table with dependents! I had to dump the data out of my tables, drop all of them, and then rebuild them in order to change the schema. I ended up rebuilding them without using inheritance, which is a potentially useful feature but not when you end up with such a brittle data model.

In short, Illustra failed to aborb the object-oriented lessons of the 1970s: class definitions change. This is much worse for a database than for those old Lisp systems. It took a few minutes to restart a Lisp Machine program; you might be off the Web for days trying to rebuild a big database.

I do not want to be around Dave Moon if he ever gets hold of a copy of Illustra.

IUS Note: I do not think that the "brittle data model" problem is fixed in IUS. Be careful and talk to your Informix sales and support people.

Dating

One of the longest persisting effects of having been an MIT undergrad is the famine psychology when it comes to dating. All of my friends from the Class of '82 still think up schemes for getting women. It wouldn't matter if they were Muslims with four wives. They'd still be trying to figure out how to meet and retain feminine companionship.

Illustra won't help them. It picks up the design flaws in the ANSI SQL date-time standard and then adds a few implementation bugs.

Example 1

SQL can be very frustrating to the programmer used to procedural languages. One of the things that I liked about using Oracle, though, was that I'd never had to change a single line of SQL once I'd gotten the query to function. Certainly, I never got an error from an SQL query depending on the data in the table or the phase of the moon. Consider the following query:
select classified_ad_id from classified_ads
where posted > (current_date - interval '1' month)
Since posted is a column containing the date that an ad was placed, you'd expect this to return rows of ads placed within the last month. Well, it was working great for my photography magazine's classified ad section so I forgot about it. Then I got a pile of complaints from users in my mailbox. The service had been dead for a couple of days because March 30 minus 1 month = February 30 according to Illustra. This wouldn't have been so bad if Illustra didn't then check the validity of the date it just generated and then fail with an error message.

The incredible thing about this is that it is the behavior specified by the ANSI standard and documented both in the Illustra User's Guide and the "red standard" book (Date/Darwen). Oracle has nonstandard syntax, but the same semantics would have returned February 28 or February 29 depending on the year.

Workaround: Use current_date - interval '30' day. Watch it fail with another error when you try current_date - interval '100' day.

Workaround for the workaround: Manually tell Illustra that you want three digits of precision on the day field (though you'd think it could see that): current_date - interval '100' day(3).

Example 2

First I created a little table to snapshot the integer session keys I was handing out....

* create table foobar (
	sample_time	timestamp,	-- when did the cookie have the value
	sample_value	integer
);

* select * from foobar order by sample_time;
------------------------------------------
|sample_time               |sample_value |
------------------------------------------
|1996-02-15 19:02:08.000000|75000        |
|1996-02-16 19:02:08.000000|76000        |
|1996-02-17 19:02:08.000000|77000        |
|1996-02-18 19:02:08.347617|77276        |
------------------------------------------

Then I patted myself on the back for constructing a studly "self-join with subquery" to get the intervals... (yes I think this really is the simplest query that will produce the desired data)

select h1.sample_time as s1, 
       h2.sample_time as s2, 
       h2.sample_time - h1.sample_time as gap_length
from foobar h1, foobar h2
where
s2 > s1
and s2 = (select min(h3.sample_time) from foobar h3 where h3.sample_time > h1.sample_time)
order by s1;

---------------------------------------------------------------------
|s1                        |s2                        |gap_length   |
---------------------------------------------------------------------
|1996-02-15 19:02:08.000000|1996-02-16 19:02:08.000000|0-0          |
|1996-02-16 19:02:08.000000|1996-02-17 19:02:08.000000|0-0          |
|1996-02-17 19:02:08.000000|1996-02-18 19:02:08.347617|0-0          |
---------------------------------------------------------------------

I had planned to rush out into the clubs of Manhattan with a printout of this result, expecting women to melt when they saw it. But it was not to be. Subtracting two timestamps a day apart produces a gap that is 0 years and 0 months long. I never really did find a workaround.

Final Dating Caveat

Illustra tables with a date-time columns seem to cause Microsoft Access to choke.

IUS Note: The syntax is very different. I'm pretty sure that the bugs are gone. I'm pretty sure that the "feature" described above (subtracting 1 month from March 30 results in an error) is still a potential problem.

Archiving

One of Illustra's coolest sounding features is archiving. You get to query the system to find out what your data looked like, say, 6 months ago. I relied on this feature in a classified ad system. I would DELETE the ads from the table but still have them around when I wanted to calculate statistics on, say, how many users had successfully sold their goods because of the service.

There are several problems with archived rows. The inflexibility of Illustra data models means that at some point you will probably need to dump your data out of your tables, drop them, and rebuild them. There is no easy way to preserve the archived rows during this procedure, so you lose your history. Some bugs in the Illustra storage manager and backup/restore software can be ameliorated by purging archived rows. Of course, here again you lose your history.

My advice: If you need to keep a history, do it in a "status" column like you would in Oracle or Sybase. Your queries will be slightly uglified but at least you'll have your data when you need it.

IUS Note: Archiving was an Illustra feature that Informix chose not to incorporate in IUS, wisely so in my opinion. So you've no choice but to keep your own status column.

I'm Dysfunctional; You're Dysfunctional

If you've ever puked over an Oracle PL/SQL program, then you'll appreciate Illustra's functions. Unfortunately, the semantics of Illustra functions aren't clear; sometimes they work like macros. Since Illustra does not provide a sequence generator like Oracle, I had to keep my own table of sequence numbers. I wanted to centralize the handing out of new keys, so I made a little Illustra function to update the sequence table and return the new key. I then did something like the following:
insert into products values ( next_product_id(), "AL'S HAND CREAM", ... );
The Unix box started to thrash. Web services ground to a halt. Since my products table was indexed by the product_id with a primary key constraint, Illustra had to check to see if the new ID was already present in a row. In real programming systems, the semantics of functions are well-defined. For example, in Lisp, the args are evaluated and then passed down. So next_product_id would have been evaluated once and the result compared to the existing rows. Instead, Illustra sequenced through the rows, calling my next_product_id over and over again. Each call tried to insert into the sequence numbers table. For some reason, this was just too much for Illustra 2.4 and it caused a complete failure of the database. Inserts into an audit table specified by an alerter weren't done, internal errors showed up in the server log.

My advice: program anything with a side effect in C, Tcl, perl, or whatever else you're using to talk to Illustra.

[Note: PL/SQL has the elegance of a Soviet locomotive, but this ADA derivative is quite solid in my experience. Not only that, Oracle is incredibly good about caching the results of PL/SQL function calls. So you can put a complex query into PL/SQL and it will be very slow the first time it executes, but will return immediately for subsequent calls (until you update one of the tables on which it depends).]

IUS Note: Illustra functions have been killed (mercifully). You can rewrite them in "SPL" which is the Informix equivalent of PL/SQL. This is a tremendous improvement and greatly reduces the need to write C functions.

Oh What a Friend You Have in Seagate

Illustra appears to have finally succeeded in making their on-line backup software generate a consistent snapshot of the database. Nonetheless, you might not wish to trust it if your data is precious. An off-line dump of a 100 MB database takes about 3 hours on a typical Unix box. If you don't want to be off the Web that long, I recommend buying a big collection of disk drives and mirroring them ("RAID Level 0").

Now you never have to back up to protect against media failure. However, you may still want snapshots of your database in case someone gets a little excited with a DELETE FROM statement. Or in case your facility is torched by a distraught user. The way the Oracle studs at Boston Children's Hospital do this with their 60 GB database is to break the mirror, back up from the disks that are off-line as far as the database is concerned, and then reestablish the mirror. For both performance and recovery reasons, they presumably keep their redo log on a separate disk from the rest of the database so they probably don't even risk losing the transactions that occur when the mirror is broken.

Note: this is probably good practice with any RDBMS. I've decided that one really needs at least five disks for a db-backed Web server: one for the root disk/swap; one for writing the Web server Unix file system log; one for reading static image files; one for the database rollback segment; one for the database per se. Then five more to mirror them. If this sounds excessive, note that the Oracle DBA Handbook recommends a 7x2 disk configuration as a minimum compromise for a machine doing nothing but database service (their solutions start at 9x2 disks and go up to 22x2).

Version Upgrades

Upgrading Illustra from one version to another is tricky.

You cannot in general restore a dump from one Illustra version into another. You'd certainly expect them to have engineered in upward compatibility (so that a 3.2 dump would restore into 3.3 if not vice versa). In practice, they put you through a tortuous process where you have to be up and running two Illustra instances simultaneously (e.g., one 3.2 and one 3.3) and xfer the data with a magic script of theirs.

It is every bit as painful as it sounds. An MIT undergraduate presided over this progress once. He did not follow the instructions to the letter and trashed everything. To Illustra's everlasting credit, they TELNETed in and spent two days fixing the installation (including doing the upgrade to 3.3 from 3.2).

If you don't have that many tables, it is probably best to micopy them all out and load them into the new clean version.


More


Return to Web Tools Review home


philg@mit.edu

Add a comment | Add a link