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.

Discussion
ulrich 51 comments Joined 09/09
02 Dec 2014

Where can we find the download?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

dbc1012 31 comments Joined 03/09
09 Dec 2014

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.

12 Jun 2015

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

12 Jun 2015

From which location i can download the
DbLinkManagerWeb.war files for Web based

dbc1012 31 comments Joined 03/09
14 Jun 2015

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.

15 Jun 2015

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
 
 

16 Jun 2015

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.

dbc1012 31 comments Joined 03/09
16 Jun 2015

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.
 

18 Jun 2015

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.
 
 
 

18 Jun 2015

Hi Sir,
As suggested above by you. I tried to install the DbLink Client and it is throwing the below error:
rthak14@spdma544:/idn/home/rthak14/rabi/dblink/DbLinkClient>java -jar DbLinkClient.jar -h dev -o 1025
DbLink Client utility version 1.02.00.00
Type "exit" to exit.
Type "help" for help.
Host: dev
Port: 1025
Session: 587
> status
status
Request No: 0
Request: Request{requestType=STATUS, dbLinkName=null, sessionNo=587, requestNo=0, stmtNo=1, userId=gm310509}
Unknown host: dev
Exception in thread "main" java.lang.NullPointerException
        at com.teradata.dblink.test.RequestProcessor.process(RequestProcessor.java:53)
        at com.teradata.dblink.test.Client.executeCommand(Client.java:201)
        at com.teradata.dblink.test.Client.go(Client.java:66)
        at com.teradata.dblink.test.Client.main(Client.java:40)
 
Please suggest to proceed further and complete the successfull installation.
Hoping for your kind help

18 Jun 2015

While Client installation . iam getting these details.
 
java -jar DbLinkClient.jar -h 10.22.76.19 -o 1025
DbLink Client utility version 1.02.00.00
Type "exit" to exit.
Type "help" for help.
Host: 10.22.76.19
Port: 1025
Session: 41645
> status
status
Request No: 0
Request: Request{requestType=STATUS, dbLinkName=null, sessionNo=41645, requestNo=0, stmtNo=1, userId=gm310509}
java.net.SocketException: Broken pipe
        at java.net.SocketOutputStream.socketWrite0(Native Method)
        at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
        at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
        at java.io.ObjectOutputStream$BlockDataOutputStream.drain(ObjectOutputStream.java:1847)
        at java.io.ObjectOutputStream$BlockDataOutputStream.setBlockDataMode(ObjectOutputStream.java:1756)
        at java.io.ObjectOutputStream.writeNonProxyDesc(ObjectOutputStream.java:1257)
        at java.io.ObjectOutputStream.writeClassDesc(ObjectOutputStream.java:1211)
        at java.io.ObjectOutputStream.writeOrdinaryObject(ObjectOutputStream.java:1395)
        at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1158)
        at java.io.ObjectOutputStream.writeFatalException(ObjectOutputStream.java:1547)
        at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:333)
        at com.teradata.dblink.common.DbLinkConnection.sendDbLinkMessage(DbLinkConnection.java:242)
        at com.teradata.dblink.test.RequestProcessor.process(RequestProcessor.java:35)
        at com.teradata.dblink.test.Client.executeCommand(Client.java:201)
        at com.teradata.dblink.test.Client.go(Client.java:66)
        at com.teradata.dblink.test.Client.main(Client.java:40)
IOException communicating with 10.22.76.19
java.io.StreamCorruptedException: invalid stream header: 03020100
        at java.io.ObjectInputStream.readStreamHeader(ObjectInputStream.java:782)
        at java.io.ObjectInputStream.<init>(ObjectInputStream.java:279)
        at com.teradata.dblink.common.DbLinkConnection.getObjectInputStream(DbLinkConnection.java:214)
        at com.teradata.dblink.common.DbLinkConnection.readDbLinkMessage(DbLinkConnection.java:258)
        at com.teradata.dblink.test.RequestProcessor.process(RequestProcessor.java:38)
        at com.teradata.dblink.test.Client.executeCommand(Client.java:201)
        at com.teradata.dblink.test.Client.go(Client.java:66)
        at com.teradata.dblink.test.Client.main(Client.java:40)
