DBQL data

Database

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

4 REPLIES

Re: DBQL data

I tried using this 

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

but nt much help.

Re: DBQL data

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

Re: DBQL data

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

Re: DBQL data

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