Teradata Stored Procedure

Using the Stored Procedure stage, you can invoke Teradata stored procedures, macros, and functions from DataStage® jobs, but you cannot create Teradata stored procedures, macros, or functions directly from DataStage.

The properties tab

You can specify the following properties:
  • Transaction mode. The mode of connection to the Teradata server. This field is active only when the selected Database vendor is Teradata or a job parameter.
    • If Database vendor is Teradata, the options for Transaction mode are ANSI, the default, and Teradata. This field controls whether the Stored Procedure stage connects to the Teradata server in ANSI or Teradata transaction mode. A connection in ANSI transaction mode cannot call stored procedures that were compiled in Teradata transaction mode, and a connection in Teradata transaction mode cannot call stored procedures that were compiled in ANSI transaction mode.
    • If Database vendor is a job parameter, the options for Transaction mode are ANSI and Native. Native transaction mode is equivalent to Teradata transaction mode if the database vendor at runtime is Teradata. The Transaction mode field has no effect for other vendors.
      Note: If an error occurs in Teradata transaction mode, Teradata automatically rolls back the current transaction. In ANSI transaction mode, an error will not affect the current transaction.
  • Client character set. The Teradata client character set to use when connecting to the Teradata server. This field is active only when Database vendor is Teradata or a job parameter. The default value is Default.
You can execute the following procedure types:
  • Source. Source procedures emulate an SQL SELECT statement and use the column metadata as output parameters to read from the database. They have only an output link.
  • Target. Target procedures emulate an SQL INSERT statement and use the column metadata as input parameters to write to the database. They have only an input link.
  • Transform. Transform procedures use stored procedure properties to execute various logic decisions at the database. They can be used with an input link only, with an output link only, or with both input and output links. Specify the input and output parameters on the parameter grid.
You can specify how the stage handles errors or warnings during a job run by providing error codes. The stage checks the fatal list first. If you specify the same code in both lists, the job aborts. The lists should contain database-specific errors reduced to just the integer part of the error. You can also provide user-defined error codes that the procedure might return. If you leave the lists empty, the stage uses the database's standard of what is and is not fatal and operates in that manner. There are two categories of errors:
  • Fatal errors. Specify errors that should be treated as fatal. If it finds one of the codes identified as fatal, the job aborts.
  • Warnings. Specify errors that should be treated as warnings. The stage checks the list of warnings only after it checks the list of fatal errors. If the stage finds any of the codes identified as a warning, the stage writes a log entry and processing continues.

The parameters tab

The Parameters tab identifies and describes the parameters used when the Procedure type is Transform.

The Stored Procedures stage allows you to map input and output columns to the input and output parameters of the procedure via parameter specifications. The stage uses the information provided on the Parameters tab to build the stored procedure call and bind the internal variables. The Parameters tab is ignored if Procedure type is set to Source or Target because the column metadata is used to map the parameter information.

You can specify the following parameter types:
Input
The stage sends data from the link or a literal value to the stored procedure. The stage must contain an input link. Select Input to satisfy WHILE or WHERE clauses for the selected operation and to insert values in the table.
Output
The stage returns data or a result set from the stored procedure. The stage must contain an output link. Select Output to satisfy SELECT @param=10. The stored procedure returns single rows or specific output parameters.
Input/Output
The stage sends data from the link and returns data or a result set. The database must support the use of parameters that can send and return information. The stage must contain an input and an output link.