Need alternative to DBQL processing

Database
Enthusiast

Need alternative to DBQL processing

Hello Friends,

I have to identify tables that are/were not accessed by any user since the past 6 months or so. The access could be select, update, delete, insert, show or help. If no-one has run any kind of such operation on a table, we want that table name.

I know that we could use DBQL -Qrylog, SQLlog, Objtables list. But, the queries are performing really slow. It is because we would put a "less than (<)" condition on "query_date" or "collect-Timestamp" columns. If I use "=" certain date, we get the output within 30 seconds. So, I am kind of struck to see if there are any other alternatives.

I tried the lastaccesstimestamp from dbc.tables which is not a best method, but, it gives at least some useless tables. 

I am checking to see if you guys know of any better alternatives. The DBQL tables contain  6 months of history information.

I will definitely post something if I found a better alternative.

Thanks,

Ss

6 REPLIES
Enthusiast

Re: Need alternative to DBQL processing

Hi Ss,

For now, I am just thinking that maybe you can break up into smaller chunk, porting to other tables only what you want or maybe partition. After that you can play anyway you like.

Cheers,

Raja

Enthusiast

Re: Need alternative to DBQL processing

Thanks Raja. I will try that approach.

Enthusiast

Re: Need alternative to DBQL processing

You are welcome. This is for temporary solution.You may need to think of long term solutions.

Good luck,

Raja

Enthusiast

Re: Need alternative to DBQL processing

Hi,

It reminds me of the posts now. Have you enquired about  PMCP or PDCR if it suits your requirement?

http://forums.teradata.com/forum/database/backuprecovery-of-dbql

http://forums.teradata.com/forum/database/where-would-i-find-information-about-pdcr

Cheers,

Raja

Re: Need alternative to DBQL processing

Is there any way to determine, from either PDCR or DBQL how many bytes of data were exported via FASTEXP? 

Thanks!

Senior Apprentice

Re: Need alternative to DBQL processing

Hi Joe,

I never tested this, but when you're able to locate the FExp sessions in DBQL the SpoolUsage of the final step in QryLogStepsV might be what you're looking for.