Discussions specific to version 8.1
User avatar
Posts: 14
Joined: Wed Feb 11, 2015 3:57 pm

Track DDL changes

by jagan.kumar » Tue Apr 21, 2015 3:14 pm


Are there any features in Kognitio 8, to track DDL/structural changes as part of auditability requirements? In oracle, using DDL trigger we can track the changes.

Reply with quote Top
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

Re: Track DDL changes

by markc » Wed Apr 22, 2015 8:55 am

You can do something with IPE_COMMAND to extract relevant DDL-modifying queries - for that to work you'd need to ensure you never filled up the disk logging slab (slab 2), as then logging stops and you'd miss potentially relevant entries.

Another approach would be to look in relevant system tables for entries which had been created since a particular transaction number, using the wx_create_tno() function, and current_transaction_number. Something like the following shows you information on tables/views which have been created since a given point in time:

>create table check_ddl_tno(tno int);
Query 6 Complete ---- 0:00.1 0:00.1 0:00.1
>insert into check_ddl_tno select current_transaction_number;
Query 7 1 Row Inserted ---- 0:00.2 0:00.2 0:00.2
>create table test1(a int, b int, c int);
Query 8 Complete ---- 0:00.1 0:00.1 0:00.1
>create view v1 as select a, c from test1;
Query 9 Complete ---- 0:00.1 0:00.1 0:00.1
>create table test2 (a int, b int, c int, d int);
Query 10 Complete ---- 0:00.1 0:00.1 0:00.1
>select * from ipe_alltable where wx_create_tno() > (select max(tno) from check_ddl_tno) order by name;
0|TEST1 | 1096| 0|T|2015-04-22 16:03:13| -1
0|TEST2 | 1098| 0|T|2015-04-22 16:04:02| -1
0|V1 | 1097| 0|V|2015-04-22 16:03:45| -1
Query 11 3 rows ---- 0:00.0 0:00.0 0:00.0

You could enhance this to capture the changes made, rather than just identify new/modified objects. You could also modify it to show e.g. dropped objects, invalidated views, etc. The only thing to watch out for these is that a reclaim which recovers space in slab 1 (the system table slab) can remove historic rows, which means you'd need to run the audit check before doing such a reclaim.

Reply with quote Top

Who is online

Users browsing this forum: No registered users and 1 guest