We are trying to solve a mystery regarding a bad update that keeps happening in our SQL/Anywhere 16 database nad seems to be hard to reproduce. I can run dbtran and find the errant UPDATE query but what I really need is the user name that issued the command. I know I have the ability to filter out or include users when running dbtran, but I have literally dozen of possible suspects. Doing it one at a time is impractical. I read some documentation about turning on auditing, but I'm not sure it will give me what I'm looking for.
asked 03 Aug '16, 11:03
Bud Durland MRP
When you see something like --UPDATE-1029-0000993526 the number '1029' is the connection identifier. Search backwards until you see the most recent 'CONNECT' entry with the same connection id: --CONNECT-1029-0000993365-DBA-3616-08-03 12:42
So this update was done by DBA.
If the transaction spans different transaction logs, I think you get a 'USER' entry rather than a 'CONNECT' entry.
answered 03 Aug '16, 12:46
In your transaction log translation the update will look something like this entry
where the connection number you have ( -1012- in the above) can be match to the previous connect operation which will look something like this log entry
where the login account follow the logoffset.
If this connection has been around from some time and you have been running incremental backups around this time you may have to chain back through a few of the renamed logs to find the connection but it should alway be logged.
All of this is possible from the transaction log translation you are currently using. You don't need to add auditing for this to work.
answered 03 Aug '16, 12:52
Nick Elson S...