Create a JDBC agent using Agent Builder

Body

If you want to create an Agent Builder agent that collects data using JDBC, below are the steps. We will use this agent to collect data from Microsoft SQL Server.

1° in the Agent Builder, start creating a new agent: File -> New -> Agent.
Click Next
2° specify a project name, and modify the location, if needed
Click Next
3° specify the operating system(s) to run on.

Note: In case your SQL Server is using SQL accounts for authentication, you are not bound to an operating system, but if you are using Windows authentication, the easiest way would be to select the Windows operating system, unless you apply the workaround described here:
Connecting to Microsoft SQL Server using integrated authentication is failing

Click Next
4° specify the agent information, and specify if you need multiple instances
Click Next
5° select the 'Data from a server' monitoring data category and 'JDBC' data source
Click Next
6° specify the desired SQL statement or stored procedure in the JDBC statement. Or, if you are not sure yet about the syntax of the SQL query to run, you can just click the 'Browse' button.

In this case we will run the sp_helpdb stored procedure on the master DB.

7° Click on the 'Browse' button to test the stored procedure. A new window will open where you can test the stored procedure.
8° In case a database connection does not exist yet, no SQL tables will be listed, and you will have to click the 'Add' button to create a connection to the SQL Server. A new wizard will start where you can enter the database connection details. Here's an example:

9° Complete the fields with the appropriate values. Check with your SQL administrator to know the required values.

Important to know here, is that the host name and port number should be entered correctly, corresponding to the SQL Server configuration. If you want to connect to the default instance, the hostname should suffice; in case it is not the default instance, you will have to specify the SQL Server in the format hostname\instance. In this case we are connecting to hostname\instance.

Another important thing is the port number. If dynamic ports are in use by the SQL Server, then you should specify '0', otherwise you will have to specify the fixed port (1433 by default).
In the 'JAR Directory' you will have to specify the location of the Microsoft JDBC Driver for SQL Server: You can download this from:
https://www.microsoft.com/en-us/download/details.aspx?id=11774       

The latest version is v6.0, but you can also use v4.0, which is the minimum required version. Let's use v6.0 in this case.
Download the v6.0 version, and unzip it to for example C:\temp\Microsoft JDBC Driver 6.0 for SQL Server\
Then, in the agent builder specify this JAR directory:
for JRE 7: C:\temp\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\jre7

The agent builder does not support JRE 8, so you will have to use the JDBC driver that is in the jre7 folder.
Click the 'Test Connection' button to test the connection with the SQL Server.

In case the test goes fine, close the window with the 'OK' button.

10° test the SQL query or stored procedure by clicking on the test button. If wanted, you can also select another table in the selected database. But let's continue with the 'sp_helpdb master' statement.

11° Run the SQL statement by clicking on the 'Run' button.
If all goes fine, the lower part of the window will have a table which will have the data resulting from running the stored procedure. For example:


The columns will be mapped to attributes.

If this is the expected result, close the window by clicking on the 'OK' button

12° click the Next button. A window will appear that contains the columns:

Either select 'Can produce more than one data row' or 'Produces a single data row', depending on whether the stored procedure returns one row or multiple rows in its output.  'Produces a single data row' is the required option when you want to create APM dashboards. In the other case you can select 'Can produce more than one data row', but then you will also have to select a key attribute.
Click the Next button

13° verify if the attributes are the expected ones, and modify if needed
Click the Finish button

14° Next you need to prepare the agent for IBM Performance Management by following this procedure:
https://www.ibm.com/support/knowledgecenter/SSHLNR_8.1.3/com.ibm.pm.doc/builder/ab_oslc_task.htm

You are now ready to generate the agent: Agent Editor -> Generate Agent :


Install the agent on the local system, and/or generate a ZIP/TAR image that you can transfer to another system to install it on that system.

We are using Agent Builder 6.3.3, so on condition that the Self-Describing Agent (SDA) feature is enabled on the TEMS, the agent will automatically update the application support on the TEMS and the TEPS, as soon as it connects.

If your Agent Builder does not support the SDA feature, you will have to copy the ZIP/TAR file to the TEMS and the TEPS, and install the application support for this agent.

Hope this helps.