The Friday Night Project #5 - TZA-Database

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 #5 - TZA-Database

Finally it’s time to start some real development within the Friday Night Project. This week we are going to create the TZA-Database project within which we will ultimately collect all of the project information (SQL, Data and build files) necessary to create and maintain a consistent version of the TZA-Database. We will also establish the base infrastructure for an ANT based build that can clear any existing database elements prior to creating the ZipCodeRiskFactors table and loading the base Risk Factors data.

Creating the TZA-Database Project.

As all of the Friday Night Project developments will operate against a single Active Data Warehouse (ADW), we create a single Eclipse Project that can maintain all of the SQL files used to Delete/Remove, Create and Load: Data, Tables, Macros, and Stored Procedures. For the sake of clarity individual SQL files are used for each specific operation with corresponding entries being added to an ANT build file (build.xml).

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

Workspace Launcher

Once the IDE is started, create a New Project.

  • Select File -> New -> Project... General -> Project -> Next >.
  • Set the project name as TZA-Database (the Location will be something like C:\Projects\FridayNightProject\TZA-Database) and select Finish.

New Project

Within the Eclipse Project Explorer view, select the TZA-Database Project and add a folder (directory) to hold all of the SQL statements we will need.

  • Right Click on the TZA-Database project and select New -> Folder and set the Folder name as "src/sql" and select OK.

Repeat this for the "src/config" folder used to hold configuration information (which will drive the Java DataBase Connection to the database, through JDBC).

Repeat for the "lib/build" folder used to hold build time libraries (JAR files).

Within the Project Explorer view the TZA-Database with the "src/sql", "src/config" and "lib/build" folders will look like this.

Project Explorer with sql, config, lib

Populate the TZA-Database Project

We now start to populate the TZA-Database Project with elements such as the JDBC Driver JAR’s, SQL statements and configuration information.

Add JDBC Driver

The first thing we do to populate the TZA-Database Project is add the necessary JAR files to the “lib/build” folder.

  • Add the Teradata JDBC Driver JAR’s you downloaded and used as part of the Getting Started with Teradata Plug-In for Eclipse exercise
  • Copy the two JAR files namely tdgssconfig.jar and terajdbc4.jar from the download location (typically C:\TeraJDBC) and Paste them into the "lib/build" folder within the TZA-Database project

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

Project Explorer with JDBC JAR's

CreateTZA_Tables.sql

Our first SQL file (CreateTZA_Tables.sql) will hold all of the Create Table statements (for now we will only have the ZipCodeRiskFactors table to worry about, but will add others as the project continues). This SQL file is created as follows.

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

New SQL File

The CreateTZA_Tables.sql can then be built up by inserting the following code segment within the CreateTZA_Tables.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 Table ZipCodeRiskFactors
(
ID integer NOT NULL PRIMARY KEY,
StartZipRange integer NOT NULL,
EndZipRange integer NOT NULL,
FireRisk float DEFAULT 1.0,
FloodRisk float DEFAULT 1.0,
TheftRisk float DEFAULT 1.0,
SubsidenceRisk float DEFAULT 1.0,
OtherRisk float DEFAULT 1.0
)
UNIQUE INDEX (StartZipRange, EndZipRange);
COMMIT;

CreateTZA_Tables.sql

DropTZA_Tables.sql

The second SQL file (DropTZA_Tables.sql) holds all of the Drop Table statements required to clear out any existing tables within TZA-Database (so we can ensure we clean up prior to reestablishing the TZA-Database, through the CreateTZA_Tables.sql).

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

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

The DropTZA_Tables.sql can then be built up by inserting the following code segment within the DropTZA_Tables.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 Table ZipCodeRiskFactors;
COMMIT;

InsertTZA_Data.sql

The next SQL file (InsertTZA_Data.sql) holds the Database Load (Insert) statements used to provide TZA-Database with some initial data. This load file provides nominal Risk Factors for each of the US States represented by the ZipCodeRiskFactors table. (note you may see some duplicate State Codes in the comments due to the vagaries of the Zip Code system that has a number of disparate Zip Code ranges for certain states).

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

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

