installsp Stored Procedure - Advanced SQL Engine - Teradata Database

Purpose

Use the installsp stored procedure to distribute a package to all nodes of an MPP system.

Definition

REPLACE PROCEDURE installsp
   (IN  name      VARCHAR(250) CHARACTER SET LATIN,
    IN  version   VARCHAR(250) CHARACTER SET LATIN,
    IN  filename  VARCHAR(250) CHARACTER SET LATIN,
    IN  path      VARCHAR(250) CHARACTER SET LATIN,
    IN  source    BLOB(200000),
    IN  operation VARCHAR(250) CHARACTER SET LATIN,
    OUT pform     VARCHAR(20)  CHARACTER SET LATIN,
    OUT cfgpath   VARCHAR(200) CHARACTER SET LATIN
   )

where:

name

Name for the package.

The name that you use here to distribute the package is the same name that you specify later in the CREATE/REPLACE statement that you use to install the package.

version

Version you assign to the package.

For example, when you initially distribute a package, you might want to assign a version of '1.0'. When you update the package and redistribute it, you might want to assign a version of '2.0' or '1.1', depending on the changes to the package.

filename

Name to use for the server-side destination file.

The filename you specify here to distribute the package is the same filename that you specify later in the CREATE/REPLACE statement to install the package.

path

Optional path to use for the server-side destination file. The path you specify is relative to the following fixed path:

teradata_installation_path/Teradata/dem

where teradata_installation_path is the path of the Teradata installation. To get the value of teradata_installation_path, enter the following on the command line:

pdepath -I

The full path to the server-side destination file is formed by appending the relative path specified by path to the fixed path. If you omit the path argument, the full path is the same as the fixed path.

The installsp stored procedure returns the full path in the cfgpath OUT argument.

source

USING variable name preceded by a COLON character.

When you call installsp to distribute a package, the CALL statement that you use must specify a USING row descriptor. During processing, installsp replaces source with the contents of the package.

operation

the operation to perform on the package. The valid values are listed below. They are case insensitive.

• 'CREATE', which specifies to copy the contents of the package specified by source to all nodes in the location specified by path with the name specified by filename. If the file already exists, do not overwrite it.
• 'REPLACE', which specifies to copy the contents of the package specified by source to all nodes in the location specified by path with the name specified by filename. Overwrite the target file if it exists.
• 'DROP', which specifies to delete the file named filename in the location specified by path.
• 'CHECK', which specifies to not perform an operation on the package, but simply return the target platform type in the pform argument.

pform

Target platform type, for example LINUX64.

cfgpath

Full path on the server where the package has been distributed, formed by appending the relative path specified by path to the fixed path for the target platform.

Later, when you use an appropriate CREATE/REPLACE statement to install the package, use the return value of cfgpath in the EXTERNAL NAME clause to specify the full path to the server-side destination file.

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 Example: Distributing a Package.

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 Teradata 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.

Example: Distributing a Package

Here is an example of how to distribute version 1.0 of a package called libcstd2.so to all nodes. In this example, the target server directory is /Teradata/dem/udfs and there is a .so file on the Windows client:

LobTeq -- Enter your DBC/SQL request or LobTeq command:
.using 'libcstd2.so'xfile

*** .using accepted

LobTeq -- Enter your DBC/SQL request or LobTeq command:
USING (a BLOB AS DEFERRED)
CALL SYSLIB.installsp('cstdlib2', '1.0', 'libcstd2.so', 'udfs',
   :a, 'CREATE', pform, cfgpath);

Sending LOB data, chunk 1
*** Procedure has been executed.

LINUX64               /Teradata/dem/udfs/

Here is an example of a CREATE FUNCTION statement that creates the function from the installed package.

CREATE FUNCTION SYSLIB.cSTD_DEV(x FLOAT)
RETURNS FLOAT
CLASS AGGREGATE
LANGUAGE C
NO SQL
PARAMETER STYLE SQL
EXTERNAL NAME 'SP!/Teradata/dem/udfs/libcstd2.so';

Note that the filename and path specified in the EXTERNAL NAME clause are the same as the filename IN argument and cfgpath OUT argument in the call to installsp.

Related Topics