How to setup DBQL (database query logging) in Teradata?
Follow below steps to enable query logging in your Teradata environment:
1) Connect to teradata using BTEQ (Please note some commands will only run in Bteq for this setup)
2) Run below command to verify if DBQL is already enabled or not
show query logging on all;
If it not enabled then you will see something like :
Rule UserName “ALL”
Rule UserId 00000000
Account (Rule for any Account)
NO RULE FOUND
You can also run below query to see if any DBQL rule exists
SELECT * FROM DBC.DBQLRulesV;
3) Now, before you enable DBQL you have to grant below permissions to your admin account that you have created (like sysdba) to do admin work. This is needed so that sysdba can flush the query logging data manually at step 6 and/or in future, if needed.
grant execute on DBC.DBQLAccessMacro to sysdba;
4) TO begin query logging just run below command. Modify it according to your requirements.
begin query logging with objects, sql, usecount, utilityinfo LIMIT SQLTEXT=0 on all;
begin query logging with objects, sql limit threshold = 5 elapsedsec and sqltext=0 on VIEWPOINT;
THis command will only run from BTEQ and not from any other client tools.
5) Run “SELECT * FROM DBC.DBQLRulesV;” and you will see this rule now exists
Alternatively run “show query logging on all;” to verify the querylogging is enabled for all users.
6) Run below command to flush query logging data from memory to table (Just one time to do a quick test if its working or not). TD does this automatically every 10 minutes.
FLUSH QUERY LOGGING with ALLDBQL;
7)Run below sql and you will see data is getting populated
select top 10 * from DBC.QryLog order by starttime desc;