The Friday Night Project #11 – Macros and Stored Procedures

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 #11 – Macros and Stored Procedures

Last time we did a full on What, Why and How description of some pretty Teradata specific information around Query Banding and showed how we could weave this into the Web Application and Data Access Layers so as to minimize the impact of this on the Business Service layer and it’s developers.

This week we are going to keep on the core Teradata trail by looking into Macros and Stored Procedures as means to provide for Isolation and Embedded Processing.

We will start the exploration of Macros and Stored Procedures by returning to the TZA-Database project within the FridayNightProject by starting up Eclipse and selecting the appropriate workspace.

Workspace Launcher

In the Project Explorer View open up the src/sql directory as this is where we will add the appropriate Create and Delete files to hold the various Macro and Stored Procedure definitions. We will also be adding appropriate elements to build.xml so that we can build and rebuild TZA_DB with all it’s Tables and Data plus the new Macro and Stored Procedure we will add this week.

Project Explorer - TZA-Database

Macros

Teradata Macros provide a means to provide an alternative Isolation Layer that allows us to protect the application implementation from changes in the underlying database. A Macro definition allows us to control the application calling mechanism for a given request (name of the Macro, type and order of parameters). It allows us to define what that calling mechanism does in terms of the SQL it executes (SQL Statement, Table and Column names), which provides a level of isolation in that should we require to change the Table or Column names within the database then we only need to change the Macro definition and not some SQL embedded within the Java code of the Data Access Objects.

The only break in our Isolation principle is that the developer does need some knowledge of the Result Set format that the Macro will return (in terms of how many columns are in each row of the result set and the data types associated with each of these), which restricts the level of change available (without recoding the DAO) to SQL within the Macro that reflects the underlying Database.

CreateTZA_Macros.sql

Start by creating a new SQL file (CreateTZA_Macros.sql) in the src/sql directory.

  • Right Click on src/sql and select New -> File -> Sql
  • Set the file Name to CreateTZA_Macros.sql.
  • Click Finish and a template SQL file will open within the Eclipse editor.

The CreateTZA_Macros.sql file can then be built up by adding the following code segment within the CreateTZA_Macros.sql 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
-- */
CREATE MACRO GetZipCodeRiskFactors (ZipCode INTEGER) AS
(
SELECT FireRisk, FloodRisk, TheftRisk, SubsidenceRisk, OtherRisk
FROM ZipCodeRiskFactors
WHERE :ZipCode BETWEEN StartZipRange AND EndZipRange; );

COMMIT;

So from the GetZipCodeRiskFactors Macro definition we can see we are executing an identical SELECT .. FROM .. WHERE.. BETWEEN statement as we did within the Java Code. However, while this looks the same it is more flexible.

The CREATE MACRO GetZipCodeRiskFactors (ZipCode INTEGER) definition acts as an interface definition that does not change. While the Select statement acts as the Implementation, which so long as it returns a Result Set of the expected format can operate in any manner required. So should the ZipCodeRiskFactors table name need to be changed to say Zip_Code_Risks it is a simple exercise to change the SQL in the Macro to reflect this without causing any code changes to applications that use the Macro interface rather than directly accessing the table, within SQL, by name.

DropTZA_Macros.sql

As we did when creating the TZA_DB tables previously we create a second SQL file (DropTZA_Macros.sql) to hold all of the Drop Macro statements required to clear out any existing Macros within TZA_DB (so we can ensure we clean up prior to reestablishing any TZA_DB Macros, through CreateTZA_Macros.sql).

As above create a new SQL file (DropTZA_Macros.sql) in the src/sql directory.

  • Right Click on src/sql and select New -> File -> Sql
  • Set the file Name to DropTZA_Macros.sql.
  • Click Finish and a template SQL file will open within the Eclipse editor.

The DropTZA_Macros.sql can then be built up by inserting the following code segment within the DropTZA_Macros.sql 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
-- */
DROP MACRO GetZipCodeRiskFactors;

Stored Procedures

