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:
- 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 Teradata Database. For more information, see C/C++ Command-line Debugging for UDFs.
- 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 Teradata Database 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. - 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 Teradata Database nodes in the system.
- Test the external stored procedure on the development or test Teradata Database until you are satisfied it works correctly.
- Install the external stored procedure on your production Teradata Database.
- Use GRANT to grant privileges to users who are authorized to use the external stored procedure.