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:
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:
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
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
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
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