17.10 - External Stored Procedures That Do Not Perform I/O or Execute SQL - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
English (United States)

Here is a synopsis of the steps you take to develop, compile, install, and invoke a C or C++ external stored procedure that does not use CLIv2 and does not perform I/O:

  1. Use CREATE PROCEDURE or REPLACE PROCEDURE to identify the location of the source code, object, or package, and install it on a development or test database.

    Recommendation: In general, you should not create external stored procedures in Teradata system databases such as SYSLIB or SYSUDTLIB. For details, see Installing a C/C++ External Stored Procedure.

    The external stored procedure is compiled, if the source code is submitted, linked to the dynamic linked library (DLL or SO) associated with the database in which the external stored procedure resides, and distributed to all database nodes in the system.

  2. Test and debug the external stored procedure in Vantage in protected execution mode until you are satisfied it works correctly.

    You can use the Teradata C/C++ UDF Debugger, which is a version of GDB (the gnu Source-Level Debugger) that contains extensions for the database. For more information, see C/C++ Command-line Debugging for UDFs.

    Protected mode is the default execution mode for an external stored procedure. In protected mode, Vantage isolates all of the data the procedure might access as a separate process in its own local workspace. If any memory violation or other system error occurs, the error is localized to the procedure and the transaction executing the procedure. This makes the procedure run slower.

  3. Use ALTER PROCEDURE to change the external stored procedure to run in nonprotected execution mode.
  4. Rerun the tests from Step 3 to test the external stored procedure in nonprotected execution mode until you are satisfied it works correctly.
  5. Install the external stored procedure on your production system.
  6. Use GRANT to grant privileges to users who are authorized to use the external stored procedure.