17.10 - External Stored Procedures that 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 uses CLIv2 to execute SQL:

  1. Write, test, and debug the C or C++ code for the external stored procedure.

    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.

  2. If the external stored procedure requires access to specific operating system resources, use CREATE AUTHORIZATION or REPLACE AUTHORIZATION to create a context that identifies a native operating system user and allows external stored procedures to perform I/O by running as separate processes under the authorization of that user.
  3. Use CREATE PROCEDURE or REPLACE PROCEDURE with options that provide specific information about the external stored procedure.
    Option Description
    • NO SQL (Default)
    • CONTAINS SQL
    • READS SQL DATA
    • MODIFIES SQL DATA
    Indicates whether the external stored procedure can execute SQL statements and whether those statements can read or modify SQL data in the database.
    • LANGUAGE C
    • LANGUAGE CPP
    Identifies the source code language of the external stored procedure.
    • PARAMETER STYLE SQL (Default)
    • PARAMETER STYLE TD_GENERAL
    Indicates whether the external stored procedure can accept null IN or INOUT arguments or return null IN or INOUT arguments.
    DYNAMIC RESULT SETS (Optional) Specifies the number of result sets that the external stored procedure returns.
    EXTERNAL NAME Provides the location of the source code and specifies the Teradata CLI package name.
    EXTERNAL SECURITY (Optional) Associates execution of the external stored procedure with the context created by the CREATE AUTHORIZATION or REPLACE AUTHORIZATION statement in the previous step.

    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, linked to a CLI-specific external stored procedure 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.

  4. Test the external stored procedure until you are satisfied it works correctly.
  5. Use GRANT to grant privileges to users who are authorized to use the external stored procedure.