USER MANUALS

Search Methods, Configuration Properties and Internationalization

Unlike a database, some data sources such as SOAP web services or REST APIs do not allow any query to their data. Instead, they provide an interface with limited query capabilities (e.g. an operation of a REST API that has mandatory parameters). Virtual DataPort is capable of “modeling” the query capabilities of a source using search methods.

The tab Search methods (Options) of a view lists:

  • The internationalization configuration (i18n). You can change it in base views, not in derived views (see below Internationalization Configuration).

  • The search methods of the views and if it has mandatory fields.

  • For JDBC base views, you can define alternative sources (see below Alternative Sources).

Search methods of a base view

Search methods of a base view

Alternative Sources

When the data of a table of a database is replicated in other databases, you should configure this base view to indicate the databases where the same data can be located.

The section Selecting the Most Optimal Source When the Data Is Replicated in Several Sources explains in more detail why defining where the data of a base view is replicated can increase the performance of the queries involving this base view.

This feature is only available for JDBC base views.

To define additional sources for the same base view, do the following:

  1. Create a JDBC data source for each database in which the table of this base view is replicated. Each data source has to point to one of the databases. If you already have data sources that point to these databases, you do not need to create them again.

  2. Go to the Search methods tab of the Options dialog of the view.

  3. Click on image1, next to Alternative wrappers.

Add alternative sources for a base view: selecting an alternative table

Add alternative sources for a base view: selecting an alternative table

  1. In the wizard “Add alternative source”, on the JDBC data source database list, select the database where you have created one of the alternative data sources.

  2. In the JDBC data source list, select the data source that points to the database with the table.

  3. Below, the Tool will populate a tree of the schemas of this data source. In this dialog, select the table/view that contains a copy of the data of the base view you are currently editing. In this tree, you can only select one table or view.

  4. Click Create selected. The Tool will display a dialog like the one below.

Add alternative sources for a base view: defining the mappings

Add alternative sources for a base view: defining the mappings

  1. In this dialog you can define mappings between the fields of the base view and the table of the database. This is useful if the names of the tables in the alternative source are different from the fields of the base view.

    The base view automatically links the fields of the base view with the fields of the table when their name is the same.

  2. After clicking Ok, there will be a new entry in the “Alternative wrappers” section of the search methods tab.

Add alternative sources for a base view

Add alternative sources for a base view

  1. Repeat these steps for each database that contains a replica of the table of this base view.

  2. For each alternative wrapper, you can define its i18n, which affects how the timestamp values are treated. However, the default value (“DEFAULT”) is almost always the correct one.

View Configuration Properties

The View Configuration Properties, also called wrapper source configuration, allow indicating specific characteristics of the underlying data sources such as their distributed transaction support capacity or whether insert operations are allowed.

This section lists the configurable properties of a base view, depending on the type of data source they have come from.

The section Data Source Configuration Properties lists the configuration properties of the data sources.

Note

Usually, users do not need to edit these properties since Virtual DataPort automatically uses suitable configurations for most common data sources.

To configure the properties of a base view, open the Options dialog of the view and then, click on the Search methods tab. In this dialog, underneath the search methods (using the scroll bar where necessary), click Wrapper Source Configuration. The configurable properties are the following:

  • Delegate SQL sentence as subquery (configurable in JDBC base views): If yes (default value) and the base view has been created from a SQL query, at runtime, Virtual DataPort will be able to delegate the SQL query of the base view as a subquery in the FROM clause. This increases the number of operations that can be delegated to the database.

    For example, let us say that we have created a JDBC base view called customer_info from the following SQL query:

    SELECT id, name
FROM customer
WHERE id = function_not_supported_by_vdp(name, address)

    Then, a user executes the following query in Virtual DataPort:

    SELECT COUNT(*)
FROM customer_info
GROUP BY name

    If this property is set to no, Virtual DataPort executes the SQL query of the view customer_info in the database and then, over the results obtained from the database, it executes the GROUP BY operation and the COUNT function.

    If this property is set to yes, the entire query can be delegated to the database by putting the SQL query of the base view in a subquery:

    SELECT COUNT(*) FROM (
    SELECT id, name
    FROM customer
    WHERE id = function_not_supported_by_vdp( name, address )
) GROUP BY name

    In this scenario, Virtual DataPort does not have to process the GROUP BY and the COUNT and the amount of traffic between the database and Virtual DataPort is diminished.

    Although this property is set to “yes”, the entire query cannot always be executed in the database. This happens in the following scenarios:

    • The query executed in Virtual DataPort involves functions not supported by the database.

    • A SQL statement is not delegated as a subquery if it contains the token @WHEREEXPRESSION.

    • A nested join cannot be delegated to the database if the following conditions are met:

      • The base view created from a SQL query, is used in the right side of the join.

      • The SQL query of the base view has interpolation variables.

      • And, the values of these variables are obtained from the query of the left side of the join.

    Note

    Set this property to no if the SQL query cannot be delegated as a subquery. For example, if the query uses “common table expressions”. E.g.

    WITH cte AS (SELECT * FROM VIEW)
SELECT * FROM cte

  • Delegate SQL sentence variables as parameters (configurable in JDBC base views): If yes and the base view has been created from a SQL query, at runtime, Virtual DataPort will be able to delegate the SQL sentence interpolation variables as parameters over the base view, to the database.

    Default value: no.

Using VQL commands, you can modify other properties of a base view. These are: Allow insert, Allow delete, Allow update, Supports distributed transactions, Fields by which the data is sorted in the source, and Delegate operators list. The page Wrapper Configuration Properties of the VQL Guide explains how to set them.

Internationalization Configuration

The internationalization configuration (i18n) of a base view is used in some types of sources to retrieve data properly.

To change the internationalization configuration of a base view, open the Options dialog of the view, click the tab Search methods and select the appropriate i18n.

The i18n of derived views cannot be changed.

The section Configuring the Default Internationalization explains how this setting affects the queries that return date values.

Add feedback