The InsertTZA_Data.sql can then be built up by inserting the following code segment within the InsertTZA_Data.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
-- */
INSERT INTO ZipCodeRiskFactors VALUES (1,'35000', '36999', 1.0, 1.0, 1.0, 1.0, 1.0); -- AL
INSERT INTO ZipCodeRiskFactors VALUES (2,'99500', '99999', 1.0, 1.0, 1.0, 1.0, 1.0); -- AK
INSERT INTO ZipCodeRiskFactors VALUES (3,'71600', '72999', 1.0, 1.0, 1.0, 1.0, 1.0); -- AR
INSERT INTO ZipCodeRiskFactors VALUES (4,'85000', '86599', 1.0, 1.0, 1.0, 1.0, 1.0); -- AZ
INSERT INTO ZipCodeRiskFactors VALUES (5,'90000', '96299', 1.0, 1.0, 1.0, 1.0, 1.0); -- CA
INSERT INTO ZipCodeRiskFactors VALUES (6,'80000', '81699', 1.0, 1.0, 1.0, 1.0, 1.0); -- CO
INSERT INTO ZipCodeRiskFactors VALUES (7,'06000', '06999', 1.0, 1.0, 1.0, 1.0, 1.0); -- CT
INSERT INTO ZipCodeRiskFactors VALUES (8,'19700', '19999', 1.0, 1.0, 1.0, 1.0, 1.0); -- DE
INSERT INTO ZipCodeRiskFactors VALUES (9,'32000', '34999', 1.0, 1.0, 1.0, 1.0, 1.0); -- FL
INSERT INTO ZipCodeRiskFactors VALUES (10,'30000', '31999', 1.0, 1.0, 1.0, 1.0, 1.0); -- GA
INSERT INTO ZipCodeRiskFactors VALUES (11,'39800', '39999', 1.0, 1.0, 1.0, 1.0, 1.0); -- GA
INSERT INTO ZipCodeRiskFactors VALUES (12,'96700', '96899', 1.0, 1.0, 1.0, 1.0, 1.0); -- HI
INSERT INTO ZipCodeRiskFactors VALUES (13,'50000', '52899', 1.0, 1.0, 1.0, 1.0, 1.0); -- IA
INSERT INTO ZipCodeRiskFactors VALUES (14,'83200', '83899', 1.0, 1.0, 1.0, 1.0, 1.0); -- ID
INSERT INTO ZipCodeRiskFactors VALUES (15,'60000', '62999', 1.0, 1.0, 1.0, 1.0, 1.0); -- IL
INSERT INTO ZipCodeRiskFactors VALUES (16,'46000', '47999', 1.0, 1.0, 1.0, 1.0, 1.0); -- IN
INSERT INTO ZipCodeRiskFactors VALUES (17,'66000', '67999', 1.0, 1.0, 1.0, 1.0, 1.0); -- KS
INSERT INTO ZipCodeRiskFactors VALUES (18,'40000', '42799', 1.0, 1.0, 1.0, 1.0, 1.0); -- KY
INSERT INTO ZipCodeRiskFactors VALUES (19,'70000', '71499', 1.0, 1.0, 1.0, 1.0, 1.0); -- LA
INSERT INTO ZipCodeRiskFactors VALUES (20,'05500', '05599', 1.0, 1.0, 1.0, 1.0, 1.0); -- MA
INSERT INTO ZipCodeRiskFactors VALUES (21,'01000', '02799', 1.0, 1.0, 1.0, 1.0, 1.0); -- MA
INSERT INTO ZipCodeRiskFactors VALUES (22,'20311', '20311', 1.0, 1.0, 1.0, 1.0, 1.0); -- MD
INSERT INTO ZipCodeRiskFactors VALUES (23,'20335', '21999', 1.0, 1.0, 1.0, 1.0, 1.0); -- MD
INSERT INTO ZipCodeRiskFactors VALUES (24,'03900', '04999', 1.0, 1.0, 1.0, 1.0, 1.0); -- ME
INSERT INTO ZipCodeRiskFactors VALUES (25,'48000', '49999', 1.0, 1.0, 1.0, 1.0, 1.0); -- MI
INSERT INTO ZipCodeRiskFactors VALUES (26,'55000', '56799', 1.0, 1.0, 1.0, 1.0, 1.0); -- MN
INSERT INTO ZipCodeRiskFactors VALUES (27,'63000', '65899', 1.0, 1.0, 1.0, 1.0, 1.0); -- MO
INSERT INTO ZipCodeRiskFactors VALUES (28,'38600', '39799', 1.0, 1.0, 1.0, 1.0, 1.0); -- MS
INSERT INTO ZipCodeRiskFactors VALUES (29,'71233', '71233', 1.0, 1.0, 1.0, 1.0, 1.0); -- MS
INSERT INTO ZipCodeRiskFactors VALUES (30,'59000', '59999', 1.0, 1.0, 1.0, 1.0, 1.0); -- MT
INSERT INTO ZipCodeRiskFactors VALUES (31,'27000', '28999', 1.0, 1.0, 1.0, 1.0, 1.0); -- NC
INSERT INTO ZipCodeRiskFactors VALUES (32,'58000', '58899', 1.0, 1.0, 1.0, 1.0, 1.0); -- ND
INSERT INTO ZipCodeRiskFactors VALUES (33,'68000', '69399', 1.0, 1.0, 1.0, 1.0, 1.0); -- NE
INSERT INTO ZipCodeRiskFactors VALUES (34,'03000', '03899', 1.0, 1.0, 1.0, 1.0, 1.0); -- NH
INSERT INTO ZipCodeRiskFactors VALUES (35,'07000', '08999', 1.0, 1.0, 1.0, 1.0, 1.0); -- NJ
INSERT INTO ZipCodeRiskFactors VALUES (36,'87000', '88499', 1.0, 1.0, 1.0, 1.0, 1.0); -- NM
INSERT INTO ZipCodeRiskFactors VALUES (37,'88900', '89899', 1.0, 1.0, 1.0, 1.0, 1.0); -- NV
INSERT INTO ZipCodeRiskFactors VALUES (38,'10000', '14999', 1.0, 1.0, 1.0, 1.0, 1.0); -- NY
INSERT INTO ZipCodeRiskFactors VALUES (39,'00400', '00599', 1.0, 1.0, 1.0, 1.0, 1.0); -- NY
INSERT INTO ZipCodeRiskFactors VALUES (40,'06390', '06390', 1.0, 1.0, 1.0, 1.0, 1.0); -- NY
INSERT INTO ZipCodeRiskFactors VALUES (41,'43000', '45999', 1.0, 1.0, 1.0, 1.0, 1.0); -- OH
INSERT INTO ZipCodeRiskFactors VALUES (42,'73400', '74999', 1.0, 1.0, 1.0, 1.0, 1.0); -- OK
INSERT INTO ZipCodeRiskFactors VALUES (43,'73000', '73199', 1.0, 1.0, 1.0, 1.0, 1.0); -- OK
INSERT INTO ZipCodeRiskFactors VALUES (44,'97000', '97999', 1.0, 1.0, 1.0, 1.0, 1.0); -- OR
INSERT INTO ZipCodeRiskFactors VALUES (45,'15000', '19699', 1.0, 1.0, 1.0, 1.0, 1.0); -- PA
INSERT INTO ZipCodeRiskFactors VALUES (46,'02800', '02999', 1.0, 1.0, 1.0, 1.0, 1.0); -- RI
INSERT INTO ZipCodeRiskFactors VALUES (47,'29000', '29999', 1.0, 1.0, 1.0, 1.0, 1.0); -- SC
INSERT INTO ZipCodeRiskFactors VALUES (48,'57000', '57799', 1.0, 1.0, 1.0, 1.0, 1.0); -- SD
INSERT INTO ZipCodeRiskFactors VALUES (49,'37000', '38599', 1.0, 1.0, 1.0, 1.0, 1.0); -- TN
INSERT INTO ZipCodeRiskFactors VALUES (50,'75000', '79999', 1.0, 1.0, 1.0, 1.0, 1.0); -- TX
INSERT INTO ZipCodeRiskFactors VALUES (51,'84000', '84799', 1.0, 1.0, 1.0, 1.0, 1.0); -- UT
INSERT INTO ZipCodeRiskFactors VALUES (52,'20040', '20199', 1.0, 1.0, 1.0, 1.0, 1.0); -- VA
INSERT INTO ZipCodeRiskFactors VALUES (53,'20301', '20301', 1.0, 1.0, 1.0, 1.0, 1.0); -- VA
INSERT INTO ZipCodeRiskFactors VALUES (54,'20370', '20370', 1.0, 1.0, 1.0, 1.0, 1.0); -- VA
INSERT INTO ZipCodeRiskFactors VALUES (55,'05000', '05499', 1.0, 1.0, 1.0, 1.0, 1.0); -- VT
INSERT INTO ZipCodeRiskFactors VALUES (56,'05600', '05699', 1.0, 1.0, 1.0, 1.0, 1.0); -- VT
INSERT INTO ZipCodeRiskFactors VALUES (57,'98000', '99499', 1.0, 1.0, 1.0, 1.0, 1.0); -- WA
INSERT INTO ZipCodeRiskFactors VALUES (58,'53000', '54999', 1.0, 1.0, 1.0, 1.0, 1.0); -- WI
INSERT INTO ZipCodeRiskFactors VALUES (59,'24700', '26899', 1.0, 1.0, 1.0, 1.0, 1.0); -- WV
INSERT INTO ZipCodeRiskFactors VALUES (60,'82000', '83199', 1.0, 1.0, 1.0, 1.0, 1.0); -- WY

