Macro Metadata Log Table

Database
Enthusiast

Macro Metadata Log Table

Hi All,

I want to create a log table which will keep metadata for macro. such as who execute, when exec, success flag, count(*) exc. 

Does anybody can give me a trick for that. thank you in advanced.

10 REPLIES
Teradata Employee

Re: Macro Metadata Log Table

If you enable query logging, DBQL will contain most of what you asked for, such as "who execute, when exec, success flag". I'm not sure what you mean by "count(*)", but if you are referring to the number of result set rows, DBQL will contain that also.

So there is no need to reinvent the wheel. There is no need to create your own log table. Use DBQL.

Enthusiast

Re: Macro Metadata Log Table

Hi,

You ca get most of the info from DBQLogTbl,DBQLObjTbl,DBQLStepTbl and some other DBQL tables if you need extra. Now-a-days, even ETL meta tables design have become easier. ETL framework, DQ framework design have become easier these days with new releases in TD. Of course, if you need extra information, then you can expand your meta tables.

Cheers,

Raja

Enthusiast

Re: Macro Metadata Log Table

Thank you both Tomnolan and Raja, I'll try to use that tables you've given me. 

Enthusiast

Re: Macro Metadata Log Table

Hi again,

by the way I used TD 13.0 . I've check the table  DBC.DBQLObjTbl where objecttype = 'Mac'  ,

but I saw this table keeps logs up to one week before. How can I find the all past log, or do I?

Teradata Employee

Re: Macro Metadata Log Table

DBQL tables fill up rapidly and can grow quite large, so most customers have automated procedures in place to archive and delete old records from DBQL tables.

You would have to work with the person in your organization who is responsible for the archived DBQL information.

Enthusiast

Re: Macro Metadata Log Table

Hi,

Maybe you can just filter out what you want in another table. 

By the way, if you can share the objective and ideas or the reason why you are doing this(only macro maybe), then others can give or share  better suggestions and ideas. The more elaborative the problem statement , the faster the solution comes.

Cheers,

Raja

Enthusiast

Re: Macro Metadata Log Table

we've so many tables and everyday we upsert this tables with their own macros and keeping history. One macro can sometimes be run twice or more . sometime later, when I check the data I can see there're duplicate records or wrong history or many other problem in those tables. so what I try to do is how many times in a day macro is executed for a table. I can check this log table every day and see which macros are executed more than once and instantly I can take action to make data consistent.

Enthusiast

Re: Macro Metadata Log Table

Ok thanks and now that you have seen, you can  implement the way according to your convenience.

Cheers,

Raja

Enthusiast

Re: Macro Metadata Log Table

Hi All

 

I cannot find any details of queries executed as part of MACRO in DBQL tables.

All I can find is exec macro however no details of queries executed inside macro is present.

 

Is there a way to find query level information present inside MACRO in teradata ?

 

TIA

Nitin