Salvage inform on who drop a table from logical lo

PLATFORM : AIX INFORMIX VERSION : 11.70FC7 Hi all, we have a database with unbuffered mode and recently we found that there is a missing object. We want to find out who drop that particular object, given the fact that there is not much activities on the box and also all logical log is still available. Trying onlog -lu <username> where <username> are those that has the DBA/RESOURCE right. Question(s) : 1) How to identified the header for DROP TABLE command from the logical log's output ? I do see example like CKPOINT, HUPBEF, HDELETE, UNIQ8ID and etc. 2) the output of the logical log is on HEX format, any tools to convert it to human readable format ? Thanks

I created a database and a table. The table name is put into systables and contains the name of the table. create database cmpdb with log; create table tab1 (col1 int, col2 int); select * from systables where tabname = "tab1";tabname tab1 owner mpruet partnum 1049089 tabid 100 rowsize 8 ncols 2 nindexes 0 nrows 0.00 created 04/11/2016 version 6553601 tabtype T locklevel P npused 0.00 fextsize 16 nextsize 16 Now I need the partnum for systables.... select * from systables where tabname = "systables";tabname systables owner informix partnum 1049024 (hex 0x1001c0) tabid 1 rowsize 500 ncols 26 nindexes 2 nrows 65.00000000000 Now all I need to to is to track the changes made to systables after the table has been dropped since dropping the table would have updated systables... drop table tab1; Now executing onlog .... 44695890 5 U-B---- 5 1:30043 5000 5000 100.00 446958d8 6 U-B---- 6 1:35043 5000 1837 36.74 44695920 7 U---C-L 7 1:40043 5000 1636 32.72 44695968 8 A------ 0 1:45043 5000 0 0.00 446959b0 9 A------ 0 1:50043 5000 0 0.00 onlog -l -n 7 > /tmp/t1 Using an editor, we find the insert into systables (when the table was created)... addr len type xid id link 65f3d0 188 HINSERT 15 0 65f390 1001c0 a0c 122 bc000000 00002800 12010000 00000000 ......(. ........ 00000000 00000000 0f000000 90f36500 ........ ......e. 5e2d0700 c0011000 c0011000 0c0a0000 ^-...... ........ 7a000004 00000000 00000000 80000000 z....... ........ 04746162 316d7072 75657420 20202020 .tab1mpr uet 20202020 20202020 20202020 20202020 20202020 20001002 01000000 64000800 ... ....d... 02000000 00000000 00000000 00a5e600 ........ ........ 64000154 50000000 00000000 00000000 d..TP... ........ 10000000 10000001 00010000 00000000 ........ ........ 00000000 00080000 00000000 00000000 ........ ........ 00000000 00000080 00412b00 ........ .A+. And then look for the HDELETE.... 662018 52 BEGIN 15 7 0 04/11/2016 22:29:45 38 mpruet 34000000 07000100 00000000 00000000 4....... ........ 00000000 00000000 0f000000 00000000 ........ ........ 952d0700 00000000 a96b0c57 f4010000 .-...... .k.W.... 26000000 &... 66204c 188 HDELETE 15 0 662018 1001c0 a0c 122 bc000000 00002900 12010000 00000000 ......). ........ 00000000 00000000 0f000000 18206600 ........ ..... f. 932d0700 c0011000 c0011000 0c0a0000 .-...... ........ 7a000000 00000000 00000000 80000064 z....... .......d 04746162 316d7072 75657420 20202020 .tab1mpr uet 20202020 20202020 20202020 20202020 20202020 20001002 01000000 64000800 ... ....d... 02000000 00000000 00000000 00a5e600 ........ ........ 64000154 50000000 00000000 00000000 d..TP... ........ 10000000 10000001 00010000 00000000 ........ ........ 00000000 00080000 00000000 00000000 ........ ........ 00000000 00000080 00410000 ........ .A.. We see the transaction index ID is 15 and the begin work log record (BEGIN) is immediately before the HDELETE log record from systables. (HDELETE is home row delete...) And that indicates that user mpruet issued the transaction which executed the "drop table tmp"... Madison Pruet Retired and Loving it On Monday, April 11, 2016 7:44 PM, LEY PATRICK <patrickley@gmail.com> wrote: PLATFORM : AIX INFORMIX VERSION : 11.70FC7 Hi all, we have a database with unbuffered mode and recently we found that there is a missing object. We want to find out who drop that particular object, given the fact that there is not much activities on the box and also all logical log is still available. Trying onlog -lu <username> where <username> are those that has the DBA/RESOURCE right. Question(s) : 1) How to identified the header for DROP TABLE command from the logical log's output ? I do see example like CKPOINT, HUPBEF, HDELETE, UNIQ8ID and etc. 2) the output of the logical log is on HEX format, any tools to convert it to human readable format ? Thanks ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Of course, I could execute the following. The begin work/commit work log record is always displayed... onlog -l -n 7 -t 0x1001c0662018 52 BEGIN 15 7 0 04/11/2016 22:29:45 38 mpruet 34000000 07000100 00000000 00000000 4....... ........ 00000000 00000000 0f000000 00000000 ........ ........ 952d0700 00000000 a96b0c57 f4010000 .-...... .k.W.... 26000000 &... 66204c 188 HDELETE 15 0 662018 1001c0 a0c 122 bc000000 00002900 12010000 00000000 ......). ........ 00000000 00000000 0f000000 18206600 ........ ..... f. 932d0700 c0011000 c0011000 0c0a0000 .-...... ........ 7a000000 00000000 00000000 80000064 z....... .......d 04746162 316d7072 75657420 20202020 .tab1mpr uet 20202020 20202020 20202020 20202020 20202020 20001002 01000000 64000800 ... ....d... 02000000 00000000 00000000 00a5e600 ........ ........ 64000154 50000000 00000000 00000000 d..TP... ........ 10000000 10000001 00010000 00000000 ........ ........ 00000000 00080000 00000000 00000000 ........ ........ 00000000 00000080 00410000 ........ .A.. addr len type xid id link 662108 100 DELITEM 15 0 66204c 1001c0 1001c0 a0c 7 1 37 64000000 00001d00 10000000 00000000 d....... ........ 00000000 00000000 0f000000 4c206600 ........ ....L f. 992d0700 c0011000 c0011000 c0011000 .-...... ........ 0c0a0000 07000000 01002500 04746162 ........ ..%..tab 316d7072 75657420 20202020 20202020 1mpruet 20202020 20202020 20202020 20202020 2075626c ubl 66216c 64 DELITEM 15 0 662108 1001c0 1001c0 a0c 2 2 4 40000000 00001d00 10000000 00000000 @....... ........ 00000000 00000000 0f000000 08216600 ........ .....!f. 9b2d0700 c0011000 c0011000 c0011000 .-...... ........ 0c0a0000 02000000 02000400 80000064 ........ .......d 662518 48 BEGCOM 15 0 6624ec 30000000 00000400 10000000 00000000 0....... ........ 00000000 00000000 0f000000 ec246600 ........ .....$f. af2d0700 00000000 a96b0c57 c3011000 .-...... .k.W.... 663044 48 COMMIT 15 0 663018 04/11/2016 22:29:45 30000000 00000200 10000000 00000000 0....... ........ 00000000 00000000 0f000000 18306600 ........ .....0f. b72d0700 00000000 a96b0c57 a96b0c57 .-...... .k.W.k.W Madison Pruet Retired and Loving it On Monday, April 11, 2016 10:37 PM, Madison Pruet <madison_pruet@yahoo.com> wrote: I created a database and a table. The table name is put into systables and contains the name of the table. create database cmpdb with log; create table tab1 (col1 int, col2 int); select * from systables where tabname = "tab1";tabname tab1 owner mpruet partnum 1049089 tabid 100 rowsize 8 ncols 2 nindexes 0 nrows 0.00 created 04/11/2016 version 6553601 tabtype T locklevel P npused 0.00 fextsize 16 nextsize 16 Now I need the partnum for systables.... select * from systables where tabname = "systables";tabname systables owner informix partnum 1049024 (hex 0x1001c0) tabid 1 rowsize 500 ncols 26 nindexes 2 nrows 65.00000000000 Now all I need to to is to track the changes made to systables after the table has been dropped since dropping the table would have updated systables... drop table tab1; Now executing onlog .... 44695890 5 U-B---- 5 1:30043 5000 5000 100.00 446958d8 6 U-B---- 6 1:35043 5000 1837 36.74 44695920 7 U---C-L 7 1:40043 5000 1636 32.72 44695968 8 A------ 0 1:45043 5000 0 0.00 446959b0 9 A------ 0 1:50043 5000 0 0.00 onlog -l -n 7 > /tmp/t1 Using an editor, we find the insert into systables (when the table was created)... addr len type xid id link 65f3d0 188 HINSERT 15 0 65f390 1001c0 a0c 122 bc000000 00002800 12010000 00000000 ......(. ........ 00000000 00000000 0f000000 90f36500 ........ ......e. 5e2d0700 c0011000 c0011000 0c0a0000 ^-...... ........ 7a000004 00000000 00000000 80000000 z....... ........ 04746162 316d7072 75657420 20202020 .tab1mpr uet 20202020 20202020 20202020 20202020 20202020 20001002 01000000 64000800 ... ....d... 02000000 00000000 00000000 00a5e600 ........ ........ 64000154 50000000 00000000 00000000 d..TP... ........ 10000000 10000001 00010000 00000000 ........ ........ 00000000 00080000 00000000 00000000 ........ ........ 00000000 00000080 00412b00 ........ .A+. And then look for the HDELETE.... 662018 52 BEGIN 15 7 0 04/11/2016 22:29:45 38 mpruet 34000000 07000100 00000000 00000000 4....... ........ 00000000 00000000 0f000000 00000000 ........ ........ 952d0700 00000000 a96b0c57 f4010000 .-...... .k.W.... 26000000 &... 66204c 188 HDELETE 15 0 662018 1001c0 a0c 122 bc000000 00002900 12010000 00000000 ......). ........ 00000000 00000000 0f000000 18206600 ........ ..... f. 932d0700 c0011000 c0011000 0c0a0000 .-...... ........ 7a000000 00000000 00000000 80000064 z....... .......d 04746162 316d7072 75657420 20202020 .tab1mpr uet 20202020 20202020 20202020 20202020 20202020 20001002 01000000 64000800 ... ....d... 02000000 00000000 00000000 00a5e600 ........ ........ 64000154 50000000 00000000 00000000 d..TP... ........ 10000000 10000001 00010000 00000000 ........ ........ 00000000 00080000 00000000 00000000 ........ ........ 00000000 00000080 00410000 ........ .A.. We see the transaction index ID is 15 and the begin work log record (BEGIN) is immediately before the HDELETE log record from systables. (HDELETE is home row delete...) And that indicates that user mpruet issued the transaction which executed the "drop table tmp"... Madison Pruet Retired and Loving it On Monday, April 11, 2016 7:44 PM, LEY PATRICK <patrickley@gmail.com> wrote: PLATFORM : AIX INFORMIX VERSION : 11.70FC7 Hi all, we have a database with unbuffered mode and recently we found that there is a missing object. We want to find out who drop that particular object, given the fact that there is not much activities on the box and also all logical log is still available. Trying onlog -lu <username> where <username> are those that has the DBA/RESOURCE right. Question(s) : 1) How to identified the header for DROP TABLE command from the logical log's output ? I do see example like CKPOINT, HUPBEF, HDELETE, UNIQ8ID and etc. 2) the output of the logical log is on HEX format, any tools to convert it to human readable format ? Thanks ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Not sure you can get everything you are looking for from the logical logs, but here is a product that can display their content in a human readable form: http://www.lintel.co.uk/infotrace/index.php Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Apr 11, 2016 at 8:44 PM, LEY PATRICK <patrickley@gmail.com> wrote: > PLATFORM : AIX > INFORMIX VERSION : 11.70FC7 > > Hi all, we have a database with unbuffered mode and recently we found that > there is a missing object. > > We want to find out who drop that particular object, given the fact that > there > is not much activities on the box and also all logical log is still > available. > > Trying onlog -lu <username> > > where <username> are those that has the DBA/RESOURCE right. > > Question(s) : > 1) How to identified the header for DROP TABLE command from the logical > log's > output ? I do see example like CKPOINT, HUPBEF, HDELETE, UNIQ8ID and etc. > > 2) the output of the logical log is on HEX format, any tools to convert it > to > human readable format ? > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0139fddc57acd80530472baa

