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
Install Jboss AS Server 5.1 (or greater)
Install Teiid 7.0 into the “../server/default” directory
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 6.3 Designer's JDBC Importer and the same jars you'll need when defining DTP Connection Profiles.
Install Teiid Designer 7.0 into JBDS or Eclipse 3.5.1 (Note 7.0 has not been upgraded to work with Eclipse 3.6)
Open console window, navigate to AS install directory's “bin” directory
Start the server with ./run.sh
Create Relational Model
Launch Eclipse, open “Designer” perspective and create New > Model Project
Select “File >Import” and select “Metadata from JDBC” option
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) similar to MMX & 6.3 Designer. They should be the same jars you dropped into Teiid's install “/lib” folder during Teiid installation.
After creating or selecting existing CP, hit “Next” and continue with importing as usual
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
Right-click in Teiid view and select “New Teiid Instance”
Provide URL: mm://<hostname>:<port1>
Default for Teiid is “mm://localhost:31443”
Default user-name is “admin”
Default password is “admin”
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.
The “Show Translators/Hide Translators” toolbar button does just that. Translators (or their names) are a required property in VDB's for each source model
The “Show All Data Sources/Show All My Data Sources” toolbar button allows filtering out any Data Sources
Create Data Source connection in Teiid
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
We've provided an action, “Modeling > Create Data Source” to assist you in quickly creating compatible DS's on your local Teiid Server.
When PREVIEW DATA mode is completed, you will not need to perform this action. (unless a connection profile has not been set on a source model). Designer will automatically generate these artifacts on your Default server when required to Preview.
This action should result in a new DS artifact deployed to Teiid and will show up in your Teiid view as a DB-typed object named “My Parts Oracle:PartsSupplier” (Connection Profile name : Source Model Name)
Select Relational Model and choose “New > Virtual Database”
This will automatically add the Model to the VDB (new little feature)
You can still select project or nothing and create Empty VDB.
VDB Editor opens and contains your model (or empty)
Add model to VDB Editor if empty
Should have one row in the top table and have the “Translator” name and “JNDI” names populated.
If 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.
Next you need to supply a 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, it'll be named something like: 8d9b22ab-f4ab-48c0-a6cd-b4d4501b81a5__TestProj_B_PartsSupplier.
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. The “generated JNDI name” described above will be set in the JNDI Name property.
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
Save the VDB
Select VDB in Model Explorer and Drag it to the Server object in Teiid View
- Or right-click select “Modeling > Deploy” action.
- 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.
- Or right-click select “Modeling > Deploy” action.
Create VDB Connection Profile and Connect
Open DTP's Database Development Perspective.
Select Database Connections folder in the tree and select “New” action
Select the “Teiid” connection profile type and hit “Next”
Provide VDB name (“PartsVdb”).
DO NOT include the “.vdb file extension”
Provide Host = “localhost”, Port = “31000”, Username = “admin”, PWD = “teiid”
In case these defaults don't work for you, when AS starts it will log these host/port properties for you. Check there and try those values out if different.
Hit “Finish” and you should be connected to Teiid
Querying your VDB
Select your new Teiid Connection
Right-click select “Open SQL Scrapbook”
Enter “Select * FROM PARTS” (or your favorite table), add criteria, whatever.
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)
Select the text and right-click select “Execute Selected Text”
Results View should be opened and result shown in the “Results1” tab on the bottom right.