Do we have any tool/utility to find Teradata usage profile statistics from the Teradata query logs? We want to pursue this activity because it is a good way to take inventory of the ad-hoc business users for KPI/Reporting rationalization.
We are looking to capture following information.
Sum(# of queries executed) - To identify most active business users
avg(lines of SQL code) - To identify most complex users
avg(number of columns retrieved). Excluding Select (*) queries - To identify users potentially extracting data for offline analysis
avg(query return size) - To identify users potentially extracting data for offline analysis
unique queries vs same query) (Score 1-10) - To identify ad-hoc exploratory users vs. cyclical standard report users
Do you have DBQL logging enabled? These all look like pretty simple queries from the dbql tables. it's a pretty direct task to develop some views that would represent these metrics.
Thanks for your response Blaine.
Yes We have DBQL enabled. What are the DBQL tables and what kind of information will the DBQL tables store ?
You can find DBQL information for below link:
Following two tables should answer your queries: