This article provides a quick-start example for importing JDBC Metadata into Designer to create a relational model, creating a VDB from that model as well as deploying and executing SQL queries via a JDBC connection through your Teiid VDB.

This guide assumes you have at least one JDBC data source available including type, driver jars, URL and other necessary connection information.

Install JBoss AS, Teiid & Teiid Designer

1. Install Jboss AS Server 5.1 (or greater)

2. Install Teiid 7.6 FINAL into the “../server/default” directory

3. Drop your JDBC driver jars into “../server/default/lib” directory. This will include the jars you used to Select while importing and creating JDBC Sources in Designer's JDBC Importer and the same jars you'll need when defining DTP Connection Profiles.

4. Open console window, navigate to AS install directory's “bin” directory

5. Start the server with ./run.sh

6. Install Teiid Designer 7.4 into your Eclipse 3.6 or above

Designer

If you don't have a database with schema and data defined, you can download the Parts Example and import into your workspace via "Import > General > Existing Projects into Workspace" and choose the "Select as archive file" option.  This example comes with a readme.html to guide you through the database set-up steps as well as some of the steps described below.

Create Relational Model

1. Launch Eclipse, open “Designer” perspective and create New > Model Project

2. Select “File >Import” and select “Metadata from JDBC” option

3. Select “Connection Profiles...” button to launch New Connection Profile (CP) wizard (DTP).

   • Note you can also create CP's in DTP's Database Development Perspective.

   • You'll need to get familiar with this perspective because it is the view where you will create Teiid JDBC Source connections to your VDB's in order to query data (replaces SQL Explorer).

   • Each CP will required setting selecting the “Driver” jar(s). They should be the same jars you dropped into Teiid's install “/lib” folder during Teiid installation.

4. After creating or selecting existing CP, hit “Next” and continue with importing as usual

5. After importing, The model will be modified to include the connection profile properties used during import.

   • Note:  You can change the model's connection profile info by selecting a model then right-click select “Modeling > Set Connection Profile” action.

Create Teiid server Instance

1. Right-click in Teiid view and select “New Teiid Instance”

2. Specify unique name for this instance.
3. Provide valid JDBC URL information (Default URL for Teiid is "mms://localhost:31000")
   • Specify Host (default = localhost)
   • Specify Port Number (default = 31000)
   • Specify User Name (default = "user")
   • Specify Password (default = "user")
   • This connection info will be used to set up JDBC connections to perform JDBC data queries to VDB's deployed on your Teiid server instance.

4. Provide valid Admin URL information (Default URL for Teiid is "mms://localhost:31443")

   • Specify Host (default = localhost)
   • Specify Port Number (default = 31443)
   • Specify User Name (default = "admin")
   • Specify Password (default = "admin")
   • This connection will be used by Designer to Add/Remove data-sources required for setting up valid VDB's and for deploy and undeploy your VDB's you wish to test.

5. Test - Select test button and verify both jdbc and admin connections

6. Hit FINISH

7. Initial Teiid View should contain your new Teiid server instance.

   • The Teiid view default contents will include any VDB's deployed to this server instance and any of your specific “workspace” Data Sources.

   • Data Sources are the  “JCA-compatible Connection Factory” xml configuration  components required for Teiid to create the necessary connections to query data at run-time.

Preview your data

Though your final deployable artifacts will be VDBs containing your modeled metadata, you can preview your data through your metadata models without creating VDBs. To preview data:

1. Select a table or procedure in a relational model in the Model Explorer
2. Right-click select the "Modeling > Preview Data" action (or select the running man in the Model Explorer toolbar)
3. Results are displayed in the SQL Results view provided by the Data Tools framework.

Create Data Source connection in Teiid

1. In order for a VDB to be executable, a Data Source (Connection Factory) needs to be deployed in the “.../server/default/deploy” directory. This will be in the form of “xxxx-ds.xml”.  You can manage these items via JON/JOPR console.

2. We've provided an action, “Modeling > Create Data Source” to assist you in quickly creating compatible DS's on your local Teiid Server.

   • The "Create Data Source" wizard displays your referenced connection profile, a default name (name of source model) and the connection properties that will be used to create the new data source.

3. This action should result in a new Data Source artifact showing as deployed to Teiid.

Create VDB

1. Select a Relational Model and choose   “New > Virtual Database”

   • This will automatically add the Model to the VDB

   • You can still select project or nothing and create Empty VDB.

2. VDB Editor opens and contains your model (or empty)

3. Add model to VDB Editor if empty

4. You should have one row in the top table and have the “Translator” name and “JNDI” names populated.

5. If the Translator name is empty, enter an appropriate name from your Teiid view reflecting the DB your imported from (“oracle”, “sqlserver”, “derby”, etc..) or select table row for model and right-click select the “Change Translator” action. This will display a simple tree view of your servers containing available Translators. Select a Translator and hit OK.

6. Next you will need to supply a valid JNDI name for a Data Source (Connection Factory) on the server you wish to deploy your VDB to.

   • If you used the “Create Data Source” option above for your local Teiid and chose the default model name, the JNDI name should be set to this same default value.

   • If you customized your data source name, select the table row for the source model and right-click select “Change JNDI Data Source” action. Select the appropriate Data Source and hit OK.

   • If you wish, you can also look in  “...server/default/deploy” folder for the generated xxxx-ds.xml file. The file name will contain the Test Project name, folder names and end with the Model Name + -ds.xml. Copy all but the -ds.xml and paste into the JNDI name field for your source model

7. Save the VDB

Execute VDB

1. Select VDB in Model Explorer and right-click select "Modeling > Execute VDB" action.

    
   • This action should result in the VDB being deployed to your default server and a connection profile created/opened in the Eclipse Database Development perpective.
     • Note: This action will deploy the VDB to the “Default” server. If you have more than one  server, the user is required to keep only one of the servers as the Default. You can change it by selecting a non-default Teiid server and right-click select “Set As Default” action.

Querying your VDB

1. Select your new Teiid connection in Data Source Explorer

2. Right-click select “Open SQL Scrapbook”

3. Enter “Select * FROM PARTS” (or your favorite table), add criteria if you wish to narrow the query's scope.

   • Note: if you are federating data with multiple sources or have similarly named tables, you'll need to fully qualify the table names (I.e. SELECT * FROM PartsView.PARTS)

4. Select the text and right-click select “Execute Selected Text”

5. Results View should be opened and result shown in the “Results1” tab on the bottom right.