Risks on mode ansi DB
Recently I am migrating some application from Oracle to Informix ,Oracle use lots of store procedure, in the store procedure, they writes some commit/rollback statement, if we use the unbuffered DB, we have to write lots of "begin work/commit work" pair, so I want to try use MODE ANSI DB, but we never used MODE ANSI DB , Do anybody know MODE ANSI db has additional disadanvange and risk except the following. 1. Default isolation is repeatable read ( can change it to last committed read using sysdbopen() procedure). 2. Write the schema in the program ( can create the synonym for the object). Any information would be welcomed.
On Mon, Sep 13, 2010 at 2:53 PM, CHUAN LU <luchuan114@sina.com> wrote: > Recently I am migrating some application from Oracle to Informix ,Oracle > use > lots of store procedure, in the store procedure, they writes some > commit/rollback statement, if we use the unbuffered DB, we have to write > lots > of "begin work/commit work" pair, so I want to try use MODE ANSI DB, but we > never used MODE ANSI DB , Do anybody know MODE ANSI db has additional > disadanvange and risk except the following. > > 1. Default isolation is repeatable read ( can change it to last committed > read > using sysdbopen() procedure). > > 2. Write the schema in the program ( can create the synonym for the > object). > > Any information would be welcomed. > You won't be able to run distributed queries between that database and a non-ANSI database. The impact of this has to be taken into account. Some stuff (like OAT) will not work properly, or better saying, not all features will work, with ANSI databases. Also, be sure to run a very recent fixpack (FC6W? or FC7 I believe) or you may hit problems with UPDATE STATISTICS.... Please take a look at the migration toolkit. It may be useful and possibly it will convert some code for you. Also check if you can get help from the migration toolkit team. We like what you're doing and maybe you can get some help or more experienced guiding... Migration toolkit page: http://www.ibm.com/developerworks/data/downloads/migration/mtk/ Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0015175cb05c960b2904902496b8
In Informix the transaction is a little bit different comparing to Oracle, you have the implicit Transaction that you don't have in Oracle. If you don't use "begin work" or "begin" you have a implicit transaction, each command is an independent transaction and you can't use commit. If you use "begin" you must use "commit/rollback" to delimit the explicit transaction. In Informix "begin" is equal "begin work", "commit" is equal "commit work", you don't need to use the word "work" you just have to insert the "begin" to start a transaction, or withdraw the commit/rollback if each command is a transaction, depends on your application. I do not recommend you to use ANSI Mode, because you only can use ansi commands instead using Informix commands. Celso Cabral Coimbra -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Fernando Nunes Enviada em: segunda-feira, 13 de setembro de 2010 11:05 Para: ids@iiug.org Assunto: Re: Risks on mode ansi DB [21247] On Mon, Sep 13, 2010 at 2:53 PM, CHUAN LU <luchuan114@sina.com> wrote: > Recently I am migrating some application from Oracle to Informix ,Oracle > use > lots of store procedure, in the store procedure, they writes some > commit/rollback statement, if we use the unbuffered DB, we have to write > lots > of "begin work/commit work" pair, so I want to try use MODE ANSI DB, but we > never used MODE ANSI DB , Do anybody know MODE ANSI db has additional > disadanvange and risk except the following. > > 1. Default isolation is repeatable read ( can change it to last committed > read > using sysdbopen() procedure). > > 2. Write the schema in the program ( can create the synonym for the > object). > > Any information would be welcomed. > You won't be able to run distributed queries between that database and a non-ANSI database. The impact of this has to be taken into account. Some stuff (like OAT) will not work properly, or better saying, not all features will work, with ANSI databases. Also, be sure to run a very recent fixpack (FC6W? or FC7 I believe) or you may hit problems with UPDATE STATISTICS.... Please take a look at the migration toolkit. It may be useful and possibly it will convert some code for you. Also check if you can get help from the migration toolkit team. We like what you're doing and maybe you can get some help or more experienced guiding... Migration toolkit page: http://www.ibm.com/developerworks/data/downloads/migration/mtk/ Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0015175cb05c960b2904902496b8 ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.
Celso, You are misinformed. Putting a database into ANSI logging mode does NOT change the available SQL at all. Every SQL command that is available in BUFFER LOG and UNBUFFERED LOG mode databases is also available in ANSI mode with a very few exceptions all related to maintaining the semantics of an ANSI mode database. Also, the 'WORK' keyword is optional but permitted in BEGIN WORK; and COMMIT WORK/ROLBACK WORK; commands in Informix and I would recommend that one use them to maintain portability of your SQL at the highest level available. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Sep 13, 2010 at 12:03 PM, Celso Cabral Coimbra < ccoimbra@cleartech.com.br> wrote: > In Informix the transaction is a little bit different comparing to > Oracle, you have the implicit Transaction that you don't have in Oracle. > If you don't use "begin work" or "begin" you have a implicit > transaction, each command is an independent transaction and you can't > use commit. If you use "begin" you must use "commit/rollback" to delimit > the explicit transaction. > > In Informix "begin" is equal "begin work", "commit" is equal "commit > work", you don't need to use the word "work" you just have to insert the > "begin" to start a transaction, or withdraw the commit/rollback if each > command is a transaction, depends on your application. > > I do not recommend you to use ANSI Mode, because you only can use ansi > commands instead using Informix commands. > > Celso Cabral Coimbra > > -----Mensagem original----- > De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de > Fernando Nunes > Enviada em: segunda-feira, 13 de setembro de 2010 11:05 > Para: ids@iiug.org > Assunto: Re: Risks on mode ansi DB [21247] > > On Mon, Sep 13, 2010 at 2:53 PM, CHUAN LU <luchuan114@sina.com> wrote: > > > Recently I am migrating some application from Oracle to Informix > ,Oracle > > use > > lots of store procedure, in the store procedure, they writes some > > commit/rollback statement, if we use the unbuffered DB, we have to > write > > lots > > of "begin work/commit work" pair, so I want to try use MODE ANSI DB, > but we > > never used MODE ANSI DB , Do anybody know MODE ANSI db has additional > > disadanvange and risk except the following. > > > > 1. Default isolation is repeatable read ( can change it to last > committed > > read > > using sysdbopen() procedure). > > > > 2. Write the schema in the program ( can create the synonym for the > > object). > > > > Any information would be welcomed. > > > > You won't be able to run distributed queries between that database and a > > non-ANSI database. > The impact of this has to be taken into account. Some stuff (like OAT) > will > not work properly, or better saying, not all features will work, with > ANSI > databases. > > Also, be sure to run a very recent fixpack (FC6W? or FC7 I believe) or > you > may hit problems with UPDATE STATISTICS.... > > Please take a look at the migration toolkit. It may be useful and > possibly > it will convert some code for you. Also check if you can get help from > the > migration toolkit team. > We like what you're doing and maybe you can get some help or more > experienced guiding... > > Migration toolkit page: > > http://www.ibm.com/developerworks/data/downloads/migration/mtk/ > > Regards. > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --0015175cb05c960b2904902496b8 > > ************************************************************************ > ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016361e8236d0b5f70490273515