Hi,
Does anyone know a method for automating the deletion of logs for a specific job.
I have a job that I want to repeat a regular intervals ( every 10 - 15 mins ) , but I don’t need the logs for more than a day. Other logs on the job server I would like to keep for about 60 days.
It is a boring task to keep going into the console and deleting!
Thanks
pxbaker 
(BOB member since 2007-01-09)
Just make a Script file to do this job and call it from the exec() via the cmd.exe in it
ganeshxp 
(BOB member since 2008-07-17)
You can store the log files for a job run within the logic of the job into a table. DI has builtin functions that return that information (get_trace_filename, get_error_filename and get_monitor_filename).
And then, using scripts as ganeshxp suggested, you can make another job with a logic to delete the files you doesn’t want anymore.
In fact, I would be nice is we can implement something like that 
Hope this helps,
Regards
Andrés
aidelia 
(BOB member since 2006-02-02)
a command line option from al_engine would be good, but currently its not there, what is the version of DI or DS, deleting the log files from the job server is not sufficient there is lot more information that is stored in repository for job execution which need to be deleted too, I can send you the SQL, you can implement that in a batch job
for manually deleting the history, that can be done from the Designer from the logs tab, but there is bug in that also which is not cleaning up all the tables, that is fixed in 12.2.1.1
manoj_d (BOB member since 2009-01-02)
You are correct. I forgot the repository statistics tables.
Could you post the extra SQL code?
Thanks,
Andrés
PS: BTW, I thing the Designer feature is not suitable for most of the production enviroments I know
aidelia (BOB member since 2006-02-02)
delete from AL_STATISTICS WHERE OBJECT_KEY = HistoryKey
AL_AUDIT* in case Audit is enabled for DF
delete from AL_AUDIT_INFO WHERE AUDIT_KEY IN ( select OBJECT_KEY FROM AL_AUDIT WHERE HISTORY_KEY = HistoryKey)
delete from AL_AUDIT WHERE HISTORY_KEY = HistoryKey
AL_QD* in case you are collecting validation data
DELETE FROM AL_QD_STATS WHERE HISTORY_KEY = HistoryKey
DELETE FROM AL_QD_ROW WHERE HISTORY_KEY = HistoryKey
DELETE FROM AL_QD_ROW_DATA WHERE HISTORY_KEY = HistoryKey
DELETE FROM AL_QD_VRULE_OFLOW WHERE PARENT_OBJID IN (SELECT QD_VRULE_KEY FROM AL_QD_STATS WHERE HISTORY_KEY = historyKey)
DELETE FROM AL_QD_VRULE WHERE OBJECT_KEY IN (SELECT QD_VRULE_KEY FROM AL_QD_STATS WHERE HISTORY_KEY = historyKey)
DELETE FROM AL_QD_COLINFO WHERE KEY1 IN
(SELECT COL_KEY1 FROM AL_QD_ROW_DATA WHERE HISTORY_KEY = historyKey) AND
KEY2 IN (SELECT COL_KEY2 FROM AL_QD_ROW_DATA WHERE HISTORY_KEY = historyKey)
delete from AL_HISTORY_INFO WHERE OBJECT_KEY = HistoryKey
delete from AL_HISTORY WHERE OBJECT_KEY = HistoryKey
manoj_d (BOB member since 2009-01-02)
Thanks,
Lots of suggestions to go at, but has to be something that handles both the log files, and the repo tables.
Would be a great to have implemented as a web service call, as you already have calls for managing jobs and returning status etc in 12.1.1.2.
pxbaker (BOB member since 2007-01-09)
Great script.
I assume that HistoryKey is the OBJECT_KEY of the JOB to be deleted.
I didn’t found a better way in DI (11.7) to get that value than:
SELECT OBJECT_KEY
FROM AL_HISTORY_INFO
WHERE NAME = ‘TRACE_LOG_INFO’ AND
VALUE = {$TRACE_FILE}
(could be either monitor, error or trace file got from the DI functions)
Do you have a better way to get it?
Regards,
Andrés
aidelia (BOB member since 2006-02-02)
yes HistoryKey is the OBJECT_KEY column of AL_HISTORY table of the job execution history that you want to delete
it depends how much history you want to delete, the SERVICE column in AL_HISTORY stores the JobName, you can delete everything keep only the last execution or last ‘n’ execution make use of SERVICE, START_TIME and END_TIME column to filter records
manoj_d (BOB member since 2009-01-02)