All Forums Database
JimTepin 8 posts Joined 08/11
06 Feb 2015
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.

ulrich 816 posts Joined 09/09
06 Feb 2015

did you consider to parse the SQL itself?
Could be a good usecase for a python script function.
On which release are you?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

JimTepin 8 posts Joined 08/11
09 Feb 2015

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.

tomnolan 594 posts Joined 01/08
09 Feb 2015

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."

JimTepin 8 posts Joined 08/11
09 Feb 2015

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.

You must sign in to leave a comment.