RE: Monitoring Sql statments.

> I tried using onlog on an unencrypted column and still could not find > any text > data in the log. I of course may be using onlog completely wrong and > any help in this area would be apreciated. > > Thanks > David Tamburin > david.tamburin@protegrity.com You may be sorry you asked, but here's an example. The test environment is IDS 7.23.UC6 on HP-UX 10.20. I created a test table, with a few indexes, like so: create table "informix".testtable ( test_id integer not null, test_dirn char(11) not null, test_priority char(1) not null, test_reqrtgcode char(1) not null, test_reqtype char(1) not null, test_sortbreak char(1) not null, test_routecode char(1) not null, test_analystid varchar(5) not null, test_awb char(10) not null, test_rcdesc varchar(20) not null, test_imr_loaddt datetime year to second not null, test_source char(1), test_path varchar(30), test_img_filename varchar(8), test_img_vol_id char(3), test_img_offset smallint, test_bi_path varchar(30), test_bi_filename varchar(12), test_splittodpf varchar(30), test_outputted char(1), test_avdevice smallint, test_avslot integer, test_avside char(1), test_avbag char(4), test_avjukeblock integer, test_avtries smallint ); create unique index "informix".testtable_id on "informix".testtable (test_id); create index "informix".testtable_dirn on "informix".testtable (test_dirn); create index "informix".testtable_av on "informix".testtable (test_avdevice, test_avslot, test_avside, test_avjukeblock); create index "informix".testtable_bi_fname on "informix".testtable (test_bi_filename); Next, run 'onstat -l' to check out your logical logs, then run 'onmode -l' to switch to a new log. Now, run an insert like: insert into testtable values (12,'11232245982', '1', '1', 'B', 'C', 'D', 'TEST1', '10CHARCOLM', 'VARCHAR 20 COL', CURRENT, 'A', 'VARCHAR 30 COL', 'FILENAME', 'ABC', 19114, 'VARCHAR 30 COL2', 'VCHAR12COL', 'VARCHAR 30 COL3', 'X', 9901, 1134242523, 'Y', 'EFGH', 167426982, 1215); Then run 'onmode -l' again. Now, you should have just one update in one of your logical log files (assuming that you can get a system with no other update activity). Assuming that your current logical log at the time of the first 'onstat -l' was number 15, then your single transaction should be in logical log 16, and number 17 should be your current log. To see the results, run 'onmode -l -n 16 | more'. You will see something like (I hope this doesn't wrap too badly): INFORMIX-OnLine Logical Log display Software Serial Number XYZ#J123456 Copyright (C) 1987-1996 Informix Software, Inc. log number: 16. addr len type xid id link 18 32 BEGIN 16 16 0 06/21/99 15:52:46 248 informix 00000020 00100100 00000010 00000000 ... .... ........ 001b1254 376ea61e 0000006a 000000f8 ...T7n.. ...j.... 38 188 HINSERT 16 0 18 100083 501 150 000000bc 00002802 00000010 00000018 ......(. ........ 001b1254 00100083 00000501 00960000 ...T.... ........ 00000000 0000000c 31313233 32323435 ........ 11232245 39383231 31424344 05544553 54313130 98211BCD .TEST110 43484152 434f4c4d 0e564152 43484152 CHARCOLM .VARCHAR 20323020 434f4cc7 13630615 0f342e41 20 COL. .c...4.A 0e564152 43484152 20333020 434f4c08 .VARCHAR 30 COL. 46494c45 4e414d45 4142434a aa0f5641 FILENAME ABCJ..VA 52434841 52203330 20434f4c 320a5643 RCHAR 30 COL2.VC 48415231 32434f4c 0f564152 43484152 HAR12COL .VARCHAR 20333020 434f4c33 5826ad43 9b2adb59 30 COL3 X&.C.*.Y 45464748 09fabba6 04bf0000 EFGH.... .... f4 44 ADDITEM 16 0 38 100083 501 1 1 4 0000002c 00001c00 00000010 00000038 ...,.... .......8 001b1256 00100083 00100083 00000501 ...V.... ........ 00000001 00010004 8000000c ........ .... addr len type xid id link 120 52 ADDITEM 16 0 f4 100083 501 2 2 11 00000034 00001c00 00000010 000000f4 ...4.... ........ 001b1258 00100083 00100083 00000501 ...X.... ........ 00000002 0002000b 31313233 32323435 ........ 11232245 39383231 9821 154 52 ADDITEM 16 0 120 100083 501 3 3 11 00000034 00001c00 00000010 00000120 ...4.... ....... 001b125a 00100083 00100083 00000501 ...Z.... ........ 00000003 0003000b a6adc39b 2adb5989 ........ ....*.Y. fabba631 ...1 188 52 ADDITEM 16 0 154 100083 501 4 4 11 00000034 00001c00 00000010 00000154 ...4.... .......T 001b125c 00100083 00100083 00000501 ...\\.... ........ 00000004 0004000b 0a564348 41523132 ........ .VCHAR12 434f4c31 COL1 1bc 28 COMMIT 16 0 188 06/21/99 15:52:46 0000001c 00000200 00000010 00000188 ........ ........ 001b125d 376ea61e 376ea61e ...]7n.. 7n.. Looking at the HINSERT entry beginning at offset 38, look 36 bytes into the data spewed out and you will see 0x0000000c, which is the value 12 for the column test_id, followed by the value '11232245982' for test_dirn, and the rest of the columns. The ADDITEM entries are for the four indexes. BONUS question - which ADDITEM is for which index?. There's a lot of fun stuff in here, if you have a twisted definition of fun. The Internal Architecture class can give you a lot of info on reading disk pages and log entries. I learned enough to know that I don't want to use onlog if I don't have to. Your quick check of encrypted vs. unencrypted data shouldn't be a problem, though. Obligatory onlog warning - DO NOT RUN ONLOG AGAINST AN ACTIVE LOG ON A PRODUCTION SYSTEM. It will lock the log file so that no one can modify it, which really annoys the users. I did this example on an isolated test environment. A safer way is to back up the log to either tape or disk and then run onlog against the backup. Mark Collins mcollins@us.dhl.com No matter how cynical I get, I just can't keep up.