Finding the real data in a table with information available from PDCRINFO.DBQLOGTBL_HST

Database
Enthusiast

Finding the real data in a table with information available from PDCRINFO.DBQLOGTBL_HST

Hi 

I have a requirement to find the part of the table (the real data) which are been accessed before a given logdate.  A part of the data in a table are not at all accessed lately..

they were accessed long before where we have the queryid to track it.. but want the real data that came as output as part of this old query without triggering the same query again.

 

For example:-

Table A has 100 records where 50 are ACTIVE, 50 are with INACTIVE status and off late, users have been accessing only the ACTIVE records..

The 50 INACTIVE records were not at all queried.  lets say these 50 records were queried 100 days back through SELECT statement.

How to trace back these exact 50 records.

Inputs i have :-

Tablename, logdate, query id (dbqlogtbl_hst)

Hurdles I am facing:-

PDCRINFO.DBQLOGTBL_HST has only query id, querytxt, numresultrows... but NOT THE EXACT DATA..

how to find the connection between the queryid (i have the queryid from 100 days back) and the exact data in a table, how we can narrow down to the real data in the table.  

Condition :- I dont want to re-execute the query and find the data.

Please guide...

 

My approach:-

Got the object id, queryid from PDCRINFO.DBQOBJTBL_HST, PDCRINFO.DBQLOGTBL_HST..

Got the rowhash (primary index) to connect to the table....

but got lost on how to connect between these two.

 

is there any table or query with which we can find the connection between queryid and rowhash(pi)


Accepted Solutions
Teradata Employee

Re: Finding the real data in a table with information available from PDCRINFO.DBQLOGTBL_HST

Not without actually executing the query (or something very close to it).

1 ACCEPTED SOLUTION
4 REPLIES
Teradata Employee

Re: Finding the real data in a table with information available from PDCRINFO.DBQLOGTBL_HST

Not without actually executing the query (or something very close to it).

Enthusiast

Re: Finding the real data in a table with information available from PDCRINFO.DBQLOGTBL_HST

Thanks Fred... :) it would have been great if there is a lookup table logged automatically between (the queryid) and (all the row ids or rowhash aggregated which the query returned) as a logging or tracking mechanism in Teradata..
Junior Contributor

Re: Finding the real data in a table with information available from PDCRINFO.DBQLOGTBL_HST

That's a nice idea, Teradata sales people will like it, when you switch it on you will need muuuch bigger systems to store all that info :-)

Enthusiast

Re: Finding the real data in a table with information available from PDCRINFO.DBQLOGTBL_HST

Ya 😄 well... any other ways to track the portion of a table which was not used for a long time ?