DeleteTZA_Data.sql

The final SQL file (for TZA-Database Table management) is used to clear the data within the ZipCodeRiskFactors tables (prior to being able to Drop the table).

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

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

The DeleteTZA_Data.sql can then be built up by inserting the following code segment within the DeleteTZA_Data.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
-- */
DELETE FROM ZipCodeRiskFactors;

Once all of the individual SQL files have been added the Project Explorer view of TZA-Database will look like this.

Project Explorer with SQL

TZA_UserGrants.sql

We add one last SQL file (TZA_UserGrants.sql) to hold all the Grants necessary to allow the TZA_USER to access the TZA_DB. As with the TZA_DB and TZA_USER set up we did last week, we typically only need do this once but it’s a good idea to maintain the source code in one place.

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

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

The TZA_UserGrants.sql can then be built up by inserting the following code segment within the TZA_UserGrants.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
-- */
--
-- You Will need DBC access to do some of these.
GRANT ALL on TZA_DB to TZA_USER WITH GRANT OPTION;
COMMIT;
GRANT EXECUTE on TZA_DB to TZA_USER;
COMMIT;
GRANT CREATE EXTERNAL PROCEDURE on TZA_DB to TZA_USER;
COMMIT;
GRANT EXECUTE PROCEDURE ON SQLJ to TZA_USER;
COMMIT;

