SQL statistics not working in Websphere Application Server 8.5/DB2 10.5

[shri palchuri]

Hi,

 I am in the process of configuring javamelody to Websphere Application Server. I am able to see all the statistics except SQL statistics. I tried various options but no luck, please help me.

Debugging Logs:

Wed Oct 21 15:35:20 EDT 2015     DEBUG     JavaMelody listener init started
Wed Oct 21 15:35:20 EDT 2015     DEBUG     datasources found in JNDI: [java:comp/env/jdbc/XPSDBDataSource, jdbc/DefaultEJBTimerDataSource, jdbc/XPSDBDataSource]
Wed Oct 21 15:35:20 EDT 2015     DEBUG     Datasource needs rewrap: java:comp/env/jdbc/XPSDBDataSource of class com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource
Wed Oct 21 15:35:20 EDT 2015     DEBUG     Datasource needs rewrap: jdbc/DefaultEJBTimerDataSource of class com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource
Wed Oct 21 15:35:20 EDT 2015     DEBUG     Datasource needs rewrap: jdbc/XPSDBDataSource of class com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource
Wed Oct 21 15:35:20 EDT 2015     DEBUG     JavaMelody listener init done in 74 ms
Wed Oct 21 15:35:41 EDT 2015     DEBUG     JavaMelody filter init started
Wed Oct 21 15:35:41 EDT 2015     DEBUG     OS: Windows 7 null, amd64/64
Wed Oct 21 15:35:41 EDT 2015     DEBUG     Java: Java(TM) SE Runtime Environment, pwa6470sr4fp1ifx-20130423_02 (SR4 FP1+IV38579+IV38399+IV40208)
Wed Oct 21 15:35:41 EDT 2015     DEBUG     Server: IBM WebSphere Application Server/8.0
Wed Oct 21 15:35:41 EDT 2015     DEBUG     Webapp context: /brokerage
Wed Oct 21 15:35:41 EDT 2015     DEBUG     JavaMelody version: 1.57.0
Wed Oct 21 15:35:41 EDT 2015     DEBUG     JavaMelody classes loaded from: file:/C:/workspace/FDXMay2015_RAD9/fdWeb/WebContent/WEB-INF/lib/javamelody.jar
Wed Oct 21 15:35:41 EDT 2015     DEBUG     Host: FDX-N...@10.101.102.28
Wed Oct 21 15:35:41 EDT 2015     DEBUG     parameter defined: log=true
Wed Oct 21 15:35:41 EDT 2015     DEBUG     parameter defined: rewrap-datasources=true
Wed Oct 21 15:35:41 EDT 2015     DEBUG     log listeners initialized
Wed Oct 21 15:35:41 EDT 2015     DEBUG     datasources found in JNDI: [java:comp/env/jdbc/XPSDBDataSource, jdbc/DefaultEJBTimerDataSource, jdbc/XPSDBDataSource]
Wed Oct 21 15:35:41 EDT 2015     DEBUG     Datasource needs rewrap: java:comp/env/jdbc/XPSDBDataSource of class com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource
Wed Oct 21 15:35:41 EDT 2015     DEBUG     Datasource needs rewrap: jdbc/DefaultEJBTimerDataSource of class com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource
Wed Oct 21 15:35:41 EDT 2015     DEBUG     Datasource needs rewrap: jdbc/XPSDBDataSource of class com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource
Wed Oct 21 15:35:41 EDT 2015     DEBUG     counters initialized
Wed Oct 21 15:35:41 EDT 2015     DEBUG     counters data read from files in C:\Users\SHRI~1.PAL\AppData\Local\Temp\javamelody\brokerage_FDX-NY-1269
Wed Oct 21 15:35:41 EDT 2015     DEBUG     collect task scheduled every 60s
Wed Oct 21 15:35:41 EDT 2015     DEBUG     first collect of data done
Wed Oct 21 15:35:41 EDT 2015     DEBUG     JavaMelody filter init done in 400 ms

System Information:

