Last time we introduced the SQL Stored Procedure as a means to provide for Embedded Analytics.

However, as of Teradata 12.0 it is possible to use the Java Language as the basis for External Stored Procedures (known as JXSP’s), so this week we will develop a Java based version of the ApplyRiskFactorsToQuote Stored Procedure.

TD12+

Java External Stored Procedures

A Java External Stored Procedure (JXSP) is a software component, written in Java, which is installed on to and executed within the Teradata database server. Java Stored Procedures are implemented as External Stored Procedures, which means that our normal Java source code is compiled externally to the DBS, into a Java Archive or JAR file. These JAR files are installed into the database, using a traditional Stored Procedure interface maintained within the SQLJ database. Once a JAR file is installed within the Teradata Database one or more Stored Procedure definitions can be mapped to Java methods of the Java classes maintained within the JAR file.

A JXSP provides an advantage over Client or Server based Java applications that access the database remotely (over a JDBC connection) in that a JXSP can take advantage of the existing database access control security. A JXSP minimizes the overhead of network latency because it is operating closer to the data and they can operate faster than Enterprise class Java applications running in an application server because they are running inside their own dedicated Java Virtual Machine (JVM). JXSP methods are safer (from a Teradata database perspective) than traditional Stored Procedures as they are executed via a protected mode server separate from the database process.

Creating an Embedded Analytic Stored Procedure

In order to explore the concept of using Java External Stored Procedures for Embedded Analytics we will investigate how we can conduct a part of the Quotation Engine calculation within the Database.

The Teradata Plug-In for Eclipse that we have being using to exercise Teradata Macros and Stored Procedures is also capable of helping us build Java External Stored Procedures so we will parallel the example provided in creating-a-simple-java-stored-procedure-using-the-teradata-plug-in-for-eclipse
and apply it to the ApplyRiskFactorsToQuote operation (as ApplyRiskFactorsToQuoteJXSP).

Start by returning to the FridayNightProject workspace by starting up Eclipse and selecting the appropriate workspace.

Workspace Launcher

Create the TZA-Embedded Project

JXSP’s require a Java project, to store the Java source code so we conduct our usual New Java project activities in order to create the TZA-Embedded project as follows:

  • Within the Project Explorer view Right Click
  • Select New -> Project.. -> Java –> Java Project
  • Select Next ->
  • Set Project name: = TZA-Embedded
  • Select Next ->
  • Select the “src” Folder
  • Select “Remove source folder 'src' from build path:”
  • Select the "Create a new source Folder" link.
  • Define the Folder name: as "src/java" and select Finish.
  • Repeat for the rest of the normal Source Folders we will use src/config and src/sql
  • Finally change the Default Output folder to TZA- Embedded/build
  • Select Finish.

New Java Project

Open Connection AS TZA_USER

If you have not already done so open up the Data Source Explorer View

  • Window -> Show View -> Other..
  • Connectivity -> Data Source Explorer
  • Select OK

Show View Data Source Explorer

Within the Data Source Explorer view select the Local-12-AS-TZA_USER Connection (as we need to run the Stored Procedure from the same User Name as it was created with, or issue appropriate Grants).

  • Right Click on Local-12-AS-TZA_UUSER
  • Select Connect

Connect AS TZA USER

Launch JXSP Wizard

Once the connection is established expand the TZA-DB Icon and then the “Stored Procedures” node.

  • Right Click on the “Stored Procedures” node
  • Select Teradata->Create Java Stored procedure.

DSE Create Java Stored Procedure

Define JXSP Multi-page Editor File

Within the Create Java Stored Procedure Dialog we define the Multi-Page Editor File by providing a Container for the ApplyRiskFactors.jsxp file (this is an internal file used by the Teradata Plug-In for Eclipse to maintain a record of the various aspects of a given JXSP as it is built and deployed). While it is not strictly SQL neither is it source or configuration and it is associated with something that will ultimately end up in the database so we use the src/sql directory as the container.

  • Browse for the Container /TZA-Embedded/src/sql
  • Set the File name: = ApplyRiskFactorsToQuote

Create Java Stored Procedure

  • Select Next ->

Within the Java Stored Procedure Class dialog:

  • Browse for the Source Folder: = /TZA-Embedded/src/java
  • Set the Package name: = com.teradata.tza.insurance.embedded
  • Set the Class name: = ApplyRiskFactorsToQuote

