Create and Use External Stored Procedures | VantageCloud Lake - Creating and Using External Stored Procedures - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905
The DBA needs to grant users the necessary privileges to create and delete external stored procedures in VantageCloud Lake.
DBC user cannot be used for creating external stored procedures. Please create a different user for this purpose.
  • GRANT LOGON ON ALL TO username WITH NULL PASSWORD;
  • GRANT CREATE EXTERNAL PROCEDURE, DROP PROCEDURE ON database TO username;
  • GRANT ALL ON SYSUDTLIB TO username;
  • When creating Java external stored procedures, an additional grant is needed:

    GRANT ALL ON SQLJ TO username;

Using External Stored Procedures

To create and use external stored procedures, you must use the External Routine Installation Tool and External Routine Migration Tool. Users must authenticate themselves using a Personal Access Token and the private key used to generate the token before they can create or use external stored procedures. Follow the instructions for Creating a Personal Access Token, and store the PAT with the private key for use during the migration of SQL scripts and installation of external stored procedures.

Use the XRMigrationTool to migrate the SQL scripts that create external stored procedures for VantageCloud Lake. Once migrated, the scripts can be run to install external stored procedures.
In the following sections, the configuration YAML file, and the database user credentials are set in environment variables.

Creating an External Stored Procedure

You want to create an external stored procedure in database MarketingDB, whose xsp_name is XSP_DEMO. You have the following:
  • Organization whose org_key is examplecorp
  • Organization tenant account_name is ExampleCorp-Tenant-001
  • Database user is bobby
  • Directory ./xsp_src which contains the XSP manifest.json file and all the source files defining the external stored procedure
You would run the following command
tdextroutine -k examplecorp -n ExampleCorp-Tenant-001 -u bobby  xsp create -s ./xsp_src -d MarketingDB XSP_DEMO
The command runs, and does the following:
  • Parses the manifest.json file and generates a .zip artifact file.
  • Uploads the artifact file to the tenant artifact bucket.
  • Initializes an XSP Creation Job.
  • Monitors the job until it finishes.
  • Displays a success or error message on job completion.

Listing an External Stored Procedure

Use the following command to list existing external stored procedures you created: 
tdextroutine xsp list
The following is an example of the output from the command:
========   =========== ========== ========== ================
XSP ID      Database      XSP      Source     Routine
                          Name     Language   Kind
=========  =========== ========== ========== ================
AADNGAAA    MarketingDB   XSP_DEMO JAVA       REGULAR_FUNCTION
========   =========== ========== ========== ================
The subcommand supports filters. To list all external stored procedures in database MarketingDB, use the following: 
tdextroutine xsp list -d MarketingDB
The following is an example of the output from the command:
========   =========== =========== ========== ================
XSP ID     Database     XSP         Source     Routine
                        Name        Language   Kind
========   =========== =========== ========== ================
AADNGAAA   MarketingDB  XSP_DEMO    JAVA       REGULAR_FUNCTION
========   =========== =========== ========== ================

For details about the options of the tdextroutine xsp list command, see tdextroutine External Stored Procedure Subcommands and Options.

Fetching Details of an Existing External Stored Procedure

Use the tdextroutine xsp fetch command to get details about an external stored procedure.
tdextroutine xsp fetch XSP_ID

XSP_ID identifies the external stored procedure. The identifier is returned by the tdextroutine xsp create command or tdextroutine xsp list command. For example, the XSP_ID returned in the previous tdextroutine xsp list example is AADNGAAA. So using the following command, you can fetch details of the external stored procedure:

tdextroutine xsp fetch AADNGAAA
Example output:
======================   =========================== 
PROPERTY                 VALUE 
======================   =========================== 
XSP ID                   AADNGAAA 
Database                 MarketingDB 
XSP Name                 XSP_DEMO 
Source Language          JAVA 
Parameter Style          JAVA 
Routine Kind             REGULAR_FUNCTION 
External Security 
Protection Mode          PROTECTED 
Parameter UDT IDs 
Auth ID Used 
GLOP Set Database Name 
Create/Replace Job IDs   1702658811851_dca940169df64 
======================   ==========================
If XSP_ID includes a slash (/) character, you must specify %2F to encode it. Slash is a reserved character. For example, if XSP_ID is AAD/GAAA, you would enter:
tdextroutine xsp fetch AAD%2FGAAA

For details about the options of the tdextroutine xsp fetch command, see tdextroutine External Stored Procedure Subcommands and Options.

Dropping an External Stored Procedure

Use the tdextroutine xsp drop command to remove the definition of an external stored procedure.
tdextroutine xsp drop XSP_ID

XSP_ID identifies the external stored procedure. The identifier is returned by the tdextroutine xsp create command or tdextroutine xsp list command. For example, the XSP_ID returned in the previous tdextroutine xsp list example is AADNGAAA. So, using the following command, you can drop the AADNGAAA stored procedure:

tdextroutine xsp drop AADNGAAA
If XSP_ID includes a slash (/) character, you must specify %2F to encode it. Slash is a reserved character. For example, if XSP_ID is AAD/GAAA, you would enter:
tdextroutine xsp drop AAD%2FGAAA

Instead of specifying XSP_ID, you can also drop an external stored procedure using its name, and optionally the database name, if it is not present in the default database.

For example:
tdextroutine xsp drop -d MarketingDB -x XSP_DEMO

For details about the options of the tdextroutine xsp drop command, see tdextroutine External Stored Procedure Subcommands and Options.