VIRTUALIZETABLE stored procedure

Creates a Db2 nickname (virtualized table) with appropriate definition for a remote table and records its collected statistics in the Db2 catalog.

Input parameters

TNAME
The type of this required parameter is VARCHAR(128). Specifies the discovered name of the remote table.
TDEF
The type of this required parameter is VARCHAR(32672). Specifies the discovered table definition. This parameter can be an empty string or NULL, if VIRTDEF is also an empty string or NULL.
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 schema name of the virtualized table.
VIRTDEF
The type of this required parameter is VARCHAR(32672). Specifies the definition of the virtualized table. This parameter can be an empty string or NULL, if COLSTATS is also an empty string or NULL.
SOURCELIST
The type of this required parameter is VARCHAR(2000). Specifies the sources that can be accessed by the Db2 nickname.
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 existing nickname.
IGNORECASE
The type of this required parameter is CHAR(1). Specifies the case sensitivity of the virtual schema, table, and column names. Valid values are:
  • Y: Makes the virtual schema, table, and column names case insensitive.
  • N: Preserves the case of the virtual schema, table, and column names.
TCARD
The type of this required parameter is BIGINT. Specifies the table cardinality.
COLSTATS
The type of this required parameter is VARCHAR(32672). Specifies the column statistics that are matched with TDEF.

Output parameters

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

Syntax

Use the following syntax and replace the parameters for this stored procedure:
CALL DVSYS.VIRTUALIZETABLE('<TNAME>', '<TDEF>' ,'<VIRTSCHEMA>', '<VIRTNAME>', '' ,'<SOURCELIST>', '<REPLACE>', '<IGNORECASE>', <TCARD>, '<COLSTATS>' , ?)

Examples

Example call that does not specify table columns:
CALL DVSYS.VIRTUALIZETABLE('REMOTETABLE', '' ,'CPADMIN', 'MYTABLE', '' ,'DB210000:REMOTESCHEMA', 'Y', 'N', -1, '' , ?)
Example call that specifies table columns:
CALL DVSYS.VIRTUALIZETABLE('branches', '"branch_id" CLOB,"branch_city" CLOB,"branch_zipcode" BIGINT,', 'MYSCHEMA', 'branches', '"branch_id" CLOB,"branch_city" CLOB,"branch_zipcode" BIGINT', 'POSTG10100:"loan-schema"', 'N', 'Y', -1, '', ?)