How to automatically Housekeep or purge Informatica and DAC logs


Informatica:

After lot of hit and trails I discovered that if you want to create your own custom scheduling of this functionality then you can leverage the ‘pmrep truncatelog’ Command which can be used to truncate the session log entries from a command line interface as follows: –

Syntax:
Truncatelog uses the following command syntax:

truncatelog -t all | endtime -f folder name -w workflow name
Option Argument Name Argument Description

-t Required all | endtime Use “all” or enter a date/time. Use “all” to delete all the logs. Otherwise enter a date/time in this format: MM/DD/YYYY HH24:MI:SS pmrep deletes logs older than this time.

-f Optional folder name Specify a folder name to delete logs only for that folder. If you do not specify a folder, then you delete the logs in the repository.

-w Optional workflow name Specify a workflow name to delete logs for a workflow. If you specify a workflow name, you must specify the folder name. Otherwise pmrep generates an error.

To delete all the logs, use the “all” option. Otherwise specify an endtime in this format: -t MM/DD/YYYY HH24:MI:SS. When you specify the endtime, you delete all logs older than the date and time you specify.

Create your own Batch file with this command and your relevant arguments and this should give you the functionality that you require.

When the Tables have not been pruned for some time, issues can occur when the log data becomes too large. If you experience issues deleting this data, the following two suggestions might help: –

1) When communicating with a large repository the resilience time between Repository Service and Repository Database can be increased. Increase Repository Service Resilience Timeout, Heartbeat Interval, DatabaseConnectionTimeout and Limit on Resilience Timeout to 600. Allowing more time for the truncation to take place could help resolve your issue.

2) If the Repository is very large, try deleting the logs in stages (for a period of a few days or weeks).

Syntax:
truncatelog
-t <logs_truncated (all or up to end time in MM/DD/YYYY HH24:MI:SS format or as number of days before current date)> [-f <folder_name>] [-w <workflow_name>]

Example:

pmrep> truncatelog -t ‘3/20/2014 12:01:01’

DAC:

There is no documented ‘Command Line Command’ for the purging the DAC logs.

However, by editing the ‘StartClient.bat’ file in the ‘\\OracleBI\DAC’ directory and uncommenting the ‘JAVA’ line and commenting out the ‘JAVAW’ line, it is possible to see what commands are issued when the menu option in the DAC is executed.

I received this from my Test environment: –

