John Alvord, IBM Corporation

jalvord@us.ibm.com

Follow on twitter

Inspiration

In these blog posts, I often need to explain how to view or capture TEMS database tables. This explains ways of doing that.

Method 1: View the table in a TEP workspace view.

Here is a technical note that shows how to do that step by step.

Display recent event information and activity on TEMS

You will need to use the correct SQL for the table. For example, the custom SQL for viewing the TNAME table is

SELECT
ID,
FULLNAME
FROM O4SRV.TNAME;

When the table is displayed, you can right-click in the table and select Export…  That will let you make a text or command separated variable file for other processing,

Note: The next two methods assume the ITM processes are installed in the default location of [Window] C:\IBM\ITM or [Linux/Unix]  /opt/IBM/ITM. Make any needed adjustments for your own installation.  You will often redirect the standard output using the > character.

Method 2: Use the KfwSQLClient program which is included with the Portal Server [TEPS]

Windows Procedure

Login to the system running the TEPS

– cd \IBM\ITM

– md \IBM\ITM\tmp

– create a file test.sql in the \IBM\ITM\tmp  directory with the SQL you want to run

– cd \IBM\ITM\bin

– KfwSQLClient -f \IBM\ITM\test.sql

I suggest creating the tmp directory to keep the structure parallel with Linux/Unix but you can use any temporary directory.

Linux/Unix Procedure

Login to the system running the TEPS

– cd /opt/IBM/ITM/tmp

– create a file test.sql in the /opt/IBM/ITM/tmp  directory with the SQL you want to run

– cd  /opt/IBM/ITM/bin

– ./itmcmd execute cq “KfwSQLClient /f  /opt/IBM/ITM/tmp\test.sql”

Method 3: Use the kdstsns program which is included with the TEMS

Windows Procedure

Login to the system running the TEMS

– cd \IBM\ITM

– md \IBM\ITM\tmp

– create a file test.sql in the \IBM\ITM\tmp  directory with the SQL you want to run

– create a test.def file in the same directory looking like this

ip.pipe
hub-hostname
1918
test.sql
end

-cd \IBM\ITM\bin
– SET SQLLIB=\IBM\ITM\tmp
– ..\cms\kdstsns <\IBM\ITM\tmp\test.def

The test.def file would be tailored to set the type of protocol, the name or ip address of the TEMS, and the port number.

Linux/Unix Procedure

Login to the system running the TEMS

– cd /opt/IBM/ITM/tmp

– create a file test.sql in the /opt/IBM/ITM\tmp  directory with the SQL you want to run

– create a test.def file in the same directory looking like this

ip.pipe
hub-hostname
1918
test.sql
end

 

– locate the name of the TEMS config file – hostname_ms_temsname.config in /opt/IBM/ITM/config
– cd /opt/IBM/ITM\bin
– . /opt/IBM/ITM/config/hostname_ms_temsname.config
– export SQLLIB=/opt/IBM/ITM/tmp
– kdstsns </opt/IBM/ITM\tmp\test.def

The test.def file would be tailored to set the type of protocol, the name or ip address of the TEMS, and the port number,

The third to the last command is a period, a space and then the fully qualified name of the TEMS config file. This is a source include command and sets up needed environment variables to run the kdstsns program.

Finally you could use some SOAP requests, that is usually more complex.

Method 4: Perl Soap program

There are several examples of Perl/SOAP program in these blog posts. The most recent example is  ITM Situation Audit. There is a “overhead” of roughly 1500 lines of Perl code – but if you need to do the work this is a good starting point.

Summary

Methods defined to display TEMS tables

Sitworld: Table of Contents