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 or Write 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.
Supported connectors
The following connectors support stored procedures. Stored procedures can be run on connectors with input links, output links, or both.
- IBM Db2®
- IBM® Db2 Big SQL
- IBM Db2 on Cloud
- IBM Db2 for i
- IBM Db2 Warehouse
- IBM Db2 for z/OS®
- Google BigQuery
- Microsoft Azure SQL Database
- Microsoft SQL Server
- Oracle
- SAP ASE
- Teradata
Stored procedures in a source stage
Select Add procedure return value to schema to add the procedure return code and message to the schema as columns ProcCode and ProcMess. The first column on the output link holds the value of the code. The second column holds the value of the message. If you also select Forward row data, the input columns are added to the output link after the ProcCode and ProcMess columns.
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.
Syntax
?
as a parameter marker. The following syntax calls a
stored procedure with name <procedure_name>
on an input parameter
@<column_name>
and output column ?
, both in
parentheses.CALL <procedure_name>(@<column_name>, ?)
If
Add procedure return value to schema is selected, the procedure assigns the
return code of the procedure to the first column on the output link, marked by the?
before the =
. This statement would be used in a stage with both input links and
output
links.? = CALL <procedure_name>(@<column_name>, ?)
? = CALL <procedure_name>('@<string_column_name>', ?)
If the stored procedure is called in a stage with only output links then ?
always indicates an output parameter. If the stored procedure is called in a stage with only input
links then ?
always indicates an input parameter.
CALL <procedure_name>()
CALL <procedure_name>(?, ?)
CALL <procedure_name>(4,?)
Parameter values
In the Parameter values section of the Stage tab, use parameter specifications to map the input and output parameters of your stored procedure to input and output columns. Parameter names in Stage tab should match to parameter names in your stored procedure. Each parameter name can be mapped to a single column. A connector with an input link will have input parameters, a connector with an output link will have output parameters, and a connector with both input and output links will have both. Google BigQuery only supports input parameters.
Query timeout
For long-running queries and stored procedures, set the Query timeout
property to limit the number of seconds that a procedure runs for. The default value is 300 seconds.
If you set a value greater than 600 seconds, you must also edit the environment variable
$OSH_JOB_START_TIMEOUT
and set a value higher than the Query
timeout. This property is available on Microsoft SQL Server, Microsoft Azure SQL
Database, IBM Db2, IBM
Db2 Big SQL, IBM Db2 on Cloud, IBM Db2 for i, IBM Db2
Warehouse, and IBM Db2 for z/OS.
User-defined functions
When Call procedure or Call procedure statement is selected, you can select User defined function to specify a user-defined function to execute. A user-defined function can take input parameters and returns either a single value (for scalar functions) or a set of values (for table functions). This property is available on Db2, IBM Db2 Big SQL, IBM Db2 on Cloud, IBM Db2 for i, IBM Db2 Warehouse, IBM Db2 for z/OS, and Teradata. In IBM Db2 for z/OS, user-defined functions must be called as part of a SELECT statement. In Teradata, macro-type functions are also available.