VIRTUALIZENATIVEQUERY stored procedure

Important: IBM Cloud Pak® for Data Version 4.8 will reach end of support (EOS) on 31 July, 2025. For more information, see the Discontinuance of service announcement for IBM Cloud Pak for Data Version 4.X.

Upgrade to IBM Software Hub Version 5.1 before IBM Cloud Pak for Data Version 4.8 reaches end of support. For more information, see Upgrading from IBM Cloud Pak for Data Version 4.8 to IBM Software Hub Version 5.1.

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 nickname, if it exists.
  • Else return error.
Note: All parameters are either VARCHAR or CHAR, so all parameter values are single-quoted (not double-quote). Since the single quote character is the reserved delimiter character for all strings (VARCHAR and CHAR). If any single quotes need to be included as part of a parameter value, the single quote must be escaped by another single quote.

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 parameters below for this stored procedure:
CALL DVSYS.VIRTUALIZENATIVEQUERY('<SOURCE>', '<NATIVEQUERY>', '<VIRTSCHEMA>', '<VIRTNAME>', '', '', '<REPLACE>', ?);

Example

Virtualize a remote Db2 data source's native Db2 query in Watson Query. 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',? );