17.00 - Running the Example External Stored Procedure Example - Parallel Transporter

Teradata® Parallel Transporter Application Programming Interface Programmer Guide

prodname
Parallel Transporter
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-2516-220K
Follow these steps to build, install, and run this example:
  1. Install the Teradata PT package.

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

  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 are located in the following directory:

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

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

  3. Move example External Stored Procedure Files onto the database system.

    If the database system was used to build the example XSP library, this step can be skipped.

    Move the example XSP library into any directory on the database system. 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 database.

    See the beginning section of Code Samples for the list of packages which are mandatory to run the Teradata PT samples. Install all of these packages on the database system.

  5. Configure the database to run the CREATE PROCEDURE SQL statement.

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

    For a database system 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/17.00/lib64 directory.

  6. Issue the CREATE PROCEDURE SQL statement.

    Issue the following CREATE PROCEDURE SQL statement to the 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, the 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 database for running Teradata PT external stored procedures.

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

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

    COPERR=/usr/lib64
    COPLIB=/usr/lib64

    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 database so that the new configuration takes effect.

  8. Create tables for the external stored procedure examples:

    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);