'); $("#tabs").tabs({ selected: 1 }); $("#ic-homepage__ic-tips").append( quickTipHTML() ); unhideOneProductTip(); $("#ic-homepage__product-tips").wrapInner('

'); $("#ic-homepage__feed-tips").wrapInner('

'); });

IBM Tivoli Monitoring > Version 6.3 > User's Guides > Agent Builder User's Guide > Monitor data from Java Database Connectivity (JDBC) IBM Tivoli Monitoring, Version 6.3

---

Stored procedures

Example SQL and DB2 stored procedures that you can use with the JDBC data provider.

The JDBC data provider can process the result sets returned by a stored procedure. String or integer input parameters can be passed to the stored procedure. The following syntax runs a stored procedure:

call[:index] procedureName [argument] ...

Where:

index

An optional integer that specifies which result set is to be used by the data provider. This parameter is useful when the stored procedure returns multiple result sets and you want to collect only the values from one of the result sets. If an index is not specified, data from each result set is collected and returned.

procedureName

The name of the stored procedure that is to be run by the JDBC data provider.

argument

An input argument to the stored procedure. Multiple arguments must be separated by a space. If the argument contains a space character, enclose the entire argument in double quotation marks. If the argument can be parsed as an integer, it is passed to the stored procedure as an integer argument. Any argument that is enclosed in double quotation marks is passed as a string argument.

SQL Server Samples

call sp_helpdb

Runs the procedure call sp_helpdb which requires no arguments. Data from all returned result sets are included in the data that is returned by the data provider. call:2 sp_helpdb master

Runs the procedure sp_helpdb with the master argument. This argument is a string input argument. Only data from the second result set that is returned by the stored procedure is included in the data that is returned by the data provider.

When the index is not specified, data from all returned results sets is collected. You must ensure that the data returned in these cases is compatible with the attributes you define. Agent Builder creates attributes from the first returned result set, and any further result sets are expected to be compatible with the first one.

DB2 stored procedure

Here is a sample DB2 function that is written in SQL. This function demonstrates how to return results that can be processed by the Agent Builder JDBC data provider:

-- Run this script as follows:
-- db2 -td# -vf db2sample.sql

-- Procedure to demonstrate how to return a query from -- a DB2 stored procedure, which can then be used by 
-- an Agent Builder JDBC provider. The stored procedure
-- returns the following columns:
-- Name               Description              Data Type
-- current_timestamp  The current system time  timestamp
-- lock_timeout       The lock timeout         numeric scale 0
-- user               The user for the session String 128 characters long
DROP procedure db2sample#

CREATE PROCEDURE db2sample()
  RESULT SETS 1
  LANGUAGE SQL
BEGIN ATOMIC
  
  -- Define the SQL for the query
  DECLARE c1 CURSOR WITH HOLD WITH RETURN FOR
  SELECT CURRENT TIMESTAMP as current_timestamp, 
CURRENT LOCK TIMEOUT as lock_timeout, CURRENT USER as user
  FROM sysibm.sysdummy1; 

  -- Issue the query and return the data
  OPEN c1;
END#

This function can be called from Agent Builder using the same syntax that is defined for other stored procedures. In this case, you define call db2sample as your JDBC statement to run this stored procedure.

Oracle stored procedures

Oracle stored procedures do not return result sets. Instead, you must write a function that returns an Oracle reference cursor. Here is a sample Oracle function that is written in PL/SQL that demonstrates how to return results that can be processed by the Agent Builder JDBC data provider:

CREATE OR REPLACE FUNCTION ITMTEST
RETURN SYS_REFCURSOR
IS
        v_rc SYS_REFCURSOR;
BEGIN
  OPEN v_rc FOR SELECT * FROM ALL_CLUSTERS;
  RETURN v_rc;
END;

This function can be called from Agent Builder using the same syntax that is defined for other stored procedures. In this case, you define call ITMTEST as your JDBC statement to run this stored procedure. Because the Oracle function must return a cursor reference, only one result set can be processed by Oracle functions. This means that the index option is not supported for Oracle because there is no way to return multiple result sets.

Parent topic:

Monitor data from Java Database Connectivity (JDBC)

---

+

Search Tips   |   Advanced Search