Skip to content

Posts tagged ‘DBQL’

Teradata – DBQL DETAIL TABLES did not load

ISSUE:

PDCR job failed with below error:
LD_Error6: The DBQL DETAIL TABLES did not load. SqlState = ‘T2646’, SqlCode = 2646 when executing SQL statement number 106

or

[Teradata Database] [TeraJDBC 15.10.00.09] [Error 2646] [SQLState HY000] No more spool space in PDCRAdmin.

DESCRIPTION:

PDCR job will fail with this error if spool for PDCRADMIN is not big enough. Try to increase the spool for PDCRAdmin user and re-run the job. No other action is needed to fix this issue.

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

OR

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;