Teradata Stored Procedures provide two different types of capability. Firstly, as with Macros, they provide an Isolation Layer that allows us to protect the application implementation from changes in the underlying database. Secondly, unlike Macros, they allow for analytically and mathematical processing to be conducted within the bounds of the Stored Procedure and therefore within the database itself. While neither of these approaches fully exploits the Parallel Nature of the Teradata Database it can improve the performance of Tactical Applications through bringing to bear the CPU power of the Teradata Database to filter or process Result Set information in order to evaluate some function (as opposed to transmitting a large Result Set from the database to the querying application, wasting Intranet Bandwidth and CPU power within the querying application).

A Stored Procedure definition allows us to control the application calling mechanism for a given request (name of the Stored Procedure, type and order of parameters). As part of this definition we control both the Input parameters and the Output parameters, giving us a true Interface layer, the Implementation of which we can provide through which ever mechanism we chose. As of Teradata 12.0 this choice has expanded (beyond the SQL Stored Procedures we will cover this week and next) to include Java based External Stored Procedures or JXSP’s (which we will cover in a few weeks).

For now we will keep it simple with two Stored Procedures. The first demonstrates the Isolation concept where a defined interface isolates the underlying implementation. The second approach (which will be covered next week) introduces the concept of Embedded Analytics by actually conducting a part of the Quotation Engine calculation within the Database.

CreateTZA_Procedures.sql

Start by creating a new SQL file (CreateTZA_Procedures.sql) in the src/sql directory.

  • Right Click on src/sql and select New -> File -> Sql
  • Set the file Name to CreateTZA_Procedures.sql.
  • Click Finish and a template SQL file will open within the Eclipse editor.

The CreateTZA_Procedures.sql file can then be built up by adding the following code segment within the CreateTZA_Procedures.sql 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
-- *
-- * @author Xin.Zhou@Teradata.com
-- *
-- */
--
--
--/**
-- *
-- * Stored Procedure to Get the Risk Factors associated with a given ZipCode
-- *
-- */
CREATE PROCEDURE "GetZipCodeRiskFactorsSP" (
IN ZipCode INTEGER,
OUT FireRisk FLOAT,
OUT FloodRisk FLOAT,
OUT TheftRisk FLOAT,
OUT SubsidenceRisk FLOAT,
OUT OtherRisk FLOAT)
BEGIN
SELECT FireRisk, FloodRisk, TheftRisk, SubsidenceRisk, OtherRisk
INTO FireRisk, FloodRisk, TheftRisk, SubsidenceRisk, OtherRisk
FROM ZipCodeRiskFactors
WHERE ZipCode BETWEEN StartZipRange AND EndZipRange;
END;
/
COMMIT;
/

The CREATE PROCEDURE “GetZipCodeRiskFactorsSP” (IN ZipCode INTEGER, OUT FireRisk FLOAT,…) definition acts as an interface definition that does not change. Note this is a complete Interface definition that explicitly defines Input and Output parameters.

Within this Stored Procedure a fairly familiar Select statement acts as the Implementation, which this time is explicitly separate from the Stored Procedure Interface. While the parameter names match the Select statement values within this example there is no direct relationship between them other than data type. Therefore, if required you could change the entire naming convention of the ZipCodeRikFactors table and its columns with out impacting and applications that were using the Stored Procedure interface (assuming you changed the Select statement within the Stored Procedure to match the new naming convention).

Note: the “/” delimiter characters are required within the Stored Procedure code in place of having white space when this is run within an Ant SQL task. I don’t know why this is necessary (but it works so lets just do it this way Toto). The linkage between Stored Procedure definitions should look like this, with no white space).

END;
/
COMMIT;
/
--/**
-- *

DropTZA_Procedures.sql

As before we create a second SQL file (DropTZA_Procedures.sql) to hold all of the Drop Procedure statements required to clear out any existing Procedures within TZA_DB (so we can ensure we clean up prior to reestablishing any TZA_DB Procedures, through CreateTZA_Procedures.sql).

As above create a new SQL file (DropTZA_Procedures.sql) in the src/sql directory.

  • Right Click on src/sql and select New -> File -> Sql
  • Set the file Name to DropTZA_Procedures.sql.
  • Click Finish and a template SQL file will open within the Eclipse editor.

The DropTZA_Procedures.sql can then be built up by inserting the following code segment within the DropTZA_ Procedures.sql 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
-- */
DROP PROCEDURE GetZipCodeRiskFactorsSP;
COMMIT;

