SAP IQ has a built in audit feature that is well documented in the manuals and in other blogs.

On the operational side, however, knowing how to process the audit data and what to do with it is just as important.  If you simply use the dbtran utility to pull out the audit data, you will get duplicate data because dbtran leaves the transaction log in place, intact.

If you prune the transaction log with dbbackup you run the risk of losing valuable audit information.

This blog will outline how to use these two utilities together so that you have a full audit log without the risk of losing audit data, outside of inadvertantly deleting the files.

First, we must backup the SAP IQ catalog.  The catalog is comprised of two major components: the database file and the transaction log.  To back up these files, we will use the dbbackup utility with the -y (create the directory if it doesn’t exist) and the -x (backup the catalog) parameters along with the target directory where we want the copy made.  You will want to customize the connection string (-c) to match your system.  This is the same connection string you would use with the dbisql or Interative SQL utility.

$> dbbackup -c "links=tcp{verify=no;host=localhost:2638};uid=dba;pwd=sqlsql" -y -x /tmp/CATALOG_BACKUP

SQL Anywhere Backup Utility Version 17.0.11.1949
 (1408 of estimated 1408 pages, 100% complete)
Transaction log truncated
Database backup completed

This command created a directory called /tmp/CATALOG_BACKUP.

$> cd /tmp/CATALOG_BACKUP/

$> ls -la
total 5624
drwxrwxr-x. 2 sap  sap       41 Jun  3 16:03 .
drwxrwxrwt. 6 root root     253 Jun  3 16:03 ..
-rw-------. 1 sap  sap  5750784 Jun  3 16:03 iqdemo.db
-rw-------. 1 sap  sap     8192 Jun  3 16:03 iqdemo.log

We don’t have the ability to tell dbbackup to only backup the transaction log, so it will create a copy of the catalog database file, iqdemo.db in this example.  That file is not needed and can be removed.

$> cd /tmp/CATALOG_BACKUP/

$> rm iqdemo.db

From here, we change into that directory and then run the dbtran utility to copy the audit entries from the transaction log and put it into a human readable file called iqdemo.audit.txt.

$> dbtran -g iqdemo.log iqdemo.audit.txt

SQL Anywhere Log Translation Utility Version 17.0.11.1949
WARNING: Do not apply chronologically ordered output to a database
Transaction log "iqdemo.log" starts at offset 0023104797
Current timeline GUID: 499d4f4b-6a28-11eb-8000-90564f6f5d11
Current timeline UTC creation time: 2021-02-08 16:11:23.120742+00:00
Current transaction log GUID: 84e65060-e353-11ec-8000-c95c3a3ec25e
Previous transaction log GUID: 96cbf4b0-6a28-11eb-8000-8c17bea37f07
 100% complete
Transaction log ends at offset 0023110053

$> ls -la
total 16
drwxrwxr-x. 2 sap  sap    48 Jun  3 16:04 .
drwxrwxrwt. 6 root root  253 Jun  3 16:04 ..
-rw-------. 1 sap  sap  6262 Jun  3 16:04 iqdemo.audit.txt
-rw-------. 1 sap  sap  8192 Jun  3 16:03 iqdemo.log

This process can be run as frequently as you wish in order to capture the audit information.  Due to how it interacts with SAP IQ, though, I would not recommend running this any more frequently than hourly, preferably just daily.  I would also strongly suggest that you use a directory name that includes the data and time so that you can retain the entire history, if desired.