Sybase SQL Anywhere 11.0.2

I need to parse the transaction log to extract the Database
Auditing records. I have uncovered a painful scenario and
wish to resolve it.
I extract audit information from the transaction log using
dbtrans -g logfilename outputfile

The audit records are usually easily pre parsed by throwing
out everything not starting with --

If you perform an update using an if expression.
Update table
set field = IF n then a else b

every record updated is stored in the transaction log as a
commented update command. I assume this is provided in case
you don't want to apply the original statement? I assume
these would not be applied since they are commented. This
is a bit of an issue since I need to evaluate each of these
lines with multiple regular expressions to determine I'm not
interested in them. The table itself contains 1 million+
records.

Can I suppress this behavior? Is it appropriate to suppress
this behavior?

Kyle Danielson

I beleive that is because the -g option implies the -d
(chronological order) option and that comments out
all ddl/dml. So I don't think this can be suppressed.

But other than a possible inconvenience, since you
are parsing this file already it is usually easy enough
to ingore prefixes like this. [If all your searches
are going to be regexp and anchored all you need
to do is prefex all your regexp searches with
"^--" or "^\-\-" would probably do that.]

Maybe I missed your purpose of posting this?


"Kyle Danielson" wrote in message
news:4b0735f4.4da3.1681692777 (AT) sybase (DOT) com...

The transactions are included separatly with the audit
information uncommented. The audit information is commented
so I cannot ignore them.

Sorry this is unrelated to an if expression but the problem
still exists. It occurs regardless of using an IF
expression.

Structure of the file is generally:
--Connection ...
--audit ...
--audit ...
--audit attempting
-- sql statement line 1
-- sql statement line 2
--audit permission checks
--audit success or failure
sql statement line 1
sql statement line 2

it's the commented lines that i need to interpret.

My problem is with an insert statement, not with anything I
need to audit.

Statement example:
INSERT INTO table1(field1, datefield1, field2)
SELECT RS.field1, RS.datefield1, RS.field2FROM table
2 RS, table3 GN
WHERE xxx= xxx AND
RS.xxx = 'xxx' AND
RS.xxx = 'xxx' AND
xxx LIKE 'xxx%'
Union
SELECT RS.field1, RS.datefield1, RS.field2 FROM
table4 RS, table5 GN
WHERE xxx = xxx AND
RS.xxx = 'xxx' AND
RS.xxx in ( 'xxx','xxx');

In the transaction file produced by DBTrans each record
inserted looks like this:
--INSERT-1026-00
--INSERT INTO DRSYS.table1 (field1,datefield1,field2)
--VALUES ('xxx','2007-05-23 15:07:48.953',xxx)

--INSERT-1026-0071978885
--INSERT INTO DRSYS.table1 (field1,datefield1,field2)
--VALUES ('xxx','2007-05-23 15:07:48.953',xxx)

I have all of these commented out insert statements which I
need to parse through for each record that was updated. I
end up with transaction files outputed from dbtran which are
5GB (5 times the size of the transaction log.) There are
other examples of inserts and updates that are producing the
same commented lines in the tbtran files.

If size of the output file is your main issue I don't think I can help you
much there. Not unless you were someone who has already coded
to the dbtools API you could have filtered those out yourself (searching,
say, only for the "----AUDIT " lines); but then again I suspect you would
also/still want/need to selectively filter some of the other lines and would
still have the same problems doing that (as noted next below).

So let me get this straight. Is what you want something like this
equivalent output:

dbtran -g ..... audit.sql
grep -e "^--*AUDIT" <audit.sql >audit_only.sql
?

If so then you may have to code for that yourself (assuming
you are a C programmer and can implement the dbtools
function DBTranslateLog ( ) ).


"Kyle Danielson" wrote in message
news:4b0accec.78e3.1681692777 (AT) sybase (DOT) com...

Yes. That is what I'm doing.

The problem is the transaction files are huge. I have an
example of 60 million lines. I can parse the bad ones out
at a rate of ~2.5 million a minute. This is still 23
minutes of processing time on a fast computer. I will be
deploying this to system in the field that I would not call
fast.

Unfortunately the only way to get to the auditing
information is by moving throught all transaction
information.

an aside:
I will say that the transaction log was a painful file to
parse and interpret. For DOD contracts, I needed to produce
7 reports to show all items of interest.

There was no guide available to help with what to expect in
the output. Linking connections to connection and
disconnection triggers was painful. Most of this process
has been discovery, with new examples popping up and
needing to be addressed with a new or modified regular
expressions.

Thanks for your feedback.

Kyle

Since you seem to be truly using this feature for the intended
purpose, I am sure any feedback you'd like to submit would
be of interest to engineering and product management. A
good newsgroup for that purpose would be the
sybase.public.sqlanywhere.product_futures_discussi on
one.

Maybe if we knew of any standard audit logging formats
those might help guide our efforts. In a similar vein
the nature and formats of the required DOD reports
themselves may also be worth communicating to
us. [though this paragraph may have more to due with
my own ignorance of these things than anything else]

But before I sign off, maybe a couple of more thoughts
might help you in your current efforts.

If it helps you could utilize multiple smaller transaction log
sections by doing frequent backups and/or renames. This can
be done in a scheduled event, a logsize event or triggered
externally. That may allow you the benefit of more memory
to parse each piece, or, maybe an opportunity for greater
parallelsim. That may or may not help you a little.

Unforturnately auditing is a fattening process and not one
that lends itself to speed or efficiency. Editing and parsing
might benefit from having a database input that information
into a table and maybe utilize the indexing and searching
capabilities of the server. A full text index or a materialized
view may not help in this case, those and other features may
be of some benefit.

I particularily like the database table approach because
a careful design here may yield to ad hoc analysis
as well as aggragate (across a facility, department, ...
basis say) analysis; with potential for other downstream
benefits.


"Kyle Danielson" wrote in message news:4b0b1da2.8a2.1681692777 (AT) sybase (DOT) com...