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.
