Return data from a DB2 Stored Procedure Call in IBM Tivoli Identity Directory Integrator (TDI)

Technote (FAQ)


Question

How do I call a DB2 Stored Procedure and return the results to TDI?

Answer

The Info Center contains a sample of a stored procedure call from TDI. The example returns a true or false based on the successful execution.

The following example returns values from the stored procedure. The sample is written in TDI 6.1.1, but may be used in later versions of TDI and/or DB2.

ENVIRONMENT: TDI 6.1.1 and DB2 9.1.3
STORED PROCEDURE:

    db2 =>
      CREATE PROCEDURE TESTMULTIRS2 (IN i_cmacct CHARACTER(5)) RESULT SETS 2 LANGUAGE SQL BEGIN DECLARE csnum INTEGER;
      DECLARE getDeptNo CHAR(50); DECLARE getDeptName CHAR(20);
      DECLARE c1 CURSOR WITH RETURN FOR s1;
      DECLARE c2 CURSOR WITH RETURN FOR s2;
      SET getDeptNo = 'SELECT DEPTNO FROM DEPT';
      SET getDeptName = 'SELECT DEPTNAME FROM DEPT';
      PREPARE s1 FROM getDeptNo; OPEN c1;
      PREPARE s2 FROM getDeptName; OPEN c2;
      END
    DB20000I The SQL command completed successfully.
    db2 =>
To execute the procedure in DB2:
    C:\Program Files\IBM\SQLLIB\BIN>db2
      db2 => connect to testtdi
      Database Connection Information
      Database server = DB2/NT 9.1.3
      SQL authorization ID = ADMINIST...
      Local database alias = TESTTDI

      db2 => call testmultirs('arc')
      Result set 1
        DEPTNO
        U2LA
      1 record(s) selected.
      Result set 2
        DEPTNAME
        IBM TDI L2 Support
      1 record(s) selected.
      Return Status = 0
      db2 =>

In the TDI Configuration editor, Create a JDBC connector in Iterator mode passive state.

SAMPLE JDBC CONNECTOR CONFIGURATION

Section: General
jdbcSource = jdbc:db2://mydb2hosname.com:50000/testtdi
jdbcDriver = com.ibm.db2.jcc.DB2Driver
jdbcLogin = administrator
jdbcPassword = password
jdbcSchema = null
jdbcTable = DEPT

SAMPLE CODE FOR A SCRIPT COMPONENT

var con = jdbcTovTulip.connector.connection;
// java.sql.Connection where jdbcTovTulip is the Connector component name
var cstmt // java.sql.CallableStatement
var resultSet // java.sql.ResultSet;

try {
// PreparedStatement call
cstmt = con.prepareCall("{call TESTMULTIRS(?)}");
cstmt.setString(1, 'arc');

success = cstmt.execute();
task.logmsg("Results from execute >> " + success);
if (!success) return 0;
var more = true;
var num = 0;
while (more) {
num++;
task.logmsg("ResultSet " + num)
resultSet = cstmt.getResultSet();
metaData = resultSet.getMetaData()
var columns = metaData.getColumnCount();
while (resultSet.next()) {
for (i=1; i <= columns; i++) {
        task.logmsg("Column " + i + " Name >> " + metaData.getColumnName(i));
        task.logmsg("Value >> " + resultSet.getString(i));
}
}
more = cstmt.getMoreResults(); }
}
catch(e) { task.logmsg("Exception message: " + e); }

RESULTS AFTER RUN IN CONSOLE:
Results from execute >> true
ResultSet 1
Column 1 Name >> DEPTNO
        Value >> U2LA
ResultSet 2
Column 1 Name >> DEPTNAME
        Value >> IBM TDI L2 Support

Related information

TDI JDBC Reference
Java SQL Interface

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

IBM Security Directory Integrator
General

Software version:

6.1.1, 7.0, 7.1, 7.1.1

Operating system(s):

All Platforms

Reference #:

1623519

Modified date:

2013-02-06

Translate my page

Machine Translation

Content navigation