Getting Started with the External Stored Procedure Example - Parallel Transporter

Teradata Parallel Transporter Application Programming Interface

Product
Parallel Transporter
Release Number
15.00
Language
English (United States)
Last Update
2018-09-27
dita:id
B035-2516
lifecycle
previous
Product Category
Teradata Tools and Utilities

Getting Started with the External Stored Procedure Example

To run the example external stored procedure example

Follow these steps to build, install, and run this example:

1 Install the Teradata PT package.

Install the Teradata PT package on the Teradata Database’s machine or on a client machine. If installed on a client machine, the machine needs to be the same platform as the Teradata Database machine.

2 Build the Example External Stored Procedure Library.

Go to the Teradata PT sample directory and build the example XSP library using the makefiles provided or the Visual Studio project file. On Linux, if the default Teradata PT installation is used, the files for the sample XSP will be located in the following directory:

/opt/teradata/client/tbuild/15.00/tptapi/sample/xsp

Building the example XSP will require two files from the Teradata Database: the sqltypes_td.h header file and the udf library (udf.so on Linux). See Database Administration for the location of these two files.

3 Move example External Stored Procedure Files onto the Teradata Database machine.

Note: If the Teradata Database machine was used to build the example XSP library, this step can be skipped.

Move the example XSP library into any directory on the Teradata Database machine. On the Linux platform, the example XSP library produced in step 2 is named xsp.so.

