Identifying the Target of an IUD (insert/update/delete) Operation

Database
Enthusiast

Identifying the Target of an IUD (insert/update/delete) Operation

We have a need to identify the target table/object of on insert/update/delete operation... ideally through DBQL where it can be tied to querybands.  The closest (potential) field that may have been able to identify this the QryObj.TypeOfUse field.

Unfortunately, that stops at "accessed" without discerning IUD.

The relatively new ObjectUsage table (of which I'm not entirely familiar) would hold some value, but from what I understand, it is loaded from a queue meaning it is not immediately available, and it only holds the last action on the object, which means I cannot historically go back to a given querybanded query and identify if the SQL associated with the queryband was involved in an IUD operation on a given table.

I really hope I'm missing an obvious solution here, so any help is appreciated.

Tags (1)
4 REPLIES
Senior Supporter

Re: Identifying the Target of an IUD (insert/update/delete) Operation

did you consider to parse the SQL itself?

Could be a good usecase for a python script function.

On which release are you?

Enthusiast

Re: Identifying the Target of an IUD (insert/update/delete) Operation

Release 14.10.  I am working on parsing, and have a general direction.  But, REGEXP functions have been pretty flaky.  One of the first steps (in my eyes) is to remove comments.  Removal of "/* ... */" is working very well, but removing "-- ... thru '0A'XC" is returning random results.  While I would like to do this "in-database", it is looking like I may need some external function to do this.

Teradata Employee

Re: Identifying the Target of an IUD (insert/update/delete) Operation

Have you considered using the EXPLAIN command?

You could retrieve the SQL request text from DBQL, prepend an EXPLAIN keyword, and then execute the resulting SQL command.

For example, given the following table:

create table tomtab1 (c1 integer, c2 integer)

The following explain commands produce the following output, in which it's somewhat easier to identify the operation (INSERT/UPDATE/DELETE) and the target table.

explain insert into tomtab1 values (1, 2)

1) First, we do an INSERT into GUEST.tomtab1.  The estimated time for"

   this step is 0.04 seconds."

-> No rows are returned to the user as the result of statement 1."

   The total estimated time is 0.04 seconds."

explain update tomtab1 set c2=3 where c1=1

1) First, we do a single-AMP UPDATE from GUEST.tomtab1 by way of the"

   primary index "GUEST.tomtab1.c1 = 1" with no residual conditions."

   The size is estimated with low confidence to be 2 rows.  The"

   estimated time for this step is 0.36 seconds."

-> No rows are returned to the user as the result of statement 1."

   The total estimated time is 0.36 seconds."

explain delete from tomtab1 where c1=1

1) First, we do a single-AMP DELETE from GUEST.tomtab1 by way of the"

   primary index "GUEST.tomtab1.c1 = 1" with no residual conditions."

   The size is estimated with low confidence to be 2 rows.  The"

   estimated time for this step is 0.01 seconds."

-> No rows are returned to the user as the result of statement 1."

   The total estimated time is 0.01 seconds."

Enthusiast

Re: Identifying the Target of an IUD (insert/update/delete) Operation

I have Tom, and that isn't off the table. Our need is particular, basically to audit entire systems of ETL SQL (thousands of queries a day). Taking the data off of Teradata and parsing, is an option.  I'm getting warm on doing it on Teradata with SQL.  Maybe to the extent that I will have few exceptions.  

Who would have thought it would be so complex :-).  The variations DEL=DELETE; INS=INSERT; comments can be all over.  Teradata knows what it's doing, I guess is isn't logged anywhere.