Cygwin: paconf.jar - export/createDB/ execSQL

Often times there is a need for recreating the Performance Analyzer configuration Database.

Here I will write about some of my experiments with the command.

The command line to run it is ( on linux 64 bit ) ( first cd to the folder where the paconf.jar is present )

/opt/IBM/ITM/JRE/li6263/bin/java -cp paconf.jar:/opt/IBM/ITM/classes/kjrall.jar:/opt/ibm/db2/V9.7/java/db2jcc.jar:/opt/ibm/db2/V9.7/java/db2jcc_license_cu.jar com.ibm.tivoli.pa.config.PAConfigMain

The various options are :
java -jar paconf.jar [-export {taskfile} [-domain domainCode] |-createDB|-destroyDB|-cleanInitDB|-gui|-bareExecSQL|-execSQL taskfile-updateTables]

        -export         Export tasks to a file. Default to: kpa_tasks.sql. Optional -domain parameter specifies which domain tasks shoud be exported. Use 000 to export tasks not assigned to any domain.
        -createDB       Create the PA DB if it is not there
        -destroyDB      Drop all tables in TEPS DB created by ITPA
        -cleanInitDB    Re-create the PA DB. Same as -destroyDB then -createDB
        -gui            Bring up the bare bone GUI (not fully functional
        -bareExecSQL    Executing SQL statements from specified file. Tasks active/inactive state will not be preserved.
        -execSQL        Executing SQL statements from specified file. Tasks active/inactive state will be preserved.
        -updateTables   Update tables to the latest version (create nonexisting ones)

-gui - does not work ( not complete )
My experiments have been with mostly -execSQL, -createDB, -cleanInitDB

-createDB on a RHEL 64 bit: ( this will zap the contents of the tables to 0, all tables will be created though empty. )

/opt/IBM/ITM/JRE/li6263/bin/java -cp paconf.jar:/opt/IBM/ITM/classes/kjrall.jar:/opt/ibm/db2/V9.7/java/db2jcc.jar:/opt/ibm/db2/V9.7/java/db2jcc_license_cu.jar com.ibm.tivoli.pa.config.PAConfigMain -createDB
-destroyDB will drop the tables ( select count(*) from itmuser."KPATASKS" will say SQL0204N "ITMUSER.KPATASKS" is an undefined name. SQLSTATE=42704

/opt/IBM/ITM/JRE/li6263/bin/java -cp paconf.jar:/opt/IBM/ITM/classes/kjrall.jar:/opt/ibm/db2/V9.7/java/db2jcc.jar:/opt/ibm/db2/V9.7/java/db2jcc_license_cu.jar com.ibm.tivoli.pa.config.PAConfigMain -destroyDB

-cleanInitDB /opt/IBM/ITM/JRE/li6263/bin/java -cp paconf.jar:/opt/IBM/ITM/classes/kjrall.jar:/opt/ibm/db2/V9.7/java/db2jcc.jar:/opt/ibm/db2/V9.7/java/db2jcc_license_cu.jar com.ibm.tivoli.pa.config.PAConfigMain -cleanInitDB
(not much help here )

-updateTables ( not operational )

On windows ( 64 bit - 6.2.3 FP1 )
On ITM 6.3.0 ( change the java search string to Java70 below )
If we are using the Oracle 12c - suggest use ojdbc7.jar
The -gui option does not work.

c:\ibm\itm\java\java60\jre\bin\java -cp c:\IBM\ITM\TMAITM6\paconf.jar;c:\IBM\ITM\TMAITM6\kjrall.jar;c:\downloads\ojdbc6.jar com.ibm.tivoli.pa.config.PAConfigMain -gui

On a windows 64 bit ITM 6.3.0 (oracle 12c ) // java is changed. oracle 6 was changed to 7.
c:\ibm\itm\java\java70\jre\bin\java -cp c:\IBM\ITM\TMAITM6\paconf.jar;c:\IBM\ITM\TMAITM6\kjrall.jar;c:\downloads\ojdbc7.jar com.ibm.tivoli.pa.config.PAConfigMain -createDB

This fails with this message.

c:\Downloads>c:\ibm\itm\java\java70\jre\bin\java -cp c:\IBM\ITM\TMAITM6\paconf.j
ar;c:\IBM\ITM\TMAITM6\kjrall.jar;c:\downloads\ojdbc7.jar com.ibm.tivoli.pa.confi
g.PAConfigMain -createDB
Mar 14, 2014 5:42:52 AM com.ibm.tivoli.pa.config.PAConfigMain process
INFO: Check if config DB already exists for PA...
Mar 14, 2014 5:42:52 AM com.ibm.tivoli.pa.config.data.DBAccessor connect
WARNING: java.sql.SQLException: Listener refused the connection with the followi
ng error:
ORA-12519, TNS:no appropriate service handler found

Mar 14, 2014 5:42:52 AM com.ibm.tivoli.pa.config.data.DBAccessor connect
WARNING: Java class path used: c:\IBM\ITM\TMAITM6\paconf.jar;c:\IBM\ITM\TMAITM6\
kjrall.jar;c:\downloads\ojdbc7.jar
Mar 14, 2014 5:42:52 AM com.ibm.tivoli.pa.config.PAConfigMain process
INFO: Done

Exporting the contents of the Performance Analyzer Configuration Data.

This is on RHEL 32 bit.

1. cd to /opt/IBM/ITM/li6263/pa/bin ( /ITM/<arch>/pa/bin folder )

2. Get the location of where the DB2 drivers are ? and also the location of the paconf.jar , cd to that folder before executing the export command.

This is a cut and paste - but you would make necessary file location changes of the DB2 and the PA Jar files.

Look at the last option - it is the export that does the activity of pulling data out and dumps to kpa_tasks.sql

3.Troubleshooting -

- check that you are executing from the same folder where the paconf.jar is ( ls -l paconf.jar )

- check that the path to the DB2 drivers are right in the command ?

4. On successful completion

Sep 12, 2013 4:12:42 AM com.ibm.tivoli.pa.config.PAConfigMain process

INFO: Exporting tasks from DB. This may take a minute...

Sep 12, 2013 4:12:42 AM com.ibm.tivoli.pa.config.data.FileTaskStorage exportDB <==check this line - for troubleshooting .

INFO: exporting tasks from DB

Sep 12, 2013 4:12:44 AM com.ibm.tivoli.pa.config.PAConfigMain process

INFO: Export completed: Thu Sep 12 04:12:44 CDT 2013

5. Check that the file kpa_tasks.sql is having the sql commands (approx 23K lines )

6. Now do the clean up and cleanInitDB

Pre-deletion.

(Pre-deletion )

I have added a task - ( pre-deletion )

Now - delete and see that the tasks are all gone - the default tasks are cleaned out

CleanInitB Option

cleanInitDB

This will zap the ITPA tasks - there will be no tasks in the PA configurartion window.

( All the "+" will be gone )

Sep 12, 2013 4:33:48 AM com.ibm.tivoli.pa.config.data.DBTaskStorage dropAllTables

INFO: Dropping all tables

Sep 12, 2013 4:33:50 AM com.ibm.tivoli.pa.config.data.DBTaskStorage dropAllTables

INFO: All tables dropped

Sep 12, 2013 4:33:50 AM com.ibm.tivoli.pa.config.data.DBTaskStorage createTables

INFO: Creating KPA Tables for the 1st time

Sep 12, 2013 4:33:51 AM com.ibm.tivoli.pa.config.data.DBTaskStorage createTables

INFO: Tables successfully created

Sep 12, 2013 4:33:51 AM com.ibm.tivoli.pa.config.PAConfigMain process

INFO: Done

This picture below says that the browser cache is still having data .
I want to see that the PA analytical tables are all empty -

IMPORTANT : Close the browser and then restart the browser again.
The below model -shows that the Browser cache was not cleaned ! so refresh cache of the browser.

execSQL kpa_tasks.sql // see results from output below

( Look below for another similar command )

or you can use just "/opt/IBM/ITM/lx8266/pa/config/deployed/kp0_tasks.sql'

Sep 12, 2013 4:41:49 AM com.ibm.tivoli.pa.config.data.FileTaskStorage execSQLFromFile

INFO: Executing SQL statements from: /opt/IBM/ITM/li6263/pa/bin/kpa_tasks.sql

Sep 12, 2013 4:42:00 AM com.ibm.tivoli.pa.config.PAConfigMain process

INFO: Done

Bring up the PA configuration panel and check for tasks ? that were exported.

The tasks are back.

/opt/IBM/ITM/JRE/li6263/bin/java -cp paconf.jar:/opt/IBM/ITM/classes/kjrall.jar:/opt/ibm/db2/V9.7/java/db2jcc.jar:/opt/ibm/db2/V9.7/java/db2jcc_license_cu.jar com.ibm.tivoli.pa.config.PAConfigMain -execSQL /opt/IBM/ITM/lx8266/pa/config/deployed/kp0_tasks.sql

/opt/IBM/ITM/JRE/li6263/bin/java -cp paconf.jar:/opt/IBM/ITM/classes/kjrall.jar:/opt/ibm/db2/V9.7/java/db2jcc.jar:/opt/ibm/db2/V9.7/java/db2jcc_license_cu.jar com.ibm.tivoli.pa.config.PAConfigMain -execSQL /opt/IBM/ITM/lx8266/pa/config/deployed/kpu_tasks.sql

/opt/IBM/ITM/JRE/li6263/bin/java -cp paconf.jar:/opt/IBM/ITM/classes/kjrall.jar:/opt/ibm/db2/V9.7/java/db2jcc.jar:/opt/ibm/db2/V9.7/java/db2jcc_license_cu.jar com.ibm.tivoli.pa.config.PAConfigMain -execSQL /opt/IBM/ITM/lx8266/pa/config/deployed/kp3_tasks.sql

/opt/IBM/ITM/JRE/li6263/bin/java -cp paconf.jar:/opt/IBM/ITM/classes/kjrall.jar:/opt/ibm/db2/V9.7/java/db2jcc.jar:/opt/ibm/db2/V9.7/java/db2jcc_license_cu.jar com.ibm.tivoli.pa.config.PAConfigMain -execSQL /opt/IBM/ITM/lx8266/pa/config/deployed/kp6_tasks.sql

/opt/IBM/ITM/JRE/li6263/bin/java -cp paconf.jar:/opt/IBM/ITM/classes/kjrall.jar:/opt/ibm/db2/V9.7/java/db2jcc.jar:/opt/ibm/db2/V9.7/java/db2jcc_license_cu.jar com.ibm.tivoli.pa.config.PAConfigMain -execSQL /opt/IBM/ITM/lx8266/pa/config/deployed/kpi_tasks.sql

/opt/IBM/ITM/JRE/li6263/bin/java -cp paconf.jar:/opt/IBM/ITM/classes/kjrall.jar:/opt/ibm/db2/V9.7/java/db2jcc.jar:/opt/ibm/db2/V9.7/java/db2jcc_license_cu.jar com.ibm.tivoli.pa.config.PAConfigMain -execSQL /opt/IBM/ITM/lx8266/pa/config/deployed/kp4_tasks.sql

destroyDB -

To be used - when we are typically uninstalling the PA component. - It iwll drop all TEPS DB related to ITPA.

How to recreate the ITPA tables back ?

Stop all processes. ( hd,sy , pa, cq, TEMS )

Run the paconf.jar command with the destroyDB option.

Stop Database db2stop

Start Database db2start.

Re-install the ITPA and configure the TEPS. ( itmcmd config -A cq ) Use the MTEMS instead.

This should bring back the original ITPA tasks.

Other flags supported by the tool are :

createDB - will create the databases - but with no content ( there is a small bug that needs to be fixed )

updateTables ( no args at the end ) - needs to be investigated.

Other options for the same command are :

java -jar paconf.jar [-export {taskfile} [-domain domainCode] |-createDB|-destroyDB|-cleanInitDB|-gui|-bareExecSQL|-execSQL taskfile-updateTables]

-export Export tasks to a file. Default to: kpa_tasks.sql. Optional -domain parameter specifies which domain tasks shoud be exported. Use 000 to export tasks not assigned to any domain.

-createDB Create the PA DB if it is not there

-destroyDB Drop all tables in TEPS DB created by ITPA

-cleanInitDB Re-create the PA DB. Same as -destroyDB then -createDB

-gui Bring up the bare bone GUI (not fully functional

-bareExecSQL Executing SQL statements from specified file. Tasks active/inactive state will not be preserved.

-execSQL Executing SQL statements from specified file. Tasks active/inactive state will be preserved.

-updateTables Update tables to the latest version (create nonexisting ones)

I will describe the other options in my later blog.

Cygwin

Thursday, September 12, 2013

paconf.jar - export/createDB/ execSQL

No comments:

Post a Comment