Usage Notes - Advanced SQL Engine - Teradata Database

Procedure for Distributing Packages

Here is a synopsis of the steps you take to distribute a package to all nodes of an MPP system. For a complete example, see wmp1593728001994.html#jic1472240858591__exdistributepkg.

1. On the target server, modify the access privileges of the directory for the server-side destination file to allow access by the user 'tdatuser'. (The path to the server-side destination file uses the path argument of the installsp stored procedure.)

   Alternatively, to distribute a package under the authorization of an operating system user other than 'tdatuser', follow these steps:

   1. Use the CREATE AUTHORIZATION or REPLACE AUTHORIZATION statement to create a context that identifies an operating system user to use for package distribution.
   2. Use the following SQL statement to get the definition of the SYSLIB.INSTALLPKG UDF (The installsp stored procedure calls the installpkg UDF.):

      SHOW FUNCTION SYSLIB.INSTALLPKG;

   3. Use the REPLACE FUNCTION statement to modify the SYSLIB.INSTALLPKG definition to include the EXTERNAL SECURITY clause, associating execution of the UDF with the context created by the CREATE AUTHORIZATION or REPLACE AUTHORIZATION statement.
   4. On the target server, modify the access privileges of the directory for the server-side destination file to allow access by the user established by the CREATE AUTHORIZATION or REPLACE AUTHORIZATION statement. (The path to the server-side destination file uses the path argument of the installsp stored procedure.)

   For details on CREATE AUTHORIZATION, REPLACE AUTHORIZATION, REPLACE FUNCTION, and the EXTERNAL SECURITY clause, see Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184.

2. Start lobteq on your Windows client system.

   The interface and commands are similar to BTEQ.

3. Log on to the target database.

   Use the same LOGON command that you use in BTEQ. For details on the BTEQ LOGON command, see Basic Teradata® Query Reference, B035-2414.

4. Use the lobteq .using command to open the package and load it into a data buffer for the USING modifier of the next CALL statement.

   The .using command supports the following syntax for opening a package:

   .using 'filename'xfile

   where filename is the filename, including the path, of the package.

5. Call the SYSLIB.installsp stored procedure.

   Use a USING modifier with the CALL statement to pass the package as a LOB in deferred mode to the server.

6. Exit lobteq.
7. [Optional] On the server, modify the access privileges of the directory for the server-side destination file to remove access by the operating system user used for package distribution.

Installing a Distributed Package

After you distribute your package, you can use the appropriate CREATE/REPLACE statement to install the package on the server. For example, after you distribute a UDF package, use the CREATE FUNCTION or REPLACE FUNCTION statement to install the UDF package on the server.

Teradata provides a stored procedure that you can use to save the CREATE/REPLACE statement with the package and version information in the dem, demddl, and dempart tables in the SYSLIB database. For details, see Procedure for Backing Up a Package.