Skip to main content
Skip table of contents

Native Query Usage

You are looking at an older version of the documentation. The latest version is found here.

To enable native queries for a specific data source, set this data source's SupportsNativeQueries translator parameter to TRUE. This can be done via the Data Virtuality Studio or a special SQL statement.

Via the Data Virtuality Studio

Via SQL Statement 

When adding a new data source, include the property in the SYSADMIN.createDataSource() procedure:

CODE
CALL SYSADMIN.createDataSource(name => ..., 
translatorProperties => 'SupportsNativeQueries=TRUE') ;;

As a result, the data source will have another procedure called native to work with. The easiest way to execute a native query is to call the procedure and send the query itself:

CODE
CALL "<dataSource>"."native"( "request" => '<myQuery>');;

The query gets executed on the source, and the result set is sent to the Data Virtuality Server. Please note that each result set row is represented as one column entry with a comma-separated list of values.

To get all columns in a proper representation, use ARRAYTABLE to split the values (see example 3 below).

Using Native Queries

Simple Query 

CODE
EXEC "aw_ms.native"( "request" => 'SELECT 1');;

This call will execute a simple SELECT natively on the data source.

This type of native query is ideal for sending simple commands to the source: for example, creating a view or table, or performing any other operation which does not require seeing the result set.

Selecting the First Tuple of the Result Set 

CODE
SELECT tuple[1] 
	FROM (EXEC "aw_ms.native"( "request" => 'SELECT 1')) AS a;;

The result set from the source comes as a list of tuples. The example above will select the first tuple and display the content.

An easy use case is performing operations on the source when you only need to know if the operation succeeded or not.

Getting All Columns From the Result Set 

CODE
SELECT x.* 
	FROM(EXEC "ms.native"( "request" => 'SELECT 1, 2 UNION SELECT 3, 4;')) r,
    	ARRAYTABLE(r.tuple COLUMNS "first" string, "second" bigdecimal) x;;

This example focuses on splitting the tuples from the result set which the Data Virtuality Server retrieved back into regular columns.

To do this, you can apply the ARRAYTABLE construct and enumerate the columns and their data types stored within the tuples.

Supported Data Sources

As of now, native query usage is supported for the following data sources:

  • BigQuery
  • Data Virtuality
  • DB2
  • DB2 AS/400
  • DB2 zOS

  • Derby
  • EXASOL
  • Generic JDBC
  • Greenplum
  • H2
  • Hive
  • Ingres
  • InterSystems Cache
  • LDAP
  • MS SQL
  • MS SQL jTDS
  • MySQL
  • Oracle
  • PostgreSQL
  • Redshift
  • Vertica

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.