The Friday Night Project #14 – Embedded Processing Part 3

UDA
The UDA channel is for Teradata’s Unified Data Architecture including the Analytical Ecosystem and other UDA influences. This channel provides information specific to the integration and co-existence of multiple systems, in particular when a mix of Aster, Teradata, and Hadoop are present. It is also meant to support information around the UDA enabling technologies so products like Viewpoint, Data Mover, Connectors, QueryGrid, etc.
Teradata Employee

The Friday Night Project #14 – Embedded Processing Part 3

In the first two parts of this Mini Section we have been looking at the concept of Embedding analytical processing directly within the Teradata Database. In the first part we used a SQL Stored Procedure to act as an Isolation layer between the Enterprise Application and the Teradata Database. In the second part we progressed along the same line of providing an Isolation layer but this time we employed the Java External Stored Procedure (JXSP) approach. In this third part we combine the JXSP, as an Isolation layer in order to cross the great divide between the enterprise and the database, with the TZA-InsuranceProcess business processes developed previously.

This approach is part of moving algorithms towards the data (instead of moving large amounts of data from the database to the middle tier, over the network, only to discard it once the algorithm has completed) better utilization of middle tier, network and database resources can be achieved. By pushing algorithms into the EDW we further extend the "One version of the Truth" message by having a single location for important algorithms rather than having these spread around various middle tier applications, which would require complex source code management to keep all versions in sync.

Creating a Fully 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 ALL of the Quotation Engine calculation within the Database.

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

Workspace Launcher

Create "/lib/build" and "/lib/runtime" directories.

Within the TZA-Embedded project we will want to use some external Libraries (JAR files).

JAR’s that are required just for the building of the project are placed into a User Library directory (/lib/build) that can then be referenced as part of the Class Path of the application during the build process.

  • Right click on TZA-Embedded project and select New > Folder
  • Set the Folder name: = 'lib/build' and Select Finish.

New Folder

JAR’s that will be needed at runtime (which in the case of a Java External Stored Procedure means they will need to be installed into the database itself) are placed in a different User Library directory (/lib/runtime). This can be referenced as part of the Class Path of the application during the build process as well as providing a source folder for these JAR files during JXSP Installation process.

  • Right click on TZA-Embedded project and select New > Folder
  • Set the Folder name: = 'lib/runtime' and Select Finish.

Populate the TZA-Embedded Project.

We now start to populate the TZA-Embedded Project with elements such as the Teradata javFnc JAR that allows us to access Teradata Java Functions within the Database.

When we need to add an external library such as commons-logging we can download a copy of the required library (typically these downloads come as a large .ZIP or .TAR file that require you to extract the required jar files using an approach appropriate to your Operating System, such WinZip on Windows), and save the required jar files to the /lib/build or /lib/runtime directories and then add them to the application classpath.

Add Teradata javFnc.jar

Teradata provides a Java library (javFnc.jar) to help write trace statements from a JXSP to a Trace table on Teradata and to allow for programmatic access to the Class Loader for use with the Spring Framework.

The javFnc.jar needs to be copied from the Teradata server to /lib/build directory of the TZA-Embedded project to allow us to compile the code that provides for Spring Framework access to the Teradata Java Function Class Loader. If you have Teradata 12.0 or 13.0 you should have javFnc.jar already on your system. On a Teradata 12.0 Express Edition Windows installation it will be somewhere like C:\Program Files\Teradata\Tdat\LTDBMS\bin).

  • Select javFnc.zip (Open with WinZip) and export javaFnc.jar into the /lib/build directory of the project.

“Special” Character Restriction

While Teradata 12.0 and 13.0 support the installation of External JAR files there is currently a restriction on the use of “Special” characters within the names of these files. The one restricted “Special’ character of note right now is the “-” hyphen which is seen in a lot JAR file names (i.e. commons-logging.jar, tdcommons-access.jar etc). For now the easiest solution is to simply rename any JAR files to remove this “Special” character in order to avoid this restriction.

Add tdcommons-context.

We use Context Capture to maintain a record of application context throughout the Thread of Execution. To utilize these Teradata Common Components download the tdcommons-context.ZIP file and export tdcommons-context.jar into the /lib/runtime directory of your project.

  • Select tdcommons-context.zip (Open with WinZip) and export tdcommons-context.jar into the /lib/runtime directory of the project.
  • Rename the file to tdcommonsContext.jar.

Add tdcommons-access.

