Access External Data - A Table UDF

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

Re: Access External Data - A Table UDF

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

Enthusiast

Re: Access External Data - A Table UDF

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.

Teradata Employee

Re: Access External Data - A Table UDF

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.

Enthusiast

Re: Access External Data - A Table UDF

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.

Enthusiast

Re: Access External Data - A Table UDF

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.

Teradata Employee

Re: Access External Data - A Table UDF

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.

Enthusiast

Re: Access External Data - A Table UDF

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

Enthusiast

Re: Access External Data - A Table UDF

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.

Teradata Employee

Re: Access External Data - A Table UDF

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.

Enthusiast

Re: Access External Data - A Table UDF

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