Version 1.37000 11 November 2016

John Alvord, IBM Corporation

jalvord@us.ibm.com

Follow on twitter

Inspiration

I have long envied the TEPS facility called migrate-export, which takes the TEPS database and creates a file of SQL commands to recreate the database.

Recently I found a way to accomplish this on a live ITM system using the KfwSQLClient utility. It does not have an obvious use case to solve a specific problem but can be useful in viewing TEMS database contents and producing data for ad-hoc reports.

The result is not suitable for a backup – see Best Practice TEMS Database Backup and Recovery because tables are often jointly updated. A capture of a table and a capture of a second table a few seconds later can miss the combined update. I have even seen cases where a capture of a single table gets inconsistent results. That was getting the node status table from a remote TEMS when there were a terrific number of duplicate agent name cases constantly changing the table.

More than anything, I had wanted to do it for a long time and after years figured out a way to accomplish the goal. If nothing else there is a pleasure in satisfying that desire and sharing the results.

Overview

The itm2sql.pl utility uses the TEMS catalog file kib.cat and the running system to capture a TEMS table current contents and produce a file of

1) INSERT SQL statements

2) Tab Separated Variables suitable for a spreadsheet program

3) Text file with fixed length columns for easy reference, sorting and searching

4) An index only file which is useful when comparing two tables for differences

ITM2SQL Package Installation

The package is itm2sql.1.37000. It contains

1) Perl program itm2sql.pl.

I suggest itm2sql.pl be placed in some convenient directory, perhaps a directory itm2sql of the installation tmp directory. That is what examples will assume. For Windows you need to create the <installdir>\tmp directory. You can of course use any convenient directory.

Linux/Unix:  /opt/IBM/IBM/tmp

Windows: c:\IBM\ITM\tmp

Linux and Unix almost always comes with Perl shell script installed. For Windows you can install a no cost Community version from www.activestate.com if needed.

The hub TEPS should be connected to the current primary hub TEMS. That can be running on any platform: Linux/Unix/Windows/zOS.

ITM2SQL and Catalog files

The itm2sql.pl processing requires a matching catalog file. This will be found in a TEMS install at

Linux/Unix:  <installdir>/tables/<temsnodeid>/RKDSCATL

Windows: <installdir>\cms\RKDSCATL

z/OS: RKANDATV

For Linux/Unix/Windows the usual catalog name will be kib.cat. For z/OS the member name will be KIBCAT.

Copy that file to the directory where itm2sql.pl will be used – for z/OS the name will have to be changed of course. If the TEPS is running on the same system as the TEMS, you can just supply the fully qualified name and not use a copy.

The kib.cat gives you references to most of the tables that TEMS uses. There are other TEMS components – e.g. remote deploy – which uses other catalogs. Remote deploy uses kdy.cat. This document does not mention such other catalogs further.

ITM2SQL Usage

The ITM2SQL package has parameters to match installation requirements. Following is a complete list.

The following table shows all options.  Extensive notes follow the table.

command	default	notes
-d	off	produce debug messages on STDERR
-help	off	Produce help summary
-home	default install location	Directory for TEPS install
-ix	off	Produce a show keys only output
-l	off	INSERT SQL output with prefix count/keys
-o [file]	STDOUT	Output to a named file or by report type
-qib	off	Do not ignore the columns starting QIB
-s key	off	Name a key, can have more than one
-si file	off	Process only named keys in index file
-sx file	off	Exclude named keys in index file
-testf file	off	Process a previously captured listing file
-txt	off	Fixed column text file
-tc		columns to process with -txt
-f		Favorite columns to process with -txt
-tlim	256	maximum column bytes to display, 0=all
-tr	off	translate tab/carriage return/line feed to blank
-v	off	Tab Separated Variable
-work directory	TMP or TEMP	where to create work files

Following the option parameters are two positional arguments. The first is the catalog file – often kib.cat. The second is the name of the table to be processed.

Notes

