Question about ANSI mode databases

Hi, I am looking through the IDS logging modes and trying to understand them better. As far as I can tell, ANSI mode means the following: * Transactions are started implicitly, all I need to do is issue a commit. * Given the implications of the above sentence this means that I basically "need" to commit after each select statement to achieve something "autocommit like. WITH LOG implies: * I am basically in an "autocommit" mode where each single statement generates an implicit transaction and immediately commits it. I can manually group statements by issuing BEGIN/COMMIT accordingly. WITH BUFFERED LOG: * The same as "WITH LOG" but better performance at the risk of loosing data. So all in all, "WITH LOG" seems to be near to Postgres behavior and "ANSI" more like Oracle. Did I miss something fundamental? Thanks, Florian

In general yes. Not sure how Postgres works (I would think it was similar to Oracle, but apparently I'd be wrong). ANSI is definitively the most "oracle like". Some other aspects to take into account: 1- AUTOCOMMIT can be specified in JAVA. So if that's your programming environment consider that/test 2- ANSI "enforces" the concept of schema which for non ANSI becomes a bit transparent. This means you can have schema1.mytable and schema2.mytable which is impossible in non-ANSI 3- The later means in ANSI you tend to use schema.table instead of just "table" 4- I'd say 99.8 of the customers use WITH LOG, 0.01 use ANSI and 0.01 use BUFFERED LOG. Numbers are my own obviously but they express a "feeling". I've seen only one customer using ANSI databases in Informix and don't remember one using BUFFERED LOG 5- BUFFERED/UNBUFFERED is a property of the database that works as default for the session. A session can change that. But basically a BUFFERED session in an highly concurrent NON BUFFERED database becomes "useless" because every time another session COMMITs the logical log buffer is flushed 6- I'm afraid I may regret this, but on Informix ANSI databases if you INSERT/UPDATE a CHAR(n) field with a CHAR(n+x) value you'll get an error. In non-ANSI the value will be silently truncated and the operation will be successfully. My possible regret is that this may influence a possible decision in the ANSI direction. Although that's not bad you'll enter a more "exclusive" club. Quickly I can only remember a bug that was specific to ANSI databases. But being a much less used environment may bring additional risks Regards On Fri, Feb 10, 2017 at 8:51 AM, FLORIAN APOLLONER <florian.apolloner@bap.at > wrote: > Hi, > > I am looking through the IDS logging modes and trying to understand them > better. As far as I can tell, ANSI mode means the following: > > * Transactions are started implicitly, all I need to do is issue a commit. > * Given the implications of the above sentence this means that I basically > "need" to commit after each select statement to achieve something > "autocommit > like. > > WITH LOG implies: > > * I am basically in an "autocommit" mode where each single statement > generates > an implicit transaction and immediately commits it. I can manually group > statements by issuing BEGIN/COMMIT accordingly. > > WITH BUFFERED LOG: > > * The same as "WITH LOG" but better performance at the risk of loosing > data. > > So all in all, "WITH LOG" seems to be near to Postgres behavior and "ANSI" > more like Oracle. > > Did I miss something fundamental? > > Thanks, > Florian > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a1144c34023bd7105482a2950

Hi Fernando, thank you for your response! One follow up if you do not mind: > 1- AUTOCOMMIT can be specified in JAVA. So if that's your programming environment consider that/test It is not, but in general: that would mean that the java driver would have to check the database mode and issue "COMMIT" explicitly after every statement if the database is in ANSI mode. > This means you can have schema1.mytable and schema2.mytable which is impossible in non-ANSI Ah, that explains a lot :D > My possible regret is that this may influence a possible decision in the ANSI direction. No worries, we are all consenting adults here ;) Jokes aside, while I love the strict gurantees postgres gives me (error out whenever possible), I do not want ANSI mode. I've been using MySQL in the past, so I am used to data truncation /o\\\\, as long as I get a warning somewhere about the truncation I am fine. Cheers & thanks, Florian

On Fri, Feb 10, 2017 at 10:11 AM, FLORIAN APOLLONER < florian.apolloner@bap.at> wrote: > Hi Fernando, > > thank you for your response! One follow up if you do not mind: > > > 1- AUTOCOMMIT can be specified in JAVA. So if that's your programming > environment consider that/test > > It is not, but in general: that would mean that the java driver would have > to > check the database mode and issue "COMMIT" explicitly after every > statement if > the database is in ANSI mode. > > > This means you can have schema1.mytable and schema2.mytable which is > impossible in non-ANSI > > Ah, that explains a lot :D > > > My possible regret is that this may influence a possible decision in the > ANSI direction. > > No worries, we are all consenting adults here ;) Jokes aside, while I love > the > strict gurantees postgres gives me (error out whenever possible), I do not > want ANSI mode. I've been using MySQL in the past, so I am used to data > truncation /o\\\\, as long as I get a warning somewhere about the truncation > I am > fine. > You may get a bit set in an sqlca structure. But you'd have to check it after each statement... Not pretty. Regards > > Cheers & thanks, > Florian > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --94eb2c19d59cc0fe3e05482af0f6