java.net.SocketException: Broken pipe
        at java.net.SocketOutputStream.socketWrite0(Native Method)
        at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
        at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
        at java.io.ObjectOutputStream$BlockDataOutputStream.drain(ObjectOutputStream.java:1847)
        at java.io.ObjectOutputStream$BlockDataOutputStream.flush(ObjectOutputStream.java:1792)
        at java.io.ObjectOutputStream.flush(ObjectOutputStream.java:699)
        at com.teradata.dblink.common.DbLinkConnection.close(DbLinkConnection.java:152)
        at com.teradata.dblink.test.RequestProcessor.process(RequestProcessor.java:53)
        at com.teradata.dblink.test.Client.executeCommand(Client.java:201)
        at com.teradata.dblink.test.Client.go(Client.java:66)
        at com.teradata.dblink.test.Client.main(Client.java:40)
Elapsed: 276 ms.
 
Please help me to move forward.
 

dbc1012 31 comments Joined 03/09
19 Jun 2015

I think it is best if we pause and reset. Please try to get it up and running by following the steps in sequence - don't just jump to the end.
Number 1) *Do not* use port 1025 for the DbLink Server. Let it run on it's default port (don't specify the -o option). When it starts, make sure it says "Listening on port: 5000".
Number 2) *Do not* use port 1025 for the DbLink Client (or the UDF). Let it run on it's default port (don't specify the -o option). When it starts, make sure it says "Port: 5000".
Number 3) For now, run the DbLInkClient and the DbLinkServer on the same computer. When running the dblinkclient *Do not* specify the host (-h option). It will try to connect to the localhost (which should exist in your hosts file).
Assuming the DbLinkServer connects to Teradata (and it looks like it has) then the DbLink client (running on the same computer as the DbLinkServer) should connect and you can try some of the commands such as status.
If this works, try entering the example from the user guide that queries the dbc.dbcinfo table. I just checked and that isn't in the online documentation - it is in the next version - coming soon!. So enter this query:
 

insert into dblinkmeta (name, description,
    jdbcDriverClassName, ConnectionUrl, UserId, pass,
    handlerClassName, remoteQuery
    )
values (
    'dbcinfo',
    'Query DBC.DBCInfo from remote system',
    'com.teradata.jdbc.TeraDriver',
    'jdbc:teradata://dbc/',
    'Your User ID goes here',
    'Your Password Goes here',
    null,
    'select * from dbc.dbcinfo order by 1;'
);

You will need to make sure that you enter the correct URL, User ID and password so that a connection can be made to your Teradata system to run the "select * from dbc.dbcinfo" query.
Once you have inserted this row, test it from the client application using these commands:
test dbcinfo
exec dbcinfo
Once you get that working, you can try running the UDF with a query like this:

select *
from table(dblinkgetmeta('dbcinfo:IP_ADDRESS_OF_YOUR_COMPUTER')) as dt
;

Note that in the query I specify "IP_ADDRESS_OF_YOUR_COMPUTER". Even if you work this out and enter it correctly, I am concerned that it still won't work for you. Why? Because I suspect that your Teradata is running as a server in a computer facility somewhere. There is a strong chance that there will be a firewall (or multiple firewalls) that prevent communications coming from the Teradata server to your computer. Which is what the above query will try to do. It needs to contact the DbLinkServer to run the remote query named "dbcinfo".
The technical solution is easy - just get port 5000 openned from the Teradata nodes (all of them) to your computer. The political solution - getting approval to do it might be a bit harder.
Most sites that are running DbLink on real Teradata in a data center will use a managed server in the Teradata cabinet to host the DbLink Server.
 
