BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

Automate log deletion for a specific job?
1 members found this topic helpful

 
Search this topic... | Search DI: Installation, Administration, and Connectivities... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Installation, Administration, and Connectivities  Previous TopicPrint TopicNext Topic
Author Message
pxbaker
Principal Member
Principal Member



Joined: 09 Jan 2007

Posts: 105


flag
PostPosted: Tue Nov 17, 2009 5:13 am 
Post subject: Automate log deletion for a specific job?

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
Back to top
ganeshxp
Forum Addict
Forum Addict



Joined: 17 Jul 2008
ASUG Icon
Posts: 3700
Location: Chicago, Illinois, USA


flag
PostPosted: Tue Nov 17, 2009 6:12 am 
Post subject: Re: Automate log deletion for a specific job?

Just make a Script file to do this job and call it from the exec() via the cmd.exe in it
_________________
Cheers
Ganesh
Back to top
aidelia
Principal Member
Principal Member



Joined: 02 Feb 2006

Posts: 336
Location: Rosario, Argentina


flag
PostPosted: Tue Nov 17, 2009 8:20 am 
Post subject: Re: Automate log deletion for a specific job?

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 icon_smile.gif

Hope this helps,


Regards

Andrés
Back to top
manoj_d
Forum Addict
Forum Addict



Joined: 01 Jan 2009

Posts: 2944



PostPosted: Tue Nov 17, 2009 12:24 pm 
Post subject: Re: Automate log deletion for a specific job?

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
Back to top
aidelia
Principal Member
Principal Member



Joined: 02 Feb 2006

Posts: 336
Location: Rosario, Argentina


flag
PostPosted: Tue Nov 17, 2009 12:31 pm 
Post subject: Re: Automate log deletion for a specific job?

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
Back to top
manoj_d
Forum Addict
Forum Addict



Joined: 01 Jan 2009

Posts: 2944



PostPosted: Tue Nov 17, 2009 7:56 pm 
Post subject: Re: Automate log deletion for a specific job?

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
Back to top
pxbaker
Principal Member
Principal Member



Joined: 09 Jan 2007

Posts: 105


flag
PostPosted: Wed Nov 18, 2009 4:54 am 
Post subject: Re: Automate log deletion for a specific job?

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.
Back to top
aidelia
Principal Member
Principal Member



Joined: 02 Feb 2006

Posts: 336
Location: Rosario, Argentina


flag
PostPosted: Wed Nov 18, 2009 9:07 am 
Post subject: Re: Automate log deletion for a specific job?

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
Back to top
manoj_d
Forum Addict
Forum Addict



Joined: 01 Jan 2009

Posts: 2944



PostPosted: Wed Nov 18, 2009 11:43 am 
Post subject: Re: Automate log deletion for a specific job?

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
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Installation, Administration, and Connectivities  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.1574 seconds using 17 queries. (SQL 0.1300 Parse 0.0010 Other 0.0264)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo