How to create a custom MBean from an SQL Query in WebLogic server using WLSDM Generic DevOps

WLSDM for WebLogic
Jan 28, 2019 · 4 min read

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 Name = SALARYBYJOB

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

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

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

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)

Query:

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

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

7.Click on the dashboard as we created above

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

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

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
Website: catgovind.com
Original Post: http://www.catgovind.com/wlsdm/how-to-create-a-custom-mbean-from-an-sql-query-in-weblogic-server-using-wlsdm-generic-devops/

WLSDM for WebLogic

WLSDM is a WebLogic console extension which enables…

WLSDM for WebLogic

Native Oracle WebLogic Monitoring WLSDM is developed by the best WebLogic developers for the best WebLogic administrators to support mission critical HA domains. Supports all plain WebLogic and Fusion Middleware (FMW) WebLogic domains. Download now! https://wlsdm.com/download

WLSDM for WebLogic

Written by

WLSDM for WebLogic

Native Oracle WebLogic Monitoring WLSDM is developed by the best WebLogic developers for the best WebLogic administrators to support mission critical HA domains. Supports all plain WebLogic and Fusion Middleware (FMW) WebLogic domains. Download now! https://wlsdm.com/download

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store