We're using Teradata 14.10 and 13.10.
I'm trying to determine which DBC tables and their "historical" counterparts can be queried to tell me how often particular databases, tables, and columns are used in our SQL statements. The purpose is to help us determine how we can improve our primary indexes. I think it looks like I should be able to get the information from the various QryLog views, but I'm not certain. If that's the right place to go, what are their "historical" counterparts?
The two view you would probably be interested in are QryLog and QryLogObjects. You will need to join on QueryID and ProcID. I'm not sure if you should join on collect timestamp... I do not know if they are the same for a single query.
A couple notes:
Do you have PDCR enabled for your system. if yes, there will be "historical counterparts" for those in the DBC tables. Usually there will be a cron which flushes out the data from DBC to PDCR on daily basis. Also there will be aretnetion period applied for the data in the PDCRDaTA database which depends how it is configured.
I will look at this info. I believe that I saw some information the other day about QryLogObjects. History is turned on in our 13.10 environments, but it is still turned off in our recently updated environments...I have no control over that. I'll see what I can get from the two Qry tables above.
Thanks for the info,