Question & Answer
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
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
Result set 2
- DEPTNAME
IBM TDI L2 Support
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
Column 1 Name >> DEPTNAME
- Value >> IBM TDI L2 Support
Related Information
[{"Product":{"code":"SSCQGF","label":"Tivoli Directory Integrator"},"Business Unit":{"code":"BU008","label":"Security"},"Component":"General","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"6.1.1;7.1;7.1.1;7.0","Edition":"","Line of Business":{"code":"LOB24","label":"Security Software"}}]
Historical Number
74779;49R;000
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21623519