Java Stored Procedure Class

  • Select Next ->

Define JXSP Method

This page allows the user to define the Java Class method that will ultimately be exposed to the outside world (think of this as the public Interface of the JXSP). As this will ultimately become a Stored Procedure definition (interface) SQL data types are used to define the parameter types in this panel as we prepare to cross the “divide” between SQL and Java.

  • Set the Method name: = applyRiskFactorsToQuote
  • Set the Parameter Name = zipCode
  • Select the parameter Type = INTEGER
  • Select the Parameter Mode = IN
  • Select Apply and New
  • Repeat with Parameter Name = inQuotation, Type = DECIMAL(18, 2), Parameter Mode = IN
  • Repeat with Parameter Name = outQuotation, Type = DECIMAL(18, 2) , Parameter Mode = OUT

Java External Stored Procedure Method

  • Select Next >

Define Answer Sets and Review Parameters

The ApplyRiskFactorsToQuote Java External Stored Procedure is a true Procedural Interface and so does not return any relational answer sets so select Next on the “Answer Set Parameters” page.

Review the Parameters Page, noting how the OUT parameter is a java.math.BigDecimal[] array, as required for JXSP output parameters.

JXSP Parameter Summary

  • Select Next >

Define Jar Files

For this initial foray into JXSP’s we are going to create an extremely simple Java File that does not depend upon any other JAR files. However, within the Define Jar Files page you can add any dependant Jar files your Embedded Analytics might depend upon.

Define Jar Files

  • Select Next >

Define DDL Options

The DDL Options page allows you to specify the name of the JXSP, as seen within the Data Source Explorer etc. The best way to think about this is as a mapping between the SQL Stored Procedure Interface (here called ApplyRiskFactorsToQuoteJXSP) and the applyRiskFactorsToQuote method of the ApplyRiskFactorsToQuote Java class. A little convoluted perhaps but you are crossing the great divide here Toto.

As we will need to issue the normal “SELECT * FROM ZipCodeRiskFactors ..” Query within our JXSP set the SQL Data Access option to “Reads SQL Data”.

JXSP Define DDL Options

Select "Finish" button and the JXSP Multi-Page Editor will be launched.

Using the JXSP Multi-Page Editor

The JXSP Multi-Page Editor brings up the contents of the JXSP properties file (ApplyRiskFactorsToQuote.jxsp) defined through the operation of the JXSP Wizard. The Multi-Page editor allows the user to edit and deploy a JXSP.

Java Stored Procedure Source

Within the JXSP Multi-Page Editor select the “Source” page of the editor. You will see the generated Java code for the stored procedure as so:

package com.teradata.tza.insurance.embedded;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ApplyRiskFactorsToQuote {

    public static void applyRiskFactorsToQuote(int zipCode,
            BigDecimal inQuotation, BigDecimal[] outQuotation)
            throws SQLException {
        // TODO Auto-generated method stub

        Connection con = null;
        con = DriverManager
                .getConnection("jdbc:default:connection/charset=utf8");

    }

} 

Note how this reflects the various IN / OUT parameters (int zipCode, BigDecimal inQuotation, BigDecimal[] outQuotation) we defined in the JXSP Wizard and provides access to the default SQL connection in order to conduct whatever SQL operations we may require.

In order to conduct the Embedded Analysis that is ApplyRiskFactorsToQuote, replace the Java source on this page by using Copy and Paste to enter the code shown below:

/** 
 * The contents of this file are Teradata Public Content and have been released to the Public Domain.
 * Please see the license.txt file in the top level directory for more information.
 *
 * @author Mike.Coutts@Teradata.com
 */
