Writing to a file from a Java External Stored Procedure

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

In this blog I would like to share my knowledge on how you can use a Java External Stored Procedure (JXSP) to write to a file on a file system. 

In order to perform an IO operation (e.g. write to a text file) from the JXSP, you need to do the following :

  1. Logon to the file system (OS) of the DBS machine and add the user that will be used to write to the file to the tdatudf group. If the OS user does not exist, execute the following command:

    useradd -G tdatudf MyUser

    If the user already exists, then execute the following command:

    ​usermod -G tdatudf MyUser

  2. Logon to the database (e.g. using BTEQ, SqlAssistant etc) and create an Authorization object that defines the user that the  JXSP will use to write to file.

    ​REPLACE AUTHORIZATION MyDatabase.MyAuthorization AS DEFINER DEFAULT USER 'MyUser' PASSWORD 'pass'

    Note: The username used in the Authorization is the same user that was added to the tdatudf group.

  3. Install the JAR on the database. The JAR contains the Java classes that perform IO operations.

    See sample Java class at bottom. It is assumed that the user knows to package the Java class in a JAR.

    To install the JAR on the database, execute the following SQL using BTEQ, JDBC or any other utility that can execute SQL.

    call sqlj.install_jar ('cj!C:\MyJar.jar', 'XSPJar', 0) ;

    For example: 

    C:/>bteq
    <provide logon credentials>

    Database MyDatabase; -- Change default database
    call sqlj.install_jar ('cj!C:\MyJar.jar', 'XSPJar', 0) -- Install JAR. XSPJar is the name provided to the JAR on the database.

    -- SQLJ access is needed to execute the above sql. E.g.
    GRANT all on SQLJ to logonUser;

  4. Once the JAR is installed, create the JXSP that calls the Java method to write to the file, e.g.

    REPLACE PROCEDURE XSP_WriteSqlToFile 
    (IN Text CLOB(1048544000) CHARACTER SET UNICODE,
    IN filePath VARCHAR(1000) CHARACTER SET UNICODE)
    LANGUAGE JAVA
    NO SQL
    PARAMETER STYLE JAVA
    EXTERNAL NAME 'XSPJar:com.teradata.datamovement.sp.util.StoredProcedureUtil.writeSqlToFile(java.sql.Clob,java.lang.String)'
    EXTERNAL SECURITY DEFINER MyAuthorization;
    • In the above example com.teradata.datamovement.sp.util is the package name and StoredProcedureUtil is the Java class name.
    • writeSqlToFile() is the method that takes a Clob and String as input parameters. See the example at the bottom.
    • Use the Authorization you created previosuly as the Security Definer
  5. Calling the JXSP should now invoke the Java class that will write to a file. E.g: 

    call XSP_WriteSqlToFile ('/home/User/Test.txt', 'HelloWorld');

Java sample code

Finally, this is sample Java code that writes to a file. This class is inside the JAR that gets installed on the database) :

package com.teradata.datamovement.sp.util;

import java.io.FileOutputStream;

public class StoredProcedureUtil {

/**

* Writes SQL updates to repository to a file

* @param rs

* @throws SQLException

*/

public static void writeSqlToFile(Clob changedSql, String fileFullPath) throws SQLException

{

String changedSqlStr = changedSql.getSubString((long)1, (int)changedSql.length());
FileOutputStream sqlOutStream = null;
FileLock sqlLock = null;
FileChannel sqlChannel = null;

try

{

sqlOutStream = new FileOutputStream(fileFullPath, true);
sqlChannel=sqlOutStream.getChannel();
/* Lock the file for write */
sqlLock=sqlChannel.lock();
sqlOutStream.write(changedSqlStr.getBytes());
sqlOutStream.flush();
sqlLock.release();
}
catch (Exception e)
{
throw new RuntimeException (e.getMessage(), e);
}
finally
{
try
{
sqlOutStream.close();
sqlLock.release();
}
catch (IOException e){}
}

}

 

2 Comments

Which node is the file going to reside?

Thank you very much

Vic