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

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