build.xml

The ANT build.xml file is used within the TZA-Database project to manage the contents of the TZA_DB database. Until now that has meant the ZipCodeRiskFactors table and the associated data, however, now we need to manage the Creation and Deletion of Macros and Stored Procedures as well. This involves defining ANT Targets for each of these operations and then integrating these targets into the larger CreateTZA_DB Target.

Double click on the build.xml file, within the Project Explorer, to open the file in the XML Editor.

At the Usage ANT Target: 

    <!-- Define the Usage ANT Target -->
<target name="Usage">
<echo message=""/>

Add the following lines, to indicate the usage of the CreateTZA-Macros, DropTZA-Macros, CreateTZA-Procedures and DropTZA-Procedures targets, using copy and paste.

        <echo message="CreateTZA-Macros   --> Create the TZA Macros"/>
<echo message="CreateTZA-Procedures --> Create the TZA Stored Procedures"/>
<echo message="DropTZA-Macros --> Drop the TZA Macros"/>
<echo message="DropTZA-Procedures --> Drop the TZA Stored Procedures"/>
  • Add the CreateTZA-Macros target to the end of the build file using copy and paste.
    <!-- Define the CreateTZA-Macros ANT Target -->
<target name="CreateTZA-Macros">
<echo message="CREATE MACROS USING: ${jdbc.driverClassName} ${jdbc.url}"/>
<sql driver="${jdbc.driverClassName}"
url="${jdbc.url}"
userid="${jdbc.username}"
password="${jdbc.password}"
onerror="stop"
src="${TZA-Database.sql.dir}/CreateTZA_Macros.sql">
<classpath refid="master-classpath"/>
</sql>
</target>
  • Add the DropTZA-Macros target to the end of the build file using copy and paste.
    <!-- Define the DropTZA_Macros ANT Target -->
<target name="DropTZA-Macros">
<echo message="Drop TZA_Macros USING: ${jdbc.driverClassName} ${jdbc.url}"/>
<sql driver="${jdbc.driverClassName}"
url="${jdbc.url}"
userid="${jdbc.username}"
password="${jdbc.password}"
onerror="continue"
src="${TZA-Database.sql.dir}/DropTZA_Macros.sql">
<classpath refid="master-classpath"/>
</sql>
</target>
  • Add the CreateTZA-Procedures target to the end of the build file using copy and paste.
    <!-- Define the CreateTZA-Procedures ANT Target -->
<target name="CreateTZA-Procedures">
<echo message="CREATE Procedures USING: ${jdbc.driverClassName} ${jdbc.url}"/>
<sql driver="${jdbc.driverClassName}"
url="${jdbc.url}"
userid="${jdbc.username}"
password="${jdbc.password}"
onerror="stop"
delimiter="/"
src="${TZA-Database.sql.dir}/CreateTZA_Procedures.sql">
<classpath refid="master-classpath"/>
</sql>
</target>

Note the delimiter="/" parameter in this Ant SQL task required to work with the "/" delimiters included in the CreateTZA_Procedures.sql file earlier.

  • Add the DropTZA-Procedures target to the end of the build file using copy and paste.
    <!-- Define the DropTZA-Procedures ANT Target -->
<target name="DropTZA-Procedures">
<echo message="Drop TZA-Procedures USING: ${jdbc.driverClassName} ${jdbc.url}"/>
<sql driver="${jdbc.driverClassName}"
url="${jdbc.url}"
userid="${jdbc.username}"
password="${jdbc.password}"
onerror="continue"
src="${TZA-Database.sql.dir}/DropTZA_Procedures.sql">
<classpath refid="master-classpath"/>
</sql>
</target>

To add these into the normal build operations we make them dependencies of the primary ANT Targets (InitialiseTZA_DB and DropTZA_DB).

  • Identify the InitialiseTZA_DB Target and overwrite it with the following using copy and paste.
 <!-- Define the Initialise TZA_DB ANT Target -->
<target name="InitialiseTZA_DB" depends="DropTZA_DB, CreateTZA-Tables, InsertTZA-Data, CreateTZA-Macros, CreateTZA-Procedures"
description="Create the TZA_DB Tables, Macros, Stored Procedures and Load Data">
</target>

