Backup/Recovery of DBQL

Database
Enthusiast

Backup/Recovery of DBQL

I have been working to to create a backup and recovery plan for the DBQL tables from DBC. We are requied to keep several years worth of this data for auditing purposes. I have tried moving the data to tables in another database and then using a fastexport and store the older data on the server to get it off the Teradata system, but I can't seem to get it to load back correctly with the created multiload, specifically some fields using Nullable TimeStamps. I have just begun exploring the ARC utility, but who knows if it will be any different.

I am beginning to wonder if I am reinventing the wheel. Are there existing best practices for this sort of procedure.

Thanks for any help you could provide.
5 REPLIES
Enthusiast

Re: Backup/Recovery of DBQL

Have you considered getting the PMCP package from Teradata ?
Enthusiast

Re: Backup/Recovery of DBQL

Is this product available in the basic install of TTU? A new product is not within the budget. I need to use the resources that are currently available. Are there no best practices for this type of procedure?
Enthusiast

Re: Backup/Recovery of DBQL

PMCP is normally a onetime setup kind of thing which you would have to purchase from teradata (I think it's sort of done by the PS guys)... and it's kind of "the" standard practice these days...

how ever that shouldn't stop you from doing similar stuff on your own...

the practice is to archive all previous days DBQL to another set of history tables (if recollect correctly tables have columns similar to the DBC.qry* views , plus logdate and the tables are partitioned on logdate to make lookups on these tables easier)

Enthusiast

Re: Backup/Recovery of DBQL

hey joe can u please throw some more light into pmcp ?
How data goes frm dbql to pmcp ?

Do u need to schedule some macros and what it is exactly ?
Enthusiast

Re: Backup/Recovery of DBQL

The PMCP package consists of a bunch of DBs and user ids that you create in the system, a bunch of tables that sort of reflects the "log" tables in DBC plus some extras.. views to manipulate these tables, macros to generate various reports from the views, an excel based tool than can pull data via using the macros and shows some nice charts and all goodies (which management folks would ask for ;) ) .... Also a bunch of bteq (actually sql files) and batch files which makes uses of another set of macros to move data from DBC to PMCP databsases.... the later batch files are usually scheduled from Teradata manger.

So as you see there are lot of things in it, which I do admit is quite useful, and sometimes you could even pick up an existing PMCP macro and make modifications to suit your requirements and create a new report using it (I have been partially successfully in the attempts, mostly limited by my microsoft-inabilities)...

And if you are forced to inventing the wheel on your own to save some bucks for the management guys, you would want to download the "Database Query Log (DBQL) Setup and Maintenance Scripts" documentation from the teradata support website which has some example setups, macros and processess mentioned.