Teiid Designer 8.1 Quick Start Guide

Version 1

    This article provides a Teiid Designer 8.0 Quick Start example.  In the example, you wil

    • Create a relational model by importing JDBC Metadata into Designer
    • Create a virtual database (VDB) from the model.
    • Deploy the VDB and execute SQL queries via a JDBC connection to the VDB.

     

    Pre-requisites:  You must have at least one JDBC data source available including driver jars, URL and all other necessary connection information.

     

    Install JBoss AS, Teiid & Teiid Designer

     

    1. Install Teiid per the Teiid Installation Guide
      • Download and install JBoss AS Server 7.1.1 (or greater)
      • Download Teiid Runtime (8.3 Final) .  Install the Teiid runtime by extracting it into the "<INSTALLDIR>/jboss-as-7.1.1.Final" directory.
    2. Launch Teiid Server
      • Open a terminal window on your computer, then navigate to the "<INSTALLDIR>/jboss-as-7.1.1.Final/bin" directory.
      • Start the server with ./standalone.sh --server-config=standalone-teiid.xml
    3. Install Teiid Designer 8.1
      • Installation of Designer 8.1 has changed slightly.  See the What's New article for installation instructions.

     

    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 the “Teiid Designer” perspective and create New > Teiid Model Project
    2. Select “File >Import” .  Under Teiid Designer, select the “JDBC Database >> Source Model” option
    3. Select the “New...” button next to Connection Profile to launch the New Connection Profile (CP) wizard (DTP).
      • Note:  you can also create CP's in DTP's Database Development Perspective.  This perspective is also where you will create Teiid JDBC Source connections to your VDB's in order to run queries.
      • 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. When you define the Connection Profile, you will need to define the driver jars to be used.  After creating the Connection Profile, click “Next” and follow the wizard steps to complete the import.
    5. Upon completion of the import, the generated model will include the connection profile properties used during import.
      • Note:  You can change the model's connection profile info at any time, by selecting the model - then right-click  “Modeling > Set Connection Profile

    Create Teiid server Instance

    1. Open the Teiid view, the click the New link next to JBoss Server
    2. In the "New Teiid Instance" wizard, specify a Display Name for the Teiid server.
    3. Next to JBoss Server, click New..., then select JBoss Community > JBoss AS 7.1 from the list of server options.  Click Next
    4. On the JBoss Runtime page, click the Browse... button in the Home Directory section.  Select the directory where you installed the JBoss AS 7.1 server.
    5. Click the Browse... button next to Configuration file:  Select the standalone-teiid.xml file from the configuration directory of your server installation.  Complete the wizard.
    6. Back in the "New Teiid Instance" wizard page, enter the Teiid JDBC Connection Info
      • User Name (default = "user")
      • Password (default = "user")
      • Teiid Template (JDBC URL Template = "jdbc:teiid:<vdbname>@mm://localhost:31000")
      • 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.
    7. Click Finish to complete the wizard.
    8. Start the JBoss Server.  If the Servers View is not open, then open it via Window > Show View > Other... > Server > Servers.  Click the 'play' button on the toolbar to start the server.
    9. The 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.
    10. If the Teiid Server View is not already open, open the Servers View.  Then click on Teiid Server Configuration node and Rt-Click > Teiid Server Properties to open the Teiid Server View.  On the Teiid Server View, test the server connection:
      • Click the Test Administration Connection link to verify the admin connection.  It should respond with 'OK'.
      • Click the Test JDBC Connection link to verify the JDBC connection.  It should respond with 'OK'.

     

    Preview your data

     

    Though your final deployable artifacts will be VDBs containing your modeled metadata, you can preview your data through your 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 VDB

    1. Select a Relational Model and choose   “New > Teiid VDB
      • This will automatically add the Model to the VDB
      • Enter a Name for the VDB, then click Finish.
    2. VDB Editor opens and should contain your model.  If the model is not contained, go ahead and add it.
    3. The VDB Editor should have one row in the top table and have the “Translator” and “JNDI Name” columns populated.
      • If the Translator column 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.
      • If the JNDI Name column is empty, 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  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.
    4. 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.
    2. A SQL Scrapbook editor will be opened displaying instructions on executing a query.
    3. In the scrapbook, type in a query - for example:  SELECT * FROM MyModel.Table. Put your cursor in the query text, then Rt-Click> Execute Current Text . The query will execute and display results in the SQL Results View.

     

    This completes the Teiid Designer Quick Start!