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

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

Enthusiast

Re: Access External Data - A Table UDF

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.

Teradata Employee

Re: Access External Data - A Table UDF

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

Enthusiast

Re: Access External Data - A Table UDF

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

Teradata Employee

Re: Access External Data - A Table UDF

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.

Enthusiast

Re: Access External Data - A Table UDF

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 ?

Teradata Employee

Re: Access External Data - A Table UDF

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.

Re: Access External Data - A Table UDF

When i tried to install DbLink facility in order to establish connection to External database Oracle by reffering the page http://developer.teradata.com/connectivity/articles/access-external-data-a-table-udf

while executing deploy.sh though i was able to create DBLink,DBLinkGetMeta,DBLinkGetVersion UDF's with below warnings for DbLinkTableUDF & DbLinkCommon JAR files .After installation is completes i am
not able to start the DBLink Server by issuing following command.Please let me know if i am missing anythig here.Thanks!


installation warnings.....


call sqlj.install_jar('CJ!/home/userid/DBLink_Install/DbLinkServer/lib/DbLinkCommon.jar', 'DbLinkLib', 0);

* Procedure has been executed.
* Warning: 9241 Check output for possible warnings encountered in Installing or R
eplacing a JAR.
* Total elapsed time was 1 second.

Check output for possible warnings.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 Fri Aug 28 11:36:08 EDT 2015 META-INF/
103 Fri Aug 28 11:36:06 EDT 2015 META-INF/MANIFEST.MF
0 Fri Aug 28 11:36:08 EDT 2015 com/
0 Fri Aug 28 11:36:08 EDT 2015 com/teradata/
0 Fri Aug 28 11:36:08 EDT 2015 com/teradata/dblink/
0 Fri Aug 28 11:36:08 EDT 2015 com/teradata/dblink/common/
2546 Fri Aug 28 11:36:08 EDT 2015 com/teradata/dblink/common/DbLink.class
6508 Fri Aug 28 11:36:08 EDT 2015 com/teradata/dblink/common/DbLinkConnect
ion.class
134 Fri Aug 28 11:36:08 EDT 2015 com/teradata/dblink/common/DbLinkMessage
.class
1897 Fri Aug 28 11:36:08 EDT 2015 com/teradata/dblink/common/DbLinkTaskSum
mary.class
2828 Fri Aug 28 11:36:08 EDT 2015 com/teradata/dblink/common/DbmsHandler.c
lass
418 Fri Aug 28 11:36:08 EDT 2015 com/teradata/dblink/common/LibVersionInf
o.class
2524 Fri Aug 28 11:36:08 EDT 2015 com/teradata/dblink/common/Metrics.class

1503 Fri Aug 28 11:36:08 EDT 2015 com/teradata/dblink/common/Request$Reque
stType.class
2837 Fri Aug 28 11:36:08 EDT 2015 com/teradata/dblink/common/Request.class

1098 Fri Aug 28 11:36:08 EDT 2015 com/teradata/dblink/common/ResultRow.cla
ss
3484 Fri Aug 28 11:36:08 EDT 2015 com/teradata/dblink/common/ResultRowSet.
class
1232 Fri Aug 28 11:36:08 EDT 2015 com/teradata/dblink/common/ServerStatus$
Mode.class
3260 Fri Aug 28 11:36:08 EDT 2015 com/teradata/dblink/common/ServerStatus.
class
996 Fri Aug 28 11:36:08 EDT 2015 com/teradata/dblink/common/Settings.clas
s
1573 Fri Aug 28 11:36:08 EDT 2015 com/teradata/dblink/common/StatusMessage
.class
132 Fri Aug 28 11:36:08 EDT 2015 com/teradata/dblink/common/package-info.
class
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
call sqlj.install_jar('CJ!/home/userid/DBLink_Install/DbLinkTableUDF/DbLinkTableUDF.jar', 'DbLinkTUdf', 0);

* Procedure has been executed.
* Warning: 9241 Check output for possible warnings encountered in Installing or R
eplacing a JAR.
* Total elapsed time was 1 second.

Check output for possible warnings.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 Fri Aug 28 11:37:10 EDT 2015 META-INF/
103 Fri Aug 28 11:37:08 EDT 2015 META-INF/MANIFEST.MF
0 Fri Aug 28 11:37:08 EDT 2015 com/
0 Fri Aug 28 11:37:08 EDT 2015 com/teradata/
0 Fri Aug 28 11:37:08 EDT 2015 com/teradata/dblink/
0 Fri Aug 28 11:37:08 EDT 2015 com/teradata/dblink/tudf/
35365 Fri Aug 28 11:37:08 EDT 2015 com/teradata/dblink/tudf/DbLink.class
1590 Fri Aug 28 11:37:08 EDT 2015 com/teradata/dblink/tudf/UDFRunTime.clas
s
1928 Fri Aug 28 11:37:08 EDT 2015 com/teradata/dblink/tudf/UdfKey.class

 

when i try to start DBLink Server after getting StatisLogger error it is hung up for ever and not returning any thing..

/home/userid/DBLink_Install/DbLinkServer) > java -jar DbLinkServer.jar -j jdbc:teradata://<host>/database=userid -u userid -p password

DBLinkServer_Start_Hungup.png

Re: Access External Data - A Table UDF

Can you please advise on the obove issue.Thanks and appreciate in advance for your help.

Re: Access External Data - A Table UDF

Hello Sir,

If you don't mind can you plaase advise on my issue i posted about DBLink.

Thanks!