sql command and transactions.
Author: firstname.lastname@example.org (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.