Note how the InitialiseTZA_DB target “depends” upon the DropTZA_DB, CreateTZA-Tables, InsertTZA-Data, CreateTZA-Macros and CreateTZA-Procedures targets, in that order.

  • Identify the DropTZA_DB Target and overwrite it with the following using copy and paste:
    <!-- Define the Drop TZA_DB ANT Target -->
<target name="DropTZA_DB" depends="DeleteTZA-Data, DropTZA-Tables, DropTZA-Macros, DropTZA-Procedures"
description="Drop all of the TZA_DB Tables, Macros, Stored Procedures and Data">
</target>

Note how the DropTZA_DB target “depends” upon the DeleteTZA-Data, DropTZA-Tables, DropTZA-Macros and DropTZA-Procedures targets, in that order.

Run the Ant Build

Having added these new targets to the build.xml we can return to the Ant View and expand the TZA-Database project. The Ant targets CreateTZA-Macros, CreateTZA-Procedures, DropTZA-Macros and DropTZA-Procedures can now be seen in the Ant view and these can be exercised individually if you require.

Ant Build Targets

However, we configured the dependencies within DropTZA_DB and InitialiseTZA_DB so that a single Ant target (InitialiseTZA_DB) could be used to rebuild TZA_DB in a repeatable manner so make sure and Double Click on this at some point before proceeding.

  • Finally in the Ant View Double Click on the InitialiseTZA_DB task to clear any existing data and rebuild your database.

Note if you haven’t run the CreateTZA-Macros or CreateTZA-Procedures targets then when the InitialiseTZA_DB target is first run the DropTZA-Macro and DropTZA-Procedure targets may fail (as nothing has been created for them to Drop), however, the onerror="" flag in the SQL Task will let this pass. A subsequent run of InitialiseTZA_DB will run as follows:

Ant Build Run

Testing the Macro and Stored Procedure

Assuming everything is configured correctly we should now be able to exercise these new Database elements. To do this we will use the Data Source Explorer (DSE) provided by the Eclipse DTP (Data Tools Project) in combination with the Teradata Plug-in for Eclipse.

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 Dialog

Create Connection AS TZA_USER

The connections that we created previously will appear in the DSE as follows:

DSE with Local-12 and Local-12-TERA

The problem with these is that they were both created using the DBC User Name while all of the Ant SQL Database Construction operations were executed under the TZA_USER User Name. Now while most operations are fully accessible to someone with DBC privileges, Stored Procedures need to be accessed by the User Name that created them (or by that User Name being granted rights to them by the creating User). So rather than granting rights to DBC (who won’t be involved in this at runtime), we will create another Connection Profile this time “AS TZA_USER”.

Within the Data Source Explorer Right Click on the "Databases" Folder and select New.. in order to create a new Connection profile. In the "New Connection Profile" dialog create a New Connection Profile by selecting Teradata Database. Give the Connection Profile a meaningful name such as Local-12-AS-TZA_USER (optionally you can provide a description of the profile) then select Next >.

In the Specify a Driver and Connection Details dialog select the Teradata JDBC from the drop down list. Provide the name of the Server, as represented in you local hosts file (in C:\WINDOWS\System32\drivers\etc\hosts we map LOCALHOST to Local-12 Local-12cop1 so the Server name is Local-12).

  • Set the User Name and Password to match that of TZA_USER (TZA_USER/TZA_PASS).
  • Set the Database: = TZA_DB

Note: The connection mode TMODE is set to ANSI because that was the mode used to create the Stored Procedure.

New Connection AS TZA_USER

  • Select Finish to create the Connection Profile and open the connection.

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 each of these to see the GetZipCodeRiskFactors Macro and the GetZipCodeRiskFactorsSP Stored Procedure.

DSE with Macro and Stored Procedure

Exercise GetZipCodeRiskFactors Macro

To exercise / test the GetZipCodeRiskFactors Macro Right Click on it and select Run...

DSE Run Macro

In the Configure Parameters dialog that opens click on the Value entry for the ZipCode Parameter and insert a valid Zip Code, say 92127. Select OK to run the Macro with this parameter.

Configure Parameters

In the SQL Results view you will see the result Set returned by the Macro based upon the Input Parameter value.

Run Macro Results

Exercise GetZipCodeRiskFactorsSP Stored Procedure

