Tivoli Workload Scheduler 8.6 Reports Problem with error code AWSUI1200E

Recently we applied the latest fix pack (at time of writing this is Fix pack 11) to the DB2 9.7 server that hosts our TWS 8.6 (fix pack 3) database.

However, when running a simple custom report in the GUI we were greeted with the following error message AWSU1200E:

Error Message

On checking the SystemErr.log the following error was detailed:

[4/25/16 13:38:28:686 BST] 0000005a SystemErr     R java.lang.NumberFormatException: For input string: “B”
[4/25/16 13:38:28:686 BST] 0000005a SystemErr     R     at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
[4/25/16 13:38:28:686 BST] 0000005a SystemErr     R     at java.lang.Integer.parseInt(Integer.java:449)
[4/25/16 13:38:28:686 BST] 0000005a SystemErr     R     at java.lang.Integer.parseInt(Integer.java:499)
[4/25/16 13:38:28:686 BST] 0000005a SystemErr     R     at com.ibm.db2.jcc.c.ad.<init>(ad.java:80)
[4/25/16 13:38:28:686 BST] 0000005a SystemErr     R     at com.ibm.db2.jcc.b.f.<init>(f.java:55)
[4/25/16 13:38:28:686 BST] 0000005a SystemErr     R     at com.ibm.db2.jcc.b.b.ib(b.java:1760)
[4/25/16 13:38:28:686 BST] 0000005a SystemErr     R     at com.ibm.db2.jcc.c.p.a(p.java:2490)
[4/25/16 13:38:28:686 BST] 0000005a SystemErr     R     at com.ibm.db2.jcc.b.b.a(b.java:470)
[4/25/16 13:38:28:686 BST] 0000005a SystemErr     R     at com.ibm.db2.jcc.b.b.<init>(b.java:264)
[4/25/16 13:38:28:686 BST] 0000005a SystemErr     R     at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:163)
[4/25/16 13:38:28:687 BST] 0000005a SystemErr     R     at java.sql.DriverManager.getConnection(DriverManager.java:419)
[4/25/16 13:38:28:687 BST] 0000005a SystemErr     R     at java.sql.DriverManager.getConnection(DriverManager.java:467)
[4/25/16 13:38:28:687 BST] 0000005a SystemErr     R     at com.ibm.tws.webui.report.utils.DataSource.getConnection(DataSource.java:103)
[4/25/16 13:38:28:687 BST] 0000005a SystemErr     R     at com.ibm.tws.webui.appservices.utils.DatabaseConnectivity.verifyConnection(DatabaseConnectivity.java:174)
[4/25/16 13:38:28:687 BST] 0000005a SystemErr     R     at com.ibm.tws.webui.auiml.application.ApplicationImpl.executeReportTask(ApplicationImpl.java:1853)
“SystemErr.log” [readonly] 103L, 12749C

A bit of a strange error but by running the db2level command we were able to shed some light on the problem.

db2level
DB21085I  This instance or install (instance name, where applicable:
“db2inst1”) uses “64” bits and DB2 code release “SQL0907B” with level
identifier “080C0107”.
Informational tokens are “DB2 v9.7.0.11”, “s150922”, “IP23944”, and Fix Pack
“11”.
Product is installed at “/db2/db2tws/V9.7”

The bit that stands out from the db2level output is the SQL0907B message. It seems that the db2jcc driver that TWS uses to access the database does not support fix pack DB2 9.7 fix pack 11.

The following command was used to verify the version of the db2jcc driver that TWS 8.6 was using:

java -cp /tws/TWA/eWAS/profiles/TIPProfile/installedApps/TIPCell/isc.ear/TWSWebUI.war/WEB-INF/lib/db2jcc.jar com.ibm.db2.jcc.DB2Jcc  -version
IBM DB2 JDBC Universal Driver Architecture 3.1.57

From the above output it was clear that TWS 8.6 fix pack 3 is using db2jcc version 3.1.57 this was verified using the following web page:

http://www-01.ibm.com/support/docview.wss?uid=swg21363866

db2jcc driver old

This clearly shows that the JDBC driver corresponds with IBM DB2 9.1 FP0(GA).

The db2jcc.jar file that comes as part of DB2 9.7 fix pack 11 was also checked for the version number using the same command:

 /db2/db2tws/V9.7/java/jdk64/bin/java -cp /db2/db2tws/V9.7/java/db2jcc.jar com.ibm.db2.jcc.DB2Jcc  -version
IBM DB2 JDBC Universal Driver Architecture 3.64.141

The output above displays that DB2 9.7 fix pack 11 provides db2jcc version 3.64.141, this was again verified using the above link.

db2jcc driver new

The webpage clearly shows that the JDBC driver corresponds with IBM DB2 9.7 FP11.

As we had access to a db2jcc.jar file that supports the version of the database we are running, this can be used to  replace the old driver that TWS is using.

It should be pointed out that all workstations were set to limit 0 and eWAS was shut down at this point.

Also please remember to make backups of the original .db2jcc.jar and  db2jcc_license_cu.jar in the TWS server (location in our server (/tws/TWA/eWAS/profiles/TIPProfile/installedApps/TIPCell/isc.ear/TWSWebUI.war/WEB-INF/lib) .

cd /tws/TWA/eWAS/profiles/TIPProfile/installedApps/TIPCell/isc.ear/TWSWebUI.war/WEB-INF/lib
ls -ltr db2*
-rw-r–r– 1 root root 1973658 Feb  6  2007 db2jcc.jar
-rw-r–r– 1 root root    1015 Feb  6  2007 db2jcc_license_cu.jar
-rw-r–r– 1 root root 1973658 Apr 25 13:44 db2jcc.jar.original
-rw-r–r– 1 root root    1015 Apr 25 13:45 db2jcc_license_cu.jar.original

Once the backups were created the newer db2jcc.jar and db2jcc_license_cu.jar files were copied from the DB2 server (location  /db2/db2tws/V9.7/java).

cd /tws/TWA/eWAS/profiles/TIPProfile/installedApps/TIPCell/isc.ear/TWSWebUI.war/WEB-INF/lib  
cp /db2/db2tws/V9.7/java/db2jcc.jar db2jcc.jar 
cp /db2/db2tws/V9.7/java/db2jcc_license_cu.jar db2jcc_license_cu.jar

eWas was then restarted and the workstations were set to their original limits.

Now when logging into the GUI and running the custom report it now produces the desired report.

Reports now showing up!

Hope you found this blog enlightening, for any related questions email me at mailto:neil.richards@orb-data.com

Visits: 114