How to find databasename from DBQL SQLTEXTINFO

Database
Enthusiast

How to find databasename from DBQL SQLTEXTINFO

Hi

I am using DBQLOGTBL and DBQLSQLTBL to check sql query performance.

However in many queries the databasename is not mentioned. May be because default database is set in previous query so subsequent queries have only table names and no databasename mentioned in it.

Is there a way to find out which all databases were used for any query present in SQLTEXTINFO columns ? I have checked defaultdatabase column too however that is not giving the desired result.

 

TIA.

Nitin 


Accepted Solutions
Enthusiast

Re: How to find databasename from DBQL SQLTEXTINFO

Got the answer

SyntaxEditor Code Snippet

 DBC.DBQLOBJTBL 

 

1 ACCEPTED SOLUTION
7 REPLIES
Enthusiast

Re: How to find databasename from DBQL SQLTEXTINFO

Got the answer

SyntaxEditor Code Snippet

 DBC.DBQLOBJTBL 

 

rjg
Supporter

Re: How to find databasename from DBQL SQLTEXTINFO

TD_Raj,

Joining to dbqlobjtbl would be ideal since the database will be logged even if it is a default database for that query.

Of course object logging would need to be enabled.

 

Rglass

Re: How to find databasename from DBQL SQLTEXTINFO

Hi, If the DBQLObjTbl logging is not activated then is there a work around to know the list of objects (tables, columns) accessed by a user?

Teradata Employee

Re: How to find databasename from DBQL SQLTEXTINFO

Getting the default database name is easy because it it stored in the dbqlogtbl: DefaultDatabase 

 

Finding all the tables and coluns referenced would require a resoruce intensive matching of sql to column and view data like we tried to do in the old days.

 

It was very hit or miss and the reason for the existence of the dbqlobjtbl :)

 

Just get that activated. There is no reason performance wise not to and tons of reasons to have it.

 

Thanks

 

Dave

Re: How to find databasename from DBQL SQLTEXTINFO

Hi Dave,

Thanks, I was using a R library to parse the SQLTEXT and it is time consuming and lots of special characters are there to handle it. Other way the output produces next level of insights.

To keep it simple, I have requested our DBA to enable logging for DBQLOBJTBL.

I am not sure whether someone has done analysis on SQLTEXT or QUERYTEXT parsing to get patterns of SQLs. It will be really an interesting topic.

Cheers,

Vijayaraj S

Senior Apprentice

Re: How to find databasename from DBQL SQLTEXTINFO

If the object table was not enabled because it contains too much details & tends to grow very fast, there's a new option in TD15.10, NO COLUMN OBJECTS, not logging column level information.

Enthusiast

Re: How to find databasename from DBQL SQLTEXTINFO

Only challenge I am facing now is the information does not differentiate between Source and Target.

I mean if I have to identify the Target Database/Table and Source Database/Table then I cannot find any flag to identify it.

In DBQLOBJTBL it is saving all the details in similar manner.

It would be great to identify Target DBNAME and Source DBNAME.