Having said all of that, the DbLinkClient (which is running on the same computer as your DbLinkServer - right?) will be able to connect and you can see it work.
Once you get all of that working, you could, for example, setup the client on one of your friends computers (and assuming the firewall on your computer isn't blocking port 5000), you can connect from your friends computer to your computer (over port 5000) using the -h option when running the DbLinkClient.
I hope this helps you (and any others) struggling with setting it up.

19 Jun 2015

Thanks for your help Sir.
Now i am able to connect with client but not able to get the data from dblinkgetmeta function. How i will connect with Oracle using the same. I will describe more about the same.

19 Jun 2015

Hi Sir,
Thank you so much for your support to run the DbLink facility. I am following up the suggested steps to connect and fetch the details from teradata and Oracle. I am able to connecta dn providing you the details
**Step1. Started DbLink server and the status of the server is ready.
java -jar DbLinkServer.jar -j jdbc:teradata://dev/LOGMECH=LDAP,DATABASE=db_link -u User-p Pass
DbLink Server version: 1.03.00.00
Initialising Metadata connection...
Loading: com.teradata.jdbc.TeraDriver
Openning session: jdbc:teradata://dev/LOGMECH=LDAP,DATABASE=db_link for user: rthak14
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: 5000
Ready

**Step 2: Open the other terminal and started the Clien and checked the status:
dblink/DbLinkClient>java -jar DbLinkClient.jar
DbLink Client utility version 1.02.00.00
Type "exit" to exit.
Type "help" for help.
Host: localhost
Port: 5000
Session: 73302
> status
status
Request No: 0
Request: Request{requestType=STATUS, dbLinkName=null, sessionNo=73302, requestNo=0, stmtNo=1, userId=gm310509}
Server Status:
Version: 1.03.00.00
State: ACTIVE
Total Requests: 1
DBLink Requests: 0
Test Requests: 0
Active Requests: 0
End of data encountered.
****When i have checked Test dbcinfo..I have got the below details..
> test dbcinfo
test dbcinfo
Request No: 1
Request: Request{requestType=TEST, dbLinkName=dbcinfo, sessionNo=73302, requestNo=1, stmtNo=1, userId=gm310509}
1: 0 InfoKey InfoKey VARCHAR(30) 30 0 0 12 UNKNOWN VARCHAR(30)
2: 1 InfoData InfoData VARCHAR(16384) 16384 0 0 12 UNKNOWN VARCHAR(16384)
End of data encountered.
Elapsed: 672 ms.
>
*** I have received the below details from DbLink server side..
Request received: Request{requestType=STATUS, dbLinkName=null, sessionNo=73302, requestNo=0, stmtNo=1, userId=gm310509}
Request received: Request{requestType=TEST, dbLinkName=dbcinfo, sessionNo=73302, requestNo=1, stmtNo=1, userId=gm310509}
Searching for testLinkTask: Request{requestType=TEST, dbLinkName=dbcinfo, sessionNo=73302, requestNo=1, stmtNo=1, userId=gm310509}
Creating new com.teradata.dblink.services.LinkMetaData task for: Request{requestType=TEST, dbLinkName=dbcinfo, sessionNo=73302, requestNo=1, stmtNo=1, userId=gm310509}
Started get metadata bacground process.9
9: Testing: dbcinfo: Loading driver class: com.teradata.jdbc.TeraDriver
9: Testing: dbcinfo: Waiting for query meta data to be retrieved.
9: Testing: dbcinfo: Connecting jdbc:teradata://dev/LOGMECH=LDAP,DATABASE=db_link, User: rthak14
9: Testing: dbcinfo: Getting remote Metadata for query:
select * from dbc.dbcinfo order by 1;
9: Testing: dbcinfo: Notifying any client providers that Database processing is complete.
9: Testing: dbcinfo: Query metadata ready.
9: Testing: dbcinfo: Closing connection.
Starting cleanup thread for: dbcinfo
Metrics advisory: Request{requestType=TEST, dbLinkName=dbcinfo, sessionNo=73302, requestNo=1, stmtNo=1, userId=gm310509}: PerformanceMetric{logonTime=369ms, queryTime=14ms, retrieveTime=13ms, rowCount=2 rows}
Removing completed DbLink task dbcinfo from run list (Request{requestType=TEST, dbLinkName=dbcinfo, sessionNo=73302, requestNo=1, stmtNo=1, userId=gm310509}).
 
*** I have entered the below details in Db_link.dblinkmeta table
insert into dblinkmeta (name, description,
jdbcDriverClassName, ConnectionUrl, UserId, pass,
handlerClassName, remoteQuery
)
values (
'dbcinfo',
'Query DBC.DBCInfo from remote system',
'com.teradata.jdbc.TeraDriver',
'jdbc:teradata://dbc/',
'gm310509',
'pass',
null,
'select * from dbc.dbcinfo order by 1;'
);
*** Tried to run the below quesries as you have suggested.
select *from table(dblinkgetmeta('dbcinfo:148.171.28.158:5000')) as dt;
Select Failed 3707: Syntax error, expeced something like '.' between the dblinkgetmeta'
*****What would be the reason for not connecting here.
**********Now.Once i am able to connect with teradata with you help. I need to develpe the connection Oracle ? Then How?
Please help me with expamle how i would connect with Oracle and fetch the details from Oracle and run with teradata queries.
Please help me sir established the DbLink facility to access the data from Oracle with teradata.
I know i have given a lot of trouble for the same. I am really very sorry about that.
You support will help me to connect with Oracle and fetch the data.
 
 

dbc1012 31 comments Joined 03/09
21 Jun 2015

This error usually occurs because the Table UDF does not exist in the named database (or in your case the current database).
You need to change to the database in which the UDF "DbLinkGetMeta" resides or specify the name of the database in which the UDF "DbLinkGetMeta" resides in the query.
select *from table(WHICHDB_IT_IS_IN.dblinkgetmeta('dbcinfo:148.171.28.158:5000')) as dt;

If the Table UDF's do not exist yet, then you need to create them - use the deploy.sh script as a basis to get them setup if you haven't already done so. And bear in mind my comment about the strong possibility that there is no network connectivity from the Teradata server back to your laptop running the DbLinkServer (becauase the required network ports probably haven't been openned by your network guys). 

 

There is an example for Oracle connection in the DbLink Documentation. I can not help you with formulating the URL for Oracle for your particular enviornment as I am not an Oracle person. However, if the above query works for you then that means the you are able to use the UDF to contact the DbLinkServer, have it run a query on a Teradata system and return the result to you via the UDF. When that works, it should do the same thing if you give it a DbLinkMeta name (i.e. dbcinfo in the above example) that references an oracle system such as the "ora-01" and "ora-02" examples in the documentation.

 

When you create the Oracle DbLinkMeta table entry - do not forget to specify the handlerClassName for oracle as specified in the documentation.

 

22 Jun 2015

I am not connecting with my Laptop. We have one Unix Test server. I created one dblink directory and installing in the same. This unix already have the Teradata connection (I means i can access the Teradata from this box and try to instal the Oracle for connection.
Meanwhile i amnot able to get the DbLinkGetMeta. As per your suggestion i checked and not able to foudn this UDF in any of database.
Even i tried to run the "deploy.sh" with change the id and password. But throwing error:
Test:/dblink/DbLinkDeployScript>./deploy.sh
ksh: ./deploy.sh: cannot execute
Not sure where i need to make any changes to execute this and create the "DbLinkGetMeta" Function to connect with Teradata.
 
I have cretaed one seprate database "db_link" and created all the metadata in same database.
It would be much appreciated if you would help me to install the UDF .
Thanks
 

22 Jun 2015

One more thing. I have changed the variable file with my login credentials. Still it is refering the by defalt id and pass not sure why.
 
> test dbcinfo
test dbcinfo
Request No: 1
Request: Request{requestType=TEST, dbLinkName=dbcinfo, sessionNo=33274, requestNo=1, stmtNo=1, userId=gm310509}
   1: 0  InfoKey  InfoKey  VARCHAR(30)  30  0  0  12  UNKNOWN  VARCHAR(30)
   2: 1  InfoData  InfoData  VARCHAR(16384)  16384  0  0  12  UNKNOWN  VARCHAR(16384)
End of data encountered.
Elapsed: 506 ms.
 

dbc1012 31 comments Joined 03/09
22 Jun 2015

Re: "ksh: ./deploy.sh: cannot execute"
To run a unix script, you need to make it executable. search the internet for the chmod command.
The user ID in the test client is a fixed value. You can not change it. The other parameters (session number etc) are generated by the client utilitiy. You can set those values if you need to, refer to the help command.

22 Jun 2015

When i am trying to run the below command from SQL assistant it is running fine without any issues
call db_link.Replace_Jar('CJ!$C:\Users\rthak14\Desktop\Software\DbLinkDistribution_1\DbLinkClient', 'DbLinkLib');
call db_link.Replace_Jar('CJ!$C:\Users\rthak14\Desktop\Software\DbLinkDistribution_1\DbLinkTableUDF', 'DbLinkTableUDF');
call db_link.alter_java_path('DbLinkTableUDF', '(*,DbLinkLib)'); 
 
When i am trying to create the below  function It is throwing error:
replace function db_link.DbLink(id varchar(200))
returns table varying columns(254)
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA
NOT DETERMINISTIC
EXTERNAL NAME 'DbLinkClient:com.teradata.dblink.tudf.DbLink.getExternalData';
 
Replace function failed 7972 :Jar 'DbLinkTableUDF' does not exist
 
Please help
 

dbc1012 31 comments Joined 03/09
22 Jun 2015

Regarding your test box. The fact that you have connection *FROM* the unix box *TO* teradata *DOES NOT* mean that you have connectivity *FROM TERADATA* back *TO Unix*.
Connectivity from a client to a server is quite normal - without this connectivity, a client can not connect. That is tools like bteq and Sql Assitant and TPT could not work.
However, it is very, very, very common that this is a one way street. That is Clients can initiate connections to the server, but it is uncommon for Server (machines) to initiate request back to a client (machine). This is not about two way communications. This is about whether one machine can initiate a connection with another - once the channel (or session) is open, two way communications will be possible. Therefore network administrators will open ports from your Unix box to Teradata so that clients such as bteq can logon to Teradata, but not the other way around (because Teradata does not log on to bteq - for example).
When running DbLinks, the roles are in fact reversed. When running DbLinks, the Table UDF running on Teradata (i.e. what is considered to be the server machine) is a client of the DbLinkServer (which is running on your Unix "client machine"). So, you need to have port 5000 (or whatever port you eventually settle on) open so that the Teradata server (all nodes) can initiate a connection to the DbLinkServer.
The easiest solution is to have your Unix client running inside the Teradata cabinet on a managed server - although I've been to sites where even then there is no open ports from the TPA nodes (Teradata nodes) back to the managed server (the managed server can connect to Teradata, but not the other way around).
 
 

22 Jun 2015

Thank you so much for your support.
After your suggestion to run the deploy.sh file. When i run the client with test dbcinfo it has given the data type and all. when i ran the same dblink name with exec command then i found the exact results from query.
> exec dbcinfo
exec dbcinfo
Request No: 11
Request: Request{requestType=EXECUTE, dbLinkName=dbcinfo, sessionNo=69782, requestNo=11, stmtNo=1, userId=gm310509}
   1: LANGUAGE SUPPORT MODE  Standard
   2: RELEASE  14.10.05.02
   3: VERSION  14.10.05.05
End of data encountered.
Elapsed: 358 ms.
Its look like and able to connect with client and fetch the results from dblink
Please suggest, if i am on right track.
After run the deploy.sh also when i tried to run the query
select *from table(dblinkgetmeta('dbcinfo:148.171.28.158:5000')) as dt;
Select Failed 3707: Syntax error, expeced something like '.' between the dblinkgetmeta'
When i am running the same command with database
select *from table(db_link.dblinkgetmeta('dbcinfo:148.171.28.158:5000')) as dt;
Error:
Select Failed 3707: Syntax error. expected something like an 'UDFCALLNAME' keyword between '.' and the word dblinkgetmeta
While UDF installation :
replace function db_link.DbLink(id varchar(200))
returns table varying columns(254)
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA
NOT DETERMINISTIC
EXTERNAL NAME 'DbLinkTableUDF:com.teradata.dblink.tudf.DbLink.getExternalData';
 
I am getting error
Replace function failed 7972: Jar DbLinkTableUDF doeas not exist.
 
Kinldy help
 

 

03 Jul 2015

Can i use this facility in production?

03 Jul 2015

Thanks for your all suggestions:
I have installed the UDF and created successfully. But when i am running the below 2 queries.
1-select * from table(dblinkgetmeta('dbcinfo:vmhost')) as dt;
2.select dt.*
from table(DbLink('dbcinfo:vmhost') returns (
InfoKey varchar(30)
,InfoData varchar(16384)
)
) as dt
;

While using the any of UDF out of 3 Then i am getting the below error
Select failed. 7584: The secure mode processes are not running : can not execute UDF.
 
Your help will be much appreciated.

dbc1012 31 comments Joined 03/09
12 Jul 2015

Re getting updates, all articles and so on are done on a voluntary basis. Sometimes we have other things to do and are unable to respond immediately.
For both of the above errors, did you try to read the Teradata messages reference manual?
7584 means something is not enabled on your teradata system I can not answer what that might be. From reading the error message, it sounds like you have as installation that has been reconfigured from the default.
The other problem (with error 7972) means, as the error message implies, that the DbLinkTableUDF could not be located. I am not sure why it can't be located. Possible you chould check the output of the deploy.sh script. Maybe the call to INSTALL_JAR failed because you don't have the right permissions? Maybe the create function is in a different database to the place where install_JAR copied up the DbLinkTableUDF.jar file? There could be any number of reasons.
The bottom line is that the create function query is referencing the DbLinkTableUDF jar which INSTALL_JAR should have copied to Teradata. However, it can't find DbLinkTableUDF hence the error 7972.
Also, since we are in this territory, you need to ensure that the call to "Alter_Java_Path" in the deploy.sh script has executed successfully.

14 Jul 2015

Thanks sir for reply. Sorry for the comment. I got a lot of help to reach on this step to install this facilities. Hoping for your kind help in future as well.
The error "Select failed. 7584: The secure mode processes are not running : can not execute UDF." has been resolved and i am able to run this command.
As you suggested by earlier, installed the UDF by same way.: now I am facing 2 different issues
After executing the UDF from SQL assitant with command i am getting the below results. It seems working fine.
select * from Table db_link.DbLinkGetVersion()) as DT;
Getting Results :1.01.00.00
But, when I am running the actual query which gives the actual results then i am getting the below error:
1. select *from table(db_link.dblinkgetmeta('dbcinfo:148.171.28.158:5000')) as dt;
Error:select Failed 7825: in UDF/XSP/UDM SYSLIB.Dblink: SQLSTATE 38U05: IOException: Connection times out
Not sure what need to do for this erro.:
 
2. Installed the Oracle client on my machine and jdbc driver as well. When i am execting any one the dblink from Client terminal it is running fine. like
> exec dbcinfo
exec dbcinfo
Request No: 1
Request: Request{requestType=EXECUTE, dbLinkName=dbcinfo, sessionNo=55135, requestNo=1, stmtNo=1, userId=gm310509}
   1: LANGUAGE SUPPORT MODE  Standard
   2: RELEASE  14.10.05.02
   3: VERSION  14.10.05.05

End of data encountered.
Elapsed: 548 ms.
But when I am trying to run the oracle dblink name with ora-01. I am getting the below error:
> exec ora-01
exec ora-01
Request No: 3
Request: Request{requestType=EXECUTE, dbLinkName=ora-01, sessionNo=55135, requestNo=3, stmtNo=1, userId=gm310509}
Reply: 17002: SQLException: getData: ora-01 SQLState:08006 Error:17002 IO Error: The Network Adapter could not establish the connection
End of data encountered.
Elapsed: 39 ms.
Please help me to resolve this issues. Hoping for your kind help on this issues. Please suggest  

14 Jul 2015

All functions has been installed succesfully. because one function if giving the results as mention in above post. But the issues only with dblinkgetmeta function.

dbc1012 31 comments Joined 03/09
20 Jul 2015

Both of your issues are networking issues.
The first issue "IOException: Connection times out" means that the UDF cannot connect to the named server (i.e. 148.171.28.158) on the specified port (I.e. port 5000). The three most likely reasons are:
1) You've specified the wrong host/IP address
2) The DbLink server is not running on the specified host.
3) The network is configured in such a way that one or more of the Teradata nodes cannot make contact with the named server/IP on the specified port.
 