To exercise / test the GetZipCodeRiskFactorsSP 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. Select OK to run the Stored Procedure with this parameter.

In the SQL Results view you will see the result Set returned by the Macro based upon the Input Parameter value.

Stored Procedure Result

Integrating Macros and Stored Procedures into the Business Process

Having created a Macro and a pair of Stored Procedures it is now time to show how to integrate these options into the TZA-InsuranceProcess Project. This initially involves creating Data Access Objects to reflect the two alternative approaches to obtaining the Zip Code Risk Factors (GetZipCodeRiskFactors Macro and GetZipCodeRiskFactorsSP Stored Procedure) before looking at the DAO Implementation and Business Service change necessary to start pushing the Business Logic into the Database as part of the ApplyRiskFactorsToQuoteSP Stored Procedure.

So we return to the TZA-InsuranceProcess project and drill into the src/java folder and the com.teradata.tza.insurance.process.quoteEngine.repository package, where we find the ZipCodeRiskFactorsDao Interface and the JdbcZipCodeRiskFactorsDao implementation.

Macro Access Object

We start be adding a new implementation of the ZipCodeRiskFactorsDao Interface that provides a Macro Access Object (MAO) using the GetZipCodeRiskFactors Macro called JdbcZipCodeRiskFactorsMao.java.

  • Within the TZA-InsuranceProcess Project Right Click on src/java folder
  • Select New -> Class
  • Browse to find the Package: com.teradata.tza.insurance.process.quoteEngine.repository
  • Set the Name: JdbcZipCodeRiskFactorsMao

New Java Class JdbcZipCodeRiskFactorsMao

  • Click Finish and a template file will open within the Eclipse editor.
  • Replace the template code with the following code segment 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.PreparedStatement;

import java.sql.ResultSet;
import java.sql.SQLException;

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

import com.teradata.tza.insurance.process.businessObject.ZipCodeRiskFactors;
import com.teradata.tza.insurance.process.quoteEngine.repository.ZipCodeRiskFactorsDao;

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

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

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

