IBM Support

How to discover who modified a table using the onlog utility

Question & Answer


Question

Is there a way of discovering who modified an Informix table in a database. Someone removed a field from a table. The time it was changed was on "some date" between 13:00 and 18:00. Yes its possible using the onlog Uiltity

Answer

Please find the steps :

1. Dump all the logs within the timeframe:

onlog [-l ] -n <log number> > log<log#>.out

EXAMPLE: onlog -l -n 16 > log16.out

2. Grep PTADESC to find out which logs have that log record type in it That will narrow down which logfiles that need to be reviewed

3. Get the hex(partnum) value from systables for the table that the column was dropped on:

echo 'select hex(partnum) from systables where tabname =
"<tabname>";'|dbaccess <dbname>

return value will look something like:

(expression)

0x00100257

EXAMPLE: echo 'select hex(partnum) from systables where tabname = "test1";'|dbaccess <testdb>

4. Get the onlog output for the partnum :Note this will return all transactions against that partnum

onlog -l -n <log number> -t 0x<value from select in #3> > log<log#>_<partnum>.out

EXAMPLE: onlog -l -n 16 -t 0x100257 > log16_100257.out

5. View the log files and look for PTADESC - onlog output will look something like:


20e8 88 PTADESC 17 0 20bc 100257 0 600 1


58000000 00005c00 10000000 00000000 X.....\. ........
00000000 00000000 11000000 bc200000 ........ ..... ..
18ce1100 57021000 57021000 00000000 ....W... W.......
58020000 01000000 00000000 00000000 X....... ........
00000000 04000002 00000000 00000000 ........ ........
03000000 00000000 ........

The 4th column in the header line is the xid (internal transaction id).

In this example the xid is 17.

Search above the PTADESC entry for the BEGIN log type with that same xid. It will look something like:

2018 56 BEGIN 17 16 0 01/09/2014 16:00:26 49 tuser


38000000 10000100 00000000 00000000 8....... ........
00000000 00000000 11000000 00000000 ........ ........
0fce1100 00000000 fa1bcf52 00000000 ........ ...R....
f6010000 31000000 ....1...

NOTE: The xid's are reused so you have to find the first BEGIN entry with the same xid immediately preceeding the PTADESC entry.

The 10th column of the BEGIN log entry is the userid of the user the executed the transaction.

In the example above the userid that executed this transaction was "tuser".

Related Information

[{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"--","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"10.0;11.1;11.5;11.7;12.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
16 June 2018

UID

swg21661849