All Forums Database
rshivha 3 posts Joined 03/16
26 Apr 2016
DBQL data

Hi Guys,
we are planning to decommision the tables which are nt much in use for that we are checking table name and its access in dbqlhist table for its log  for last 1 year data bt below query is taking huge time  (50 min) as anyhow i have to use like statement ,

any solution where i can get get object name from any other dbc tables? so that i can join?

SELECT

 

     UserID,

 

     UserName,

 

     DefaultDatabase,

 

   

     LogonDateTime,

 

     LogonSource,

 

     AppID,

 

     ClientID,

 

     ClientAddr,

 

     StartTime,

 

     QueryText,

 

     EstResultRows

 

     FROM dbql_hist.dbqlogtbl_hst

 

 

WHERE QueryText LIKE '%xyz%' AND CAST(logondatetime AS DATE) BETWEEN  '2015-04-20' AND  '2016-04-18'

 

thanks in advance

rshivha 3 posts Joined 03/16
26 Apr 2016

I tried using this 
WHERE QueryText LIKE '%AGGSHCSTRDLYADJSLSPRODSUMM%' AND logondatetime > TIMESTAMP '2015-04-19 00:00:00' 

but nt much help.

StevenSchmid 33 posts Joined 07/11
26 Apr 2016

Hi
The DBQL history tables through PDCR have a PPI on LOGDATE, so if you use LOGDATE in your where clause you will only access the partitions for the year in question.
In regards to tables not used often, you can check LASTACCESS and LASTACCESSTIMESTAMP columns in DBC.TABLES as well.  If you have object logging enabled, then you can check the DBQLOBJTBL_HST table as well.
Cheers
Steven

Steven Schmid
Teradata DBA
Canberra, Australia

rshivha 3 posts Joined 03/16
27 Apr 2016

HI Steven,
I tried doing some thing below but no result.

SELECT

 

    a.UserID,

 

    a. UserName,

 

    a. DefaultDatabase,

 

   a. AcctString,

 

    a.ExpandAcctString

 

        FROM dbql_hist.dbqlogtbl_hst a  JOIN DBQL_HIST.dbqlobjtbl_hst b

     ON a.queryid=b.queryid

      WHERE b.ObjectTablename='XYZ'    

     AND a.logdate  BETWEEN  '2015-04-20' AND  '2016-04-18'  

     AND a.queryid=b.queryid

StevenSchmid 33 posts Joined 07/11
27 Apr 2016

Hi
Most likely you don't have object logging enabled (no Object data), you could try an outer join to confirm, or try your original query with the logdate where clause in place of your logondatetime clause.  The query should run faster than the original time as only accessing the partitions for that year.
Cheers
Steven

Steven Schmid
Teradata DBA
Canberra, Australia

You must sign in to leave a comment.