OS:	Windows 7, null, amd64/64 (8 cores)
Java:	Java(TM) SE Runtime Environment, pwa6470sr4fp1ifx-20130423_02 (SR4 FP1+IV38579+IV38399+IV40208)
JVM:	IBM J9 VM, 2.6, JRE 1.7.0 Windows 7 amd64-64 Compressed References 20130421_145945 (JIT enabled, AOT enabled) J9VM - R26_Java726_SR4_FP1_2_20130421_2353_B145945 JIT - r11.b03_20130131_32403ifx4 GC - R26_Java726_SR4_FP1_2_20130421_2353_B145945_CMPRSS J9CL - 20130422_145945
PID of process:	6696
Server:	IBM WebSphere Application Server/8.0
Webapp context:	/brokerage
Start:	10/21/15 3:35 PM
JVM arguments:	-Xoptionsfile=C:\IBM\WebSphere\AppServer\java_1.7_64\jre\bin\compressedrefs\options.default -Xlockword:mode=default,noLockword=java/lang/String,noLockword=java/util/MapEntry,noLockword=java/util/HashMap$Entry,noLockword=org/apache/harmony/luni/util/ModifiedMap$Entry,noLockword=java/util/Hashtable$Entry,noLockword=java/lang/invoke/MethodType,noLockword=java/lang/invoke/MethodHandle,noLockword=java/lang/invoke/CollectHandle,noLockword=java/lang/invoke/ConstructorHandle,noLockword=java/lang/invoke/ConvertHandle,noLockword=java/lang/invoke/ArgumentConversionHandle,noLockword=java/lang/invoke/AsTypeHandle,noLockword=java/lang/invoke/ExplicitCastHandle,noLockword=java/lang/invoke/FilterReturnHandle,noLockword=java/lang/invoke/DirectHandle,noLockword=java/lang/invoke/ReceiverBoundHandle,noLockword=java/lang/invoke/DynamicInvokerHandle,noLockword=java/lang/invoke/FieldHandle,noLockword=java/lang/invoke/FieldGetterHandle,noLockword=java/lang/invoke/FieldSetterHandle,noLockword=java/lang/invoke/StaticFieldGetterHandle,noLockword=java/lang/invoke/StaticFieldSetterHandle,noLockword=java/lang/invoke/IndirectHandle,noLockword=java/lang/invoke/InterfaceHandle,noLockword=java/lang/invoke/VirtualHandle,noLockword=java/lang/invoke/InvokeExactHandle,noLockword=java/lang/invoke/InvokeGenericHandle,noLockword=java/lang/invoke/VarargsCollectorHandle,noLockword=java/lang/invoke/ThunkTuple -Xjcl:jclse7b_26 -Dcom.ibm.oti.vm.bootstrap.library.path=C:\IBM\WebSphere\AppServer\java_1.7_64\jre\bin\compressedrefs;C:\IBM\WebSphere\AppServer\java_1.7_64\jre\bin -Dsun.boot.library.path=C:\IBM\WebSphere\AppServer\java_1.7_64\jre\bin\compressedrefs;C:\IBM\WebSphere\AppServer\java_1.7_64\jre\bin -Djava.library.path=C:\IBM\WebSphere\AppServer\java_1.7_64\jre\bin\compressedrefs;C:\IBM\WebSphere\AppServer\java_1.7_64\jre\bin;C:\Windows\system32;C:\Windows;C:\IBM\WebSphere\AppServer\lib\native\win\x86_64;C:\IBM\WebSphere\AppServer\bin;C:\IBM\WebSphere\AppServer\java_1.7_64\bin;C:\IBM\WebSphere\AppServer\java_1.7_64\jre\bin;C:\Program Files\IBM\SQLLIB\BIN;C:\Program Files\IBM\SQLLIB\FUNCTION;C:\Program Files\Java\jdk1.6.0_45;C:\Sencha\Sencha\Cmd\5.1.2.52;C:\Program Files (x86)\IBM\WebSphere MQ\Java\lib;C:\Program Files (x86)\Intel\iCLS Client\;C:\Program Files\Intel\iCLS Client\;C:\Program Files (x86)\NVIDIA Corporation\PhysX\Common;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files (x86)\Common Files\lenovo\easyplussdk\bin;C:\SWTOOLS\ReadyApps;C:\Program Files (x86)\Common Files\Lenovo;C:\PROGRA~2\IBM\SQLLIB\BIN;C:\PROGRA~2\IBM\SQLLIB\FUNCTION;C:\PROGRA~2\IBM\SQLLIB\SAMPLES\REPL;C:\Program Files (x86)\IBM\WebSphere MQ\bin;C:\Program Files (x86)\IBM\WebSphere MQ\tools\c\samples\bin;C:\Ruby22-x64\bin;C:\Program Files (x86)\SSH Communications Security\SSH Secure Shell;. -Djava.home=C:\IBM\WebSphere\AppServer\java_1.7_64\jre -Djava.ext.dirs=C:\IBM\WebSphere\AppServer\java_1.7_64\jre\lib\ext -Duser.dir=C:\IBM\WebSphere\AppServer\profiles\AppSrv01\bin -Djava.runtime.version=pwa6470sr4fp1ifx-20130423_02 (SR4 FP1+IV38579+IV38399+IV40208) -Dconsole.encoding=Cp437 -Djava.class.path=C:\PROGRA~1\IBM\SQLLIB\java\db2java.zip;C:\PROGRA~1\IBM\SQLLIB\java\db2jcc.jar;C:\PROGRA~1\IBM\SQLLIB\java\db2jcc_license_cu.jar;C:\PROGRA~1\IBM\SQLLIB\bin;.;C:\PROGRA~1\IBM\SQLLIB\bin.;C:\PROGRA~2\IBM\SQLLIB\java\db2java.zip;C:\PROGRA~2\IBM\SQLLIB\java\db2jcc.jar;C:\PROGRA~2\IBM\SQLLIB\java\sqlj.zip;C:\PROGRA~2\IBM\SQLLIB\java\db2jcc_license_cu.jar;C:\PROGRA~2\IBM\SQLLIB\bin;C:\PROGRA~2\IBM\SQLLIB\java\common.jar -Declipse.security -Dosgi.install.area=C:\IBM\WebSphere\AppServer -Dosgi.configuration.area=C:\IBM\WebSphere\AppServer\profiles\AppSrv01/servers/server1/configuration -Dosgi.framework.extensions=com.ibm.cds,com.ibm.ws.eclipse.adaptors -Xshareclasses:name=webspherev85_1.7_64,nonFatal -Dsun.reflect.inflationThreshold=250 -Xbootclasspath/p:C:\IBM\WebSphere\AppServer/java_1.7_64/jre/lib/ibmorb.jar -Djava.class.path=C:\IBM\WebSphere\AppServer\profiles\AppSrv01/properties;C:\IBM\WebSphere\AppServer/properties;C:\IBM\WebSphere\AppServer/lib/startup.jar;C:\IBM\WebSphere\AppServer/lib/bootstrap.jar;C:\IBM\WebSphere\AppServer/lib/jsf-nls.jar;C:\IBM\WebSphere\AppServer/lib/lmproxy.jar;C:\IBM\WebSphere\AppServer/lib/urlprotocols.jar;C:\IBM\WebSphere\AppServer/deploytool/itp/batchboot.jar;C:\IBM\WebSphere\AppServer/deploytool/itp/batch2.jar;C:\IBM\WebSphere\AppServer/java_1.7_64/lib/tools.jar -Dibm.websphere.internalClassAccessMode=allow -Xms50m -Xmx256m -Xcompressedrefs -Xscmaxaot4M -Xscmx60M -Dws.ext.dirs=C:\IBM\WebSphere\AppServer/java_1.7_64/lib;C:\IBM\WebSphere\AppServer\profiles\AppSrv01/classes;C:\IBM\WebSphere\AppServer/classes;C:\IBM\WebSphere\AppServer/lib;C:\IBM\WebSphere\AppServer/installedChannels;C:\IBM\WebSphere\AppServer/lib/ext;C:\IBM\WebSphere\AppServer/web/help;C:\IBM\WebSphere\AppServer/deploytool/itp/plugins/com.ibm.etools.ejbdeploy/runtime -Dderby.system.home=C:\IBM\WebSphere\AppServer/derby -Dcom.ibm.itp.location=C:\IBM\WebSphere\AppServer/bin -Djava.util.logging.configureByServer=true -Duser.install.root=C:\IBM\WebSphere\AppServer\profiles\AppSrv01 -Djava.ext.dirs=C:\IBM\WebSphere\AppServer/tivoli/tam;C:\IBM\WebSphere\AppServer/java_1.7_64/jre/lib/ext -Djavax.management.builder.initial=com.ibm.ws.management.PlatformMBeanServerBuilder -Dpython.cachedir=C:\IBM\WebSphere\AppServer\profiles\AppSrv01/temp/cachedir -Dwas.install.root=C:\IBM\WebSphere\AppServer -Djava.util.logging.manager=com.ibm.ws.bootstrap.WsLogManager -Dserver.root=C:\IBM\WebSphere\AppServer\profiles\AppSrv01 -Dcom.ibm.ws.management.event.pull_notification_timeout=120000 -Djavamelody.rewrap-datasources=true -Xquickstart -Djava.library.path=C:\IBM\WebSphere\AppServer/lib/native/win/x86_64/;C:\IBM\WebSphere\AppServer\java_1.7_64\jre\bin\compressedrefs;C:\IBM\WebSphere\AppServer\java_1.7_64\jre\bin;C:\Windows\system32;C:\Windows;C:\IBM\WebSphere\AppServer\lib\native\win\x86_64;C:\IBM\WebSphere\AppServer\bin;C:\IBM\WebSphere\AppServer\java_1.7_64\bin;C:\IBM\WebSphere\AppServer\java_1.7_64\jre\bin;C:\Program Files\IBM\SQLLIB\BIN;C:\Program Files\IBM\SQLLIB\FUNCTION;C:\Program Files\Java\jdk1.6.0_45;C:\Sencha\Sencha\Cmd\5.1.2.52;C:\Program Files (x86)\IBM\WebSphere MQ\Java\lib;C:\Program Files (x86)\Intel\iCLS Client\;C:\Program Files\Intel\iCLS Client\;C:\Program Files (x86)\NVIDIA Corporation\PhysX\Common;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files (x86)\Common Files\lenovo\easyplussdk\bin;C:\SWTOOLS\ReadyApps;C:\Program Files (x86)\Common Files\Lenovo;C:\PROGRA~2\IBM\SQLLIB\BIN;C:\PROGRA~2\IBM\SQLLIB\FUNCTION;C:\PROGRA~2\IBM\SQLLIB\SAMPLES\REPL;C:\Program Files (x86)\IBM\WebSphere MQ\bin;C:\Program Files (x86)\IBM\WebSphere MQ\tools\c\samples\bin;C:\Ruby22-x64\bin;C:\Program Files (x86)\SSH Communications Security\SSH Secure Shell;.; -Djava.endorsed.dirs=C:\IBM\WebSphere\AppServer/endorsed_apis;C:\IBM\WebSphere\AppServer/java_1.7_64/jre/lib/endorsed;C:\IBM\WebSphere\AppServer\endorsed_apis;C:\IBM\WebSphere\AppServer\java_1.7_64\jre\lib\endorsed -Djava.security.auth.login.config=C:\IBM\WebSphere\AppServer\profiles\AppSrv01/properties/wsjaas.conf -Djava.security.policy=C:\IBM\WebSphere\AppServer\profiles\AppSrv01/properties/server.policy -Dsun.java.command=com.ibm.wsspi.bootstrap.WSPreLauncher -nosplash -application com.ibm.ws.bootstrap.WSLauncher com.ibm.ws.runtime.WsServer C:\IBM\WebSphere\AppServer\profiles\AppSrv01\config FDX-NY-1269Node02Cell FDX-NY-1269Node02 server1 -Dsun.java.launcher=SUN_STANDARD
Mean age of http sessions (min):	3
Memory:	Non heap memory = 131 Mb (Perm Gen, Code Cache), Loaded classes = 24,155, Garbage collection time = 969 ms,
Free disk space:	298,508 Mb
DataBase:	java:comp/env/jdbc/XPSDBDataSource: jdbc:db2://10.11.100.110:60000/FDXDB DB2/LINUXX8664, SQL10055 Driver JDBC: IBM DB2 JDBC Universal Driver Architecture, 3.66.46com.ibm.websphere.ce.cm.StaleConnectionException: Database 'C:\IBM\WebSphere\AppServer\profiles\AppSrv01/databases/EJBTimers/server1/EJBTimerDB' not found. DSRA0010E: SQL State = XJ004, Error Code = 40,000
Dependencies:	4 dependencies ;      Details  javamelody.jar, jcommon-1.0.15.jar, jrobin-1.5.9.1.jar Thanks Sri

