You can use an ODBC connection in a mapping to call a stored procedure or process saved queries when you connect to Teradata.
When you configure the ODBC connection, you must select the ODBC subtype as Teradata. You can then use the ODBC connection in a SQL transformation to call a stored procedure or to process saved queries.
You can use the SQL transformation to process SQL queries midstream in a pipeline. You can configure the SQL transformation to process the following types of SQL statements:
Stored procedure
Stored procedures reside in the database and run within the database. When you configure the SQL transformation to process a stored procedure, it passes input parameters to the stored procedure. The stored procedure passes the return value or values to the output fields of the transformation.
SQL Query
You can configure the SQL transformation to process a saved query that you create in Data Integration or you can enter a query in the SQL editor.
You can also parameterize the ODBC connection with the Teradata ODBC subtype in an SQL transformation.
For more information about SQL transformations, see Transformations.
Rules and guidelines for calling a stored procedure
Consider the following rules and guidelines for calling a stored procedure using the ODBC subtype as DB2 or Teradata:
•You can't configure an unconnected stored procedure using the SQL transformation.
•When you use an SQL transformation to call a stored procedure in DB2 or Teradata, ensure that the stored procedure definitions do not contain keywords, special characters, and Unicode characters.
•You can't process a stored function in an SQL transformation.
•You can't configure the input or in-out parameter in an entered query that you define in the SQL editor.
•When you use DB2 subtype, you cannot call overloaded stored procedure using an SQL transformation.