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.
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.
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?
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.
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.
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.
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 ?