Technical Blog Post
Abstract
ITM Nuggets: SPUFI - Feel the power of extracting information directly from the TEMS database with KDSTSNS (SPUFI)
Body
Always wondered what SPUFI is, how it works and what it can do for you (if you haven't you should have)... time to be enlightened! 
Over the next two weeks, I will be blogging a series of use cases of how to extract key information via the command line from your TEMS. This will cover everything from how to check what situations have fired in the environment, to how to find MSL associations and everything in between. This information will allow you health check, sanity check and recheck multiple critical areas of your ITM estate. If anyone wants to know how to obtain a specific piece of information from ITM, add it to the comments box below and I will build a use case blog on it for you! Before we get to the uses case, I will show the basics of using the tool.
Link with me:
or
What does SPUFI stand for?
“SQL Processing Using File Input”
What is SPUFI?
SPUFI is a command line based tool that allows you to query the TEMS database using SQL statements contained in a text file. It can be run in an interactive mode or through a single command (great for batch files and scripts). For the TEMS is is also known as KDSTSNS as this is the file needed to run SPUFI.
What is the point in using SPUFI?
SPUFI is used to "dump" information directly from your TEMS database. You can source data for many reasons.....To see if a situation has fired, or to see what agents are registered online at a certain TEMS, etc. SPUFI can also be used to insert/repair/remove data from a TEMS. It can be extremely powerful to extract large volumes of data quickly that would take you a long time to see in the GUI (or find at all).
How to use SPUFI
The following example will show you how to build payloads and use SPUFI against the TEMS. This is the first important step for you to be able to action all of the following blogs use cases.
1. Open a text editor, then enter the SQL you wish to run against the TEMS database.
(You can add any SQL statement you wish to run against your TEMS in this file)
Example of an SQL statement that could be added to the file:-
SELECT ISITSTSH.DELTASTAT,
ISITSTSH.SITNAME,
ISITSTSH.ATOMIZE,
ISITSTSH.ORIGINNODE,
ISITSTSH.GBLTMSTMP,
ISITSTSH.LCLTMSTMP,
ISITSTSH.NODE FROM O4SRV.ISITSTSH;
;
2. Name the notepad file “EventStatus.sql” and save it in the following directory:
Windows: %CANDLEHOME%\cms\SQLLIB
Linux: %CANDLEHOME%/<platform>/ms/bin/sqllib
(NOTE: You can name the file anything you like, but give it the “.sql” extension. Using different file names means you can have multiple files containing different SQL statements. These can be stored on the TEMS for future use)
Running the SPUFI (Kdstsns)
From here you have two options to execute the SQL, you can use the interactive mode, or the one line command.
One Line command Mode:
Kdstsns is the executable needed here. It is located under the following directories:
Windows - $CANDLEHOME\cms
UNIX - $CANDLEHOME/Mplatform>/ms/bin
How to set the location where the SQL file you want to run resides:
Example:
Windows: set SQLLIB=c:\IBM\ITM\cms\sqllib
UNIX: export SQLLIB=/opt/IBM/ITM/<platform>/ms/bin/sqllib
Example of CLI command needed:
kdstsns EventStatus.sql *HUB (returns output to screen)
kdstsns EventStatus.sql *HUB > EventStatus.out (to pipe the output to a file)
All Done - You should now have the contents of your event status table in the output file
Interactive Mode:
1. Go to the %CANDLEHOME%\cms directory and double click on the file called “kdstsns.exe”
This will bring up a command prompt window.
This screen is asking you what protocol your environment uses, e.g. ip.pipe
2. Enter the protocol you use in your environment. This example environment uses ip.pipe so the rest of the screen shots will display ip.pipe.
3. You will now be asked to enter your hostname/address:
Enter the hostname of the server that the TEMS resides on, then press return.
4. It will now prompt you for the port number, use the port number you assigned to your TEMS. The default is 1918, again the example environment uses 1918.
5. You should now see the output as displayed in the screen shot below.
You now need to type “EventStatus.sql” then press return.
6. You should now get an output that looks like this:
This example displays the contents of the events table within the TEMS in the command window itself.
Press end, or close the window (if you close the window you may be prompted to kill the service - this is ok to do)
Questions. recommendations .... Comment below and I will get back to you ASAP!
Thanks for reading
Mark
Check out all our other posts and updates:
Academy Blogs: h
Academy Videos:
Academy Google+:
Academy Twitter Handle:
[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"","label":""},"Component":"","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}}]
UID
ibm11082715