Finding Unprotected Mode UDFs & Changing the Execution Mode | Teradata Vantage - 17.10 - Finding Unprotected Mode UDFs and Changing the Execution Mode - 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)

Teradata provides BTEQ scripts that you can use to generate a list of UDFs that currently execute in unprotected mode and change the execution mode.

The scripts are in the etc directory of the Teradata software distribution. To identify this directory, enter the following on the command line:

pdepath -e

To run the scripts, you must logon to BTEQ as user DBC. For example:

bteq .logon dbc,dbc_password

where dbc_password is the password for user DBC.

IF you want to … THEN use this script …
get a list of unprotected mode UDFs on the system udflist.bteq.

The script creates a file called udflist.sql in the current working directory. You must have write permission on the current working directory.

read a list of UDFs and change the execution mode to protected udf_u2p.bteq.

The script reads a list of UDFs from a file called udf_u2p.sql and changes the execution mode to protected. To create the contents of the udf_u2p.sql file, use the list of UDFs that the udflist.bteq script generates in the udflist.sql file.

read a list of UDFs and change the execution mode to unprotected udf_p2u.bteq.

The script reads a list of UDFs from a file called udf_p2u.sql and changes the execution mode to unprotected. To create the contents of the udf_p2u.sql file, use the list of UDFs that the udflist.bteq script generates in the udflist.sql file.

For example, suppose you installed two UDFs on the cs database that execute in unprotected mode: imult and isqr. If you run udflist.bteq, it produces a file called udflist.sql in the current working directory with the following contents:

/* -------------------------------------------------------------- */
/* List Of Unprotected Mode UDFs                                  */
/* -------------------------------------------------------------- */
'6373'xn.'696D756C74'xn /* cs.imult */
'6373'xn.'69737172'xn /* cs.isqr */

To change the execution mode of the UDFs to protected, copy the UDF names from udflist.bteq into a file called udf_u2p.sql in the current working directory:

'6373'xn.'696D756C74'xn /* cs.imult */
'6373'xn.'69737172'xn /* cs.isqr */

When you run udf_u2p.bteq, it reads the contents of udf_u2p.sql from the current working directory.