With the addition of the TZA_UserGrants.sql file the Project Explorer view of TZA-Database will look like this.

Project Explorer with Grants

jdbc.properties

By convention we typically use an external configuration file (here called jdbc.properties) to define how to connect into the database. This file will contain entries for the Database servers we wish to use (server= and dbs.port=), the default database (base.db=), the JDBC driver properties (jdbc.*=).

Start by creating a new file (jdbc.properties) in the /src/config directory.

  • Right Click on /src/config and select New -> File
  • Set the file Name to jdbc.properties.
  • Click Finish and a template file will open within the Eclipse editor.

The jdbc.properties can then be built up by inserting the following code segment within the jdbc.properties file using copy and paste.

# Properties file with JDBC-related settings.
# Applied by PropertyPlaceholderConfigurer from "applicationContext.xml".
# Targeted at system administrators, to avoid touching the context XML files.

server=Local-12
dbs.port=1025
base.db=TZA_DB

# JDBC-related settings.
jdbc.driverClassName=com.teradata.jdbc.TeraDriver
jdbc.url=jdbc:teradata://${server}/DATABASE=${base.db},DBS_PORT=${dbs.port},TMODE=ANSI
jdbc.username=TZA_USER
jdbc.password=TZA_PASS

# JDBC.DBC Properties set to default, it is assumed you will change these on your Production System (at least)
jdbc.dbc.username=dbc
jdbc.dbc.password=dbc

# Name of the Default Database we are going to use
base.dbc.db=dbc

# JDBC-related settings.
jdbc.dbc.url=jdbc:teradata://${server}/DATABASE=${base.dbc.db},DBS_PORT=${dbs.port},TMODE=ANSI

Once the jdbc.properties file is added to the src/config folder the Project Explorer view of TZA-Database will look like this.

Project Explorer with jdbc.properties

build.xml

In order to automate the process of Clearing, Creating and Populating the TZA_DB database we employ the ANT build capability that comes bundled with Eclipse.

A series of ANT tasks are used to provide for each of these processes. Individual tasks (such as CreateTZA_Tables) can be grouped into larger tasks (like InitialiseTZA_DB).

Start by creating a new file (build.xml) in the root directory of the TZA-Database project.

  • Right Click on TZA-Database and select New -> Other -> XML -> XML
  • Set the file Name to build.xml.
  • Click Finish and a template file will open within the Eclipse XML editor.

The build.xml file can then be built up by switching to Source Mode and replacing the template code with the following code segment using copy and paste.

<?xml version="1.0"?>

<project name="TZA-Database" basedir="." default="InitialiseTZA_DB">

<property name="name" value="TZA-Database"/>

