All Forums Database
nevermore 8 posts Joined 03/13
09 Dec 2013
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.
 

tomnolan 594 posts Joined 01/08
09 Dec 2013

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.

Raja_KT 1246 posts Joined 07/09
09 Dec 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

nevermore 8 posts Joined 03/13
09 Dec 2013

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

nevermore 8 posts Joined 03/13
11 Dec 2013

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?
 
 
 

tomnolan 594 posts Joined 01/08
11 Dec 2013

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.

Raja_KT 1246 posts Joined 07/09
11 Dec 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

nevermore 8 posts Joined 03/13
11 Dec 2013

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.

Raja_KT 1246 posts Joined 07/09
11 Dec 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.