The Friday Night Project #15 – Embedded Processing Part 4

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 #15 – Embedded Processing Part 4

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. Part one used a SQL Stored Procedure to act as an Isolation layer between the Enterprise Application and the Teradata Database. Part two replicated this isolation concept using the Java External Stored Procedure (JXSP) approach. Part three combined the JXSP with the previously prepared, Spring Framework based, Business Processes. This final Part illustrates how to little code is now required in the Presentation Veneer (Web service process) due to the embedding of the Business Logic within the Teradata Database.

So having established the GetEmbeddedQuotationJXSP and its associated Java Method we now look at how to update the TZA-InsuranceProject in order to provide an alternative implementation of the Quotation Engine Interface. We can then connect this into the TZA-InsuranceService Web service as before in order to test out the full round trip from the Web service client through the middle tier into the database.

Update the TZA-InsuranceProcess Project.

Start up your Eclipse IDE (including the Teradata Plug-In) against your Friday Night Project Workspace.

Workspace Launcher

Create the Repository / Data Access Objects.

Having created the EmbeddedSimpleQuotation JXSP we need to provide a Data Access mechanism that can be integrated into our Business services. The Property Details object can be seen as the input to the Embedded Simple Quotation Engine Business Process so our Data Access Object needs to concentrate on getting all of the properties of this object across the Java / SQL divide.

Add the EmbeddedQuotationEngineTao Interface.

In accordance with the Solid Architecture Principles we discussed many weeks ago we will create an Interface class and a Concrete implementation of the EmbeddedQuotationEngineTao DAO class.

  • Right Click on src/java and select New -> Interface
  • Browse to the Package "com.teradata.tza.insurance.process.quoteEngine.repository".
  • Set the class Name to EmbeddedQuotationEngineTao.
  • Click Finish and a template source file will open within the Eclipse editor.

The EmbeddedQuotationEngineTao Interface can then be built up by replacing the template code with the following code segment within the EmbeddedQuotationEngineTao.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.tza.insurance.process.quoteEngine.repository;
import java.math.BigDecimal;
import com.teradata.commons.access.TaoSession;
import com.teradata.commons.access.exception.DataAccessException;
import com.teradata.tza.insurance.process.businessObject.Property;

public interface EmbeddedQuotationEngineTao
{
public void setTaoSession(TaoSession taoSession);
public BigDecimal getQuotation(Property propertyDetails) throws DataAccessException;
}

Add the JdbcEmbeddedQuotationEngineTao Class.

In order to access the EmbeddedSimpleQuotation JXSP we will be using a pure JDBC implementation of the EmbeddedSimpleQuotation interface. This means we will be embedding the required SQL directly within the Java code. This provides for the greatest amount of control over how we access the underlying repository but does so at the cost of requiring us to change Java code should we need to change the table or repository structure. However, as we are interacting with a defined Java Stored procedure interface here this should work out fine.

To add the JdbcEmbeddedQuotationEngineTao class:

  • Within TZA-InsuranceProcess Right Click on src/java and select New -> Class
  • Browse to the Package "com.teradata.tza.insurance.process.quoteEngine.repository".
  • Set the class Name to JdbcEmbeddedQuotationEngineTao.
  • Click Finish and a template source file will open within the Eclipse editor.

The JdbcEmbeddedQuotationEngineTao Class can then be built up by replacing the template code with the following code segment within the JdbcEmbeddedQuotationEngineTao.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.tza.insurance.process.quoteEngine.repository;

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.math.BigDecimal;

import com.teradata.commons.access.TaoSession;
import com.teradata.commons.access.exception.DBException;
import com.teradata.commons.access.exception.DataAccessException;

import com.teradata.tza.insurance.process.businessObject.Ownership;
import com.teradata.tza.insurance.process.businessObject.Property;
import com.teradata.tza.insurance.process.businessObject.PropertyType;

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

private TaoSession taoSession = null;
public void setTaoSession(TaoSession taoSession)
{
this.taoSession = taoSession;
}

