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 , next to Alternative wrappers.

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

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

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

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

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

Add alternative sources for a base view: defining the mappings¶

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

9. 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¶

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

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

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:

• Allow insert (configurable in JDBC and ODBC base views): If yes, it indicates that the underlying data source supports INSERT operations. If no, the INSERT operations over this view will be rejected.

• Allow delete (configurable in JDBC and ODBC base views): If yes, it indicates that the underlying data source supports DELETE operations. If no, the DELETE operations over this view will be rejected.

• Allow update (configurable in JDBC and ODBC base views): If yes, it indicates that the underlying data source supports UPDATE operations. If no, the UPDATE operations over this view will be rejected.

• 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
  

• Supports distributed transactions (configurable in JDBC and ODBC base views): If yes, it indicates that the underlying data source can take part in XA distributed transactions.

• Fields by which the data is sorted in the source (configurable in all the base views, except Custom ones): This property indicates the fields by which the data is ordered. The syntax of this property is the following: <field name> { ASC | DESC } [, <field name> { ASC | DESC }]*

  For example, field 1 DESC

  E.g., let us say that we have a Web service base view with the fields F1 and F2. We know that the results of querying the Web service are ordered in ascendant order by the field F1 and then, by descendant order by the field F2. By setting this property to F1 ASC, F2 DESC, we are telling Virtual DataPort the order of the result and it may be able to perform certain optimizations such as selecting a most efficient join algorithm. See more about this in the section Merge Join.

• Delegate operators list (configurable in Multidimensional base views that connect to SAP and Web service base views): This property determines the list of operators that can be delegated to the data source. This list allows Virtual DataPort to optimize the query plan by delegating part of the processing to the source. While the Server carries out this action automatically on relational databases, other types of sources do not provide this information about their metadata.

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.