Red Hat Training

A Red Hat training course is available for Red Hat JBoss Data Virtualization

7.3. Model a JDBC Source

This section shows how to model a JDBC Source, using the Guide View action set. For this example, we will connect to a MySQL database, but you can use the same process to connect to any supported database.
  1. Open Guides View

    To open the Teiid Designer's Guides view, on the main menu, click Window >Show View > Other... and then click Teiid Designer > Guidesview in the dialog.
    The Guides view is shown below, with the Model JDBC Source Action Set selected:
    Guides View

    Figure 7.1. Guides View

  2. Define Teiid Model Project

    1. The Define Teiid Model Project action launches the New Model Project wizard. In the Action Set list, double-click the action (or select the action, then click Execute selected action). The wizard is launched as shown below:
      Enter a project name, for example, MyProject in the Name field. Then click Next.
      New Project Wizard

      Figure 7.2. New Project Wizard

    2. The next page of the wizard is shown below:
      New Project Folders

      Figure 7.3. New Project Folders

    3. Under Create Folders, clear schemas and web_services. We will not need them for this example. Now, click Finish to exit the wizard. The project has now been created.
      Your Model Explorer view will look like this:
      Model Explorer

      Figure 7.4. Model Explorer

  3. Create JDBC connection

    The Create JDBC connection action will create the Connection profile for your database. The connection profile defines the properties and driver to be used when connecting to the database. In the Action Set list, double-click the action (or select it, then click Execute selected action). The wizard is launched as shown below:
    Connection Profile Name and Type

    Figure 7.5. Connection Profile Name and Type

    Select the type of database that you are connecting to (for example, MySQL), and enter a name for the connection profile, for example, TestMySQL. Click Next.
    Connection Profile properties

    Figure 7.6. Connection Profile properties

    Now, select the driver and enter the login properties for your database. Click Finish to complete the profile creation.
  4. Create source model for JDBC data source

    1. The Create source model for JDBC data source action will now utilize the Connection profile that you have created, to import the metadata from the database to create your Teiid Source Model. In the Action Set list, double-click the action (or select it, then click Execute selected action). The wizard is launched as shown below:
      Select Connection Profile

      Figure 7.7. Select Connection Profile

    2. On this page, select the TestMySQL Connection profile that you created in the previous step. Click Next.
      Select Database Metadata

      Figure 7.8. Select Database Metadata

    3. On this page, select the database metadata that you want to import. When finished, click Next.
      Select Database Objects

      Figure 7.9. Select Database Objects

    4. On this page, select the specific objects from the database that you want to import. When finished, click Next.
      Import Options

      Figure 7.10. Import Options

    5. Finally, choose the name for the model to be created (defaults to profileName.xmi). The Into Folder field defines the target location for your new model. Select the MyProject/sources folder. Now, click Finish. The source model has now been created. Your Model Explorer view will look like this:
      Model Explorer

      Figure 7.11. Model Explorer

  5. Preview Data

    All execution capabilities in Teiid Designer (Preview Data, VDB execution) require you to connect to a running JBoss Data Virtualization Server. See teiid-view for instructions on establishing a JBoss Data Virtualization Server connection. Once you are connected to a JBoss Data Virtualization Server, you can proceed with the following steps.
    1. The Preview Data action allows you to preview a sample of data rows from your source. In the Action Set list, double-click the action (or select it, then click Execute selected action).
    2. In the dialog, select the source table you want to preview, as shown below:
      Select Preview Table

      Figure 7.12. Select Preview Table

    3. After selecting the table, click OK. Now, the preview results will be displayed:
      Preview Results

      Figure 7.13. Preview Results

  6. Define VDB

    1. The Define VDB action allows you to create a VDB (Virtual Database) artifact for deployment to a JBoss Data Virtualization Server. In the Action Set list, double-click the action (or select it, then click Execute selected action). The following dialog is displayed:
      New VDB

      Figure 7.14. New VDB

    2. In the dialog, select the target In Folder location where the VDB will be placed. Enter a Name for the VDB, for example myVDB. Finally, select the models that will be included in the VDB. When finished, click Finish. The VDB will be created in your Teiid Model Project as shown in the following figure.
      Model Explorer

      Figure 7.15. Model Explorer

  7. Execute VDB

    The Execute VDB action allows you to execute your VDB and run sample queries against it. In the Action Set list, double-click the action (or select it, then click Execute selected action). In the dialog, select the VDB you want to execute, then click OK. The VDB will be deployed and executed, and the perpective will switch to the Database Development perspective. You can now run queries against the VDB, as show in the following example:
    Execute VDB Example

    Figure 7.16. Execute VDB Example