[shri palchuri]

To below error is fixed by adding a system property javamelody.datasources=java:comp/env/jdbc/XPSDBDataSource,jdbc/XPSDBDataSource since I am not using EJBTimerDB  but  Still  don't see SQL statistics, any reason?

IBM DB2 JDBC Universal Driver Architecture, 3.66.46com.ibm.websphere.ce.cm.StaleConnectionException: Database 'C:\IBM\WebSphere\AppServer\profiles\AppSrv01/databases/EJBTimers/server1/EJBTimerDB' not found. DSRA0010E: SQL State = XJ004, Error Code = 40,000

Wed Oct 21 15:56:12 EDT 2015     DEBUG     JavaMelody listener init started
Wed Oct 21 15:56:12 EDT 2015     DEBUG     datasources found in JNDI: [java:comp/env/jdbc/XPSDBDataSource, jdbc/XPSDBDataSource]
Wed Oct 21 15:56:12 EDT 2015     DEBUG     Datasource needs rewrap: java:comp/env/jdbc/XPSDBDataSource of class com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource
Wed Oct 21 15:56:12 EDT 2015     DEBUG     Datasource needs rewrap: jdbc/XPSDBDataSource of class com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource
Wed Oct 21 15:56:12 EDT 2015     DEBUG     JavaMelody listener init done in 42 ms
Wed Oct 21 15:56:28 EDT 2015     DEBUG     JavaMelody filter init started
Wed Oct 21 15:56:28 EDT 2015     DEBUG     OS: Windows 7 null, amd64/64
Wed Oct 21 15:56:28 EDT 2015     DEBUG     Java: Java(TM) SE Runtime Environment, pwa6470sr4fp1ifx-20130423_02 (SR4 FP1+IV38579+IV38399+IV40208)
Wed Oct 21 15:56:28 EDT 2015     DEBUG     Server: IBM WebSphere Application Server/8.0
Wed Oct 21 15:56:28 EDT 2015     DEBUG     Webapp context: /brokerage
Wed Oct 21 15:56:28 EDT 2015     DEBUG     JavaMelody version: 1.57.0
Wed Oct 21 15:56:28 EDT 2015     DEBUG     JavaMelody classes loaded from: file:/C:/workspace/FDXMay2015_RAD9/fdWeb/WebContent/WEB-INF/lib/javamelody.jar
Wed Oct 21 15:56:28 EDT 2015     DEBUG     Host: FDX-N...@10.101.102.28
Wed Oct 21 15:56:28 EDT 2015     DEBUG     parameter defined: log=true
Wed Oct 21 15:56:28 EDT 2015     DEBUG     parameter defined: datasources=java:comp/env/jdbc/XPSDBDataSource,jdbc/XPSDBDataSource
Wed Oct 21 15:56:28 EDT 2015     DEBUG     parameter defined: rewrap-datasources=true
Wed Oct 21 15:56:28 EDT 2015     DEBUG     log listeners initialized
Wed Oct 21 15:56:28 EDT 2015     DEBUG     datasources found in JNDI: [java:comp/env/jdbc/XPSDBDataSource, jdbc/XPSDBDataSource]
Wed Oct 21 15:56:28 EDT 2015     DEBUG     Datasource needs rewrap: java:comp/env/jdbc/XPSDBDataSource of class com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource
Wed Oct 21 15:56:28 EDT 2015     DEBUG     Datasource needs rewrap: jdbc/XPSDBDataSource of class com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource
Wed Oct 21 15:56:28 EDT 2015     DEBUG     counters initialized
Wed Oct 21 15:56:28 EDT 2015     DEBUG     counters data read from files in C:\Users\SHRI~1.PAL\AppData\Local\Temp\javamelody\brokerage_FDX-NY-1269
Wed Oct 21 15:56:28 EDT 2015     DEBUG     collect task scheduled every 60s
Wed Oct 21 15:56:28 EDT 2015     DEBUG     first collect of data done
Wed Oct 21 15:56:28 EDT 2015     DEBUG     JavaMelody filter init done in 353 ms

[Sri Palchuri]

I solved the issue by adding below snippet of code in our datasource initialization.

net.bull.javamelody.JdbcWrapper.SINGLETON.createDataSourceProxy(fDataSource);