sql command and transactions. | Uniface Development | Forum

Avatar

Please consider registering
guest

sp_LogInOut Log In sp_Registration Register

Register | Lost password?
Advanced Search

—  Results per page  —








— Match —





— Forum Options —





Minimum search word length is 3 characters - maximum search word length is 84 characters
For a group of consecutive words like 'end of support' use Match phrase

sp_Feed Topic RSS sp_TopicIcon
sql command and transactions.
16 Nov 2017
7:23 pm
Avatar
Iain Sharp
Member
Forum Posts: 648
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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. 

17 Nov 2017
8:17 am
Avatar
ulrich-merkel
Frankfurt/Germany
Member
Forum Posts: 1762
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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

17 Nov 2017
10:13 am
Avatar
Iain Sharp
Member
Forum Posts: 648
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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. 

17 Nov 2017
3:14 pm
Avatar
Knut
Member
Forum Posts: 201
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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

17 Nov 2017
3:51 pm
Avatar
Iain Sharp
Member
Forum Posts: 648
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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. 

25 Jan 2018
11:30 am
Avatar
sochaz
Member
Forum Posts: 173
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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.

25 Jan 2018
12:48 pm
Avatar
Iain Sharp
Member
Forum Posts: 648
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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

30 Jan 2018
10:56 am
Avatar
PBeugel
Member
Forum Posts: 31
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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

Kind regards Peter Beugel Sr. Technical Support Analyst Uniface B.V.
31 Jan 2018
10:10 am
Avatar
Iain Sharp
Member
Forum Posts: 648
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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. 

Forum Timezone: Europe/Amsterdam

Most Users Ever Online: 131

Currently Online: Adrian Gosbell, tommyb42
17 Guest(s)

Currently Browsing this Page:
1 Guest(s)

Top Posters:

ulrich-merkel: 1762

Iain Sharp: 648

Theo Neeskens: 353

gianni: 297

rogerw: 251

istiller: 235

Knut: 201

lalitpct: 197

Arjen van Vliet: 175

sochaz: 173

Member Stats:

Guest Posters: 3

Members: 8444

Moderators: 0

Admins: 8

Forum Stats:

Groups: 1

Forums: 62

Topics: 2161

Posts: 9344

Newest Members:

briancoleman@aurion.com, kevinmckeever@aurion.com, WarrenNob, brigittebr18, agrohimcsh, englewtat, AbbalibaJor, HanhitCholo, RobertEdumb, lenorajr69

Administrators: admin: 23, Adrian Gosbell: 303, diseli: 935, Bob Maier: 3, Nico Peereboom: 75, Michael Rabone: 4, richiet: 406, JanCees: 28