Manual Cleaning of ITM Offline Managed Systems

Answer

Normal Methods of Clearing Offline Managed Systems

When a managed system shows as Offline in the Portal Client Navigation view, there are two standard ways to clear it.

1) View the Enterprise Workspace - Managed System Status, find the Offline agent, right-click and select Clear Offline Entry.

2) Use the tacmd cleanms function

tacmd login ...

tacmd cleanms -m <agent_name>

However the techniques do not always work. This document explains why and how you can remove such offline agents.

When do the normal methods Fail?

One reason for failure is the information about managed systems is inconsistent between the two tables that define the node status table. An example is a managed system in the Node Status table but not in the Node List table. If the rows are not consistent , the normal methods fail.

A second failure reason involves managed systems which are defined with special characters. One frequent example is percent [%]. It is possible to configure some agents with the percent character in the name, like %COMPUTERNAME%. However, percent has a special meaning in the ITM TEMS SQL syntax and attempts to use it as a normal character will result in a syntax error and a failure to do the needed work.

There may be other reasons as yet undiscovered.

Overview of Manual Cleaning Process

The following will show some SQL that needs running. See Appendix 1 on the best way to run this SQL.

The general pattern is that we get the entire contents of the two tables and diagnose the problem. From that we generate DELETE SQL statements to remove the problem rows. When it is convenient to recycle the hub TEMS and the TEPS, the DELETE SQL statements are run just before the hub TEMS recycle. After the TEMS/TEPS are recycled, the Portal Client is checked to verify that the problem managed systems are absent.

Gather full table data for TNODELST and TNODESAV

SELECT
LSTDATE, NODE, NODELIST, NODETYPE
FROM O4SRV.TNODELST;

SELECT
GBLTMSTMP, NODE, THRUNODE, ORIGINNODE, HOSTADDR, VERSION
FROM O4SRV.TNODESAV;

There are other columns, but this is usually sufficient to determine what is needed. Run the SQL [Appendix 1] and capture the results in tnodelst.lst and tnodesav.lst. Now search for the rows which contain the needed data. Lets assume this is %COMPUTER%.

Linux/Unix:
grep %COMPUTER% tnodelst.lst tnodesav.lst
Windows:
findstr "%COMPUTER%" tnodelst.lst tnodesav.lst

Here is an example output from a linux system using grep. The column results are placed on separate lines for clarity instead of a single line.

tnodelst.lst:[22]
1100528153759000
%COMPTERNAME%:LZ
*LINUX_SYSTEM
V

tnodesav.lst:[28]
1101130145133000
%COMPTERNAME%:LZ
REM_NMP184
%COMPTERNAME%:LZ
ip.pipe:#w.x.y.z[6015]<NM>%COMPTERNAME%</NM>
06.02.22

For the TNODELST table, the problem name sometimes shows as a NODE and a second time as a NODELIST. In that case the DELETE SQLs created below must include each test.


Finding DELETE SQLs that will work successfully

There is no one way to generate the needed DELETE SQL. The goal is to discover something about the output rows that specify them uniquely but do not involve something that will cause a syntax error. If the problem node name was COMPUTERNAME%:LZ, we could suspect that the name "COMPUTERNAME" at the beginning of the node was unique. First we prove uniqueness this way

SELECT
LSTDATE, NODE, NODELIST, NODETYPE
FROM O4SRV.TNODELST
WHERE NODE LIKE "COMPUTERNAME";

SELECT
GBLTMSTMP, NODE, THRUNODE, ORIGINNODE, HOSTADDR, VERSION
FROM O4SRV.TNODESAV
WHERE NODE LIKE "COMPUTERNAME";

After you are determine only relevant rows showed in the results, use the following delete statements:

DELETE
FROM O4SRV.TNODELST
WHERE NODE LIKE 'COMPUTERNAME';

DELETE
FROM O4SRV.TNODESAV
WHERE NODE LIKE 'COMPUTERNAME';