1) -home if unspecified use environment variable [Windows CANDLE_HOME] or [Linux/Unix CANDLEHOME]. If those are absent use default install locations [Windows C:\IBM\ITM] or [Linux Unix /opt/IBM/ITM].

2) -ix is used to create a show keys only output. You must specify at least one key using -s and the combination of keys must make the reference unique. The resulting file can be used in -si or -sx to include or exclude those keys. This is extremely useful when comparing a capture at one time with a later time – or if comparing one hub TEMS with another hub TEMS.

3) -ix and -txt and -v and -l are mutually exclusive. If all are missing the default is the INSERT SQL output format is produced.

4) -o with no output file [and followed by another – option] will pick a name based on table name and period and [txt=TXT, v=TSV, ix=IX, l=LST, default=SQL]. -o with a following name will use that as output file. If -o is absent, results are printed to standard output.

5) -qib will include columns beginning with QIB which are not represented in the disk files and thus relative uninteresting.

6) -s key – internally these are known as “show” keys because they will present at the beginning of the -l output type. ln combination they should uniquely identify the object.

7) -txt – output report in a fixed column width presentation. The width of column also depends on the length of the column name and a blank is left between columns. This can be useful to feel into your own ad hoc reports.

8) -tc column – list of the columns to display. You can have more than one or you can use multiple columns separated by commas  -tc col1,col2,col3

10) -tlim  – maximum size of txt display columns. If -tlim 0, size chosen is the maximum size in the catalog file.

11) -tr – some columns have spacing controls like tab, carriage return or line feed. This can make the -txt output look strange. With -tr they are replaced by blanks.

12) -v – produce .TSV or tab separated variable output format. This can be opened with a spreadsheet program

13) -work – specify a work directly for temporary files. If -work not specified environment variables [Windows TEMP] or [Linux/Unix TMP] are used. If those are absent the [Windows C:\TEMP] or [Linux/Unix TMP] is used. If -work itself is absent, the current directory is used.

TEMS tables

Here is a list of TEMS tables of some interest. There are over 50 such tables but the following are the ones I find of interest, The columns are not listed here. You could do an SQL type report and get a list of the table columns.

Override definitions

CCT	Portal Client Take Action
EVNTMAP	Event Mapping
EVNTSERVER	Event Server
INODESTS	In core node status
ISITSTSH	In core Situation Status History
TACTYPCY	Workflow Policy Activities
TAPPLPROPS	SDA Support – 623 FP1
TCALENDAR	Calendar definitions
TGROUP	Group
TGROUPI	Group Entries
TNAME	Long Situation Name and index
TNODELST	Node List – online and MSLs
TOBJACCL	Object Access – distribution
TOVERITEM	Override Items
TOVERRIDE	Override definitions
TPCYDESC	Workflow Policy Description
TSITDESC	Situation Description
TSITSTSC	Situation Status Cache
TSITSTSH	Situation Status History

Example usage

To make maximum usage of this utility, you would need to know the TEMS schema and logic. Since that is not published, the process will be more experiment and discovery. Some tables need different catalog files, such as kdy.cat for tables connected with remote deploy.

1) Produce INSERT SQL report for Situation Description and Fullname Table

perl itm2sql.pl kib.cat TSITDESC

perl itm2sql.pl kib.cat TNAME

2) Produce fixed column report for Situation Description table for specified columns

perl itm2sql -txt -s SITNAME -tc SITNAME,AUTOSTART,LSTDATE,LSTUSRPRF,PDT  kib.cat TSITDESC

3) Produce fixed column report for Situation Description table for specified columns and exclude AUTOSTART=*NO

perl itm2sql -txt -s SITNAME -x AUTOSTART=*NO -tc SITNAME,AUTOSTART,LSTDATE,LSTUSRPRF,PDT  kib.cat TSITDESC

Summary

The itm2sql program produces reports on TEMS database tables..

Sitworld: Table of Contents

History

itm2sql.1.37000
Correct some -help and filename detection issues

Note: Eighteen Inch Zucchini From Garden 19 June 2016 – End Section Used For Vegetable Stew