Aug 15, 2014 2:35:08 PM com.siebel.etl.gui.data.StaticDatabaseCalls getDefnIds
INFO: SELECT
ROW_WID
FROM
W_ETL_DEFN_RUN
WHERE
STATUS = ‘Completed’
AND ROW_WID != ?
Aug 15, 2014 2:35:08 PM com.siebel.etl.gui.data.StaticDatabaseCalls getDefnIds
INFO: 8 run(s) found
Aug 15, 2014 2:35:08 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDeta
ils
INFO: DELETE
FROM
W_ETL_RUN_SDTL
WHERE
RUN_STEP_WID IN
(SELECT ROW_WID
FROM W_ETL_RUN_STEP
WHERE RUN_WID IN (’85C0734149D33952665F29E26ABD0′, ‘DF645EF112E
8647C95A86018C97FE7C4′, ’54B048D2799F90DEBDD527D6BF651147’, ‘482A524D44211A1CF2E
C08573AFF7AB’, ‘D9CE165FE2AEA7AB13B586D11669F30’, ‘C55F996E574E2482CC72FBC6942BE
2E’, ‘F7EF2C3B3B47A476A69836E66A428957′, ’53FA118792AEC54AE78A47011F96F60′)
)
Aug 15, 2014 2:35:20 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDetails
INFO: 29145 records deleted in W_ETL_RUN_SDTL
Aug 15, 2014 2:35:20 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDetails
INFO: DELETE
FROM
W_ETL_RUN_AUDIT
WHERE
RUN_WID IN (’85C0734149D33952665F29E26ABD0’, ‘DF645EF112E8647C95A86018C9
7FE7C4′, ’54B048D2799F90DEBDD527D6BF651147’, ‘482A524D44211A1CF2EC08573AFF7AB’,
‘D9CE165FE2AEA7AB13B586D11669F30’, ‘C55F996E574E2482CC72FBC6942BE2E’, ‘F7EF2C3B3
B47A476A69836E66A428957′, ’53FA118792AEC54AE78A47011F96F60′)
OR RUN_STEP_WID IN
(SELECT ROW_WID
FROM W_ETL_RUN_STEP
WHERE RUN_WID IN (’85C0734149D33952665F29E26ABD0’, ‘DF645EF112E
8647C95A86018C97FE7C4′, ’54B048D2799F90DEBDD527D6BF651147’, ‘482A524D44211A1CF2E
C08573AFF7AB’, ‘D9CE165FE2AEA7AB13B586D11669F30’, ‘C55F996E574E2482CC72FBC6942BE
2E’, ‘F7EF2C3B3B47A476A69836E66A428957′, ’53FA118792AEC54AE78A47011F96F60′)
)
Aug 15, 2014 2:35:20 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDetails
INFO: 2635 records deleted in W_ETL_RUN_AUDIT
Aug 15, 2014 2:35:20 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDetails
INFO: DELETE
FROM
W_ETL_RUN_SUMM
WHERE
RUN_WID IN (’85C0734149D33952665F29E26ABD0’, ‘DF645EF112E8647C95A86018C9
7FE7C4′, ’54B048D2799F90DEBDD527D6BF651147’, ‘482A524D44211A1CF2EC08573AFF7AB’,
‘D9CE165FE2AEA7AB13B586D11669F30’, ‘C55F996E574E2482CC72FBC6942BE2E’, ‘F7EF2C3B3
B47A476A69836E66A428957′, ’53FA118792AEC54AE78A47011F96F60′)
Aug 15, 2014 2:35:20 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDetails
INFO: 0 records deleted in W_ETL_RUN_SUMM
Aug 15, 2014 2:35:20 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDetails
INFO: DELETE FROM W_ETL_RUN_STEP
WHERE
RUN_WID IN (’85C0734149D33952665F29E26ABD0’, ‘DF645EF112E8647C95A86018C9
7FE7C4′, ’54B048D2799F90DEBDD527D6BF651147’, ‘482A524D44211A1CF2EC08573AFF7AB’,
‘D9CE165FE2AEA7AB13B586D11669F30’, ‘C55F996E574E2482CC72FBC6942BE2E’, ‘F7EF2C3B3
B47A476A69836E66A428957′, ’53FA118792AEC54AE78A47011F96F60′)
Aug 15, 2014 2:35:27 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDetails
INFO: 2000 records deleted in W_ETL_RUN_STEP
Aug 15, 2014 2:35:27 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDetails
INFO: DELETE
FROM
W_ETL_DEFN_RUN
WHERE
ROW_WID IN (’85C0734149D33952665F29E26ABD0’, ‘DF645EF112E8647C95A86018C9
7FE7C4′, ’54B048D2799F90DEBDD527D6BF651147’, ‘482A524D44211A1CF2EC08573AFF7AB’,
‘D9CE165FE2AEA7AB13B586D11669F30’, ‘C55F996E574E2482CC72FBC6942BE2E’, ‘F7EF2C3B3
B47A476A69836E66A428957′, ’53FA118792AEC54AE78A47011F96F60’)
Aug 15, 2014 2:35:27 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDetails
INFO: 8 records deleted in W_ETL_DEFN_RUN

As you can see the ‘Purge Run Details’ executes the above SQL Statements directly to the DAC Database Repository Tables. The logic to this deletion is more complex and covers a larger set of Log Tables. If you can find some logic to complete this yourself in a Batch File which meets your needs then you may be able to find an alternative solution, like you have for the Informatica Table..

Thanks,
~KKT~

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s