Version 1.37000 11 November 2016
John Alvord, IBM Corporation
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.
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
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 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
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.
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.
|-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.
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.
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.
|CCT||Portal Client Take Action|
|INODESTS||In core node status|
|ISITSTSH||In core Situation Status History|
|TACTYPCY||Workflow Policy Activities|
|TAPPLPROPS||SDA Support – 623 FP1|
|TNAME||Long Situation Name and index|
|TNODELST||Node List – online and MSLs|
|TOBJACCL||Object Access – distribution|
|TPCYDESC||Workflow Policy Description|
|TSITSTSC||Situation Status Cache|
|TSITSTSH||Situation Status History|
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
The itm2sql program produces reports on TEMS database tables..
Correct some -help and filename detection issues
Note: Eighteen Inch Zucchini From Garden 19 June 2016 – End Section Used For Vegetable Stew