Analyzing DBQL

Database
Teradata Employee

Analyzing DBQL

Dear all,

I am trying to do some analysis on the Query Log, and I am already failing at something that should be absolutely basic: How can I find out, among the objects in DBQLObj, which one was written to in an Update/Insert/Delete statement?

  Failed approach 1: Field TypeOfUse - contains strange information - no help here.

  Failed approach 2: Parse QueryText - apart from being a nightmare to begin with, this field contains line comments but no line breaks - unusable.

  Failed approach 3: Parse ExplainText - often contains aliases only - unusable.

  Failed approach 4: AccLog table (field AccessKind? not documented) - no connection to Query Log.

Bonus question: How can I find out in DBQLObj which table was created in a Create Table statement? Entries contain tables read from in a Create Table From stmt, but not the created table.

Any directions would be welcome! Thanks,

Michael

Tags (1)
1 REPLY
Teradata Employee

Re: Analyzing DBQL

I haven't figured out a simpler way either. If you know some basic coding, you might try parsing XML explain and look at locking information at the begining of explain for more accurate answers. But otherwise this question is in the same league as some other questions you'd think should be easy to answer. For example,

  • How do you figure out activitycount from DBQL if your statement type isn't SELECT and you don't have stepinfo enabled?
  • How do you figure out what views directly dependent on a table or another view?

For the create table, I think that's because PE can't find the new object name in DD (obviously) and hence it doesn't end up in DBQL.

There are many nice tools in Teradata to get details you need, but I haven't found many that answer above questions.