Monitoring JDBC Connection Pools on WebSpere 7

Question

Recently we started in production with a new application hosted in several WebSphere Aplication Server and would be nice to have them monitored/graphed with more or less the same parameters we monitor/graph our Jboss servers.

Right now I managed to monitor several points with wsadmin using jython scripts:

• Java HEAP
• Issue a "Test connection" to all datasources

However I'm not able to find the way to monitor JDBC Connection Pools, to check PoolSize, WaitingThreadCount, and FreePoolSize values. I can monitor them on RealTime using Tivoli Performance Viewer included in the WAS DMGR:

But I didn't find the way to get it through wsadmin (or any other way) so I can obtain the values and add to Cacti/RRD to obtain graphs like we already have with Jboss:

Does anyone managed to get this data from websphere with wsadmin or any other tools?

Answer 1

if you really insist on wsadmin

A number of WAS MBeans exposes stats attribute. This attribute represents runtime statistics of the component. In your case, the MBean type would be JDBCProvider and its stats object implements javax.management.j2ee.statistics.JDBCStats interface defined in JSR-77.

Having that stats attribute value at hand, you'll be able to extract all other data.

One important note: in wsadmin you'll need to use getAttribute_jmx function of AdminControl, not just getAttribute.

Advertisement mode Working with wsadmin and MBeans can be tough, especially when it comes to accessing complex attributes. You may find this process easier with WDR.

Other options

Starting wsadmin process periodically only to query one or two attributes seem add too much overhead to me. An alternative is to install some code in your WSAS that could expose those stats in easily consumable way. One such tool is Jolokia. Jolokia is a web application exposing MBeans via HTTTP, using XML or JSON formats. Having Jolokia running in your WSAS, you can simply query it periodically from any programming language and then feed your time series choice.

Obviously, WSAS has its own specifics: extra MBeanServer, security, hence you'll need extra descriptors and code. Also, by default Jolokia can't serialize JSR-77 objects, so you'll need to provide those serializers yourself. I've been using Jolokia with WSAS in the past and all of those missing parts can be found in a clone of Jolokia repo. Roland Huss (the author of Jolokia) implemented some of those (excluding EAR and WSAS descriptors) in Jolokia-Extra project.

Answer 2

Finally I managed to obtain the stats following these addresses https://www.ibm.com/developerworks/websphere/techjournal/1112_guillemenot/1112_guillemenot.html?ca=drs-

Basically I've created a function to get the statistics about JDBC connection pools I need:

def getStats(server,driver,datasource):
        perfStr = AdminControl.queryNames( 'type=Perf,process=' + server + ',*')
        if perfStr == "":
                print "Sorry I can't find server " + server
                sys.exit(1)
        perfObj = AdminControl.makeObjectName( perfStr)
        srvrStr = AdminControl.queryNames( 'type=Server,process=' + server + ',*')
        srvrObj = AdminControl.makeObjectName( srvrStr)
        stats = AdminControl.invoke_jmx( perfObj, 'getStatsObject', [ srvrObj, java.lang.Boolean('true')], ['javax.management.ObjectName', 'java.lang.Boolean'])
        try:
                waitingThreads=stats.getStats('connectionPoolModule').getStats(driver).getStats(datasource).getStatistic('WaitingThreadCount').getCurrent()
                poolSize=stats.getStats('connectionPoolModule').getStats(driver).getStats(datasource).getStatistic('PoolSize').getCurrent()
                freePoolSize=stats.getStats('connectionPoolModule').getStats(driver).getStats(datasource).getStatistic('FreePoolSize').getCurrent()
                percentUsed=stats.getStats('connectionPoolModule').getStats(driver).getStats(datasource).getStatistic('PercentUsed').getCurrent()
                print "WaitingThreadCount=" + str(waitingThreads) + ", PoolSize=" + str(poolSize) + ", FreePoolSize="  + str(freePoolSize) + ", PercentUsed=" + str(percentUsed)
        except:
                print "Ooops, something went wrong :("
                raise

And in order to identify server, driver and datasource variables I also added a function to list them:

def listServers():
        """List the servers Database Connection Pools"""
        servers = AdminControl.queryNames( 'type=Perf,*').split("\n")
        for i in range(0, len(servers)):
                srvName = servers[i].split(",")[1].split("=")[1]
                if srvName == "nodeagent":
                        continue
                print "Server: " + srvName
                perfStr = AdminControl.queryNames( 'type=Perf,process=' + srvName +',*')
                perfObj = AdminControl.makeObjectName( perfStr)
                srvrStr = AdminControl.queryNames( 'type=Server,process=' + srvName +',*')
                srvrObj = AdminControl.makeObjectName( srvrStr)
                stats = AdminControl.invoke_jmx( perfObj, 'getStatsObject', [ srvrObj, java.lang.Boolean('true')], ['javax.management.ObjectName', 'java.lang.Boolean'])
                for driver in stats.getStats('connectionPoolModule').subCollections():
                        print "\tDriver Name: " + driver.getName()
                        for datasource in stats.getStats('connectionPoolModule').getStats(driver.getName()).subCollections():
                                print "\t\tDatasource: " + datasource.getName()

Which will output something like:

Server: APP_CLUSTER_APP01
        Driver Name: Oracle JDBC Driver (XA)
                Datasource: jdbc/AppEngine
                Datasource: jdbc/AppEngineH
Server: APP_CLUSTER_APP02
        Driver Name: Oracle JDBC Driver (XA)
                Datasource: jdbc/AppEngine
                Datasource: jdbc/AppEngineH
Server: SOL_CLUSTER_SOL01
        Driver Name: Oracle JDBC Driver (XA)
                Datasource: jdbc/dict1ds
                Datasource: jdbc/dict2ds
                Datasource: jdbc/dict3ds
Server: SOL_CLUSTER_SOL02
        Driver Name: Oracle JDBC Driver (XA)
                Datasource: jdbc/dict1ds
                Datasource: jdbc/dict2ds
                Datasource: jdbc/dict3ds