The Teradata Access approach is used to provide a consistent approach to accessing a Teradata Database through a JDBC based Data Source / Connection Pool. To utilize these Teradata Common Components download tdcommons-access.jar and save it to the /lib/runtime directory of your project.

  • Select tdcommons-access.zip (Open with WinZip) and export tdcommons-access.jar into the /lib/runtime directory of the project.
  • Rename the file to tdcommonsAccess.jar.

Add TZA-InsuranceProcess.

TZA-InsuranceProcess provides a utility JAR file that ultimately will contain all of the Business Processes used within TZA-Insurance (for now it only contains the Simple Quotation Engine).

  • Within the TZA-InsuranceProcess project Right Click on the TZA-InsuranceProcess.jar within the /dist directory and select Copy.
  • Paste this file into the /lib/runtime directory of the TZA-Embedded project.
  • Rename the file to TZAInsuranceProcess.jar.

Add commons-logging.

As the TZA-InsuranceProcess classes use Apache commons-logging as their logging mechanism we need to include the commons-logging.jar from http://commons.apache.org/logging/.

  • Download the latest commons-logging.jar file to your local /lib/runtime directory.
  • Rename the file to commonsLogging.jar.

Add spring-framework JAR

We will be using the spring-framework to wire our application together. Download the spring-framework from http://www.springframework.org/download.

  • Extract spring.jar into your /lib/runtime directory of the project.

Refresh Eclipse

Whenever changes are made to the directory structure of an Eclipse Project (out with the Eclipse environment) it is necessary to let Eclipse know what is going on "under the covers".

  • Select the TZA-Embedded project and use the F5 function key to refresh the environment.
  • Alternatively Right Click on TZA-Embedded project and select the Refresh option.

Within the Project Explorer view the TZA-Embedded "lib/build" and "lib/runtime" folders will look like this.

Project Explorer

Add the JAR's in /lib/build and /lib/runtime Folders to the classpath

Once all the libraries / JAR files have been added to the /lib/build and /lib/runtime directories they can be added to the project classpath within Eclipse, this allows Eclipse to make these classes available within the editing environment and compile all of the code without errors.

  • Right click on TZA-Embedded and select Build Path > Configure Build Path...
  • In the Properties for TZA- Embedded dialog select the "Libraries" tab -> Add Library... -> User Library
  • Select Next >, User Libraries..., New... and provide a User library name like EmbeddedBuildLib.
  • Use the Add JARs.. button to browse to the TZA- Embedded /lib/build directory.
  • Add all the JAR files to the classpath (Use the Shift Key to select all the files at once).
  • Select Open to "Add" these to your ' EmbeddedBuildLib' User Library.
  • Repeat for the TZA- Embedded /lib/runtime directory.

Java Build Path Preferences

  • Select OK, then Finish, then OK.

The EmbeddedBuildLib and it's associated JAR files will appear in Project Explorer like this.

Project Explorer Build Path

Create JavFncLoader ClassLoader reference