/**
*
* @param propertyDetails
* @return ZipCodeRiskFactors
* @throws DataAccessException
*/
public BigDecimal getQuotation(Property propertyDetails) throws DataAccessException
{
String sCall = "{CALL \"GetEmbeddedQuotationJXSP\"(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}";
CallableStatement cStmt = null;

BigDecimal quotation = null;

// Make the Stored Procedure Call
try
{
cStmt = taoSession.prepareCall(sCall);

cStmt.setInt(1, getBooleanInt(propertyDetails.isAlarmed()));
cStmt.setInt(2, getBooleanInt(propertyDetails.isBuildingsAccidentalCover()));
cStmt.setBigDecimal(3, propertyDetails.getBuildingsAmountInsured());
cStmt.setInt(4, getBooleanInt(propertyDetails.isBuildingsCover()));
cStmt.setInt(5, getBooleanInt(propertyDetails.isContentsAccidentalCover()));
cStmt.setBigDecimal(6, propertyDetails.getContentsAmountInsured());
cStmt.setInt(7, getBooleanInt(propertyDetails.isContentsCover()));
cStmt.setInt(8, propertyDetails.getNumBedrooms());

// Ownership Enumeration
if (propertyDetails.getOwnership().equals(Ownership.Mortgaged))
cStmt.setInt(9, 0);
else if (propertyDetails.getOwnership().equals(Ownership.Owned))
cStmt.setInt(9, 1);
else
cStmt.setInt(9, 2);

// PropertyType Enumeration
if (propertyDetails.getPropertyType().equals(PropertyType.Apartment))
cStmt.setInt(10, 0);
else if (propertyDetails.getPropertyType().equals(PropertyType.Condominium))
cStmt.setInt(10, 1);
else if (propertyDetails.getPropertyType().equals(PropertyType.Detached))
cStmt.setInt(10, 2);
else if (propertyDetails.getPropertyType().equals(PropertyType.Duplex))
cStmt.setInt(10, 3);
else
cStmt.setInt(10, 4);

cStmt.setInt(11, getBooleanInt(propertyDetails.isSecurityPatrolled()));
cStmt.setBigDecimal(12, propertyDetails.getSingleItemLimit());
cStmt.setInt(13, new Integer(propertyDetails.getYearBuilt()).intValue());
cStmt.setInt(14, new Integer(propertyDetails.getZipCode()).intValue());

// Register the only output parameter
cStmt.registerOutParameter(15, Types.DECIMAL);

log.debug("Getting Quotation for " + propertyDetails.getZipCode() + " using " + sCall);
cStmt.executeQuery();

quotation = cStmt.getBigDecimal(15);
cStmt.close();
}
// Check for unrecoverable database errors
catch (SQLException ex)
{
// Translate SQLException to DBException
log.error(TaoSession.FindError + ":" + ex.getMessage());
DBException dbEx = new DBException(TaoSession.FindError, ex);
throw dbEx;
}
catch (DBException ex)
{
log.error(TaoSession.FindError + ":" + ex.getDbMsg());
throw ex;
}
finally
{
// Close cStatement to prevent leaks
try
{
if (cStmt != null)
cStmt.close();
}
catch (SQLException ex)
{
log.error("Error closing result set or statement" + ex);
DBException dbEx = new DBException ("Error closing result set or statement", ex);
throw dbEx;
}
}

return quotation;
}

private int getBooleanInt(boolean booleanValue)
{
if (booleanValue)
return 1;
else
return 0;
}
} // end JdbcEmbeddedQuotationEngineTao

All relatively standard code for Stored Procedure setup and calling. Note the helper routine getBooleanInt, which is used to transform from the Java Boolean type to a simple Integer (0 representing false, 1 representing true). Note also how the Ownership and PropertyType Enumerations are converted to Integer values in order to cross the Java / SQL divide (if you recall from last week, Toto, we have similar but inverse processes within the EmbeddedSimpleQuotation JXSP to convert these back from SQL to Java).

Adding Embedded Quotation Engine to TZA-InsuranceProcess.

Finally we add an implementation of the QuotationEngine interface that can utilize these Data Access Objects. First review the QuotationEngine interface from the com.teradata.tza.insurance.process.quoteEngine.businessProcess package.

