SQR and Sybase Transactions
Julie Lawton
I have a sqr script which produces flat files. Originally the script
contained
one procedure which selected all the necessary records using a begin-select
paragraph. It would then update those records using a begin-sql paragraph.
Problems were occuring where records were being updated but not selected and
from investigation it appeared that the select and the update were being run
as
separate Sybase transactions therefore allowing a new record to be inserted
between the select and update.
I have modified the script and changed the update to be a separate
procedure.
Then from the select, I have called the update procedure. A copy of the
code is
below. I would just like confirmation from Sybase experts that this method
now
constitutes one Sybase transaction.
begin-setup
ask dbname 'Type a database name to use'
use {dbname}
page-size 1 3633
no-formfeed
end-setup
begin-report
let $dbname = '{dbname}'
do body ()
end-report
begin-procedure body ()
Show 'Select of tickets started'
begin-select
code
convert(char(10), convert(int, groupID) - 1 ) &NewGroupLess1
convert(char(10), convert(int, groupID) + 1 ) &NewGroupPlus1
TradeRole &RefTradeRole
TradeProcess &RefTradeProcess
TradeCode (,,30)
AuditTimeStamp (,,11) edit YY|MM|DD|HH:MI
AuditUserName (,,30)
TicketStatus (,,4)
InstrumentType (,,4)
ReferenceInstrument (,,24)
Commodity (,,24)
OrderID (,,24)
TicketID (,,24)
ExternalTransactionID (,,24)
CreationReferenceID (,,24)
GroupID (,,24)
StructuredTradeRefID (,,24)
Valid (,,4)
Counterparty (,,24)
Broker (,,24)
BrokerStatus (,,4)
TradedFor (,,15)
LocalCcyKey (,,5)
LegalVehicle (,,24)
AccountingGroup (,,24)
TradingArea (,,24)
TradeStatus (,,4)
CreationMethod (,,4)
TradeRole (,,4)
OpeningUser (,,15)
OpeningDateTime (,,11) edit YY|MM|DD|HH:MI
UserField1 (,,16)
UserField2 (,,16)
UserField3 (,,16)
UserField4 (,,51)
UserField5 (,,51)
MarginTrader (,,24)
MarginRate (,,17) edit 9999.999999999999
InstrSubType (,,4)
Currency1 (,,24)
Currency2 (,,24)
Currency1Amount (,,17) edit 8888888888888.888
Currency2Amount (,,17) edit 8888888888888.888
AmountCcyFlag (,,4)
QuotingTerms (,,4)
DealtRate (,,17) edit 99999.99999999999
MarketRate (,,17) edit 99999.99999999999
ValueDate (,,11) edit YY|MM|DD
OptionDate (,,11) edit YY|MM|DD
SettlementDate (,,11) edit YY|MM|DD
ReadAt (,,8)
FixingRate (,,17) edit 88888.88888888888
LiquidationMethod (,,24)
ExportStatus (,,1)
new-page
do update ()
from FXTicket
where
UserField3 != "AimTicket"
and TradeRole != 10
and TradingArea not in ( "LMFI", "GEM")
! no nostros
and TradeRole != 9
! no fx balance
and (InstrSubType != 2 and TradeRole != 1)
! no storno voids
and (TicketStatus in (1,2) or ( TicketStatus = 3 and (TradeProcess &
536870912) = 536870912 ) )
order by Code
end-select
Show 'Select completed'
end-procedure
begin-procedure update()
Show 'Update commenced'
begin-sql
update FXTicket set UserField3 = "AimTicket"
where TradingArea not in ("LMFI", "GEM")
and UserField3 != "AimTicket"
and TradeRole != 10
and TradeRole != 9
and (InstrSubType != 2 and TradeRole != 1)
and (TicketStatus in (1,2) or ( TicketStatus = 3 and (TradeProcess &
536870912) = 536870912) )
end-sql
Show 'Update completed'
end-procedure
Many thanks
Julie
Anthony Mandic
> I have modified the script and changed the update to be a separate
> procedure. Then from the select, I have called the update procedure.
> A copy of the code is below.
> I would just like confirmation from Sybase experts that this method
> now constitutes one Sybase transaction.
Hard to say since few of us are SQR knowledgeable. What I
would suggest you do is configure and start auditing on the
server and capture the command text of the user making the
SQR connection and then run the code. Then extract the extras
column from the sysaudits(_01 - it may have this extension,
depending on the server version) and examine it. You may need
to format it to make it legible. If it looks like one transaction
there, then you should be OK. If not, redo your code and repeat
the above, noting the resultant T-SQL code differences.
-am