How to connect to a Teradata database via JDBC toolkit

How to connect to a Teradata database via JDBC toolkit

The following SPL sample demonstrates how to connect to a Teradata database and select data from a table using JDBCRun operator.

Required Streams Version = 4.1.x.x or higher

Required JDBC Toolkit Version = 1.2.0 or higher

https://github.com/IBMStreams/streamsx.jdbc/releases

Preparation

To connect to database, the following parameters need to be specified:

jdbcDriverLib: the jdbc driver libraries.

download the jdbc driver files from: http://downloads.teradata.com/download/connectivity/jdbc-driver

and store them in opt folder, write the path and the jar file names comma separated in one string e.g. ("opt/terajdbc4.jar, opt/tdgssconfig.jar" )

jdbcClassName: the class name for teradata jdbc driver (com.teradata.jdbc.TeraDriver)

jdbcUrl: the database URL. (e.g. jdbc:teradata://your-db-host/db-name)

jdbcUser: the database user on whose behalf the connection is being made.

jdbcPassword: the user’s password.

In the SPL sample:

The "select" operator demonstrates how to run an SQL statement from stream attribute via statementAttr parameter.

In this sample the JDBCRun operator connect to the database and read all table names from database and write them into a text file data/output.txt

SPL sample

namespace application;
use com.ibm.streamsx.jdbc::* ;

composite Main
{
	expression<rstring> $jdbcDriverLib : getSubmissionTimeValue("jdbcDriverLib", "opt/terajdbc4.jar, opt/tdgssconfig.jar");
	expression<rstring> $jdbcClassName : getSubmissionTimeValue("jdbcClassName", "com.teradata.jdbc.TeraDriver");
	expression<rstring> $jdbcUrl : getSubmissionTimeValue("jdbcUrl", "jdbc:teradata://your-db-host/your-db-name");
	expression<rstring> $jdbcUser : getSubmissionTimeValue("jdbcUser", "your-db-userName");
	expression<rstring> $jdbcPassword : getSubmissionTimeValue("jdbcPassword", "your-db-password");
	expression<rstring> $statement : getSubmissionTimeValue("statement", "SELECT TableName FROM dbc.tables");

graph


        stream<int32 counter> pulse = Beacon() {
		param
			iterations : 1u ;
		output
			pulse : counter = 1;
	}

	stream<rstring TableName> select = JDBCRun(pulse) {
		param
			jdbcDriverLib: $jdbcDriverLib;
			jdbcClassName: $jdbcClassName;
			jdbcUrl: $jdbcUrl;
			jdbcUser: $jdbcUser;
			jdbcPassword: $jdbcPassword;
			statement:  $statement;
	}

	() as WriteToFile = FileSink(select) {		                                                           
		                                                                                      
		logic
		state :
		{
			mutable int64 counter = 0;
		}

		onTuple select :
		{
			printStringLn((rstring)counter++ + " TableName = " +(rstring) TableName) ;
		}
  			
		param   
      			file	: "output.txt";                                                                            
			format	: line ; 
			flush	: 1u;		/** flush the output file after 1 tuple */
	}
 }