In the previous TZA-Embedded exercise (FNP #13) we created a simple Java External Stored Procedure which was exactly that, a single Java Class (ApplyRiskFactorsToQuote) with a single self contained Java Method (applyRiskFactorsToQuote). This time we are going to create a Java Method which will use the Spring Framework to invoke a whole section of business logic embodied with the TZA-InsuranceProcess.jar file (i.e. the ZipCodeQuotationEngine and the associated JdbcZipCodeRiskFactorsDao). However, in order to achieve this we need to help the Teradata Java Function Class Loader and the Spring Framework to hook up. This help is provided by a tiny helper class we call JavFncLoader as follows.

Add Embedded Package

We start by adding a Java Package to the src/java folder to hold the embedded helper class.

  • Create a new package within src/java (Right Click src/java -> New -> Package ->
  • Set the Name: = com.teradata.commons.embedded.

Add javFncLoader

Start by creating a new class (JavFncLoader.java) in package com.teradata.commons.embedded.

  • Right Click on src/java and select New -> Class
  • Browse to the Package "com.teradata.commons.embedded".
  • Set the class Name to JavFncLoader.
  • Click Finish and a template source file will open within the Eclipse editor.

The JavFncLoader class can then be built up by replacing the template code with the following code segment within the JavFncLoader.java file using copy and paste.

/** 
* 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.commons.embedded;

public class JavFncLoader
{
ClassLoader classLoader;

public JavFncLoader()
{
classLoader = super.getClass().getClassLoader();
}

public ClassLoader getClassLoader()
{
return classLoader;
}
}

Create the EmbeddedSimpleQuotation JXSP.

We can now create the EmbeddedSimpleQuotation JXSP using the Teradata Plug-in for Eclipse JXSP Wizard and Multi-Page Editor.

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_USER
  • 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.

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 EmbeddedSimpleQuotation.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 this is not strictly SQL, neither is it source or configuration either. As it is associated with something that will ultimately end up in the database we chose to use the src/sql directory as the container.

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

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: = EmbeddedSimpleQuotation

Create Java Stored procedure Class

  • Select Next ->

Define JXSP Method

This page allows the user to define the Java 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: = getQuotation
  • Set the Parameter Name = alarmed
  • Select the parameter Type = INTEGER
  • Select the Parameter Mode = IN
  • Select Apply and New
  • Repeat with Parameter Name = buildingsAccidentalCover, Type = INTEGER, Parameter Mode = IN
  • Repeat with Parameter Name = buildingsAmountInsured, Type = DECIMAL(18,2), Parameter Mode = IN
  • Repeat with Parameter Name = buildingsCover, Type = INTEGER, Parameter Mode = IN
  • Repeat with Parameter Name = contentsAccidentalCover, Type = INTEGER, Parameter Mode = IN
  • Repeat with Parameter Name = contentsAmountInsured, Type = DECIMAL(18,2), Parameter Mode = IN
  • Repeat with Parameter Name = contentsCover, Type = INTEGER, Parameter Mode = IN
  • Repeat with Parameter Name = numBedrooms, Type = INTEGER, Parameter Mode = IN
  • Repeat with Parameter Name = ownership, Type = INTEGER, Parameter Mode = IN
  • Repeat with Parameter Name = propertyType, Type = INTEGER, Parameter Mode = IN
  • Repeat with Parameter Name = securityPatrolled, Type = INTEGER, Parameter Mode = IN
  • Repeat with Parameter Name = singleItemLimit, Type = DECIMAL(18,2), Parameter Mode = IN
  • Repeat with Parameter Name = yearBuilt, Type = INTEGER, Parameter Mode = IN
  • Repeat with Parameter Name = zipCode, Type = INTEGER, Parameter Mode = IN
  • Repeat with Parameter Name = quotation, Type = DECIMAL(18, 2) , Parameter Mode = OUT

Create JXSP Method

Note: Teradata does not support the Java Boolean value across this divide so we use the SQL INTEGER Type for parameters such as alarmed and securityPatroled (with a 0 = False and 1 = True convention being applied by the caller and the JXSP code). Similarly the Java Enumeration value is not supported so we again use the SQL INTEGER Type and process the Enumerations to and from this type at the caller and JXSP level (more on this later Toto).

  • Select Next >

Define Answer Sets and Review Parameters

The getQuotation 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.

Create JXSP Parameter Summary

  • Select Next >

Define Jar Files

As we want to use the existing TZA-InsuranceProcess JAR file created in previous projects as the Implementation of the Interface provided by the JXSP it and any other required JAR files (from /lib/runtime) need to be added to this project.

  • Verify the JAR Specification:
  • Container: = /TZA-Embedded
  • JAR Name: = EmbeddedSimpleQuotationJarFile.jar
  • JAR ID: = EmbeddedSimpleQuotationJarId
  • Add the javFncLoader class by selecting Add.. from the Additional Classes pane
  • Browse to build/com/Teradata/commons/embedded/JavFncLoader.class

Add Additional Classes

  • Select OK >
  • Add all the JAR’s on the Build Path using the Add Build Path button in the Additional JARs pane.

Create JXSP Define JAR Files

You can not add the javFnc.jar file as it is pre-installed as part of the Teradata Database, so select it from the list and hit Remove.

  • Select \lib\build\javFnc.jar from the Additional JAR’s List
  • Select Remove to remove it from the Additional JAR’s List
  • 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 GetEmbeddedQuotationJXSP) and the getQuotation method of the EmbeddedSimpleQuotation Java class. A little convoluted perhaps but you are crossing the great divide here.

As ultimately the TZA-InsuranceProcess will need to issue a “SELECT * FROM ZipCodeRiskFactors ..” Query within the JXSP or it’s dependent JAR’s set the SQL Data Access option to “Reads SQL Data”.

Create JXSP DDL Options

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

If you switch to the Source tab of the Multi-Page Editor you will see the default code generated that represents EmbeddedSimpleQuotation JXSP with access to the default JDBC connection 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 EmbeddedSimpleQuotation {

public static void getQuotation(int alarmed, int buildingsAccidentalCover,
BigDecimal buildingsAmountInsured, int buildingsCover,
int contentsAccidentalCover, BigDecimal contentsAmountInsured,
int contentsCover, int numBedrooms, int ownership,
int propertyType, int securityPatrolled,
BigDecimal singleItemLimit, int yearBuilt, int zipCode,
BigDecimal[] quotation) throws SQLException {
// TODO Auto-generated method stub

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

}
}

We will replace this code, a section at time in order to explain what is going on. Start off be clearing all the code in the Source tab of the Multi-Page Editor (Ctrl-A, Ctrl-X should do it). Now Copy and Paste the following code into the Source tab:

/** 
* 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.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.teradata.commons.embedded.JavFncLoader;
import com.teradata.tza.insurance.process.businessObject.Ownership;
import com.teradata.tza.insurance.process.businessObject.Property;
import com.teradata.tza.insurance.process.businessObject.PropertyType;
import com.teradata.tza.insurance.process.exception.ApplicationException;
import com.teradata.tza.insurance.process.exception.SystemException;

import com.teradata.tza.insurance.process.quoteEngine.businessProcess.QuotationEngine;

public class EmbeddedSimpleQuotation
{
/** Logger for this class **/
private static Log log = LogFactory.getLog(EmbeddedSimpleQuotation.class);

/** JavFncLoader statically Loaded on Class Instantiation with access to the javFnc Class Loader**/
private static JavFncLoader javFncLoader = new JavFncLoader();

Most of this is just package, imports and class definition code. The last line is the important one where we create a static instance of JavFncLoader. During it’s construction it has access to the Teradata javFnc Class Loader (which it saves a reference to) that we will need later.

Next Copy and Paste the following code into the Source tab:

   public static void getQuotation(int alarmed, int buildingsAccidentalCover,
BigDecimal buildingsAmountInsured, int buildingsCover,
int contentsAccidentalCover, BigDecimal contentsAmountInsured,
int contentsCover, int numBedrooms, int ownership,
int propertyType, int securityPatrolled,
BigDecimal singleItemLimit, int yearBuilt, int zipCode,
BigDecimal[] quotation) throws SQLException
{
// TODO Auto-generated method stub
Property propertyDetails = new Property();

// initialize required parameters
propertyDetails.setAlarmed(getBooleanValue(alarmed));
propertyDetails.setBuildingsAccidentalCover(getBooleanValue(buildingsAccidentalCover));
propertyDetails.setBuildingsAmountInsured(buildingsAmountInsured);
propertyDetails.setBuildingsCover(getBooleanValue(buildingsCover));
propertyDetails.setContentsAccidentalCover(getBooleanValue(contentsAccidentalCover));
propertyDetails.setContentsAmountInsured(contentsAmountInsured);
propertyDetails.setContentsCover(getBooleanValue(contentsCover));
propertyDetails.setNumBedrooms(numBedrooms);

switch (ownership)
{
case 0:
propertyDetails.setOwnership(new Ownership(Ownership.Mortgaged));
break;

case 1:
propertyDetails.setOwnership(new Ownership(Ownership.Owned));
break;

default:
propertyDetails.setOwnership(new Ownership(Ownership.Rented));
break;
}

switch (propertyType)
{
case 0:
propertyDetails.setPropertyType(new PropertyType(PropertyType.Apartment));
break;

case 1:
propertyDetails.setPropertyType(new PropertyType(PropertyType.Condominium));
break;

case 2:
propertyDetails.setPropertyType(new PropertyType(PropertyType.Detached));
break;

case 3:
propertyDetails.setPropertyType(new PropertyType(PropertyType.Duplex));
break;

default:
propertyDetails.setPropertyType(new PropertyType(PropertyType.Townhouse));
break;
}

propertyDetails.setSecurityPatrolled(getBooleanValue(securityPatrolled));
propertyDetails.setSingleItemLimit(singleItemLimit);
propertyDetails.setYearBuilt(new Integer(yearBuilt).toString());
propertyDetails.setZipCode(new Integer(zipCode).toString());

// initialize optional parameters - not used in calculation
propertyDetails.setHouseNameNumber("A3-2002");
propertyDetails.setStreetAddress1("17095 via Del Campo");
propertyDetails.setCity("San Diego");
propertyDetails.setState("CA");

Here we enter the getQuotation method of the EmbeddedSimpleQuotation JXSP with a series of Java parameters. Some of these can be copied directly into the corresponding properties of the com.teradata.tza.insurance.process.businessObject.Property object. Others such as the Boolean values (alarmed, securitypatrolled, etc) and the Enumeration values (PropertyType and Ownership) need to be transformed according to our predetermined conventions.

Finally Copy and Paste the following code into the Source tab:

        try
{
// Setup the base applicationContext in order to use the Spring Framework - Note refresh is set to false
ClassPathXmlApplicationContext applicationContext =
new ClassPathXmlApplicationContext(new String [] {"com/teradata/tza/insurance/embedded/sqlApplicationContext.xml"}, false);

// Set the javFnc Class Loader captured earlier and refresh the applicationContext with it
applicationContext.setClassLoader(javFncLoader.getClassLoader());
applicationContext.refresh();

// Get the Quotation Engine to use from the applicationContext
QuotationEngine quotationEngine = (QuotationEngine)applicationContext.getBean("sqlQuotationEngine");
quotation[0] = quotationEngine.getQuotation(propertyDetails);
}
catch (ApplicationException appEx)
{
quotation[0] = new BigDecimal(0.0);
log.info(appEx.getMessage());
}
catch (SystemException sysEx)
{
quotation[0] = new BigDecimal(0.0);
log.info(sysEx.getMessage());
}
catch (Exception ex)
{
quotation[0] = new BigDecimal(0.0);
log.info(ex.getMessage());
}
}

/**
*
* @param intValue
* @return
*/
private static boolean getBooleanValue(int intValue)
{
if (intValue == 0)
return false;
else
return true;
}
}

We use the Spring Framework ClasPathXmlApplcationContext class to obtain our applicationContext, however, we use the constructor that allows us to defer loading of the context definitions until later (refresh = false). This allows us to change the Class Loader for the applicationContext to the one known to the javFncLoader (which has knowledge of all the classes in the Additional JARs assigned to the EmbeddeSimpleQuotation JXSP). The applicationContext can then be refreshed and used to obtain the sqlQuotationEngine bean in the normal manner. We then apply the getQuotation(propertyDetails) method as normal and let Spring, TZA-InsuranceProcess and JDBC take care of the rest of the work of wiring up the Business Process and Data Access objects, Querying the ZipcodeRiskFactors table for propertyDetails.getZipCode and conducting the quotation Engine calculation in order to get back a quotation amount to place in the JXSP output parameter of quotation[0].

Deploy the JAR File

Within the JXSP Multi-Page Editor select the “JAR Files” page in the editor. Select the Deploy button. This will create the EmbeddedSimpleQuotationJarFile.jar using the EmbeddedSimpleQuotation class and the JacFncLoader class and deploy it to the database using the Local-12-AS-TZA_USER connection. It will also install the Additional JARs (TZAInsuranceProcess, commonsLogging, spring, tdcommonsContext and tdcommonsAccess) and alter the class path to reflect that EmbeddedSimpleQuotationJarFile depends upon them.

JXSP MPE Deploy JAR

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.

JXSP MPE Run SQL

You should also use the “Automate Build” option within this page in order 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.

Automated Build Wizard

We will use this build file in the next section to include the sqlApplicationContext.xml into the EmbeddedSimpleQuotationJarFile.

Adding sqlApplicationContext.xml to EmbeddedSimpleQuotationJarFile

In the implementation of the getQuotation method above you no doubt recognized the Application Context definition com/teradata/tza/insurance/embedded/sqlApplicationContext.xml which needs to be part of the EmbeddedSimpleQuotationJarFile in order to be accessible on the classpath. The current version of the Teradata Plug-In for Eclipse does not allow you to add resource files (only Additional Classes) within the JAR Files tab of the JXSP Multi-Page Editor (the Teradata Plug-in for Eclipse team is aware of this and will probbably add this capability in an upcoming release).

However, this offers us an opportunity to drill into the automatically generated build.xml file and do some small changes, something you may need to do as your project complexity goes beyond what a general purpose wizard can do for you.

sqlApplicationContext.xml

To use spring within our JXSP we add a new XML file to the src/resources directory called sqlApplicationContext.xml. This file allows us to "wire" Plain Old Java Objects (POJO's) together and is used to configure how the application will operate.

  • Right Click on src/resource within the TZA-Embedded project.
  • Select New -> Package.
  • Set Package Name: = com.teradata.tza.insurance.embedded
  • Click Finish and a new package will be added to src/resources
  • Right Click the com.teradata.tza.insurance.embedded package
  • Select New -> Other -> XML -> XML
  • Select Next>
  • Set the File name: = sqlApplicationContext.xml
  • Click Finish and an empty file will open within the Eclipse editor.

The sqlApplicationContext.xml file can then be built up by adding the following context information 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">

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

<!-- =================== Query Band Configuration Bean ============================== -->
<bean id="queryBandConfig"
class="com.teradata.commons.access.QueryBandConfig"
scope="prototype">
<property name="queryBandingEnabled" value="false"/>
</bean>

<!-- ======================== Session Manager Bean ================================== -->
<!-- JDBC Teradata Access Object Session manager used by Quotation Processes -->
<bean id="jdbcTaoSessionManager"
class="com.teradata.commons.access.JdbcTaoSessionManager"
scope="prototype">
<property name="dataSource" ref="dataSource"></property>
<property name="queryBandConfig" ref="queryBandConfig"></property>
</bean>

<!-- =========================== DataSource Bean ===================================== -->
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="url" value="jdbc:default:connection/charset=utf8"/>
</bean>
</beans>

Pretty standard stuff for an applicationContext file, we import the the TZA-InsuranceProcess SqlQuoteEngine context and define the queryBandContext as false (as we will be running within the database and so should already have our Query Band information as part of the default connection). We define the jdbcTaoSessionManager that the TZA-InsuranceProcess DAO’s will need basing it’s dataSource on a simple Driver Manager data source based on our default JDBC connection.

Add sqlApplicationContext.xml to build.xml

Now we return to the automatically generated build.xml and identify the point where the jar file is created using the ANT <JAR .. task:

       <!-- Create the TZA-Embedded.jar file -->
<jar destfile="${TZA-Embedded.jarbuild.dir}/EmbeddedSimpleQuotationJarFile.jar"
basedir="${TZA-Embedded.build.dir}"
includes="com/teradata/tza/insurance/embedded/EmbeddedSimpleQuotation.class
,com/teradata/commons/embedded/JavFncLoader.class"
/>

Notice how “includes=” references EmbeddedSimpleQuotation.class and JavFncLoader.class. So we need to add sqlApplicationContext.xml file as a third include as so:

       <!-- Create the TZA-Embedded.jar file -->
<jar destfile="${TZA-Embedded.jarbuild.dir}/EmbeddedSimpleQuotationJarFile.jar"
basedir="${TZA-Embedded.build.dir}"
includes="com/teradata/tza/insurance/embedded/EmbeddedSimpleQuotation.class
,com/teradata/commons/embedded/JavFncLoader.class
,com/teradata/tza/insurance/embedded/sqlApplicationContext.xml"
/>

Add Buildfile to Ant View

The TZA-Embedded build.xml can be run from a stand alone Ant environment or from within Eclipse.

  • In Eclipse select Windows -> Show View -> Other... -> Ant -> Ant to open the Ant view
  • Within the Ant view select the Add Buildfiles.. option.
  • Use the Buildfile Selection Dialog to find the TZA-Embedded build.xml and add it to this view.
  • In the Ant View open up the TZA-Embedded - Build file.
  • Double Click the "BuildAll" task to build the EmbeddedSimpleQuotation.jar file (with sqlApplicationContext.xml included), and reinstall the JAR and all dependencies into the database.

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 and ApplyRiskFactorsToQuoteSP Stored Procedures, the previous Java External Stored Procedure ApplyRiskFactorsToQuoteJXSP and the newly deployed GetEmbeddedQuotationJXSP Java External Stored Procedure.

Data Source Explorer GetEmbeddedQuotationJXSP

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

Exercise GetEmbeddedQuotationJXSP Java External Stored Procedure

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

In the Configure Parameters dialog that opens click on the Value entry for each of the elements as shown below and enter the Property details we have used elsewhere in the FNP. Click on OK to run the Stored Procedure with these parameters.

Configure Parameters for GetEmbeddedQuotationJXSP

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 GetEmbeddedQuotationJXSP

You can experiment to see what difference changing the input parameters makes. For the Boolean values like alarmed use 0 or 1 for the Enumerations like Ownership keep 0, 1 or 2 and PropertyType 0, 1, 2, 3 or 4. Again you can experiment with the underlying ZipCodeRisk factors 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.


Well that it for this Friday Night Project, given that it is Wednesday of the following week (3 days of debugging to come up with the 7 lines of code that are JavFncLoader, some holiday!!).

This coming Friday Night Project will be a short one as we wire this Embedded Business Process into the TZA-InsuranceService getQuotation Web service method (how few lines of code do you think I can do that in!!).