Tracking DAT / Database Changes Over Time, Similar to SQL ChangeLog |

Question

James Hipp · Apr 21, 2021

#Databases #DevOps #Red Hat Enterprise Linux (RHEL) #SQL #InterSystems IRIS

Hello,

We have a need to track Database changes over time - down to the SQL level of granularity if possible. Such as: User xyz runs routine ^abc and we get something similar to a changelog that tells us: table A had this value updated, insert, update etc....

Is that possible using IRIS level tools (Audit Log, Journal File, etc...) , is there a way to convert the global sets and kills from the journals into SQL level changes?

I also found a third-party java-based tool named Liquibase , but it seems more oriented to tracking schema and table based changes or pre-defined definitions.

Thanks,

James

Product version: IRIS 2020.1

Discussion (3)2

Erik Hemdal · Apr 28, 2021

James,

The system-provided audit events are documented here:

https://docs.intersystems.com/iris20201/csp/docbook/Doc.View.cls?KEY=GCAS_audit#GCAS_audit_sys_events

Check to see if these events will give you what you need:

%System/%DirectMode/DirectMode audits commands run at the IRIS terminal.
The events under %System/%SQL audit SQL commands.
%System/%System/OSCommand audits $ZF calls.

There are many others that track logins and system changes.

Erik

0 0

Marc Mundt · Apr 28, 2021

If you control the DB schema, you can use triggers for this.

0 0

Yaron Munz · Jun 2, 2021

Hello,

I assume you are looking for something like CDC (capture data changes) for .

The basic idea is to programmatically read journal files record by record and analyze the SET/KILL ones (according to some dictionary you build to determine which globals or classes need the CDC capability).

I have done something similar using the ^JRNUTIL

https://docs.intersystems.com/irislatest/csp/docbook/Doc.View.cls?KEY=GC...

0 0