As described briefly in Section 1.3.8, “Testing Your Models”, you can test your models in Teiid Designer by using the
Preview Data action 
or test your models via your deployable VDB. These two options will be described in detail in this chapter
as well as managing your required connection profiles.
Teiid Designer utilizes the Eclipse Data Tools Platform (DTP) Connection Profile framework for connection management. Connection Profiles provide a mechanism to connect to relational and non-relational sources to access metadata for constructing metadata source models. Teiid Designer also provides a custom Teiid connection profile template designed as a JDBC source to a deployed VDB.
By selecting various Teiid Designer Import options, any applicable Connection Profiles you have defined in your Database Development perspective will be available to use as your import source. From these import wizards you can also create new connection profiles or edit existing connection profiles without leaving the wizard.
The Teiid server contents in the Section 3.1, “Setting up a Server” provides access to running Teiid instances and shows data source and VDB artifacts deployed there. The "Create Data Source" action available on this view utilizes the available and applicable connection profiles.
Teiid Designer integrates Data Tools Connection Profiles by persisting pertinent connection information in each source model. This can occur through Importing process or through the Modeling > Set Connection Profile action.
In addition to setting the connection profile on a source model you can also view a source model's connection profile information via the Modeling > View Connection Info action which displays the detailed properties of the connection.
Note
If a source model has no associated connection profile the following dialog will be displayed.
As a user, you may not want this connection information (i.e. URL, username, etc...) shared through your VDB. Designer provides a means to remove this connection information via a Modeling > Remove Connection Info action. When adding a source model without connection information will require the user to supply or select the correct translator type.
Designing and working with data is often much easier when you can see the information you're working with. The Designer's Preview Data feature makes this possible and allows you to instantly preview the information described by any object, whether it's a physical table or a virtual view. In other words, you can test the views with actual data by simply selecting the table, view, procedure or XML document. Previewing information is a fast and easy way to sample the data. Of course, to run more complicated queries like what your application likely uses, simply execute the VDB Via DTP and type in any query or SQL statement.
After creating your models, you can test them by using the
Preview Data action . By selecting a desired table object and
executing the action, the results of a simple query will be displayed
in the Section 11.2.5, “Sample SQL Results for Preview Data” view. This
action is accessible throughout the
Teiid Designer in various view toolbars and context menus.
There are two requirements for previewing your data:
- The selected object must be one of several previewable model object types
- All source models within the model dependency tree must reference a connection profile.
Model objects that can be previewed include: relational tables and views (including tables involving access patterns), relational procedures, Web service operations and XML document staging tables.
Note
Any virtual table, view or procedure is previewable as long as all "physical" source models reference sufficient connection info. (See ??? view)
After selecting the Preview Data action, Designer will insure that all source models have corresponding deployed data sources .
If the model selected for preview is a source model and there is insufficient connection info for that model, the following dialog will be displayed and the action terminated.
If any of the source models in the corresponding project require a password that can't be retrieved from an existing connection profile, the user will be queried for each missing password
In order to preview, the Teiid Runtime requires deployed metadata representing your source and view metadata. If all required data sources are deployed the Preview Data dialog will be displayed containing 2 tabs to view the simple SQL Query to be executed as well as the VDB Xml Content that will be temporarily deployed in order to execute the query. Clicking OK will execute the SQL query.
Testing Your Transformations
When editing transformation SQL in the Transformation Editor, a special SQL Results data action is provided in the
editor tool-bar 
.
You can change your transformation SQL, re-validate and preview your the data for your modified SQL.
The following sections provide steps for previewing your data.
Note
All steps assume that all source models referenced by your models, either directly or through dependencies, are bound to connector bindings.
To preview a relational table, relational view or staging table:
Step 1 - Select a relational table or view in the Section D.2.1, “Model Explorer View” or diagram. The table or view can be in a view model as well as a source model. Staging tables are not visible in the Section D.2.1, “Model Explorer View”, so you need to open the mapping diagram and select it there.
Step 2 - Right-click select the Preview Data action
. You can also select the same action in the tool-bar of either
the Section D.2.1, “Model Explorer View” or diagram. Step 3 - Your query results will be displayed in the Section 11.2.5, “Sample SQL Results for Preview Data” view. The view will automatically open or get focus if not visible in your perspective.
To preview a relational table or view with access pattern:
Step 1 - Select a relational table or view in the Section D.2.1, “Model Explorer View” or diagram that contains an access pattern. The table or view can be in a view model as well as a source model.
Step 2 - Right-click select the Preview Data action
. You can also select the same action in the tool-bar of either
the Section D.2.1, “Model Explorer View” or diagram. Step 3 - A column input dialog is presented. Select each access pattern and enter a value for each required column.
INote
If data entered does not match the column datatype (String, integer, etc...), an error message will be displayed in the dialog header. When all required values are entered, click the OK button to execute the query.
Step 4 - Your query results will be displayed in the Section 11.2.5, “Sample SQL Results for Preview Data” view. The view will automatically open or get focus if not visible in your perspective.
To preview a relational procedure:
Step 1 - Select a relational procedure in the Section D.2.1, “Model Explorer View” or diagram. The procedure can be in a view model as well as a source model.
Step 2 - Right-click select the Preview Data action
. You can also select the same action in the tool-bar of either
the Section D.2.1, “Model Explorer View” or diagram. Step 3 - An input parameter input dialog is presented. Enter a valid value for each parameter.
Note
If data entered does not match the parameter datatype (String, integer, etc...), an error message will be displayed in the dialog header. When all required values are entered, click the OK button to execute the query.
Step 4 - Your query results will be displayed in the Section 11.2.5, “Sample SQL Results for Preview Data” view. The view will automatically open or get focus if not visible in your perspective.
To preview a Web service operation:
Step 1 : Select a Web service operation in the Section D.2.1, “Model Explorer View” or diagram. The operation can be in a view model as well as a source model.
Step 2 : Right-click select the Preview Data action
. You can also select the same action in the tool-bar of either
the Section D.2.1, “Model Explorer View” or diagram. Step 3 : An input parameter input dialog is presented. Enter a valid value for each parameter.
Note
If data entered does not match the parameter datatype (String, integer, etc...), an error message will be displayed in the dialog header. When all required values are entered, click the OK button to execute the query.
Step 4 - Your query results will be displayed in the Section 11.2.5, “Sample SQL Results for Preview Data” view. The view will automatically open or get focus if not visible in your perspective.
Preview Data results are displayed in the Eclipse Datatools SQL Results view as shown below.
Note
There are a number of display preference and filter options for this view via toolbar buttons and the dropdown menu.
In Teiid Designer you can execute a VDB to test/query actual data.
The requirements for VDB execution are:
A deployed VDB backed by valid deployed Data Sources
An instance of a Teiid Connection Profile configured for the deployed VDB
Teiid Designer simplifies this process via Deploy VDB and Execute VDB actions. Deploy VDB does just that, deploy a selected VDB to a running Teiid instance. Execute VDB performs the VDB deployment, creates a Teiid Connection Profile, opens the Database Development perspective and creates a connection to your VDB.
The mechanism by which VDBs are able to query actual data sources is the Data Source. These are deployed configurations backed by database or source connection jars. Each source model referenced within a VDB requiries a JNDI name representing a deployed Data Source.
When creating VDBs you do not need to have deployed data sources on your Teiid server, but if you wish to test your VDB, the data sources need to be present.
Teiid Designer provides a Create Data Source action so you can create compatible data sources for your source model. If you wish to create a data source for a specific model you can select that source model in your workspace and select the Modeling > Create Data Source action. This will extract the connection profile data from your source model and create a corresponding data source on your default Teiid server.
You can also create data sources from the Servers view. Select a Teiid server instance in the Servers view and right-click select the Create Data Source action. This will launch the Create Data Source Dialog shown below.
You can either select and existing Connection Profile from the drop-down list (Use Connection Profile Info option) or check the Use Model Info option and select an existing source model containing connection info.
After creating your new data source it should now be shown in the Data Sources folder of the corresponding Teiid server.
If you have a Teiid instance defined and connected in your Servers view you can:
Step 1 : Right-click a VDB in your Model Explorer select Modeling > Execute VDB action. This action will insure your selected VDB is deployed to Teiid, create a Teiid Connection Profile specific for that VDB, open the Database Development perspective and create a connection to your VDB.
Step 2 : Select your new Teiid connection profile and right-click select Open SQL Scrapbook, enter your designer SQL (i.e. SELECT * FROM TableXXXX), select all text and right-click select Execute Selected Text
Step 3 : Results of query should be displayed in the SQL Results view on the Result1 tab.
You can also deploy your VDB first by selecting it in the Model Explorer and dragging/dropping it onto a connected Teiid instance in the Servers view, or right-click select Modeling > Deploy action.
Once deployed, you can select the VDB in the Teiid View and right-click select the Execute VDB action there. This will create a Teiid Connection Profile specific for that VDB, open the Database Development perspective and create a connection to your VDB. Continue with Step's 2 and 3 above.
Note
You can deploy a dynamic VDB to the server, but the VDB may end up being INACTIVE if any referenced data source (JNDI name) is not already be deployed. Dynamic VDBs do not contain enough connection or data source information to automatically find and/or deploy a data source.
Note
If you do not have a Teiid instance defined or your default Teiid instance is disconnected, the following dialog will be displayed if the Modeling > Deploy action is launched.
To execute a VDB, that's been deployed manually, follow the steps below:
To execute a VDB, that's been deployed manually, follow the steps below:
Step 1 : Open the Database Development perspective.
Step 2 : Select the Database Connections folder and choose the New action to display the New Connection Profile dialog.
Step 3 : Enter unique name for your profile, select an existing connection profile type and hit Next.
Step 4 : In the Teiid Profile Wizard page, select the New Driver Definition button to locate and select the Teiid client jar on your file system. Configure your URL using your VDB Name, Host, Port, Username (default = "admin") and Password (default = "teiid").
Step 5 : Select Next to view a summary of your new Teiid Connection Profile.
Step 6 : Select Finish.
Step 7 : Select your new Teiid connection profile and right-click select Open SQL Scrapbook, enter your designer SQL (i.e. SELECT * FROM TableXXXX), select all text and right-click select Execute Selected Text.
Step 8 : Results of query should be displayed in the SQL Results view on the Result1 tab.
Step 9 : The query Execution Plan should also be displayed on the Teiid Execution Plan view tab. The Execution Plan can also be generated without running the query. In the SQL scrapbook, rt-click then select Teiid_7.x > Get Execution Plan.
