The Friday Night Project #4 - TZA_DB and TZA_USER

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 #4 - TZA_DB and TZA_USER

Having previously introduced the “What”, “Why” and “How” of Active Integration and Solid Architecture, this week we start to build up the development environment (Eclipse with the Teradata Plug-In), Teradata Database Schema (TZA_DB) and User (TZA_USER) required to support TZA-Insurance.

Setting up the Environment

If you are lucky enough to have access to a real Teradata (not a production system of course) then you are good to go. However, if you do not you can install Teradata Express Edition 12.0 to your Windows PC or Laptop.

We will be using Eclipse and the Teradata Plug-in for all Friday Night Project development so you should follow the instructions in Getting Started with Teradata Plug-In to initialize Eclipse and Test out your configuration.

Depending upon how your Teradata instance is setup you may have different options in creating the TZA_DB and TZA_USER elements (if you have a local TD12 instance you will likely have complete control over this, alternatively your DBA may provide these elements for you on a shared Test or Development instance). Either way it is a useful exercise to see how we create these elements within the Teradata IDE.

Open the Database Development Perspective

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

Workspace Launcher

Once the IDE is started select a New Perspective by clicking on the Perspective Icon and selecting Other..

In the Open Perspective dialog select Database Development and select OK.

Create a New Connection Profile

Within the Data Source Explorer Right Click on the "Databases" Folder and select New.. in order to create a new Connection profile.

In the "Connection Profile" dialog create a New Connection Profile by selecting Teradata Database. Give the Connection Profile a meaningful name such as Local-12 for a local TD 12 Express Installation or Remote-12 for a remote machine (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). User name and password, at this time are the Teradata defaults of dbc/dbc (obviously you never leave your production system with these values; perhaps you use toto/toto instead). Note: The connection mode TMODE is set to ANSI (Teradata Advocated Approach).

Select Finish in order to complete creation of the Connection Profile.

Within the Data Source Explorer view Right Click on the Local-12 connection profile and select Connect. Once the connection is made, open up the tree to reveal the contents of the Local-12 Instance.

Create TZA_DB Database

You can now add the TZA_DB database by right clicking on the Schemas icon, select Teradata -> Create Schema

Then fill in the Create Schema dialog



Select the SQL Preview tab hit the “Run SQL” button to create the TZA_DB Database (you can examine the SQL used to create the database/schema and this can be saved to a file if required).

Create TZA_USER

To add a user associated with the TZA_DB database Right Click on the Schemas icon and select Teradata -> Create User

The Create User Dialog will appear, fill in User name (TZA_USER) and Password (TZA_PASS) information on the User Tab.



Select the Database tab and fill in the Database information

Select the SQL tab and hit the “Run SQL” button to create the TZA_USER User (you can examine the SQL used to create the user and this can be saved to a file if required).

Reviewing TZA_DB / TZA_USER

So we have used the Teradata Plug-In for Eclipse to connect to a Teradata Database and create a Database Schema and a Database User that we will utilize throughout the rest of the Friday Night Project as illustrated below.


So this illustrates how to use Eclipse with the Teradata Plug-in to work directly with your Teradata Instance (you may never need Teradata Administrator again).

Next week on the Friday Night Project we will create the TZA-Database Project as a container to hold SQL files that can Create, Populate and Clear this Database along with ANT focused XML that will introduce you to the concept of providing automated builds that will work from within the Eclipse IDE as well as from external build tools (there goes BTEQ too!).