Re: ANSI databases

>From: davek@informix.com (David Kosenko) >Subject: Re: ANSI databases >Date: 5 Mar 1994 02:23:55 GMT >X-Informix-List-Id: <news.5750> >Naomi Walker writes: >>Because of a problem we are having with row-locking, we are considering >>changing our database to MODE ANSI. I am in the process of learning the >>ramifications of this move. So far, we have seen that modifying the >>create on the database to ANSI helps in one particular circumstance >>(without ansi-compliant code (-ansi switch in ESQL/c)). >> >>What other ramifications (especially subtle) might we experience? I can >>create new databases with ansi mode. Can we change a production database >>to ansi, or will it have to be unloaded and recreated? >There's nothing particularly ANSI about a database apart from its logging. >Via tbmonitor you can change the logging mode of the database to >Unbuffered, Mode ANSI and have an ansi db (you'll have to do an archive I >think). Dave thinks correctly. >The most noteable thing, from an application perspective, about an ansi db >in OnLine is that you are always in a transaction - you just choose to >commit. If you app is coded to use transactions *carefully* (I.e. don't >leave them open for loooong periods of time), you should be ok. Course, >you'll have to pull all your BEGIN WORKs as they are non-ansi & will >generate an sql error. You are always in a transaction in SE too. >... > >Oh, once you make a db ansi, you can never make it un-ansi again. You'd >have to unload and recreate. What Dave Kosenko said is all correct, though I would dispute whether the "most notable" item is the transaction behaviour or the fact that if the person running the application doesn't own the table, the table must be referenced by 'owner'.tablename -- that is a far bigger headache in my view, though the transaction behaviour is also significant. In fact, when the database is MODE ANSI, I suggest (strongly) that all tables are owned by a single user, so that all applications can be written using 'owner'.tablename notation thoughout. And all SELECT statements will need to be written using a variant of the notation: SELECT A.*, B.* FROM 'owner'.TableA A, 'owner'.TableB B WHERE A.Column01 = B.Column01 That is, to prevent writing 'owner'.tablename throughout the select statement, you use an alias for every table. And then you have to consider revising the CONSTRUCT statements to use the correct aliases. There are all sorts of other oddities to watch for. For example, if you use the following code and there is no entry in SomeTable with the primary key value, then you are likely to see an error message 100: ISAM error: duplicate value for a record with a unique key. Why? Well, the UPDATE returned NOTFOUND (aka 100), and since the test was for STATUS != 0 rather than STATUS < 0, there was a problem, and the error message for error 100 is as quoted (the same message applies to error -100). The cause: MODE ANSI databases have to return NOTFOUND if no rows are processed. UPDATE 'somebody'.SomeTable SET SomeColumn = somevalue WHERE PrimaryKey = anothervalue IF STATUS != 0 THEN LET msg = ERR_GET(STATUS) ERROR "UPDATE error: ", msg CLIPPED ... END IF Remember that the default isolation level for an OnLine MODE ANSI database is REPEATABLE READ, the highest level of isolation; this implies you are going to need lots, and lots, and lots, and lots, of locks. Or your code is going to have to set COMMITTED READ at startup. Another interesting ANSI-compliance feature which is implemented in Version 6.00 and above, is that DECIMAL(10) means the same as DECIMAL(10, 0) (which is a 10-digit integer) when the database is MODE ANSI, whereas it means a floating point 10-digit number in a non-ANSI database. We wouldn't have done it except that the ANSI standard mandates it! Summary: I wouldn't touch a MODE ANSI database unless I had to. And I'd expect all sorts of interesting little problems arising out of the ANSI standard. And all testing must be done by someone other than the table owner! Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>