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:
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:
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:
CALL xsptest ('TD_LOAD','input.lod',null,'infile.littleEndian',null);
CALL xsptest ('TD_UPDATE','input.upd',null,'infile.littleEndian',null);
CALL xsptest ('TD_STREAM','input.stm',null,'infile.littleEndian',null);
CALL xsptest ('TD_EXPORT','input.exp',null,'outfile.littleEndian',null);
CALL xsptest ('TD_EXPORT2LOAD','input.exp','input.lod',null,null);
CALL xsptest
('TD_EXPORT2UPDATE','input.exp2','input.lod',null,'2007-03-03 10:30:00');
CALL xsptest
('TD_EXPORT2STREAM','input.exp2','input.stm',null,'2007-03-03 10:30:00);