Creating a Simple Java User Defined Function using the Teradata Plug-in for Eclipse

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Enthusiast

Re: Creating a Simple Java User Defined Function using the Teradata Plug-in for Eclipse

That is the case. The container project of the UDFs compiles against java 1.5 (uses jre1.6 though).
Enthusiast

Re: Creating a Simple Java User Defined Function using the Teradata Plug-in for Eclipse

Thanks for the helpful article. The problem I am facing is that using Multi-page editor plug-in for eclipse it takes long time to be able to deploy the jar files using the 'Deploy' button on JAR Files tab as well as running sql using 'Run SQL' on the SQL tab. It can take up to 4 to 5 minutes for each operation. Also, after establishing connection a traversal in the Data Source explorer for database like SYSLIB triggers a time consuming operation during which nothing else can be done in eclipse. I have also seen perpetual running process visible in Window> Show view> Progress named 'Run Serialize Schemas'. Could you please suggest where would be the best place to start looking for what the problem might be for these long running processes?
Teradata Employee

Re: Creating a Simple Java User Defined Function using the Teradata Plug-in for Eclipse

Are you caching your schemas? These properties are set in the Teradata JDBC Connection Properties->Cache Properties.
Enthusiast

Re: Creating a Simple Java User Defined Function using the Teradata Plug-in for Eclipse

I have tried both ways. It doesn't make a difference to the time taken in deploying jar files and running the DDL query for the UDF. The 'Run Serialize Schema' process also runs with caching on or off and displays to be 'running' even when its 100% complete - in the process window or on the main status bar on eclipse (at the bottom of the eclipse window). I could send you screenshots if you need a visual.
What would be the best place (other than teradata machine itself) to see what interaction eclipse is doing with the teradata and how long its taking - I am thinking log files but specifically which ones? I will look for an article on logging through eclipse using the teradata plug-in shortly but if it’s already not present it would be great help if one can be written about it.
Teradata Employee

Re: Creating a Simple Java User Defined Function using the Teradata Plug-in for Eclipse

@teraJava, The Serialize Schema process only runs when caching is first turned on and you open the Schemas folder or you specify to 'Refresh the Schema Cache'. If for some reason this process is taking too long, you can kill it by clicking the red box from the Progress view. The cache files can be deleted by clicking the Delete Cache Files in the Connection Properties.

Another option if it is taking a long time to traverse objects in the Data Source Explorer tree is to filter out objects, such as schemas or UDTs, that you don't need. Filters can be enabled by right clicking on the folder and choosing the Properties option.
Enthusiast

Re: Creating a Simple Java User Defined Function using the Teradata Plug-in for Eclipse

Thanks fgrimmer. This definitely resolves the slow traversal in the DSE. I finally managed to turn off the caching which also stops the Serialize Schema process. So please ignore my earlier comment about serialize running when caching was off. I realized that there was an additional pop-up after clicking 'Cache Properties' button which in my previous runs was either not showing up or was getting ignored due to my subsequent clicks etc. due to the long delay in eclipse response.

A suggestion\ question - Is it possible to not have this button but have the caching as one of the properties listed down the left hand side just like other properties e.g. filters, version etc.

Now on to other issues: I am still experiencing delays in deploying jar files and running the install SQL for java UDF through eclipse. Eclipse also seems to take a long time to open the .judf file. Are there logs that I can look at to work out what is taking so long? I suspect that there are non fatal or warning type errors happening which are causing delay in the operation.
Teradata Employee

Re: Creating a Simple Java User Defined Function using the Teradata Plug-in for Eclipse

You can get to the Error log by selecting the menu option
Window->Show View->Error Log in Eclipse
Enthusiast

Re: Creating a Simple Java User Defined Function using the Teradata Plug-in for Eclipse

@js185064, I have not been able to find anything that could help me in the error logs view mentioned. My operations of deploying the jar files using the JUDF and Run SQL functions still take a very long time. E.g. a test java UDF took me about 7 minutes today; all it has is 5 lines of code. It tool similar amount of time to run the SQL using the JUDF file.
Where could I start looking for the problem?
Teradata Employee

Re: Creating a Simple Java User Defined Function using the Teradata Plug-in for Eclipse