The second error "IO Error: The Network Adapter could not establish the connection" has something to do with your oracle configuration. The error is an Oracle error generated by the JDBC driver and has nothing to do with the DbLink software. You should search the web for solutions to this problem. If you do search, you will see all sorts of suggestions relating to the format of the JDBC URL, TNS names, SID's, making entries in listener.ora and many, many more.

28 Jul 2015

Thank you so much sir, Now i have opned the port and able to connect with Teradata To Oracle and able to fetch the records from Oracle. Finally got success to use this DbLink facility with your help.
Thank you so much again. i will update more what other problem i faced so that other's can also used.
Thanks again

dbc1012 31 comments Joined 03/09
16 Nov 2015

Today I've updated the DbLink code. The new version can be downloaded from Part 1 of the DbLink bookset.
Please read the information about not mixing and matching different DbLink components/modules from different versions. It is critical that all components/modules in a DbLink implementation come from the same release.

25 Jan 2016

We have installed the new version on DbLink and it is woking fine. But it is shuting down automatically. We need to start every time(That we should do). Not sure what would be the reason to shut down every time with below error. Could you please help if possible what we can do to avoide the shut down during normal oprational time.
INFO   | jvm 1    | 2016/01/25 10:13:51 | 10:13:51.473 [WrapperStartStopAppMain] ERROR  - SQLException retrieving dbLink Metadata: java.sql.SQLException: [Teradata JDBC Driver] [TeraJDBC 15.00.00.20] [Error 804] [SQLState 08S01] Socket communication failure for Packet receive Mon Jan 25 10:13:51 MST 2016 socket orig=dev local=0.0.0.0/0.0.0.0:39620 remote=devcop3/10.22.76.12:1025 keepalive=unavailable nodelay=unavailable receive=unavailable send=unavailable linger=unavailable traffic=unavailable concurrent=3 contimeout=10000 conwait=1000 connecttime=2 connecttotaltime=2 connectattempts=1 connectfailures=0 reconnectattempts=0 recoverable=false redrive=false failurecache={} cid=ba75769 sess=1620968 java.io.IOException: Incomplete LAN message header: read 0 bytes  at com.teradata.jdbc.jdbc_4.io.TDNetworkIOIF.read(TDNetworkIOIF.java:711)  at com.teradata.jdbc.jdbc_4.io.TDPacketStream.readStream(TDPacketStream.java:773)  at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.action(StatementReceiveState.java:137)  at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:123)  at com.teradata.jdbc.jdbc_4.statemachine.StatementController.run(StatementController.java:114)  at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:385)  at com.teradata.jdbc.jdbc_4.TDStatement.prepareRequest(TDStatement.java:569)  at com.teradata.jdbc.jdbc_4.TDPreparedStatement.<init>(TDPreparedStatement.java:117)  at com.teradata.jdbc.jdk6.JDK6_SQL_PreparedStatement.<init>(JDK6_SQL_PreparedStatement.java:29)  at com.teradata.jdbc.jdk6.JDK6_SQL_Connection.constructPreparedStatement(JDK6_SQL_Connection.java:81)  at com.teradata.jdbc.jdbc_4.TDSession.prepareStatement(TDSession.java:1357)  at com.teradata.jdbc.jdbc_4.TDSession.prepareStatement(TDSession.java:1401)  at com.teradata.jdbc.jdbc_4.TDSession.prepareStatement(TDSession.java:1387)  at com.teradata.dblink.meta.SessionManager.getDbLinkMeta(SessionManager.java:304)  at com.teradata.dblink.server.MasterController.handle(MasterController.java:210)  at com.teradata.dblink.server.MasterController.go(MasterController.java:124)  at com.teradata.dblink.server.Server.go(Server.java:106)  at com.teradata.dblink.server.Server.main(Server.java:71)  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)  at java.lang.reflect.Method.invoke(Method.java:597)  at org.tanukisoftware.wrapper.WrapperStartStopApp.run(WrapperStartStopApp.java:316)  at java.lang.Thread.run(Thread.java:662)

