Version 3

    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.1 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 6.3 Designer's JDBC Importer and the same jars you'll need when defining DTP Connection Profiles.

    4. Install Teiid Designer 7.1 into JBDS or Eclipse 3.6

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

    6. Start the server with ./run.sh

     

    Designer

     

    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) similar to MMX & 6.3 Designer. 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. Provide URL: mm://<hostname>:<port1>

      • Default for Teiid is “mm://localhost:31443”

    3. Default user-name is “admin

    4. Default password is “admin

    5. Hit finish

    6. 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.

     

    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

    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 DataSource artifact showing as deployed to Teiid.

     

    Create VDB

     

    1. 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.

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

    3. Add model to VDB Editor if empty

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

    5. 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.

    6. 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 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, a connection profile created and 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, 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)

        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.