Using stored procedures

A stored procedure is a block of procedural constructs and embedded SQL statements. Use the Call procedure option under the Read method property of the Stage tab to call an existing stored procedure from a database. Use the Call procedure statement option to write your own procedure statement syntax in the text box.

Stored procedures in a source stage

Select Add procedure return value to schema to add the procedure return code to the schema as column ProcCode. The first column on the output link holds the value of the code. The procedure return code typically indicates whether the procedure succeeded or failed.

Stored procedures in a target stage

Select Execute procedure for each row to execute the procedure for each row of the input data. If you do not select Execute procedure for each row, the procedure will only execute once, for the first row. By default, Execute procedure for each row is selected.

Supported connectors

The following connectors support stored procedures.

  • IBM Db2®
  • IBM® Db2 Big SQL
  • IBM Db2 on Cloud
  • IBM Db2 Hosted
  • IBM Db2 for i
  • IBM Db2 Warehouse
  • IBM Db2 for z/OS®
  • Microsoft Azure SQL Database
  • Microsoft SQL Server
  • Oracle
  • Teradata
  • Teradata (optimized)