transaction quesiton

I have below shell script dbaccess - - <<! 1>>$HOME/log/front.log 2>&1 database $FRONTDB; begin work; update tablenameconfig set active='N'; update tablenameconfig set frontrecycle=$frontrecycle wheretablename="$tablename"; update tablenameconfig set active='Y' where frontrecycle="$frontrecycle"; update frnt_para set (FRNT_DT,DL_KEY)=($frontrecycle,0) whereFRNT_DT!=$frontrecycle; commit work; ! the first Update SQL failed because of lock, and the other Update SQL and 'commit work' SQL execute successful. Why the whole transacton doesn't rollback as I expect? Do dbaccess has any trick? thanks for your time

It doesn't rollback because you included the COMMIT WORK; in the script! Two things: 1. To prevent the lock out error, include SET LOCK MODE TO WAIT 10; after the DATABASE statement. 2. To avoid the automatic commit, make this a stored procedure were you can check the SQL error after each step and either continue the statements in the transaction or immediately rollback and return if there is an error. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ 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 Tue, May 1, 2012 at 11:17 PM, CHUAN LU <luchuan@cn.ibm.com> wrote: > I have below shell script > > dbaccess - - <<! 1>>$HOME/log/front.log 2>&1 > database $FRONTDB; > begin work; > update tablenameconfig set active='N'; > update tablenameconfig set frontrecycle=$frontrecycle where> tablename="$tablename"; > update tablenameconfig set active='Y' where frontrecycle="$frontrecycle"; > update frnt_para set (FRNT_DT,DL_KEY)=($frontrecycle,0) where> FRNT_DT!=$frontrecycle; > commit work; > ! > > the first Update SQL failed because of lock, and the other Update SQL and > 'commit work' SQL execute successful. > Why the whole transacton doesn't rollback as I expect? Do dbaccess has any > trick? > thanks for your time > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340d593277ff04bf053647

On Tue, May 1, 2012 at 8:17 PM, CHUAN LU <luchuan@cn.ibm.com> wrote: > I have below shell script > > dbaccess - - <<! 1>>$HOME/log/front.log 2>&1 > database $FRONTDB; > begin work; > update tablenameconfig set active='N'; > update tablenameconfig set frontrecycle=$frontrecycle where> tablename="$tablename"; > update tablenameconfig set active='Y' where frontrecycle="$frontrecycle"; > update frnt_para set (FRNT_DT,DL_KEY)=($frontrecycle,0) where> FRNT_DT!=$frontrecycle; > commit work; > ! > > the first Update SQL failed because of lock, and the other Update SQL and > 'commit work' SQL execute successful. > Why the whole transacton doesn't rollback as I expect? Because DB-Access continues by default after any error. > Do dbaccess has any trick? > Look up the DBACCNOIGN (DB-Access No Ignore) environment variable. When it is set appropriately, DB-Access won't ignore errors and will stop. Alternatively, use SQLCMD from the IIUG web site; you can control whether it stops or not (at least crudely). Or check out SQSL from http://4glworks.com/sqsl.htm. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --f46d0421ad619e848604bf0544de

By defaul dbaccess continues the scripts when an error occurs. You may set DBACCNOIGN environment variable to control this. Please check the documentation in the SQL Reference manual. Regards On Wed, May 2, 2012 at 4:17 AM, CHUAN LU <luchuan@cn.ibm.com> wrote: > I have below shell script > > dbaccess - - <<! 1>>$HOME/log/front.log 2>&1 > database $FRONTDB; > begin work; > update tablenameconfig set active='N'; > update tablenameconfig set frontrecycle=$frontrecycle where> tablename="$tablename"; > update tablenameconfig set active='Y' where frontrecycle="$frontrecycle"; > update frnt_para set (FRNT_DT,DL_KEY)=($frontrecycle,0) where> FRNT_DT!=$frontrecycle; > commit work; > ! > > the first Update SQL failed because of lock, and the other Update SQL and > 'commit work' SQL execute successful. > Why the whole transacton doesn't rollback as I expect? Do dbaccess has any > trick? > thanks for your time > > > > ******************************************************************************* > 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... --00235452fd9c8d638804bf0ac608

DBACCNOIGN The *DBACCNOIGN* environment variable affects the behavior of the DB–Accessutility if an error occurs under one of the following circumstances: - You run DB–Access in nonmenu mode. - In IBM® Informix® only, you execute the LOAD command with DB–Access in menu mode. Set the *DBACCNOIGN* environment variable to 1 to roll back an incomplete transaction if an error occurs while you run the DB–Access utility under either of the preceding conditions. [image: Read syntax diagram][image: Skip visual syntax diagram] <http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlr.doc/id s_sqr_205.htm#d394039e53> >>-setenv--DBACCNOIGN--1--------------------------------------->< For example, assume DB–Access runs the following SQL commands: DATABASE mystore BEGIN WORK INSERT INTO receipts VALUES (cust1, 10) INSERT INTO receipt VALUES (cust1, 20) INSERT INTO receipts VALUES (cust1, 30) UPDATE customer SET balance = (SELECT (balance-60) FROM customer WHERE custid = 'cust1') WHERE custid = 'cust1 COMMIT WORK Here, one statement has a misspelled table name: the *receipt* table does not exist. If *DBACCNOIGN* is not set in your environment, DB–Accessinserts two records into the *receipts* table and updates the *customer* table. Now, the decrease in the *customer* balance exceeds the sum of the inserted receipts. But if *DBACCNOIGN* is set to 1, messages open that indicate that DB–Accessrolled back all the INSERT and UPDATE statements. The messages also identify the cause of the error so that you can resolve the problem. On Tue, May 1, 2012 at 11:27 PM, Jonathan Leffler < jonathan.leffler@gmail.com> wrote: > On Tue, May 1, 2012 at 8:17 PM, CHUAN LU <luchuan@cn.ibm.com> wrote: > > > I have below shell script > > > > dbaccess - - <<! 1>>$HOME/log/front.log 2>&1 > > database $FRONTDB; > > begin work; > > update tablenameconfig set active='N'; > > update tablenameconfig set frontrecycle=$frontrecycle where> > tablename="$tablename"; > > update tablenameconfig set active='Y' where frontrecycle="$frontrecycle"; > > update frnt_para set (FRNT_DT,DL_KEY)=($frontrecycle,0) where> > FRNT_DT!=$frontrecycle; > > commit work; > > ! > > > > the first Update SQL failed because of lock, and the other Update SQL and > > 'commit work' SQL execute successful. > > Why the whole transacton doesn't rollback as I expect? > > Because DB-Access continues by default after any error. > > > Do dbaccess has any trick? > > > > Look up the DBACCNOIGN (DB-Access No Ignore) environment variable. When it > is set appropriately, DB-Access won't ignore errors and will stop. > > Alternatively, use SQLCMD from the IIUG web site; you can control whether > it stops or not (at least crudely). Or check out SQSL from > http://4glworks.com/sqsl.htm. > > -- > Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> > Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org > "Blessed are we who can laugh at ourselves, for we shall never cease to be > amused." > > --f46d0421ad619e848604bf0544de > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9d2f262aa0acc04bf28fd34