In addition to the example XSP library, the following input files will be needed in order to run the example:

  • input.exp
  • input.lod
  • input.stm
  • input.upd
  • infile.littleEndian
  • 4 Install all required Teradata PT packages on the Teradata Database.

    See the beginning section of Appendix B: “Code Samples” for the list of packages which are mandatory to run the Teradata PT samples. Install all of these packages on the Teradata Database machine.

    5 Configure the Teradata Database to run the CREATE PROCEDURE SQL statement.

    Depending on which platform the Teradata Database is running on, follow the above instructions for configuring the Teradata Database’s environment so that the CREATE PROCEDURE SQL statement can be used to create the example Teradata PT XSP.

    For a Teradata Database running on Linux, move the following files into the /usr/tdbms/lib directory:

  • libtelapi.so
  • libpxicu.so
  • If the default Teradata PT installation is used, these libraries can be found in the
    /opt/teradata/client/tbuild/15.00/lib64 directory.

    6 Issue the CREATE PROCEDURE SQL statement.

    Issue the following CREATE PROCEDURE SQL statement to the Teradata Database using a database user account that has the CREATE EXTERNAL PROCEDURE permission:

    CREATE PROCEDURE xsptest
    (
    IN operatortype VARCHAR(64),
    	IN configfilename varchar(64),
    	IN extraconfigfilename VARCHAR(64),
    	IN datafilename VARCHAR(64),
    	IN updatetime VARCHAR(64)
    )
    LANGUAGE CPP
    MODIFIES SQL DATA
    EXTERNAL NAME 'SP!CLI!SP!/xsptest/xsp.so!F!xsptest'
    PARAMETER STYLE SQL;

    Note that for this example CREATE PROCEDURE SQL statement “/xsptest/xsp.so” should be replaced with the name and current location of the example external stored procedure library.

    7 Configure the Teradata Database for running Teradata PT external stored procedures.

    Depending on the Teradata Database platform, follow the above instructions for configuring the Teradata Database’s environment for running Teradata PT External Stored Procedures.

    For a Teradata Database Linux installation, create a file with the following environment variable settings:

    NLSPATH=/OPT/TERADATA/CLIENT/TBUILD/15.00/MSG/%N 
    /* Default location of opermsgs.cat */
    COPERR=/usr/lib64
    COPLIB=/usr/lib64
    LD_LIBRARY_PATH=/opt/teradata/client/tbuild/15.00/lib64 
    /* Default location of the driver libraries */

    Add the current location of the example XSP library to the above LD_LIBRARY_PATH definition.

    Next, create another file with the following information:

    CLIEnvFile:/<location and name of the first file created above>

    Then use the Cufconfig utility to set the CLIEnvFile variable:

    cufconfig -f/<location and name of the second file created above>

    Restart the Teradata Database so that the new configuration takes effect.

    8 Create tables for the external stored procedure examples

    Use the following SQL commands to create the tables used in the example XSP:

    CREATE SET TABLE tdexport, NO FALLBACK,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL,
    CHECKSUM = DEFAULT
    (
    		Associate_Id INTEGER,
    		Associate_Name CHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
    		Salary FLOAT,
    		DOJ date format ‘YY/MM/DD’,
    		Designation VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
    		Loan_Amount DECIMAL(5,2),
    		Martial_Status CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
    		No_Of_Dependents BYTEINT,
    		update_date TIMESTAMP(6)
    )
    PRIMARY INDEX (Associate_Id);
     
    CREATE SET TABLE tdexportA, NO FALLBACK,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL,
    CHECKSUM = DEFAULT
    (
    		Associate_Id INTEGER,
    		Associate_Name CHAR(25) CHARACTER SET LATIN NOT CASEPECIFIC,
    		Salary FLOAT,
    		DOJ DATE FORMAT ‘YY/MM/DD’,
    		Desigination VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
    		Loan_Amount DECIMAL(5,2),
    		Martial_Status CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
    		No_Of_Dependents BYTEINT,
    		update_date TIMESTAMP(6)
    )
    PRIMARY INDEX (Associate_Id);
     
    CREATE SET TABLE tdexportB, NO FALLBACK,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL,
    CHECKSUM = DEFAULT
    (
    		Associate_Id INTEGER,
    		Associate_Name CHAR(25) CHARACTER SET LATIN NOT CASEPECIFIC,
    		Salary FLOAT,
    		DOJ DATE FORMAT ‘YY/MM/DD’,
    		Desigination VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
    		Loan_Amount DECIMAL(5,2),
    		Martial_Status CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
    		No_Of_Dependents BYTEINT,
    		update_date TIMESTAMP(6)
    )
    PRIMARY INDEX (Associate_Id);
     

    Create the tables on the same database that the example XSP is running on or on any other database. For the example scenarios which transfer data between multiple tables, the tables to export data from (source tables) can be on one database and the tables to load data into (target tables) can be on a different database.

    9 Issue the SQL CALL statement.

    Use one of the following SQL CALL statements to run the example XSP:

  • Use the Load driver to load data from infile.litleEndian into the database:
  • CALL xsptest ('TD_LOAD','input.lod',null,'infile.littleEndian',null);
     
  • Use the Update driver to load data from infile.litleEndian into the database:
  • CALL xsptest ('TD_UPDATE','input.upd',null,'infile.littleEndian',null);
     
  • Use the Stream driver to load data from infile.litleEndian into the database:
  • CALL xsptest ('TD_STREAM','input.stm',null,'infile.littleEndian',null);
     
  • Use the Export driver to export data from the database into a file:
  • CALL xsptest ('TD_EXPORT','input.exp',null,'outfile.littleEndian',null);
     
  • Use the Export and Load drivers to transfer data between two tables:
  • CALL xsptest ('TD_EXPORT2LOAD','input.exp','input.lod',null,null);
     
  • Use the Export and Update drivers to transfer data from one table into two tables. The timestamp is used to determine which rows to transfer. In this example, only rows with timestamps newer than '2007-03-03 10:30:00' will transfer:
  • CALL xsptest
    ('TD_EXPORT2UPDATE','input.exp2','input.lod',null,'2007-03-03 10:30:00');
     
  • Use the Export and Stream drivers to transfer data between two tables. The timestamp is used to determine which rows to transfer. In this example, only rows with timestamps newer than '2007-03-03 10:30:00' will be transferred.
  • CALL xsptest
    ('TD_EXPORT2STREAM','input.exp2','input.stm',null,'2007-03-03 10:30:00);