Thursday, September 12, 2013

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

/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 -export



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 ) 
/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


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  )
/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 kpa_tasks.sql

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.

No comments:

Post a Comment