It would be great help if we can avoide to shut down. Not sure it is ebcause of heavy load or any other issues.

 

Thanks in advance

26 Jan 2016

Need some suggestion regarding the securit.
1. How i can hide the password in TD where we have put the ID/Password to connect with Oracle.
2. can we pass the query in DbLink function during selection.
It it is possible then that would be great help. Major thing is password. We need to either encrypt or hide the password. 
Thanks in advance.

dbc1012 31 comments Joined 03/09
26 Jan 2016

Hi there thakurra,
Thanks for your questions.
1) UNfortunately you can not hide the password using DbLink, but you can with Teradata views and permissions. What I recommend is that you create a views database over the top of the DbLink Metadata database. The views database can show as many (or few) of the details as you want. In the case of the password, simply do not select it from the base table in the view (or use asterisks or blanks etc in place of the password).
In this case the DbLink Server would still operate against the base database - only DbLink DBA's and the DbLink Server would have access to the base metadata. Other users must go through the views. You can control this via permissions (i.e. GRANT query).
2) No. I do not think it makes sense for an ordinary user to define their own query - they could run anything and bring the remote system, Teradata system, DbLink Server and/or the network (or any combination thereof) to it's knees.
However, if you feel strongly about this, you could allow people to directly insert records into the DbLink metadata table (again I would not advise allowing this). You could let them do it via a utility that you build, or via a generic query tool such as SQL Assistant.
Again, I would not recommend letting users define their own query and running it via DbLink without at least one form of review via a DBA.
 
