All Forums Extensibility
emilwu 72 posts Joined 12/07
05 May 2010
External Store procedure to read a local client file

External Store procedure to read a local client file

I notice that the SQLJ.INSTALL_JAR can read a local binary file and store it to the server side. Wondering how to implement such function to read a client side file and parse and perform some insert/udpate/delete within the store procedure. well, server side IO is not my goal here. Authorization is not what I am looking for. I am looking for how to specify something like this:

call parseFile('filename');

Then the parseFile store proc will read the file and perform SQL actions.

Any hints?

frankjustme 2 posts Joined 09/09
14 May 2010

I looked at TPT for doing this type of server side load. There is a API that you can use that will invoke a fastload on a file from a stored proc. The requirement is that the file is serverside. If you can have some website that will allow an upload from a client and then ftp,scp the file to one of the nodes that could work but there are many potential points for breakage. Do you have any more info on the sqlj.install_jar app that sounds interesting.

emilwu 72 posts Joined 12/07
26 May 2010

well, using scp/ftp to server side is not quite an option here. the node is out of reach from security point of view.
It is interesting to see that the SQLJ.Install_Jar() can actually doing this type of file streaming from simply calling store procedure. There must be something hidden in ODBC/BTEQ/JDBC implementing the client side file read and send bytes to server side socket which can be reached via the store procedure. The idea of using such special function is fun. In practical world, the file read function can always be implemented using client side code. the requirement I mentioned in the original post was trying to get rid of the client side code so that the users can rely on a simple SQL assistant/BTEq and some simple logic can be implemented in the server side. Then processing a data file and load to the target table will be as simple as calling a store procedure without having to deal with load utilities or third party software like AbInitio or Informatica :)

tomnolan 594 posts Joined 01/08
26 May 2010

>>> There must be something hidden in ODBC/BTEQ/JDBC implementing the client side file read and send bytes to server side socket which can be reached via the store procedure.

BTEQ and the Teradata JDBC Driver provide that functionality. At the present time, that functionality is only used for creating UDFs and XSPs, and for SQLJ.INSTALL_JAR.

Adeel Chaudhry 773 posts Joined 04/08
16 Jun 2010

The process of "call parseFile('filename');" can be easily achieved using a combination of UDFs and SPs. UDFs for reading data from file and SPs for using that data and INSERTs/UPDATEs.



-- If you are stuck at something .... consider it an opportunity to think anew.

Kris_Kaza 3 posts Joined 08/10
03 Aug 2010

Dear All

I have just registered to Teradata forum and very new. If my question is posted under wrong section sincere apologies.

In one of our current project data is extracted from 1200 source tables (Residing on Oracle 10G), data is extracted using Oracle Data_Pump + PLSQL into a dump file (.dmp or .exp) and imported into Oracle Target 1200 tables using Oracle PLSQL. Just an overnight CDC from source. No transformation of Data.

We do have intentions to move our Target across to Teradata. When this happens,

Question: Can Teradata utilities read contents from Oracle (.dmp or .exp) file which is binary format in import data to target tables residing on Teradata?

emilwu 72 posts Joined 12/07
26 Aug 2010

Please elaborate the combination you mentioned here..

UDF reading data from file ... I do not think it is possible. The UDF may able to read file on the server node, while it will have NO access to a client system. From pure programming point of view, how can a program running in server process to gain an IO to a different machine?? I like tom's answer better, looks like the JDBC/ODBC/CLI opened a specific channel to gain access to local file system by only recognizing the keyword (sqlj.install_jar, create function) in the SQL requests.

emilwu 72 posts Joined 12/07
26 Aug 2010

your question is not related to this thread. However, Teradata utility cannot read the binary format file generated from the Oracle application. There are ways to do it by programming your own imod library, but I will not bother to do such thing. it will be easier by using standard interface(ODBC/JDBC) to extract the data and put the result into a delimited file or a fixed width text file. Teradata Utilities works quite well with clear text data file.

You must sign in to leave a comment.