Imagine we have some major interfaces that use database connections and when database connections go offline, it causes transactions to fail in essential interfaces. This situation requires our operations team to invest significant effort in monitoring these connections and taking corrective measures. The complexity of this task escalates when we have multiple servers in the mix.
In this article, we will go through the step-by-step procedure of creating a utility that sends an email containing the JDBC connection status report.
Create Utility :
- Create a package with the name ‘HG_JDBC_Monitor’ and follow the below package structure.
- In the pub folder create a flow service with the name ‘monitorAdapterConnection’, as we will schedule this service to run in 1o min time interval so we will not supply any input to this service.
- Now we need to understand how our integration server gets adapter connection status in the JDBC Adapter page, if you open the Integration server page and go to the JDBC adapter page you can see in the backend which service is invoked to get these details.
- If you observe the above screenshot closely, you will notice that in the backend it is invoking the ListResources.dsp page which resides in the WmArt package with adapterTypeName as a parameter, so now we need to check the .dsp file and understand in the .dsp page which service is exactly called to get the JDBC adapter resource status.
- You can find the ListResources.dsp page in ‘C:\SoftwareAG\IntegrationServer\instances\default\packages\WmART\pub’ location, this location may change based on your installation directory.
- Now if you open the DSP page you will notice there are a lot many services invoked but you can find ‘wm.art.admin.connection:listResources‘ service which is actually getting the resource list along with resource status but if you check this service in the designer you will not able to find it because it is hidden.
- Before using this service please keep in mind that the services under WmArt or WmRoot are the private services used by webmethods, please use them at your own risk. Any change to those services will not be entertained by SoftwareAG and SoftwareAG will not provide support for the same but there are a lot of projects that use these private services to perform some sort of automation.
- As ‘wm.art.admin.connection:listResources‘ service is hidden, we can’t drag this and drop it in our flow service to use it, so right-click on the flow service canvas and click Insert -> Invoke.
- Now a new window will be opened so in the element name paste ‘wm.art.admin.connection:listResources‘ and click ok.
- Hardcode adapterTypeName as ‘JDBCAdapter’ in the service input and run the service.
- Now if you will notice we are able to extract the JDBC adapter report for both active and inactive connections.
- Now as we are going to send the email with a tabular report, we need to generate the HTML formatted data for which we will be using XSLT which works on XML so invoke ‘pub.xml:documentToXMLString‘ service to convert our output document to XML and follow below mappings.
- Now let’s create an XSLT service to generate the HTML table code for the JDBC connection report. If you’re new to XSLT, you can click here to learn more about it.
- Create an XSLT service in the priv folder with the name ‘transformToHTML’ using Basic Stylesheet – XSLT 2.0 template.
<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> <!-- TODO: Auto-generated template --> </xsl:template> </xsl:stylesheet>
- Please update the XSLT code as follows, where we have implemented logic to traverse through the XML, extract only disabled connection details, and generate an HTML table template.
<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> <!-- TODO: Auto-generated template --> <html> <body> <table border="1"> <thead> <tr> <th style="background-color: lightblue;">Connection Name</th> <th style="background-color: lightblue;">Status</th> </tr> </thead> <tbody> <!-- Here we will put our logic to traverse the xml and transformation --> <xsl:for-each select="ConnectionDetails/connDataNode"> <xsl:if test="connectionState = 'disabled'"> <tr> <td><xsl:value-of select="connectionAlias"/></td> <td style="background-color: red;">Disabled</td> </tr> </xsl:if> </xsl:for-each> </tbody> </table> </body> </html> </xsl:template> </xsl:stylesheet> - We have completed the HTML generation. Now, please invoke the XSLT service and follow the mapping instructions below.
- Invoke ‘pub.io:stringToStream’ to transform our HTML report to a stream object so that we can use the same to send emails.
- Invoke ‘pub.client:smtp’ service and follow the below mapping to send the report over email.
- to: recipient email id.
- subject: subject of your email.
- from: sender email id.
- mailhost: host of your email server.
- mailhostPort: port of your email server.
- auth/type: Basic.
- auth/user: username.
- auth/pass: password.
- secure/transportLayerSecurity: implicit.
- attachments[0]/content: HTML string as Stream object.
- attachments[0]/contenttype: text/html.
- Let’s run the service and see if we are able to send an email with the report.
- Congratulations !! We did it.
- Now let’s schedule this service to run the service in a 10-minute interval and this interval you can update based on your requirement and the interface impact.
- Open your integration server and click the Scheduling option under the Server tab.
- Now, click on the ‘Create a Scheduled Task’ button and configure it as shown in the screenshot below.
- After configuring the scheduler and clicking ‘Save,’ it will become visible, and you’ll receive email notifications at 10-minute intervals.
Hope you have enjoyed this article and found this useful, please click the below link to download the source code.