You can try using Ant to create your Java User Defined Function. The following article will show you how to do an Ant Build from the Java User Defined Function Multi-Page Editor.
http://developer.teradata.com/tools/articles/automating-a-build-for-java-stored-procedures-using-the-teradata-plug-in-for-eclipse.

If the build is still taking a lot of time, use “-v” on the command line when you invoke the Ant build to see any errors or warnings that may come up.
Enthusiast

Re: Creating a Simple Java User Defined Function using the Teradata Plug-in for Eclipse

 I have following java UDF which i'm calling from external stored procedure. Issue is when an SQLException occurs i get truncated error message in Parent SP Exit handler MESSAGE_TEXT of GET DIAGNOSTIC EXCEPTION 1 clasue


 DECLARE EXIT HANDLER FOR SQLEXCEPTION

 BEGIN

  /* Preserve Diagnostic Codes from errored Statement*/

  SET vSQL_Code  = SQLCODE;

  SET vSQL_State = SQLSTATE;

  SET vCode = vSQL_State;

  GET DIAGNOSTICS EXCEPTION 1 vError_Text = MESSAGE_TEXT, vMsg_length = MESSAGE_LENGTH;

  END IF;

vError text:

"SQLSTATE 38U01: [Teradata Database] [TeraJDBC 14.00.00.09] [Error 3707] [S"

Whereas at trace write the error message is completely logged.

Code is below.

REPLACE PROCEDURE ExternalSP

  (IN iText CLOB(1048576) CHARACTER SET LATIN, 

   OUT oACTIVITY_COUNT INTEGER) 

 LANGUAGE JAVA 

 MODIFIES SQL DATA

 NO EXTERNAL DATA

 PARAMETER STYLE JAVA 

 SQL SECURITY DEFINER

 EXTERNAL NAME 'myExternJAR:ExecuteSQL.exec(java.sql.Clob,int[])'

JAVA CODE:

public class ExecuteSQL{

    /** The connection URL for the XSP internal session. */

    private static String connectionURL = "jdbc:default:connection";

    /**

     * Executes a piece of SQL.

     * @param sqlText the sql to execute.

     * @param activityCount return parameter

     * @throws if there is a problem of some sort.

     */

    public static void exec(Clob sqlText,int activityCount[])

            throws SQLException {

        // Now all we need to is run the query.

        String inRec;

        String sql = "";

        try {

            if(sqlText != null){

                BufferedReader br = new BufferedReader(sqlText.getCharacterStream());

                while ((inRec = br.readLine()) != null) {

                    sql = sql + inRec;

                }

                br.close();

            }

            DbsInfo.traceWrite("Extracted: " + sql);

            DbsInfo.traceWrite("Get connection...");

            Class.forName("com.teradata.jdbc.TeraDriver");

            Connection conn = DriverManager.getConnection(connectionURL);

            DbsInfo.traceWrite("Create Statement...");

            Statement stmt = conn.createStatement();

            DbsInfo.traceWrite("Execute SQL...");

            boolean isSelect = stmt.execute(sql);

            DbsInfo.traceWrite("After Execute SQL...");

            if(!isSelect) {

                DbsInfo.traceWrite("Within !isSelect If...");

                activityCount[0] = stmt.getUpdateCount();

                DbsInfo.traceWrite("Activity Count = "+activityCount[0]);

            } else {

                DbsInfo.traceWrite("Within Else part of !isSelect If...");

                activityCount[0] = 0;

                 DbsInfo.traceWrite("Activity Count = "+activityCount[0]);

            }

            DbsInfo.traceWrite("Close...");

            stmt.close();

            DbsInfo.traceWrite("Done...");

        } catch (IOException e) {

            DbsInfo.traceWrite("IOException: " + e.getMessage());

            throw new SQLException(e.getMessage(), "38U01");

        } catch(SQLException se){

            DbsInfo.traceWrite("SQL Exception :"+se.getMessage());

           throw new SQLException(e.getMessage(), "38U01");

        } catch(ClassNotFoundException CNF){

            throw new SQLException(CNF.getMessage(), "38U01");

        }

    }

}

Any solution to this?