sql command and transactions.

Author: i.sharp@pcisystems.co.uk (Iain Sharp)

I want to run a sql command to update 29million (in some cases more) records in my customers' databases as part of a version rollout.  In sql management I would run something like. 

 declare @rowc bigint
 set @rowc=1
 while @rowc > 0
 begin
 begin transaction iain
 UPDATE top (5000) file SET field = thing WHERE field is null
 set @rowc = @@ROWCOUNT
 commit transaction iain
 end; 

 This does things in 'bite size' chunks, and keeps the transactoin log file small (simple logging).  If I run this same script from within uniface (sql or sql/print) it is wrapped in an outer transaction which means that the log file has to expand to keep a rollback position on all 29million records and it runs really slowly as a result.  Is there some way I can run the sql script from within Uniface transaction = true?  I know I could build a specific program in uniface to test for field being null, run 5000 (or 50000), do a commit in uniface etc. However, this kind of thing comes up regularly, and I'd like to just run all the required sql scripts of which this is but one in a loop. 

8 Comments

  1. Hi Iain, wouldn't it be easier to let this SQLs be executed by an external program you just SPAWN "#...." from inside uniface? You may have more control over the logfile contents etc. compared to the options you have inside of uniface. Greetings from a lousy cold (3°) Frankfurt/Germany morning, Uli


    Author: ulrich-merkel (ulrichmerkel@web.de)
  2. I am debating taking it out of the Uniface part of our startup, (we start our uniface app using a .exe so we can copy asn files and stuff (when modified) from a central store to the client, before they are needed).  However, it is vital that these scripts are run correctly when we upgrade the 100 or so databases on different servers we support. So I am loathe to change the entire infrastructure if there's something else we can do inside the currently functioning system.  spawn has in the past given me somewhat variable results as to modality and such. During an update we will be running a couple of hundred scripts, which must run sequentially, and must terminate if one of the scripts fails. (So as not to run the other scripts following it until the problem script has been dealt with). As such, clear and meaningful feedback is a must. 


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)
  3. If I read the manual correctly, don't name the innermost (your) transaction - https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql Don't know if that helps? Knut


    Author: Knut (knut.dybendahl@gmail.com)
  4. I think it means that the inner transaction doesn't need a name, and also that it is relatively pointless. (Which is what I just determined by creating a 14Gb log file trying to update one of our smaller customers testing database.)  So, unless there's some hidden switch to allow the sql script to run un-encumbered by a uniface transaction level. I think I have to work out how to do all this without uniface in the mix. 


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)
  5. You haven't mentioned which database you're using... seems like you're looking for an autonomous_transaction pragma - we use it in Oracle. But it seems you're on Microsoft SQL, so.... no suggestion here, sorry.


    Author: sochaz (zdenek.socha@fullsys.cz)
  6. Well the name autonomous_transaction did give me something to google, and there are SQL server workarounds, not terribly user friendly workarounds, but then I'm not a terribly friendly user. Cool It would appear that the use of a loopback linked server with remote proc transaction promotion = 'FALSE'  allows the user to call a procedure in it's own transaction, which we could then (presumably) have it call sp_executesql and run a 'random' sql script (or we could write the procedure, commit that, then run the thing via the loopback.).  I may investigate this in my ample spare time. Confused Iain


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)
  7. Hello Iain Have you looked at Service Stored Procedures activated from Uniface ? In G102 for Uniface 9.7 we have done a fix: Solved: https://unifaceinfo.com/fixes/issuelist/27922.php BUG: 27922 - SSP does not run in the same transaction as the Uniface component. So now when activating an Uniface component with transaction = true and activating an SSP from this component the SSP is part of the transaction from the component.   You can not do this for a redirected SSP like [SERVICES_EXEC] EXAMPLE $ALT:EXAMPLE Not solved: https://unifaceinfo.com/fixes/issuelist/31423.php BUG: 31423 - Redirected SSP activated in Uniface transaction does not work.   Peter


    Author: PBeugel (peter.beugel@uniface.com)
  8. I think the problem is that it is wrapped in an outer transaction however it is started by Uniface. MSS SQL server then (effectively) ignores the inner transactions and builds the log file and locks for all the rows changed. 


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)