5 Replies Latest reply on Mar 20, 2013 2:29 PM by Chrisjenkins-Oracle

    How to log delete queries

      hello team,

      How I can log any delete query into log.

      I want audit but for only delete queries.

      Paata Lominadze.
        • 1. Re: How to log delete queries
          Take a look at the Transaction Log API (XLA) i believe this will do what you want.

          In particular take a look at the demo

          You would simply inspect the record header and only print out (log to file) the deletes.

          If you're using C/C++ then I would recommend using ttClasses

          Or if you are in the Java world there is a JMS/XLA interface

          • 2. Re: How to log delete queries

            Thanks for your answer, it was very helpful. We have created a simple XLA application based on xlaSimple.c and for every xla generates SQL using ttXlaGenerateSQL() function. Unfortunately the result is not suitable for our needs. Ideally, we would like to retrieve the actual SQL statement which triggered the update. If it's not possible to get the actual SQL statement, maybe it's possible to retrieve a process id which triggered an update or any other information which can help us to track an application which caused an update.

            Below you can find some information about our table, original query and the result from ttXlaGenerateSQL()
            Command> describe SC.TBL;
            Table SC.TBL:
               *GROUPID                         TT_INTEGER NOT NULL
               *ITEM                            TT_VARCHAR (32) INLINE NOT NULL
               *STARTDATE                       TT_BIGINT NOT NULL
                ENDDATE                         TT_BIGINT NOT NULL
            1 table found.
            (primary key columns are indicated with *)
            Command> select * from SC.TBL where groupid = 100100;
            < 100100, 790500010, 1358487630745, 2524593600000 >
            1 row found.
            Command> delete from SC.TBL where groupid = 100100;
            query generated by ttXlaGenerateSQL()
            DELETE from "SC"."TBL" WHERE "GROUPID"=100100 and "ITEM"='790500010' and "STARTDATE"=1358487630745
            Thanks in advance for any support. It's very much appreciated.
            • 3. Re: How to log delete queries

              XLA is not intended as an 'auditing' solution. it;s main purpose is to allow applications to 'mine' the TimesTen transaction logs and react to data changes or DDL. Tim suggested it may be useful as we do have some customers who have used it to implement rudimentary auditing.

              TimesTen does not currently have any audit capability. The information you are seeking (actual SQL statement, process id, user, timestamp etc.) is not currently logged anywhere and thus it is not possible to obtain any historical record of this info from within TimesTen.


              • 4. Re: How to log delete queries

                Thanks, It's clear. Meanwhile I have found out that I can use connID to narrow down the search. BTW, sorry for an offtopic here, but can you point me to C API functions or SYS table name which can be used to get connection information by connID (how did they code ttStatus?:))

                Thanks for your time.
                • 5. Re: How to log delete queries
                  Check out the two builtin procedures ttDataStoreStatus and ttContext in the TimesTen Database Reference Guide. They might be helpful.