IBM Support

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

Directory Integrator Reference Guide
Java SQL Interface

Historical Number

74779
49R
000

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): Platform Independent

Reference #: 1623519

Modified date: 25 April 2014