Oracle SQL Run-time Transformation: SQL Result Set to Java JMX MBeans (by Govindan Padmanaban)

In my previous post, I have explained creating a custom MBean in WebLogic using Shell/Batch script using WLSDM. This post is the continuation of my earlier post, about creating a WebLogic MBean from an SQL query. We don’t require to write a single line of Java code or follow any MBean rules to create and register an MBean. All we need is SQL Query skills and navigation knowledge of WLSDM.

In this post, we are going to register a WebLogic MBean using WLSDM. After that, we can view the MBean attribute dynamically at runtime in WLSDM smart dashboard. Also, we can access this MBean attributes in external Java application. Let’s begin with an example

Here we are going to Create an MBean which read the total salary of IT Programmer.


MBean attribute name = TOTAL_SALARY

SQL Query =  select job_id, sum(salary) as TOTAL_SALARY from employees where job_id = ‘IT_PROG’ group by job_id  

Create Custom MBean from SQL Query using WLSDM

1.First, we need to go to the WLSDM dashboard. Assume we have installed the WLSDM already.

2.Once you installed the WLSDM, WLSDM Dashboard and Monitoring will appear like below in the WebLogic console. Click on the link as shown in the red rectangular box which will navigate you to the WLSDM Dashboard Console

WebLogic console extension WLSDM

3.The WLSDM Dashboard console will appear like below, click on the Monitoring & Diagnostics under Configurations as shown below

WebLogic WLSDM Configuration > Monitoring & Diagnostics

4.Click on the tab Generic DevOps MBeans tab, here you will see all the pre-created custom MBeans. Now click on the four dots on the right side and click on the New Generic DevOps MBean as shown below

WebLogic DevOps MBean: WLSDM Generic MBean

5.A dialog box opens like below. Select Type as SQL Script and move the servers to the applied servers. The MBean will be registered to the servers that you are moved right.

6.Choose the Data Source connection. I have a data source HR created in the WebLogic. If you want different database connection, then follow the previous link and create a data source in WebLogic for your database. Select SQL Parse Method as Horizontal Fetch and input as SQL query. The query must return a single record, otherwise, WLSDM picks the first record from the result set. Also, the record must have one or more numeric value So we can display the numeric data in the WLSDM smart dashboard.

Here I wrote a query that fetches total salary allocated for IT programmers in a company (HR schema comes with Oracle database)


select job_id, sum(salary) as TOTAL_SALARY from employees where job_id = ‘IT_PROG’ group by job_id  

Horizontal Fetch = Column name as the Attribute name, and Column value as the attribute value. Only the first record in the result set is accepted.

Vertical Fetch = First column values in the result set are Attribute Names and Second column values in the result set are the Attribute value. Multiple records in the result set are accepted

Read the 2.1 and 2.2 in WLSDM DevOps tutorial for Horizontal and Vertical Fetch

Transform SQL to Java JMX MBean via WLSDM for WebLogic

6.Here, I have created a Dashboard name, MBean Name and selected each attribute in the different chart.

WebLogic JMX Chart Options

7.Click on the dashboard as we created above

Create WebLogic JMX MBean Dashboard in WLSDM for WebLogic

8.The above-created dashboard render the query value as shown below

WebLogic WLSDM DevOpsMBean: Dynamic WebLogic JMX Registration

9.I increased an employee salary to 90000. Now the total salary of IT Programmers is 109800

Oracle SQL Query to Oracle WebLogic Server JMX MBean Transformation

10.I refreshed the WLSDM dashboard. As you see there is a spike in the Total salary. WLSDM updates the value at runtime.

WLSDM is a powerful add-on tool for WebLogic server. This section we talked about creating MBean from an SQL Query using WLSDM. WLSDM has more features as well as Monitoring thread locks, Automatic WebLogic server restart when CPU or Memory is full, Server Health etc. We will cover each one in detail in upcoming posts.

Author: Govindan Padmanaban
Original Post: