Docs
...
VIRTUALIZENATIVEQUERY stored procedure

VIRTUALIZENATIVEQUERY stored procedure

Last updated: Mar 17, 2025
VIRTUALIZENATIVEQUERY stored procedure in Data Virtualization

Virtualize a remote query to a Db2 nickname with an appropriate definition of a remote native query. The native query can be any dialect that the remote data source and the underling driver supports.

Input parameters

SOURCE
The type of this required parameter is VARCHAR(2000). Specifies the sources that can be accessed by the Db2 nickname.
NATIVEQUERY
The type of this required parameter is VARCHAR(32672). Specifies the native query statement to be run against the remote data source for this virtualization.
VIRTSCHEMA
The type of this required parameter is VARCHAR(128). Specifies the local schema of the virtualized table.
VIRTNAME
The type of this required parameter is VARCHAR(128). Specifies the local name of the virtualized table.
REPLACE
The type of this required parameter is CHAR(1). Valid values are:
  • Y: Replaces the nickname, if it exists.
  • N: Does not replace the nickname, if it exists.
  • Else return error.
Note: All parameters are either VARCHAR or CHAR, so all parameter values are single-quoted (not double quotation mark). Since the single quotation mark character is the reserved delimiter character for all strings (VARCHAR and CHAR). If any single quotation marks need to be included as part of a parameter value, the single quotation mark must be escaped by another single quotation mark.

For example, the character A is denoted as ' ' A ' '.

Output parameters

DIAGS
The type of this parameter is VARCHAR(32672). Represents the diagnostic messages.

Syntax

Use the following syntax and replace the following parameters for this stored procedure:
CALL DVSYS.VIRTUALIZENATIVEQUERY('<SOURCE>', '<NATIVEQUERY>', '<VIRTSCHEMA>', '<VIRTNAME>', '', '', '<REPLACE>', ?);

Example

Virtualize a remote Db2 data source's native Db2 query in Data Virtualization. The following example creates a virtual table WQ_VIRTUAL_TABLE_TEST, under the virtual schema WQ_VIRTUAL_SCHEMA_A, using the data returning from the remote Db2 data source (DB210000)'s native Db2 query SELECT * FROM SCHEMA.TABLE WHERE COLUMN_A='A'.
CALL DVSYS.VIRTUALIZENATIVEQUERY('DB210000', 'SELECT * FROM SCHEMA.TABLE WHERE COLUMN_A=' 'A' ' ','WQ_VIRTUAL_SCHEMA_A','WQ_VIRTUAL_TABLE_TEST','','','Y',? );