package com.teradata.tza.insurance.embedded;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ApplyRiskFactorsToQuote 
{
    public static void applyRiskFactorsToQuote
    (
            int zipCode,
            BigDecimal inQuotation,
            BigDecimal[] outQuotation) throws SQLException 
     {
        Connection con = null;
        con = DriverManager.getConnection("jdbc:default:connection/charset=utf8");
        try
        {
            /* Executing the command */
            String queryString = new String ("Select fireRisk, floodRisk, theftRisk, subsidenceRisk, otherRisk from ZipCodeRiskFactors where ? BETWEEN StartZipRange AND EndZipRange");
            PreparedStatement stmt = con.prepareStatement(queryString);
            stmt.setInt(1, zipCode);
            ResultSet rs = stmt.executeQuery();
            
            rs.next();
        
            // Conduct the Embedded Analysis
            BigDecimal workingQuotation = inQuotation;
            
            workingQuotation = workingQuotation.multiply(new BigDecimal(rs.getDouble("fireRisk")));
            workingQuotation = workingQuotation.multiply(new BigDecimal(rs.getDouble("floodRisk")));
            workingQuotation = workingQuotation.multiply(new BigDecimal(rs.getDouble("theftRisk")));
            workingQuotation = workingQuotation.multiply(new BigDecimal(rs.getDouble("subsidenceRisk")));
            workingQuotation = workingQuotation.multiply(new BigDecimal(rs.getDouble("otherRisk")));

            // Set the result into the OUT Parameter 
            outQuotation[0] = workingQuotation;
            
            stmt.close();
        }
        catch (Exception e)
        {
            throw new SQLException(e.getMessage(),"38100");
        }
    }
}

Basically we conduct exactly the same exercise, within this JXSP, as we did within the Java world in that we issue the SELECT * FROM ZipCodeRiskFactors .. query in order to get the Risk Factors associated with a given ZipCode value (provided as input). We then apply these risk factors to the input quotation value in order to generate a working quotation value that is passed back through the output quotation parameter.

Note the use of the outQuotation[0] as the transport mechanism for the output parameter.

While this example is, by design, extremely simple it should be clear that you have the full power of both Java and SQL available to you directly within the Teradata Database at this point. Imagine if our algorithm required us to access a number of tables (with the result of one query being used to either select between one access and another or become the input to a subsequent query, not something that SQL is particularly adept at) how each access is conducted within the Teradata database with all orchestration being done internally to the JXSP. The big advantage is being able to operate right up beside the data, which removes the effects of network latency, as well as being able to Leverage, Extend and Reuse existing Java based algorithms.

Deploy the JAR File

Within the JXSP Multi-Page Editor select the “JAR Files” page in the editor. Select the Deploy button. This will deploy the JAR for the JXSP onto Teradata using the Local-12-AS-TZA_USER connection.

Deploy the Jar File

Run Generated SQL

Within the JXSP Multi-Page Editor select the “SQL” page in the editor. Select the “Run SQL” button in order to create the connection between the Stored Procedure interface and the Method of the Java Class.

Generated SQL for JXSP

You can also use the “Automate Build” option within this page to create a build.xml file in a similar vein to the ones we built by hand in previous FNP exercises, with Ant tasks to deploy the Jar files and create the corresponding Stored Procedure interface.

Testing the Embedded Analytics Java External Stored Procedure

Assuming everything is configured correctly we should now be able to exercise this new Database element. In the Data Source Explorer view expand the TZA_DB item, then the Schemas item to see the TZA_DB database. Expand this to see its contained objects including Macros and Stored Procedures. Expand the Stored Procedures icon to see the GetZipCodeRiskFactorsSP Stored Procedure, the previous ApplyRiskFactorsToQuoteSP Stored Procedure and the newly deployed ApplyRiskFactorsToQuoteJXSP Java External Stored Procedure.

ApplyRiskFactorsToQuoteJXSP

Note nothing but the naming convention we have applied singles this out as a JXSP within this view.

Exercise ApplyRiskFactorsToQuoteJXSP Java External Stored Procedure

To exercise / test the ApplyRiskFactorsToQuoteJXSP Java External Stored Procedure Right Click on it and select Run...

In the Configure Parameters dialog that opens click on the Value entry for the ZipCode Parameter and insert a valid Zip Code, say 92127. Click on Value entry for the inQuotation Parameter and insert a valid Quotation amount, say 100. Click on OK to run the Stored Procedure with these parameters.

Configure Parameters

In the SQL Results view you will see the parameter returned by the Stored Procedure based upon the Input Parameter values provided.

SQL Results for Apply Risks JXSP

You can experiment to see if the calculation is correct by changing say the Fire Risk value in the ZipCodeRiskFactors Table for a given ZipCode range. Double the RiskFactor from 1.0 to 2.0 and the outQuotation value should double to say 200. Half it from 1.0 to 0.5 and the outQuotation value should half to say 50.

Implementing the Simple Quotation Engine (Quotation Transform JXSP)

