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
