Procedure

Use the following procedure to add a JDBC data source:

1. On the Agent Initial Data Source page (Figure 167) or the Data Source Location page, click Data from a server in the Monitoring Data Categories area.
   Figure 167. Adding JDBC data

   
2. In the Data Sources area, click JDBC.
3. Click Next.
4. On the JDBC Information area in the JDBC Information page (Figure 168), click Browse to connect to a database and build your SQL Query.

   With the JDBC Browser you can connect to a database and view its tables so you can easily build an SQL query that collects the data that you need. When you select a table and columns, a query is generated for you. You can modify and test the query that is generated to make sure the data that is returned is what you need.

   Note:

   You can also manually create the JDBC Data Source without clicking Browse. If you want to manually create the data source, specify the query and click Next. You must define the first attribute for this data source right away in the wizard. Other attributes can be added to the data source later.

   With the JDBC data provider you can run SQL queries and stored procedures against a database to collect monitoring data. When you specify an SQL query to collect data you can include a where clause in your SQL statement to filter the data that is returned. The SQL statement can also join data from multiple tables. In addition to SQL select statements, the JDBC data provider can run stored procedures. For information about running stored procedures, see Stored procedures.

   Figure 168. JDBC Information page

   
5. The first time the Browser is launched, the Java Database Connectivity (JDBC) Browser window (Figure 169) indicates that no connections are selected. You must add a connection, so click Add and perform the Steps to add a connection. If you have a connection already defined that connection is used and you can proceed to Step 6.
   Note:

   The Status field shows the status of the current connection.
   Figure 169. JDBC Browser window

   

   Steps to add a connection:

   1. On the JDBC Connections page (Figure 170), click New Connection, and click Next.
      Figure 170. Database Connection Wizard: JDBC Connections page

      
   2. On the Connection Properties page (Figure 171), complete the fields as follows:

      Name

      Name of the JDBC connection. Type a unique name for this connection. You use this name to reference the connection in the browser.

      Database Type

      Type of database. Select the database product to which you are connecting. For example, to connect to the IBM® DB2® database, select DB2.

      User Name

      Must be defined with at least read access to the database, but does not need to be the database administrator

      Password

      Must be defined with at least read access to the database, but does not need to be the database administrator

      Hostname

      Host name on which the database server is running. With JDBC you can monitor remote databases so you are not restricted to monitoring databases on the local system.

      Port

      Port on the host name on which the database server is listening.

      Database

      Name of the database to which to connect.

      Jar Directory

      Directory containing the JDBC driver jar files needed to connect to the database. Type the path name, or click Browse to locate the directory.

      (Optional) Select the Set as agent configuration defaults check box if you want the defaults for this application server type to be copied from these properties.

      After completing the fields, click Test Connection to create a connection to the database using the configuration parameters you have specified. A message on the Connection Properties page indicates whether the connection succeeds. When you have a working connection, click Finish.

      If you are building the agent on a system that is similar to your monitored systems, it is good to check this box. If you do not, the user configuring the agent is faced with an empty field and must determine the values for all of the information without default values.

      Figure 171. Database Connections Wizard: Connection Properties page

      
6. In the Java Database Connectivity (JDBC) Browser window (Figure 172), a connection is made to the configured database. The tables contained in the database are shown in the Database Tables area. Select a database table to see the columns contained in that table in the Columns in the selected table area.
   Notes:

   1. Click the binoculars icon to search for a table in the Database Tables list.
   2. All tables are shown by default. You can filter the tables shown by selecting a different filter option. The available filter options are shown in Table 12.

      Table 12. Filter options

      Filter option	Description
      All	Show all tables
      User	Show only user tables
      System	Show only system tables
      View	Show ony database views

   Figure 172. Java Database Connectivity (JDBC) Browser window

   
   Note:

   Select only columns if you want to retrieve specific columns. If you select the table, it automatically builds a query that gathers all of the columns from the table.

   You can select columns in the following ways:

   • Select the table and get the default query for all columns.
   • Select columns to get only those columns.
   • Click Test to modify the query by hand (with the addition of Where clauses, and so on), and test the result.
7. Click Test (Figure 173) to test the query.
   Figure 173. Test Command window

   

   Continue modifying the query and testing the query until you are satisfied with the data being returned. Selecting OK in the Test Command window indicates that you are finished browsing and takes you back to the JDBC Browser window.

8. On the JDBC Information page (Figure 174), Operating Systems section, select the operating systems, and click Next.

   See Specifying operating systems for information about which operating systems to select.

   Note:

   Click Insert Configuration Property to select a property to insert. For more information see Customizing configuration.
   Figure 174. JDBC Information page

   
9. On the Select key attributes page, select key attributes or indicate that this data source produces only one data row. See Selecting key attributes for more information.
10. If you want to test a data source that you previously defined, in the Agent Editor window (Figure 129), select the Data Sources tab and select a JDBC data source. In the JDBC Attribute Group Information area, click Test.
    Figure 175. Data Source Definition page Attribute Group Information

    
11. If you want to view the configuration sections that were automatically generated, click the Runtime Configuration tab of the Agent Editor (Figure 176). You can change the labels or default values for these configuration properties to match the defaults you want a user to see when they initially configure the agent.
    Figure 176. Runtime Configuration Information page, Configuration for Java Virtual Machine (JVM)

    
12. (Optional) If you chose to manually create the JDBC Data Source without clicking Browse in Step 4, complete the information on the Attribute Information page using Table 6.
    Figure 177. Attribute Information page

    

The Agent Builder JDBC Data Source supports collecting data from most SQL types. The information in Table 13 describes the type of attribute that is created by the JDBC Browser when it detects a column of one of these types. These are the supported data types for use with a monitoring agent.