VIRTUALIZEFILE stored procedure

Creates a Db2 nickname and view with appropriate definition for a flat file that is accessed through a remote node.

Input parameters

NODEID
The type of this required parameter is VARCHAR(2000). Specifies the node ID where the file is hosted. Supported formats are CSV and XLS.
FILEPATH
The type of this required parameter is VARCHAR(32672). Specifies the full file path on the remote agent's file system.
OPTIONSCSV
The type of this required parameter is VARCHAR(32672). Specifies the comma-separated list of strings for extra options. The following are supported options:
  • MAXCHARS=<N>, where <N> is the max number of characters in a string cell. Longer cell values in the source are truncated to that length. The default is 32672.
  • SCANROWS=<N>, where <N> is the number of rows to pre-scan to infer column types. The default is 200. If the number provided is 0, then no rows are scanned. If the number provided is negative, then all rows are scanned.
  • COLSNAMES=<True or False>, determines whether the first row will be interpreted as column names. The default is true.
  • XLS,XLS=<Sheetname>,XLS=(<Sheetname> <TopLeftCell>, <BottomRightCell or RightColumn>), optional sheet name and cell coordinates of the table. The following are all valid examples: "XLS", "XLS=Sheet3", "XLS=(Sheet3,B6,R8)", and "XLS=(Sheet3,B6,R)".
  • Note: For sheet names that include commas, the name should be wrapped in single or double quotation marks. Nested quotation marks should be escaped by doubling them.
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.
VIRTDEF
The type of this required parameter is VARCHAR(32672). Specifies a definition 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 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.

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.VIRTUALIZEFILE('<NODEID>', '<FILEPATH>', '<OPTIONSCSV>', 'DRV', 'TEST2', 'LAST VARCHAR(255), FIRST VARCHAR(255), ADDR VARCHAR(255), PLACE VARCHAR(255), STATE VARCHAR(255), ZIP INT', 'Y', 'Y', ?)

Example

call DVSYS.virtualizefile('qpendpoint_1:6415', '/opt/ibm/address.xls', 'XLS=(Sheet1,A1,F9),COLNAMES=false', 'DRV', 'TEST2', 'LAST VARCHAR(255), FIRST VARCHAR(255), ADDR VARCHAR(255), PLACE VARCHAR(255), STATE VARCHAR(255), ZIP INT', 'Y', 'Y', ?)