In our example case, the problem name begins with a percent sign and so that won't work.

Another possibility is to use the LSTDATE and GBLTMSTMP fields. Do the test like this:

SELECT
LSTDATE, NODE, NODELIST, NODETYPE
FROM O4SRV.TNODELST
WHERE LSTDATE = '1100528153759000';

SELECT
GBLTMSTMP, NODE, THRUNODE, ORIGINNODE, HOSTADDR, VERSION
FROM O4SRV.TNODESAV
WHERE GBLTMSTMP = '1101130145133000';

Where the time tests are taken from the above results. If only relevant rows are displayed, then delete them using the LSTDATE/GBLTMSTMP tests.

There was one case where the times were the same for the TNODESAV table but the version number was different. In that case

SELECT
GBLTMSTMP, NODE, THRUNODE, ORIGINNODE, HOSTADDR, VERSION
FROM O4SRV.TNODESAV
WHERE GBLTMSTMP = '1101130145133000' AND VERSION = '06.22.02';

There was another case where the HOSTADDR field was different because the ip address had changed. In that case

SELECT
GBLTMSTMP, NODE, THRUNODE, ORIGINNODE, HOSTADDR, VERSION
FROM O4SRV.TNODESAV
WHERE GBLTMSTMP = '1101130145133000' AND HOSTADDR LIKE '*w.x.y.z*'


TNODESAV Data will be Automatically Recreated

You can delete more TNODESAV rows than the ones needed. Since DELETEs are followed by a hub TEMS recycle, the agents associated with the deleted rows will reconnect and the row data will be rebuilt. Thus if the test with GBLTMPSTMP shows the problem rows and also some OK rows, you can safely delete all those rows.

That is not always true with TNODELST records. You can delete NODETYPE = 'V' records, since they are re-established during the agent connection and registration process. The NODETYPE = 'M' records represent Managed System List contents that have been created for the local ITM system. If you delete those entries, they must be recreated using the object editor.


If this process makes you nervous or if it doesn't work...

Contact IBM support. This document will guide you through simple cases, but if you are uncomfortable or have a more complex case, IBM Support can work through what is needed.


Summary

This document shows how to correct many cases where a management system shows as Offline and the standard facilities do not remove it.

Appendix 1 - Performing SQL

Warning: Running SQL to the hub TEMS can make the TEMS unusable such that a reinstall may be necessary. Making small well defined changes when you know what you are doing is sometimes useful. If you have any doubts, please contact IBM Support.

The easiest way to run SQL is via the KfwSQLClient program. This program is part of the Portal Server installation and makes use of the TEPS connection to the TEMS. Therefore you do not need any extra knowledge about how to connect to the TEMS. It has the added benefit that the results are never truncated as can occur with other methods. You can put multiple SQL statements in the input file with semicolons at the end of each statement.

The directions assume the default install directory. Make any needed changes if you are using a different install path.

Linux/Unix

Logon to the server running the Portal Client and make /opt/IBM/ITM/bin the current directory. In general you should be logged on as the same userid as the one the TEPS was started under.

1) create a file doit.sql containing the needed SQL statements in /opt/IBM/ITM/tmp

2) run the following command

./itmcmd execute cq "KfwSQLClient /f /opt/IBM/ITM/tmp/doit.sql" >/opt/IBM/ITM/tmp/doit.lst

Windows

Logon to the server running the Portal Client and make c:\IBM\ITM\bin the current directory.

1). Make a new directory for the SQL input and output files if it doesn't exist

md \IBM\ITM\tmp

2) create a file doit.sql containing the needed SQL statements in \ITM\ITM\tmp

3) run the following command

KfwSQLClient /f \IBM\ITM\tmp\doit.sql >doit.lst

After running

Review the doit.lst for errors. In the case of INSERT/UPDATE/DELETE there will typically be no output on a success. You can run it without redirection to see the results immediately. On a SELECT there will be a count field and then the selected columns.