Enabling and Disabling query logging in Teradata

Database query logging (DBQL) is an optional feature in Teradata through which we can track processing behavior of database by logging user activity. User activity can be logged in various level namely object, sql text, steps, explain etc. one can also set limit to the length of query text to be logged.

since this is resource consuming task that database has to perform on each activity, on should assess impact on database performance before enabling it in various level.

Query logging can be enabled on user using its account name. Every user created in database has its account name (account string) assigned while user creation, though account string can be assigned to a user or group of user manually.

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’

The DBQL can be enable for 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 detail option for query logging is available namely step logging, xml explain, summery logging.  The text size of sql text can be limit 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')

Query logging can be disable with end query logging command as show below.

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

DBQL can be enabled for all users irrespective of their account string by below command

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

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

select * from dbc.dbqlrules