public ZipCodeRiskFactors find(String zipCode) throws DataAccessException
{
// Note: we are hard coding the Macro Name within the Macro Access Object
String queryString = new String ("exec GetZipCodeRiskFactors(" + zipCode + ");");
log.debug("Getting ZipCodeRiskFactors for " + zipCode + " using " + queryString);

// Create ZipCodeRiskFactors object
ZipCodeRiskFactors zipCodeRiskFactors = new ZipCodeRiskFactors();
ResultSet rs = null;
PreparedStatement stmt = null;

// Manage the query process - get a connection/statement/execute
try
{
// Create prepared statement
stmt = taoSession.prepareSQLStatement(queryString);

// Execute prepared statement
rs = taoSession.execute(stmt);

// Check result set
if (rs.next())
{
zipCodeRiskFactors = new ZipCodeRiskFactors(zipCode);
zipCodeRiskFactors.setFireRisk(rs.getDouble("fireRisk"));
zipCodeRiskFactors.setFloodRisk(rs.getDouble("floodRisk"));
zipCodeRiskFactors.setTheftRisk(rs.getDouble("theftRisk"));
zipCodeRiskFactors.setSubsidenceRisk(rs.getDouble("subsidenceRisk"));
zipCodeRiskFactors.setOtherRisk(rs.getDouble("otherRisk"));
}
else
{
// Result set empty - Throw zipCodeRiskFactors not found exception
zipCodeRiskFactors = null;
DataAccessException daEx = new DataAccessException("ZipCodeRiskFactors for " + zipCode + " not found");
throw daEx;
}

// Close result set and statement
rs.close();
stmt.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 result set and statement to prevent leaks
try
{
if (rs != null)
rs.close();

if (stmt != null)
stmt.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 zipCodeRiskFactors;

} // end find(zipCode)

} // end JdbcZipCodeRiskFactorsMao

The majority of this code is exactly the same as the JdbcZipCodeRiskFactorsDao implementation from FNP #6 with the exception of the means we use to call upon the Database to obtain the ZipCodeRiskFactors for a given ZipCode. i.e.

String queryString = new String ("exec GetZipCodeRiskFactors(" + zipCode + ");");

Add the macroQuoteEngineAppContext.xml

Spring Application Context files are used to "wire" Plain Old Java Objects (POJO's) together. We will create a macroQuoteEngineAppContext.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 GetZipCodeRiskFactors Macro and the associated Macro Access Object.

To add macroQuoteEngineAppContext.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 macroQuoteEngineAppContext.xml
  • Select Finish and a template XML file will open within the Eclipse editor.

The macroQuoteEngineAppContext.xml file can then be built up by replacing the template code with the following code segments within the macroQuoteEngineAppContext.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 Macro Quotation Engine -->
<bean id="macroQuotationEngine"
class="com.teradata.tza.insurance.process.quoteEngine.businessProcess.ZipCodeQuotationEngine"
scope="prototype">
<property name="taoSessionManager" ref="jdbcTaoSessionManager"/>
<property name="zipCodeRiskFactorsDao" ref="zipCodeRiskFactorsMao"/>
</bean>

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

</beans>

Note how we define a new bean called macroQuotationEngine, implemented by the ZipCodeQuotationEngine, however this time the ZipCodeRiskFactorsDao property is provided by our JdbcZipCodeRiskFactorsMao object and therefore the GetZipCodeRiskFactors Macro.

Stored Procedure Access Object

The other approach to obtaining Zip Code Risk Factors, for use within the Simple Quotation Engine is through the GetZipCodeRiskFactorsSP Stored Procedure.

Again we add a new implementation of the ZipCodeRiskFactorsDao Interface, which this time is a Stored Procedure Access Object (SPAO) called JdbcZipCodeRiskFactorsSPao.java.

  • Expand the TZA-InsuranceProcess Project
  • Right Click on src/java folder
  • Select New -> Class
  • Browse to find the Package: com.teradata.tza.insurance.process.quoteEngine.repository
  • Set the Name: JdbcZipCodeRiskFactorsSPao

New Java Class JdbcZipCodeRiskFactorsSPao

  • Click Finish and a template file will open within the Eclipse editor.
  • Replace the template code with the following code segment 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 com.teradata.tza.insurance.process.businessObject.ZipCodeRiskFactors;
import com.teradata.tza.insurance.process.quoteEngine.repository.ZipCodeRiskFactorsDao;

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

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

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

/**
*
* @param zipCode
* @return ZipCodeRiskFactors
* @throws DataAccessException
*/
public ZipCodeRiskFactors find(String zipCode) throws DataAccessException
{
String spCall = "{CALL \"GetZipCodeRiskFactorsSP\"(?,?,?,?,?,?)}";

// Create ZipCodeRiskFactors object
ZipCodeRiskFactors zipCodeRiskFactors = new ZipCodeRiskFactors();
CallableStatement cStmt = null;

// Make the Stored Procedure Call
try
{
cStmt = taoSession.prepareCall(spCall);
cStmt.setString(1, zipCode);
cStmt.registerOutParameter(2, Types.REAL);
cStmt.registerOutParameter(3, Types.REAL);
cStmt.registerOutParameter(4, Types.REAL);
cStmt.registerOutParameter(5, Types.REAL);
cStmt.registerOutParameter(6, Types.REAL);

log.debug("Getting ZipCodeRiskFactors for " + zipCode + " using " + spCall);
cStmt.executeQuery();

zipCodeRiskFactors.setFireRisk(new Double(cStmt.getDouble(2)));
zipCodeRiskFactors.setFloodRisk(new Double(cStmt.getDouble(3)));
zipCodeRiskFactors.setTheftRisk(new Double(cStmt.getDouble(4)));
zipCodeRiskFactors.setSubsidenceRisk(new Double(cStmt.getDouble(5)));
zipCodeRiskFactors.setOtherRisk(new Double(cStmt.getDouble(6)));

}
// 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 zipCodeRiskFactors;
}

} // end JdbcZipCodeRiskFactorsSPao

Again the majority of this code is exactly the same as the JdbcZipCodeRiskFactorsDao implementation from FNP #6, except that the setup and tear down associated with a stored procedure is a little more complicated.

Firstly consider the setup process for the GetZipCodeRiskFactorsSP call:

        // Make the Stored Procedure Call
String spCall = "{CALL \"GetZipCodeRiskFactorsSP\"(?,?,?,?,?,?)}";

cStmt = taoSession.prepareCall(spCall);
cStmt.setString(1, zipCode);
cStmt.registerOutParameter(2, Types.REAL);
cStmt.registerOutParameter(3, Types.REAL);
cStmt.registerOutParameter(4, Types.REAL);
cStmt.registerOutParameter(5, Types.REAL);
cStmt.registerOutParameter(6, Types.REAL);

Note how we define the Stored Procedure “CALL” with it’s “?” Parameters and prepare this call, through the taoSession, into the Call Statement (cStmt) before setting the input parameter (zipCode) and registering the Output Parameters.

Now consider how we execute the GetZipCodeRiskFactorsSP Stored Procedure and obtain the results of the operation from the Output Parameters:

            cStmt.executeQuery();

zipCodeRiskFactors.setFireRisk(new Double(cStmt.getDouble(2)));
zipCodeRiskFactors.setFloodRisk(new Double(cStmt.getDouble(3)));
zipCodeRiskFactors.setTheftRisk(new Double(cStmt.getDouble(4)));
zipCodeRiskFactors.setSubsidenceRisk(new Double(cStmt.getDouble(5)));
zipCodeRiskFactors.setOtherRisk(new Double(cStmt.getDouble(6)));

Note how we specifically map from the Stored Procedure Output parameters (maintained within the cStmt and indexed by the parameter number) into the ZipCodeRiskFactors Business Object.

Add the storedProcQuoteEngineAppContext.xml

As before we need to create a Spring Application Context 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 GetZipCodeRiskFactorsSP Stored Procedure and the associated Stored Procedure Access Object.

To add storedProcQuoteEngineAppContext.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 storedProcQuoteEngineAppContext.xml
  • Select Finish and a template XML file will open within the Eclipse editor.

The storedProcQuoteEngineAppContext.xml file can then be built up by replacing the template code with the following code segments within the storedProcQuoteEngineAppContext.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 Stored Procedure Quotation Engine -->
<bean id="storedProcQuotationEngine"
class="com.teradata.tza.insurance.process.quoteEngine.businessProcess.ZipCodeQuotationEngine"
scope="prototype">
<property name="taoSessionManager" ref="jdbcTaoSessionManager"/>
<property name="zipCodeRiskFactorsDao" ref="zipCodeRiskFactorsSPao"/>
</bean>

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

</beans>

Note how we define a new bean called storedProcQuotationEngine, implemented by the ZipCodeQuotationEngine, however this time the ZipCodeRiskFactorsDao property is provided by our JdbcZipCodeRiskFactorsSPao object and therefore the GetZipCodeRiskFactorsSP 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.

Project Explorer

Testing the Macro and Stored Procedure Integration

So now that we have created the new TZA-InsuranceProcess.jar file with SQL, Macro and Stored Procedure access to the Zip Code Risk Factors table we better test if this works.

We could return to the Console Veneer of FNP #7 and simply change the SqlApplicationContext.xml file to import the two new contexts from TZA-InsuranceProcess as so:

    <!-- Import the TZA-InsuranceProcess SqlQuoteEngine 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" />

Then we just need to add these lines of code to the ConsoleQuotation.java class and leave the line we wish to execute uncommented:

                // Get the Quotation Engine to use from the applicationContext
QuotationEngine quotationEngine = (QuotationEngine)applicationContext.getBean("sqlQuotationEngine");
//QuotationEngine quotationEngine = (QuotationEngine)applicationContext.getBean("macroQuotationEngine");
//QuotationEngine quotationEngine = (QuotationEngine)applicationContext.getBean("storedProcQuotationEngine");

However, the Console Veneer seems to be getting the Thumbs Down and we don’t really want to keep changing code just to swap in one implementation for another (kind of defeats the purpose of using the Spring Framework), so lets return to the TZA-InsuranceService project and look at how we can enhance that in order to test out the new Macro and Stored Procedure capability.

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 “simpleQuotationEngine” Bean in TZA-InsuranceService

Now we revisit how the Quotation Engine is used within the PropertyInsuranceSoapBindingImpl.java file.

  • Expand the src/java folder within TZA-InsuranceService.
  • Expand the com.teradata.tza.insurance.service package.
  • Open PropertyInsuranceSoapBinfingImpl.java (Double Click it, or Right click it and select Open or F3)
  • Search for the following line of code
        // finally we can load and use the quotationEngine bean that is managed by Spring 
QuotationEngine quotationEngine = (QuotationEngine) webAppContext.getBean("sqlQuotationEngine");

Note how it explicitly calls out the use of the “sqlQuotationEngine”, which is one of the three possibly versions of the Quotation Engine available within the TZA-InsuranceProcess jar file (along with macroQuotationEngine and storedProcQuotationEngine). We could simply change this line of code to explicitly call upon each of these in turn (restarting the server in between test runs), however it would be better if it was possibly to swap implementations at the configuration level (applicationContext.xml) rather than at the code level. Code changes require the source code be available to be recompiled where Configuration changes only require the use of a text editor and a restart of the server.

To make the code more general we change this line of code to call upon a new bean called simpleQuotationEngine that we can configure separately.

        // finally we can load and use the quotationEngine bean that is managed by Spring 
QuotationEngine quotationEngine = (QuotationEngine) webAppContext.getBean("simpleQuotationEngine");

Defining the “simpleQuotationEngine” 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
    <!-- Import the TZA-InsuranceProcess QuoteEngine context -->
<import resource="classpath:com/teradata/tza/insurance/process/quoteEngine/sqlQuoteEngineAppContext.xml" />

At this point we are importing the sqlQuoteEngineAppContext.xml, which defines the sqlQuotationEngine. However we now need a definition for a bean called simpleQuotationEngine so add the following line of code to applicationContext in order to make an association between these two definitions:

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

Note how even this seamingly inocuous line of configuration requires the scope="prototype" modifier to ensure that each new Thread of Execution gets a unique instance of the Quotation Engine (See the FAQ "Why are my Multi-Threaded SOA Applications failing").

Having made this simple configuration chnage we need to test that this works and that we still have at least SQL access to the Zip Code Risk Factors for the Quotation Engine to run against. Save the modified files and start up the Application Server and Web service Test Client.

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/samplePropertyInsuranceProxy/TestClient.jsp. 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. Write down the Quotation amount.

Lock down the Query Logging by selecting 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 we can see how each statement was executed with “Select fireRisk..” etc displayed as the Query Text.

SQL Query Text

Defining the “simpleQuotationEngine” as “macroQuotationEngine”

Go back to the applicationContext.xml file and change the simpleQuotationEngine definition to make an association with the macroQuotationEngine as follows:

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

Note the second import statement used to bring the macroQuoteEngineAppContext into the Application Context.

Run the test on the Web service again to see the Query Text we have now configured.

  • Restart the Server to establish the new configuration.
  • Within the SQL Editor Clear DBQL and Begin Logging
  • Within the Web service Test Client run the getQuote Web service Method.
  • Compare the Quotation Amount displayed in the results with the one you wrote down earlier, they should match.
  • Within the SQL Editor Turn off Logging (to lock the results in place) and examine the DBQL.

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

Macro Query Text

Defining the “simpleQuotationEngine” as “storedProcQuotationEngine”

Go back to the applicationContext.xml file and change the simpleQuotationEngine definition to make an association with the storedProcQuotationEngine as follows:

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

Note the third import statement used to bring the storedProcQuoteEngineAppContext into the Application Context.

Run the test on the Web service again to see the Query Text we have now configured.

  • Restart the Server to establish the new configuration.
  • Within the SQL Editor Clear DBQL and Begin Logging
  • Within the Web service Test Client run the getQuote Web service Method.
  • Compare the Quotation Amount displayed in the results with the one you wrote down earlier, they should match.
  • Within the SQL Editor Turn off Logging (to lock the results in place) and examine the DBQL.

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

Stored Procedure Query Text


So there you go three different ways to interact with the Teradata database, SQL, Macros and Stored Procedures. You also learned how to adapt your Spring Framework based application code in order to be able to make quite dramatic changes in the implementation at the configuration level without having to recompile the code.

Next week we are going to dig deeper into Stored Procedures to see how they can actually embed application logic inside the database itself, which brings various advantages such as improved Isolation and network bandwidth utilization, network latency protection and consistency improvements across applications.