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 ,
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.
I tried doing some thing below but no result.
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.