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

Re: Access External Data - A Table UDF

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

Enthusiast

Re: Access External Data - A Table UDF

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

Enthusiast

Re: Access External Data - A Table UDF

Can i use this facility in production?

Enthusiast

Re: Access External Data - A Table UDF

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.

Teradata Employee

Re: Access External Data - A Table UDF

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.

Enthusiast

Re: Access External Data - A Table UDF

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  

Enthusiast

Re: Access External Data - A Table UDF

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.

Teradata Employee

Re: Access External Data - A Table UDF

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.

Enthusiast

Re: Access External Data - A Table UDF

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

Teradata Employee

Re: Access External Data - A Table UDF

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.