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%20Durland%20MRP's gravatar image

Bud Durland MRP
3126
accept rate: 0%


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.

permanent link

answered 03 Aug '16, 12:46

John%20Smirnios's gravatar image

John Smirnios
8.9k377112
accept rate: 39%

Thanks to both John and Nick; knowing what was the connection id was the missing element.

(03 Aug '16, 12:55) Bud Durland MRP

In your transaction log translation the update will look something like this entry

   --UPDATE-1012-logoffset
   UPDATE owner.table_name
      SET . . . VALUES ( . . . ) WHERE  . . . 
   go

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

--CONNECT-1012-logoffset-DBA-2016-07-31 11:27

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.

permanent link

answered 03 Aug '16, 12:52

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 31%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×10

question asked: 03 Aug '16, 11:03

question was seen: 239 times

last updated: 03 Aug '16, 12:55