All you ever wanted to know about Topology Builder agents - Issue #4 AliasesJnTableCleanupAgent

Body

In my last two posts I discussed two different aliases cleanup agents, the AliasesCleanupAgent and the ObjectsWithoutAliasesCleanupAgent. So I thought now was a good time to talk about the "AliasesJnTableCleanupAgent".   This agent does not act upon the aliases table, but rather the "aliases_jn" table. The agent is new in TADDM 7.2.1 fix pack 4.  The table was added in fix pack 3 with manual cleanup instructions in the publications, which the agent has now replaced. This table is basically a historical table that keeps track of changes to the aliases table via DB triggers. It is used only by the verify-data script over merge option to find objects that may have unexpectedly merged. Support also uses this table to when diagnosing 'missing' objects in TADDM. By tracking changes to aliases, this table allows the verify-data tool to speculate when specific guids have potentially over merged by analyzing the alias activity for the guid.

This topology builder agent removes old rows in this table to keep the table at a reasonable size.  By default it removes anything older then 30 days.  You can change the interval to remove data faster if you do not run verify-data with the over merge option or need to diagnose missing objects.  We do not recommend keeping data much longer then 30 days as that can impact TADDM performance depending on how large the table is. Starting with fix pack 5, the agent removes the rows in batches of 100. Prior to fix pack 5 it deleted all rows older then 30 days in one batch as long as it took less then 30 minutes.

Properties for this agent

The following collation.properties entries apply to this topology builder agent;

com.ibm.cdb.topomgr.topobuilder.agents.AliasesJnTableCleanupAgent.removeOlderThanDays

This property determines the age of entries to remove. The default value is: 30 days.

com.ibm.cdb.topomgr.topobuilder.agents.AliasesJnTableCleanupAgent.timeout

This property determines how long this agent can run in seconds.
The default value is 1800 seconds(30 minutes).  If set to 0, the agent will log DEBUG message "AliasesJnTableCleanupAgent is disabled" and return without deleting any rows.

What if the Table gets too big?

If you find that the aliases_jn table is very large despite the AliasesJnCleanup agent running, you may want to simply clear the table.  It can grow large if you ran 7213 for some time and never cleared it as clearing was manual in that release.  The row count may have gotten so large at that time that the 30 minute run time will not allow it to catch up now.  Also, if it is a huge table verify-data may take a very long time to run, making the tool not useful to detect over merges. Over merge data will re-appear over time as you run discoveries if the problem remains persistent, so it is OK to clear the table if you are not actively working on over merge issues(missing computer systems).

**NOTE - this procedure involves deleting data directly from the database, which is only safe for a very small group of TADDM tables. Be sure to have a database backup before implementing this procedure.  Only delete from table 'aliases_jn', not its parent table 'aliases'. Deleting from aliases would corrupt the database and require a restore of it.

You could also increase the agent timeout, or lower the number of days, however, if you truncate the table, it will typically keep up after that. If you find that it is not keeping up, eg. the agent runs 30 minutes every time and the row count of aliases_jn starts to grow large again, clear the table, and increase the agent timeout to 1 hour(3600) and then verify that it is keeping up.  Typically you do not want to increase agent run time greatly as doing so impacts the other agents, which will have to wait for this agent to complete before they can run.

Agent Logging

With INFO logging, you can see useful messages in the log/services/TopologyBuilder.log or log/agents/AliasesJnTableCleanupAgent.log about the number of rows the agent has cleaned up;

2013-08-18 20:41:18,316 TopologyBuilder [TopologyBuilderEngineThread$Cleanup@4.0]  INFO agents.AliasesJnTableCleanupAgent - Removed 114101 rows in ALIASES_JN table older than 30 DAYS
 

With DEBUG logging you can see the values in use for the properties mentioned above as well as the timing data for the agent;

2013-08-18 20:11:17,287 TopologyBuilder [TopologyBuilderEngineThread$Cleanup@4.0]  DEBUG agents.AliasesJnTableCleanupAgent - AliasesJnTableCleanupAgent timeout (seconds): 1800

2013-08-18 20:11:17,287 TopologyBuilder [TopologyBuilderEngineThread$Cleanup@4.0]  DEBUG agents.AliasesJnTableCleanupAgent - AliasesJnTableCleanupAgent removes rows in ALIASES_JN table older than (days): 30

2013-08-18 20:41:18,318 TopologyBuilder [TopologyBuilderEngineThread$Cleanup@4.0]  DEBUG engine.TopologyBuilderEngine - !TBE! Agent com.ibm.cdb.topomgr.topobuilder.agents.AliasesJnTableCleanupAgent with scope periodic completed after 1801.033 seconds
 

I hope that explains what this agent is for and it's importance. If you have any questions, please feel free to comment below.
 
Next Up : PersobjCleanupAgent - the last of the Cleanup group!