External Stored Procedures that Issue Operating System I/O Calls and Do Not Execute SQL - Analytics Database - Teradata Vantage

SQL External Routine Programming

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
iiv1628111441820.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1147
lifecycle
latest
Product Category
Teradata Vantage™

Here is a synopsis of the steps you take to develop, compile, install, and invoke an external stored procedure that issues operating system I/O calls:

  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. Determine the level of access to operating system services that the external stored procedure requires.
    IF the external stored procedure … THEN …
    accesses operating system resources that ordinary operating system users have access to the external stored procedure can run in protected execution mode as a separate process under 'tdatuser', a local operating system user that the Vantage installation process creates.
    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 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.

    IF you … THEN the CREATE PROCEDURE or REPLACE PROCEDURE statement …
    did not use CREATE AUTHORIZATION or REPLACE AUTHORIZATION in the previous step sets the default execution mode for the external stored procedure to protected mode.

    The external stored procedure runs under the tdatuser operating system user and can access the system resources for which tdatuser has privileges.

    used CREATE AUTHORIZATION or REPLACE AUTHORIZATION in the previous step must specify the EXTERNAL SECURITY clause to associate execution of the external stored procedure with the context created by the CREATE AUTHORIZATION or REPLACE AUTHORIZATION statement.

    The external stored procedure runs under the operating system user identified by the specified context and can access the system resources for which the user has privileges.

    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.

  4. Test the external stored procedure on the development or test system 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.