DBQL related Queries in Teradata

Enabling/Disabling Query Logging for an Account String:

Getting Account String for a User:

Account string of a user can be verified by querying "dbc.accountinfo" view or better edit profile to check account string.

select * from dbc.accountinfo where username =’username’

Enabling DBQL for an Account String:

The DBQL can be enabled for a specific user using their account string by below command:

begin query logging with explain,objects, sql on all account=(' $M$BUSI$S$D$H')

In the above command query logging will be enabled for objects, sql text and explain on all user having account ‘$M$BUSI$S$D$H' . There are few more detailed options for query logging available namely 

• step logging, 
• xml explain, 
• summery logging.

The text size of sql text can be limited by limit clause as below. Here 0 means unlimited whereas any positive integer will limits to its value.

begin query logging with explain,sql limit sqltext=0 on all account=(' $M$BUSI$S$D$H')

Disabling DBQL for an Account String:

Query logging can be disabled with end query logging command as shown below.

end query logging with explain,objects, sql on all account=(' $M$BUSI$S$D$H')

Enabling/Disabling Query Logging for an Account String:

DBQL can be enabled/disabled for all users irrespective of their account string by below commands:

Begin query logging with objects, sql limit sqltext=0 on all;

End query logging with objects, sql limit sqltext=0 on all

Verifying DBQL Rules:

DBQL rules can be verified by querying "dbc.dbqlrules" view.

select * from dbc.dbqlrules

Deleting DBQL Logs:

Finally, these 2 queries are used for deleting DBQL Logs in order to create free space in the database.

Del  dbc.dbqlogtbl 
Del  dbc.DBQLSqlTbl