<!-- Define the Usage ANT Target -->
<target name="Usage">
<echo message=""/>
<echo message="${name} build file"/>
<echo message="--------------------------------------------"/>
<echo message=""/>
<echo message="Available targets are:"/>
<echo message=""/>
<echo message="------------- Database content management operations -------------------------"/>
<echo message="initialiseTZA_DB --> Initialize the TZA_DB (Default)"/>
<echo message="CreateTZA-Tables --> Create the TZA Tables"/>
<echo message="InsertTZA-Data --> Insert the TZA_DB Data"/>
<echo message="DeleteTZA-Data --> Delete the TZA Data from the Database"/>
<echo message="DropTZA-Tables --> Drop the TZA Tables"/>
<echo message="Usage --> Illustrate Usage of the build file"/>
<echo message=""/>
</target>

<!-- Define the TZA-Database config, lib and sql directories -->
<property name="TZA-Database.config.dir" value="../TZA-Database/src/config"/>
<property name="TZA-Database.lib.dir" value="../TZA-Database/lib/build"/>
<property name="TZA-Database.sql.dir" value="../TZA-Database/src/sql"/>

<!-- Define the master-classpath for ALL TZA Java Compilation -->
<path id="master-classpath">

<!-- Add the Development Path Libraries used by all applications -->
<fileset dir="${TZA-Database.lib.dir}">
<include name="*.jar"/>
</fileset>

</path>

<!-- Include the jdbc.properties file for use in classpath -->
<property file="${TZA-Database.config.dir}/jdbc.properties"/>

<!-- Define the UserGrants ANT Target -->
<target name="UserGrants">
<echo message="GRANT TZA_USER Right USING: ${jdbc.driverClassName} ${jdbc.dbc.url}"/>
<sql driver="${jdbc.driverClassName}"
url="${jdbc.dbc.url}"
userid="${jdbc.dbc.username}"
password="${jdbc.dbc.password}"
onerror="stop"
src="${TZA-Database.sql.dir}/TZA_UserGrants.sql">
<classpath refid="master-classpath"/>
</sql>
</target>

<!-- Define the Initialise TZA_DB ANT Target -->
<target name="InitialiseTZA_DB" depends="DropTZA_DB, CreateTZA-Tables, InsertTZA-Data"
description="Create the TZA_DB Tables and Load Data">
</target>

<!-- Define the Drop TZA_DB ANT Target -->
<target name="DropTZA_DB" depends="DeleteTZA-Data, DropTZA-Tables"
description="Delete all of the TZA_DB Data and drop the Tables">
</target>

<!-- Define the CreateTZA-Tables ANT Target -->
<target name="CreateTZA-Tables">
<echo message="CREATE TZA_DB Tables 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_Tables.sql">
<classpath refid="master-classpath"/>
</sql>
</target>

<!-- Define the InsertTZA-Data ANT Target -->
<target name="InsertTZA-Data">
<echo message="INSERT DATA 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}/InsertTZA_Data.sql">
<classpath refid="master-classpath"/>
</sql>
</target>

<!-- Define the DeleteTZA-Data ANT Target -->
<target name="DeleteTZA-Data">
<echo message="DELETE ALL DATA 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}/DeleteTZA_Data.sql">
<classpath refid="master-classpath"/>
</sql>
</target>

<!-- Define the DropTZA-Tables ANT Target -->
<target name="DropTZA-Tables">
<echo message="Drop TZA_DB Tables 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_Tables.sql">
<classpath refid="master-classpath"/>
</sql>
</target>

</project>

Within the Project Explorer view the TZA-Database project with build.xml will look like this.

Project Explorer with build.xml

Add Buildfile to Ant View

The TZA-Database build.xml can be run from within Eclipse using the built in ANT View (so you never have to leave Eclipse).

  • In Eclipse select Windows -> Show View -> Other... -> Ant -> Ant to open the Ant view
  • In the Ant View select the Add Buildfiles.. option (Green Plus Sign with a little Ant beside it, real easy stuff Toto).

Ant Add Build files

  • Use the Buildfile Selection Dialog to find the TZA-Database -> build.xml and add it to this view.

Buildfile Selection Dialog

  • In the ANT View open up the TZA-Database build file and Double Click the UserGrants task (one time only) to setup the Database Grants.

Ant Build view

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

Note when the InitialiseTZA_DB target is first run the various Delete Data, Drop Table, targets may fail (as nothing has been created for them to Delete or Drop, however, the onerror="" flag in the SQL Task will let this pass). A subsequent run of InitialiseTZA_DB will run as follows.

Buildfile Results

And that is it. You should now have the TZA_DB database with the ZipCodeRiskFactors Table populated with a base set of default Zip Code oriented Risk Factors.

ZipCodeRiskFactors

Next week on the Friday Night Project we will start to create our first simple business process that can access this table and use it to provide the Simple Quotation Engine.