Access External Data - A Table UDF

Connectivity
Connectivity covers the mechanisms for connecting to the Teradata Database, including driver connectivity via JDBC or ODBC.
Teradata Employee

Access External Data - A Table UDF

With the introduction of Query Grid in Teradata 15, many people have been asking "what about me? I am on not going to be on TD 15 for some time.".

In this series of articles I provide a tool I call DbLink which provides a similar capability for versions of Teradata prior to 15.0. The DbLink tool consists of a Table UDF and supporting components that may be used to access data from a remote RDBMS via JDBC.

Currently my DbLink supports Teradata and Oracle.

The first article contains the DbLink tool which you can just download, configure and use to retrieve data from remote Oracle and Teradata systems. No programming skill is required although you will need some knowledge of installing Java applications and of course Teradata DBA privileges to install the UDF and some tables that DbLink requires.

The second article provides a guide that shows you how you can add support for additional database technologies (e.g. SQLServer). Through this mechanism, you can support virtually any data source that can be queried via JDBC. You will need basic Java Programming skills to undertake this step.

I will try to answer any questions on a best endeavours basis. However, often, due to commitments, I do not have many free endeavours. So, before posting a question, I would request that you review all of the information in the two articles and the user guide (especially the troubleshooting section) before posting.

39 REPLIES
Supporter

Re: Access External Data - A Table UDF

Where can we find the download?

Teradata Employee

Re: Access External Data - A Table UDF

Due to technical complexities, the top level article had to be published before the two sub articles. I've now published the sub-articles with "the stuff". Apologies for the delay in between posting the top level article and the related articles, that was due to workload issues.

Enthusiast

Re: Access External Data - A Table UDF

Hi,

I am implementing the DBLink in Teradata. To query from Oracle. I have gone through all the given reference documents and process. Still I am facing to install the UDF. I am hoping for your Kind help for the same. It is throwing error Jar

DbLinkTUdf udf does not exist. Please help to setup this facility for our POC.

replace function DbLink(id varchar(200))

returns table varying columns(254)

LANGUAGE JAVA

NO SQL

PARAMETER STYLE JAVA

NOT DETERMINISTIC

EXTERNAL NAME 'DbLinkTUdf:com.teradata.dblink.tudf.DbLink.getExternalData';

Enthusiast

Re: Access External Data - A Table UDF

From which location i can download the

DbLinkManagerWeb.war files for Web based

Teradata Employee

Re: Access External Data - A Table UDF

Unfortunately, I can not distribute the web manager app I have removed it from a later version of the documentation which I am yet to upload.

As for the error creating the function, have you uploaded the DbLinkCommon and DbLinkTableUDF jar files yet? You need to do this with the SQLJ.INSTALL_JAR (or SQLJ.REPLACE_JAR) functions. If these do not complete successfully, there is nothing to base the creation of the function on.

For simplicty I would suggest installing the 2 JAR files and creating the table functions in the same database.

Enthusiast

Re: Access External Data - A Table UDF

Hi Sir,

This is my kind request to please help me how i can install the DbbLink to coonect remote database and use effectivaly.

I have the scenario.

Oracle client has been install at my personal laptop.

I am connecting Teradata from My personal Laptop using the TD Admin and SQl assistannt.

Created the Metadata tables under seprate database DB_link.

It can run on solaris or need the Linux server (explicitly).

When i am trying to install the SQLJ.INSTALL_JAR (or SQLJ.REPLACE_JAR) functions(as suggested by you) in either SQLJ or in DB_LINK database ( which i have created for seprate DB_link process).

Using below steps

step 1: compiled the all the procedure from SQLJ to db_link and run the below procedure and it has complied successfully.

Call   db_link.INSTALL_JAR ('C:\Users\rthak14\Desktop\Software\DbLinkDistribution_1\DbLinkClient\lib$DbLinkCommon', 'DbLinkLib', 0);

call db_link.Replace_Jar('C:\Users\rthak14\Desktop\Software\DbLinkDistribution_1\DbLinkTableUDF\DbLinkTableUDF', 'DbLinkTUDF');

call db_link.alter_java_path('C:\Users\rthak14\Desktop\Software\DbLinkDistribution_1\DbLinkTableUDF\DbLinkTableUDF', '(*,DbLinkLib)');

Step 2. While creating the function using below

replace function db_link.DbLink(id varchar(200))

returns table varying columns(254)

LANGUAGE JAVA

NO SQL

PARAMETER STYLE JAVA

NOT DETERMINISTIC

EXTERNAL NAME 'DbLinkTUdf:com.teradata.dblink.tudf.DbLink.getExternalData';

Getting error:  Replace function failed 7972: Jar 'DbLinkTUDF' does not exist.

It would be great help if you can help me with all the process based on my available scenario. Kinldy help to install the DbLink facilities. I have read all Teradata DbLink User Guid V1.2

Enthusiast

Re: Access External Data - A Table UDF

Today i ahve make some progress to install the DbLinkserver. but it has been stuck on ready step. Not sure after that how i can install the DbLink Client and move forward. where i can check that DBLink server has been started or not or installed or not.

Openning session: jdbc:teradata://dev/LOGMECH=LDAP,DATABASE=database (Seprate database for dblink) for user: username

MetaData connection established.

Verifying dblink model.

DbLink Model verified.

Preparing load DBLinkLog statement...

Library Path: lib

Adding: terajdbc4.jar

Adding: tdgssconfig.jar

Adding: TDUtilityLibrary.jar

Adding: commons-cli-1.2.jar

Adding: DbLinkCommon.jar

5 jars added to classpath

Starting DBLink UDF Server

Listening on port: 1025

Ready

I am looking desperatly for your help . Please help me to move forward and DbLink facility works properly.

Teradata Employee

Re: Access External Data - A Table UDF

At this point, the DbLink Server is ready and waiting for a request from a client. The client can either be the table UDF or the DbLinkClient application.

So at this point, you could start another terminal window (e.g. putty for Linux or CMD for windows) and run the DbLink client. You can then enter the "status" command into the client and the server should respond (if it can connect).  If this works and you have setup some entries in the DbLinkMeta table, you can try them as well. For example if you have a valid dblinkMeta table entry with the name="myTestLink", you could enter the command "test myTestLink" or "exec myTestLink" into the DbLinkClient.

I do have a couple of thoughts re your setup if you don't mind my sharing them:

1) I would not recommend using the DbLink Servers "lib" directory as your source for plugins. In my case, I have created a directory called plugin and I put my Foreign JDBC drivers and the DbLinkSupport libraries (i.e. the ojdbc14.jar and DbLinkOracleSupport.jar) into that "plugin" directory. Then when you run the dblink server use the -l option to specify this path.

2) Don't use port 1025. Port 1025 is used by Teradata for incoming connections. If you fully understand networking, you could do this, but to start with, I would suggest using the default port (5000) if you can. If you use port 1025, you open yourself to the risk of encountering a conflict as two servers on one system can not listen to the same port at the same time.

Enthusiast

Re: Access External Data - A Table UDF

Hi Sir

Thank you so much for your time spending to set up the DbLink facility. Really appreciated.

I will follow the steps to setup the client as well and will update you with progress. Need help to set up the query to run and fetch the dtaa from other database like Oracle. If you can help me how i need to setup a quersy or process and where i need to run the query using DbLink to fetch the data. If you have any set of exapmle please share with me or any case study that would help me alot.

Please help me to make this process success and it would be best if you cna share you email id then i will share more details about the same. because I am expecting much help to get this success.

Thanks for your help and support.