The ApplyRiskFactorsToQuoteJXSP Implementation conforms to the same Interface as the ApplyRiskFactorsToQuoteSP we defined earlier and used within the Quotation Transform process. As we provided a hook into that process (by way of the setStoredProcedureName method of the zipCodeRiskFactorsTransformDao Interface) we now have a “configuration only” means of adding this JXSP based implementation to our TZA-InsuranceProcess and therefore the TZA-InsuranceService projects.

Add the jxspTransformQuoteEngineAppContext.xml

Spring Application Context files are used to "wire" Plain Old Java Objects (POJO's) together. We will create a jxspTransformQuoteEngineAppContext.xml to configure how this part of the application will operate. We will use this file to create a version of the SimpleQuoteEngine that uses the ZipCodeTransformQuotationEngine and the ApplyRiskFactorsToQuote Java External Stored Procedure.

To add jxspTransformQuoteEngineAppContext.xml start with the TZA-InsuranceProcess project:

  • Expand the src/resources folder
  • Right click on the com.teradata.tza.insurance.process.quoteEngine package
  • Select New -> Other.. -> XML -> XML
  • Set the file name as jxspTransformQuoteEngineAppContext.xml
  • Select Finish and a template XML file will open within the Eclipse editor.

The jxspTransformQuoteEngineAppContext.xml file can then be built up by replacing the template code with the following code segments within the jxspTransformQuoteEngineAppContext.xml file using copy and paste.

<?xml version="1.0" encoding="UTF-8"?>

<beans
 xmlns="http://www.springframework.org/schema/beans"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
   
    <!-- TZA-InsuranceProcess Transform based Quotation Engine -->
    <bean id="jxspTransformQuotationEngine"
          class="com.teradata.tza.insurance.process.quoteEngine.businessProcess.ZipCodeTransformQuotationEngine"
          scope="prototype">
        <property name="taoSessionManager" ref="jdbcTaoSessionManager"/>    
        <property name="zipCodeRiskFactorsTransformDao" ref="zipCodeRiskFactorsTransformDao"/>
    </bean>
    
    <bean id="zipCodeRiskFactorsTransformDao"
          class="com.teradata.tza.insurance.process.quoteEngine.repository.JdbcZipCodeRiskFactorsTransformSPao"
          scope="prototype">
        <property name="storedProcedureName" value="ApplyRiskFactorsToQuoteJXSP"/>    
    </bean>

 </beans>

Note how we define a new bean called jxspTransformQuotationEngine, implemented by the ZipCodeTransformQuotationEngine, which has a property of ZipCodeRiskFactorsTransformDao that is implemented using the JdbcZipCodeRiskFactorsTransformSPao object and in this instance the ApplyRiskFactorsToQuoteJXSP Stored Procedure.


Rebuild the TZA-InsuranceProcess.jar

  • Within the Ant View: Open up the TZA-InsuranceProcess - Local Build file.
  • Double Click the jar default task to create the TZA-InsuranceProcess.jar file.

Refresh Eclipse

Because the Ant build works at the Hard Disk Level you need to Refresh Eclipse so it can pick up changes such as the creation of the /dist directory and the TZA-InsuranceProcess.jar.

  • Select the TZA-InsuranceProcess project and use the F5 function key to refresh the environment.
  • Open up the /dist directory and the TZA-InsuranceProcess.jar file will be visible.

Testing the Embedded Analytic Java External Stored Procedure Integration

So now that we have created the new TZA-InsuranceProcess.jar file with a configuration that can access a Java External Stored Procedure, which can access the Zip Code Risk Factors table and perform Embedded Analytics upon the results, we better test if this works.

Update the TZA-InsuranceProcess.jar

First task is to copy the new version of the TZA-InsuranceProcess.jar from the /dist directory of the TZA-InsuranceProcess project to the /lib directory of TZA-InsuranceService.

  • Expand the /dist folder within TZA-InsuranceProcess
  • Right click on TZA-InsuaranceProcess.jar
  • Select Copy (or use Ctrl-C)
  • Expand the WEB-INF/lib folder within TZA-InsuranceService
  • Right Click on the lib folderSelect Paste (or use Ctrl-V)
  • On the Resource Exists dialog select Yes to overwrite with the new version

Defining the “jdbcTransformQuotationEngine” Bean in applicationContext.xml

Now we revisit how the Quotation Engine is defined within the applicationContext.xml file.

  • Expand the web/WEB-INF folder within TZA-InsuranceService.
  • Open applicationContext.xml (Double Click it, or Right click it and select Open or F3)
  • Search for the following line of code
    <!-- Define the simpleQuotationEngine bean as one or other of sql, macro or storedProc -->
    <bean id="simpleQuotationEngine" parent="transformQuotationEngine" scope="prototype"/>

The simpleQuotationEngine bean will be defined as sql, macro, storedProcQuotationEngine or transformQuotationEngine (depending upon the last approach you have been testing with). Modify the simpleQuotationEngine definition to make an association with the jdbcTransformQuotationEngine as follows:

    <!-- Define the simpleQuotationEngine bean as one or other of sql, macro or storedProc -->
    <bean id="simpleQuotationEngine" parent="jxspTransformQuotationEngine" scope="prototype"/>

    <!-- Import the TZA-InsuranceProcess QuoteEngine context -->
    <import resource="classpath:com/teradata/tza/insurance/process/quoteEngine/sqlQuoteEngineAppContext.xml" />
    <import resource="classpath:com/teradata/tza/insurance/process/quoteEngine/macroQuoteEngineAppContext.xml" />
    <import resource="classpath:com/teradata/tza/insurance/process/quoteEngine/storedProcQuoteEngineAppContext.xml" />
    <import resource="classpath:com/teradata/tza/insurance/process/quoteEngine/transformQuoteEngineAppContext.xml" />
    <import resource="classpath:com/teradata/tza/insurance/process/quoteEngine/jdbcTransformQuoteEngineAppContext.xml" />

Note the fifth import statement used to bring the jdbcTransformQuoteEngineAppContext into the Application Context.

Start the Application Server

If you had closed down your application server you will need to start it up.

  • Start / Restart the Server using the Start Icon on the Server View

Restart Server

Start the Web service Test Client

You will probably need to start up the Web service Test Client again also. You can simply browse to it by pointing your favorite Web browser at http://localhost:8080/TZA-InsuranceServiceClient/samplePropertyInsurance.... Alternatively you can stay within Eclipse.

  • Open up the TZA-InsuranceServiceClient project WebContent/samplePropertyInsuranceProxy/ directory.
  • Right Click TestClient.jsp
  • Select Run As.. -> Run on Server -> Finish
  • Within the Web Services Test Client browser select the getQuote method in the Methods frame.
  • Scroll down through the Inputs frame and Select the Invoke button.
  • Examine the Web service response in the Result frame.

Web service Test Client

Examine the Query using DBQL

We can use DBQL to examine exactly what queries are being used as we exercise the getQuote method of the Web service. Clear any existing DBQL entries and Begin Query Logging by selecting the appropriate SQL within the ManageDBQL.sql file.

  • Expand the src/sql folder within TZA-Database.
  • Open ManageDBQL.xml (Double Click it, or Right click it and select Open or F3)
  • Search for the following line of code
--
--Clear Existing DBQL and BEGIN QUERY LOGGING;
--
DELETE FROM DBC.DBQLogTbl;
BEGIN QUERY LOGGING ON ALL;

Right Click on the selected text and select Execute as Individual Statements.

Run the getQuote Web service Method again by selecting Invoke within the Web service Test Client. Check the Quotation amount against the one you wrote down a week or two ago (it’s probably fallen down behind the back of your LCD panel by now!).

Lock down the Query Logging by running the appropriate SQL within the ManageDBQL.sql file:

--
-- END QUERY LOGGING and Show the current DBQL;
--
END QUERY LOGGING ON ALL;
SELECT QueryBand, QueryText FROM DBC.DBQLogTbl order by StartTime;

Once the execution is complete we can drill into the final operation “SELECT QueryBand..” and open up the Result1 View so we can see how each statement was executed with “CALL ApplyRiskFactorsToQuoteJXSP(?, ?, ?);” displayed as the Query Text.

ApplyRiskFactorsToQuoteJXSP SQL Results

Note the SELECT fireRisk, floodRisk.. FROM ZipCodeRiskFactors.. query that is being issued from within the JXSP. Also note that it has all the same Query Band Information attached to it as the original JXSP call as they exist within the same transaction.


So there you have another way to interact with the Teradata database using Java External Stored Procedures. However, this time our emphasis was really on embedding, Java based, processing logic (albeit relatively simple stuff) within the Teradata Database.

Next time we will look at how to extend the Embedded Analytics concept a little more deeply through the use of dependent JAR files as part of the Java External Stored Procedure approach.