/** 
* 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.process.quoteEngine.businessProcess;

import java.io.Serializable;
import java.math.BigDecimal;

import com.teradata.commons.access.TaoSessionManager;
import com.teradata.tza.insurance.process.exception.ApplicationException;
import com.teradata.tza.insurance.process.exception.SystemException;
import com.teradata.tza.insurance.process.businessObject.Property;

public interface QuotationEngine extends Serializable
{
public void setTaoSessionManager(TaoSessionManager taoSessionManager);
public BigDecimal getQuotation(Property propertyDetails) throws ApplicationException, SystemException;
}

Now we need to add a new implementation (EmbeddedQuotationEngine ) of this interface that will work with the JdbcEmbeddedQuotationEngineTao Data Access Object and therefore the EmbeddedSimpleQuotation JXSP.

To add the EmbeddedQuotationEngine class:

  • Within TZA-InsuranceProcess Right Click on src/java and select New -> Class
  • Browse to the Package "com.teradata.tza.insurance.process.quoteEngine.businessProcess".
  • Set the class Name to EmbeddedQuotationEngine.
  • Click Finish and a template source file will open within the Eclipse editor.

The EmbeddedQuotationEngine Class can then be built up by replacing the template code with the following code segment within the EmbeddedQuotationEngine.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.tza.insurance.process.quoteEngine.businessProcess;

import java.math.BigDecimal;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.teradata.commons.access.TaoSession;
import com.teradata.commons.access.TaoSessionManager;
import com.teradata.commons.access.exception.DBException;
import com.teradata.commons.access.exception.DataAccessException;
import com.teradata.tza.insurance.process.exception.ApplicationException;
import com.teradata.tza.insurance.process.exception.SystemException;
import com.teradata.tza.insurance.process.businessObject.Property;
import com.teradata.tza.insurance.process.quoteEngine.businessProcess.QuotationEngine;
import com.teradata.tza.insurance.process.quoteEngine.repository.EmbeddedQuotationEngineTao;

public class EmbeddedQuotationEngine implements QuotationEngine
{
/** Logger for this class **/
private static Log logger = LogFactory.getLog(EmbeddedQuotationEngine.class);

private static final long serialVersionUID = 1L;

private EmbeddedQuotationEngineTao embeddedQuotationEngineTao;

public void setEmbeddedQuotationEngineTao(EmbeddedQuotationEngineTao embeddedQuotationEngineTao)
{
this.embeddedQuotationEngineTao = embeddedQuotationEngineTao;
}

private TaoSession taoSession = null;
private TaoSessionManager taoSessionManager = null;
public void setTaoSessionManager(TaoSessionManager taoSessionManager)
{
this.taoSessionManager = taoSessionManager;
}

public BigDecimal getQuotation(Property propertyDetails) throws ApplicationException, SystemException
{
logger.info("getEmbeddedQuotation for " +
propertyDetails.getHouseNameNumber() + " " + propertyDetails.getStreetAddress1() + " " + propertyDetails.getZipCode());

BigDecimal quotation = null;

try
{
// Create Session
taoSession = taoSessionManager.getSession();
logger.debug("Session created:" + taoSession);

// Initialize DAO objects - set current session
embeddedQuotationEngineTao.setTaoSession(taoSession);

// Begin session / transaction
taoSession.beginSession();
taoSession.beginTransaction();

// The quotation amount is returned from the getQuotation method based on the propertyDetails.
quotation = embeddedQuotationEngineTao.getQuotation(propertyDetails);

// Commit transaction
taoSession.commitTransaction();
}
// Catch recoverable application error - zipCodeRiskFactors not found
catch (DataAccessException ex)
{
// Rollback Transaction
taoSession.rollbackTransaction();

// Throw Application Exception
ApplicationException appEx = new ApplicationException("getQuotation exception", ex);
throw appEx;
}
// Catch unrecoverable database error
catch (DBException ex)
{
// Rollback Transaction
taoSession.rollbackTransaction();

// Throw System Exception
SystemException sysEx = new SystemException("getQuotation exception", ex);
throw sysEx;
}
// Always end session
finally
{
taoSession.endSession();
}

return quotation;
}

} // end EmbeddedQuotationEngine