On the password issue, a few people have asked for encryption, this could be added but would require a work order of some sort (Teradata PSA code). Also, the end product would need to be permitted to be updated on dev ex (this site). Also, note that it isn't just a matter of encrypting the password, some sort of utility will need to be created to allow users (i.e. the DBA) to create and maintain DBLink Entries. This utility would encrypt the password entered by the user using the same algorithm and same encryption keys that the DB Link server uses to decrypt the password and update the DbLink tables.
​An alternative is to simply creating a Encrypt and Decrpyt UDF, but IMHO, this would not help as you would be back at point number 1 - you would have to use permissions and or views to allow the DbLink Server (and maybe DBA's) to access the decrypt function, but not ordinary users. A solution involving encrypt/decrypt UDF's would, again IMHO, become complicated very quickly as other people want to use the same functions for their personal data (but wouldn't be allowed to use them for DbLink data).
My estimate is about 20 days of effort to add encrypted passwords to the DbLink facility and build the afore mentioned utility with basic add/update/delete DbLink entry functionality.
 
If enough teams express interest in this feature (encrypted passwords) and are willing to contribute to the funding and agree to allowing the updated version of DbLink to be published here, I will speak to my management about running such a project (I am sure they would be happy to do it - as long as there is funding).
 

28 Jan 2016

Is there any performance improvement for Java UDF because it is consuming more CPU during ftech the data from Oracle. We can make any improvement. It consumes 14K CPU with only 22 million data using 86 column. We can do any performance improvement or we have any orange book for performance improvement that help to tune the Java UDF

dbc1012 31 comments Joined 03/09
28 Jan 2016

Not really. Indeed, I have logged a report with engineering on this very matter.
The UDF code is very simple - read a block of records from the Server and return them to Teradata - there is virtually no processing in the UDF itself. The High CPU times seems to be in the transition of the data from the JVM on Teradata to Teradata itself. You may note that the DbLink server reports end of data has been sent to the UDF, but Teradata remains in the data acquisition phase for some time after the last row has been sent (use Viewpoint to see this)
Engineering have come back with one suggestion (remove debugging support from the UDF) but I have not had the opportunity to implement it to see if it makes a difference or not. Of course removing debugging support means that if ever there is a problem with the UDF, it might be difficult to diagnose without debugging support. Additionally this is unlikely to address the issue of the delay after the UDF has received all the data and returned it to Teradata.

31 May 2016

We are trying to compile attached views on (the target System) and its failing with error ‘REPLACE VIEW Failed. 9881: Function Dblink called with invalid number or type of parameters’
These views have more than 254 columns in them. Could that be a reason ? Do we have any column limitation for views using dblink function ?
When I reduce number of columns to 254 it works fine, if it’s 254+ columns it doesn’t compile. Is there any limitation with DbLink to not create view or it will not pull more than 254 columns ?

dbc1012 31 comments Joined 03/09
05 Jul 2016

Firstly, apologies for my late reply, I've only just seen and got around to this.
There is a limit of 254 columns, but it isn't a DbLink limitation. UDF's are limited (by Teradata) to 255 paramaters. The DbLink UDF uses 1 parameter to receive it's control information (the 'host name:link name' string). This leaves 254 parameters that may be used to return data.
Thus the RETURNS clause of the create function is limited to 254 columns.
 

You must sign in to leave a comment.