Existing tool listing database changes ?

[KubiaK]

Does anyone know about a powerful tool that could list updates commited on a database in Progress v8 ?

For example, i want to know which records of which tables are created, updated and deleted in my db after a major treatment, in order to check that my treatment works well.

This tool should know about the database status before and after my procedure is launched, and recover all changes in the db during this period of time. Generally, the goal is to request the last db uses between 2 hours like :

Between 1:15 pm to 2:00 pm :

Table Order
Record(s) Created :
1:17 pm num-order 465465
1:45 pm num-oder 498651

Record(s) Updated :
1:25 pm num-order 465465 amount = 45,00 (prev. 5,50)

Record(s) Deleted :
1:49 pm num-order 479460

Table Customer
Records Updated :
1:54 cust-num 81112 adress-1 = "18 Grand Avenue" (prev. "23 Oxford Street")
name = "DUPONT" (prev. DUPOND)

Etc...

 

[bendaluz2]

You could do this with database triggers writing to an audit table

Originally posted by KubiaK
Does anyone know about a powerful tool that could list updates commited on a database in Progress v8 ?

For example, i want to know which records of which tables are created, updated and deleted in my db after a major treatment, in order to check that my treatment works well.

This tool should know about the database status before and after my procedure is launched, and recover all changes in the db during this period of time. Generally, the goal is to request the last db uses between 2 hours like :

Between 1:15 pm to 2:00 pm :

Table Order
Record(s) Created :
1:17 pm num-order 465465
1:45 pm num-oder 498651

Record(s) Updated :
1:25 pm num-order 465465 amount = 45,00 (prev. 5,50)

Record(s) Deleted :
1:49 pm num-order 479460

Table Customer
Records Updated :
1:54 cust-num 81112 adress-1 = "18 Grand Avenue" (prev. "23 Oxford Street")
name = "DUPONT" (prev. DUPOND)

Etc...

 

[KubiaK]

Originally posted by bendaluz2
You could do this with database triggers writing to an audit table

But it should overweight the database a lot if every single db i-o triggers an audit, wrong ? Do you mean i should put for example a trigger WRITE on each table that would update a text file or a special audit table ?

 

[bendaluz2]

Correct, it will slow things down, but if you need to store this information, then however you do it, it will have this impact. Yes, this is what I mean, you will need a trigger for each table you wish to monitor. While this sounds a lot of work, I just wrote a program to create my triggers.

You need to add a line at the top of the trigger produced:

TRIGGER PROCEDURE FOR WRITE OF <table-name> OLD BUFFER buf$<table-name>.

The trigger produced will create a field c$change which contains all the changes between the buffers in the following format.

field-nameld-value|new-value,field-nameld-value|new-value, etc...

You will need to add code at the bottom to do what you want with this information. e.g. create records in an audit table or output to a file.

n.b. you will also need delete triggers.

If you are worried about performance and you only want to monitor these changes at specific times, then put a check in at the top of the trigger so that it only runs when a flag is set, file exists or some other condition.

Hope this helps.

define variable c$table as character no-undo initial "<table-name>".
define variable i as integer no-undo.

output to value(c$table + "-trig.p").

find _file
    where _file._file-name = c$table
    no-lock no-error.
if available _file then
do:
    put unformatted
        "DEFINE VARIABLE c$change AS CHARACTER NO-UNDO."
        SKIP(1).
    for each _field
        where _field._file-recid = recid(_file)
        no-lock:
        if _field._extent > 1 then
        do i = 1 to _field._extent:
            put unformatted
                "if " c$table "."
                _field._field-name
                "[" i "] <> buf$"
                c$table "." _field._field-name
                "[" i "] then"
                skip
                "do:"
                skip
                "    if c$change = '' then"
                skip
                "        assign c$change = c$change + '" + _field._field-name + "[" + string(i) + "]"
                                         + ":' + string(" + c$table + "." + _field._field-name + "[" + string(i) + "])"
                                         + " + '|' + string(buf$" + c$table + "." + _field._field-name + "[" + string(i) + "])."
                skip
                "    else"
                skip
                "        assign c$change = c$change + '," + _field._field-name + "[" + string(i) + "]"
                                         + ":' + string(" + c$table + "." + _field._field-name + "[" + string(i) + "])"
                                         + " + '|' + string(buf$" + c$table + "." + _field._field-name + "[" + string(i) + "])."
                skip
                "end."
                skip.
        end.
        else
            put unformatted
                "if " c$table "."
                _field._field-name
                " <> buf$"
                c$table "." _field._field-name
                " then"
                skip
                "do:"
                skip
                "    if c$change = '' then"
                skip
                "        assign c$change = c$change + '" + _field._field-name
                                         + ":' + string(" + c$table "." + _field._field-name + ")"
                                         + " + '|' + string(buf$" + c$table + "." + _field._field-name + ")."
                skip
                "    else"
                skip
                "        assign c$change = c$change + '," + _field._field-name
                                         + ":' + string(" + c$table "." + _field._field-name + ")"
                                         + " + '|' + string(buf$" + c$table + "." + _field._field-name + ")."
                skip.
    end.
end.

output close.

Originally posted by KubiaK
But it should overweight the database a lot if every single db i-o triggers an audit, wrong ? Do you mean i should put for example a trigger WRITE on each table that would update a text file or a special audit table ?

 

[KubiaK]

Thanks for this easy to implement solution. I thought there was a way to use some kind of Database Admin tool and to customize it, or customize the "Activity" report in order to get more details, but i'm not a dba .

 

[bendaluz2]

If you just want to monitor these "major treatments" as you say, you could define all the triggers as overridable, and then on a normal client session define an override trigger at startup which does nothing. this would completely remove the overhead for your users. However, when you do your "treatments", dont override the triggers, and the audits will be created

 

[bendaluz2]

LOL

Just thinking about it, it would clearly be easier just to define these audit triggers in the session where you do your "treatments" and then this would mean no changes your existing system, which is always a good thing