If you remember all the code that was in the original SqlZipCodeQuotationEngine implementation, which is now embedded within the EmbeddedSimpleQuotation JXSP. Ultimately the middle tier algorithm becomes a very thin routing layer between the Presentation Veneer and the Data Access Object that calls upon the Java Stored Procedure i.e.:

        // The quotation amount is returned from the getQuotation method based on the propertyDetails.
quotation = embeddedQuotationEngineTao.getQuotation(propertyDetails);

Configure EmbeddedQuotationEngine within the Application Context.

As the EmbeddedQuotationEngine concrete class, within the TZA-InsuranceProcess implements the QuotationEngine interface it is a simple exercise to include another Application Context XML file as follows:

  • Within TZA-InsuranceProcess open up the src/resource folder.
  • Right Click on the com.teradata.tza.insurance.process.quoteEngine
  • Select File -> Other –XML -> XML
  • Set the File Name: = embeddedQuotationEngineAppContext.xml.
  • Click Finish and a template XML file will open within the Eclipse editor.

The embeddedQuotationEngineAppContext.xml can then be built up by replacing the template code with the following code segment within the embeddedQuotationEngineAppContext.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 Embedded JXSP Quotation Engine -->
<bean id="embeddedQuotationEngine"
class="com.teradata.tza.insurance.process.quoteEngine.businessProcess.EmbeddedQuotationEngine"
scope="prototype">
<property name="taoSessionManager" ref="jdbcTaoSessionManager"/>
<property name="embeddedQuotationEngineTao" ref="embeddedQuotationEngineTao"/>
</bean>

<bean id="embeddedQuotationEngineTao"
class="com.teradata.tza.insurance.process.quoteEngine.repository.JdbcEmbeddedQuotationEngineTao"
scope="prototype">
</bean>

</beans>

Note how we define a new bean called embeddedQuotationEngine, implemented by the EmbeddedQuotationEngine, whose embeddedQuotationEngineTao property is provided by our JdbcEmbeddedQuotationEngineTao implementation, which therefore uses the EmbeddedSimpleQuotation JXSP.

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.

Project Explorer

Testing the Embedded Analytic Java External Stored Procedure Integration.

So now that we have created the new TZA-InsuranceProcess.jar file which can utilize the EmbeddedSimpleQuotation JXSP in order to conduct the entire Quotation Engine business process within the database, 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 “embeddedQuotationEngine” 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="storedProcQuotationEngine" 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 embeddedQuotationEngine as follows:

    <!-- Define the simpleQuotationEngine bean as one or other of sql, macro or storedProc -->
<bean id="simpleQuotationEngine" parent="embeddedQuotationEngine" 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/embeddedQuoteEngineAppContext.xml" />

Note the fifth import statement used to bring the embeddedQuoteEngineAppContext 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

Start 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
  • ithin 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 Queries 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;
-- Select these Queries, Right Click and Ececute as Individual Statements
--
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 was probably in that pile pf paper you shredded yesterday, Toto).

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 GetEmbeddedQuotationEngineJXSP(?,?,?,?,?,?,?,?,?,?,?,?,?,?);” displayed as the Query Text.

Embedded SQL Results

Note how the GetEmbeddedQuotationJXSP call is made into the database and how it execute the normal Select from ZipCodeRiskFactors from within database. While this algorithm only requires a single table access you can imagine how a more complex algorithm making multiple SQL requests would gain an advantage from being embedded closer to the data. This becomes particularly important when we consider disaster recovery type scenarios where network latency between the traditional middle teir server and the database could be multiplied up by thousands of miles of network.


So there you go multiple ways to provide for the Simple Quotation Engine (SQL, Macros, Stored Procedure, Java External Stored Procedure). We can even embedd the entire Spring Framework based middle tier algorithm within the database through the use of the JavFncLoader class.

The Friday Night Project is going to take a short break (as you have plenty to be experimenting with for now) but when we come back we will look at some alternative ways to access Teradata beyond pure JDBC with Object-Relational Mapping tools such as iBatis.