For the future: Use auditing... Much simpler than this and won't overload the database (if you set it carefully and avoid the row level operations). If you had auditing setup you could answer your question in a matter of seconds. This is what you need to audit drop tables: castelo@primary:informix-> onaudit -l 1 -p /usr/informix/logs -s 10000000 Onaudit -- Audit Subsystem Configuration Utility castelo@primary:informix-> onaudit -m -u _default -e DRTB Onaudit -- Audit Subsystem Configuration Utility castelo@primary:informix-> castelo@primary:informix-> cat /usr/informix/logs/castelo.0 ONLN|2016-04-12 12:24:14.531|primary|26322|castelo|informix|0:DRTB:stores:819:test_drop:informix :0:8389593 Simple isn't it? And it's free... Can't really think of a reason not to use it... except that you need to: 1- Consider the number of times the operation you're auditing will happen. If you drop ten's of tables for second you may want to reconsider it. 2- You need to manage your auditing logs But assuming you don't, the DROP TABLE will translate into a "HDELETE" on the systables partition... and a few more things. The logical logs are not meant for auditing. They're meant to allow recovery. So they're not friendly and they don't contain the statements. This is an example. My table was called test_drop. The HEX(partnum) for it is "800002". Good luck. e755fc 56 BEGIN 21 597 0 04/12/2016 12:24:13 301 informix 38000000 55020100 00000000 00000000 8...U... ........ 00000000 00000000 15000000 00000000 ........ ........ e2c01c0f 00000000 cdcc0c57 00000000 ........ ...W.... ea030000 2d010000 ....-... e75634 192 HDELETE 21 0 e755fc 800002 1603 127 c0000000 00002900 12010000 00000000 ......). ........ 00000000 00000000 15000000 fc55e700 ........ .....U.. e2c01c0f 02008000 02008000 03160000 ........ ........ 7f000000 00000000 00000000 80000333 ........ .......3 09746573 745f6472 6f70696e 666f726d .test_dr opinform 69782020 20202020 20202020 20202020 ix 20202020 20202020 20200080 03d90000 ...... 03330004 00010000 00000000 00000000 .3...... ........ 0000a5e7 03330001 54520000 00000000 .....3.. TR...... 00000000 00100000 00100000 01000100 ........ ........ 00000000 00000000 00000800 00000000 ........ ........ 00000000 00000000 00000000 80004174 ........ ......At addr len type xid id link e756f4 104 DELITEM 21 0 e75634 800002 800002 1603 19 1 42 68000000 00001d00 10000000 00000000 h....... ........ 00000000 00000000 15000000 3456e700 ........ ....4V.. e2c01c0f 02008000 02008000 02008000 ........ ........ 03160000 13000000 01002a00 09746573 ........ ..*..tes 745f6472 6f70696e 666f726d 69782020 t_dropin formix 20202020 20202020 20202020 20202020 20202020 20202020 e7575c 64 DELITEM 21 0 e756f4 800002 800002 1603 2 2 4 40000000 00001d00 10000000 00000000 @....... ........ 00000000 00000000 15000000 f456e700 ........ .....V.. e3c01c0f 02008000 02008000 02008000 ........ ........ 03160000 02000000 02000400 80000333 ........ .......3 e7579c 100 HDELETE 21 0 e7575c 800003 181e 33 64000000 00002900 12010000 00000000 d.....). ........ 00000000 00000000 15000000 5c57e700 ........ ....\\\\W.. e5c01c0f 03008000 03008000 1e180000 ........ ........ 21000000 00000000 00000000 80000333 !....... .......3 04636f6c 31000003 33000100 02000480 .col1... 3....... 00000080 00000000 00000000 00000000 ........ ........ 00735f70 .s_p addr len type xid id link e7601c 68 DELITEM 21 0 e7579c 800003 800003 181e 30 1 6 44000000 00001d00 10000000 00000000 D....... ........ 00000000 00000000 15000000 9c57e700 ........ .....W.. e7c01c0f 03008000 03008000 03008000 ........ ........ 1e180000 1e000000 01000600 80000333 ........ .......3 80016472 ..dr e76060 64 DELITEM 21 0 e7601c 800003 800003 181e 18 2 4 40000000 00001d00 10000000 00000000 @....... ........ 00000000 00000000 15000000 1c60e700 ........ .....`.. e8c01c0f 03008000 03008000 03008000 ........ ........ 1e180000 12000000 02000400 80000000 ........ ........ e760a0 144 HDELETE 21 0 e76060 800005 e0d 77 90000000 00002900 12010000 00000000 ......). ........ 00000000 00000000 15000000 6060e700 ........ ....``.. eac01c0f 05008000 05008000 0d0e0000 ........ ........ 4d000000 00000000 00000000 80000000 M....... ........ 696e666f 726d6978 20202020 20202020 informix 20202020 20202020 20202020 20202020 7075626c 69632020 20202020 20202020 public 20202020 20202020 20202020 20202020 00000333 73752d69 64782d2d 2d000100 ...3su-i dx---... addr len type xid id link e76130 128 DELITEM 21 0 e760a0 800005 800005 e0d 15 1 68 80000000 00001d00 10000000 00000000 ........ ........ 00000000 00000000 15000000 a060e700 ........ .....`.. eac01c0f 05008000 05008000 05008000 ........ ........ 0d0e0000 0f000000 01004400 80000333 ........ ..D....3 696e666f 726d6978 20202020 20202020 informix 20202020 20202020 20202020 20202020 7075626c 69632020 20202020 20202020 public 20202020 20202020 20202020 20202020 e761b0 96 DELITEM 21 0 e76130 800005 800005 e0d 13 2 36 60000000 00001d00 10000000 00000000 `....... ........ 00000000 00000000 15000000 3061e700 ........ ....0a.. ebc01c0f 05008000 05008000 05008000 ........ ........ 0d0e0000 0d000000 02002400 80000333 ........ ..$....3 7075626c 69632020 20202020 20202020 public 20202020 20202020 20202020 20202020 e76210 44 PERASE 21 0 e761b0 8003d9 2c000000 00003700 10000000 00000000 ,.....7. ........ 00000000 00000000 15000000 b061e700 ........ .....a.. ecc01c0f d9038000 d9038000 ........ .... addr len type xid id link e7623c 56 BEGCOM 21 0 e76210 38000000 00000400 10000000 00000000 8....... ........ 00000000 00000000 15000000 1062e700 ........ .....b.. ecc01c0f 00000000 cdcc0c57 05008000 ........ ...W.... 0d0e0000 0d000000 ........ e76274 44 ERASE 21 0 e7623c 8003d9 2c000000 00000d00 10000000 00000000 ,....... ........ 00000000 00000000 15000000 3c62e700 ........ ....<b.. ecc01c0f d9038000 d9038000 ........ .... e762a0 56 COMMIT 21 0 e76274 04/12/2016 12:24:13 38000000 00000200 10000000 00000000 8....... ........ 00000000 00000000 15000000 7462e700 ........ ....tb.. eec01c0f 00000000 cdcc0c57 05008000 ........ ...W.... cdcc0c57 00000000 ...W.... On Tue, Apr 12, 2016 at 1:44 AM, LEY PATRICK <patrickley@gmail.com> wrote: > PLATFORM : AIX > INFORMIX VERSION : 11.70FC7 > > Hi all, we have a database with unbuffered mode and recently we found that > there is a missing object. > > We want to find out who drop that particular object, given the fact that > there > is not much activities on the box and also all logical log is still > available. > > Trying onlog -lu <username> > > where <username> are those that has the DBA/RESOURCE right. > > Question(s) : > 1)