Virtualizing a table with many columns or long column names fails in Data Virtualization

Virtualizing some tables in Data Virtualization might fail with a Virtual table creation failed message while other tables in the same schema are virtualized successfully.

Symptoms

Some tables contain columns with large STRING and CLOB data. However, virtualizing the table by reducing the column width or even choosing just one of the columns fails with an error message.

Virtual table creation failed:  <Response [500]>
'{"code":500,"message":"Error: CREATE OR REPLACE NICKNAME -- DB2 SQL Error: SQLCODE=-204, 
SQLSTATE=     , SQLERRMC=CASE, DRIVER=4.31.10"}\n'

You might also see a message that is similar to the following message in the logs.

GDB_ERROR: ENGINE_GET_ROWS_FOR_CONFIG_ERROR: Unable to get rows for config request: TABLE_STATISTICS, 
cause: java.sql.SQLSyntaxErrorException: Table/View 'CASE' does not exist.

The Data Virtualization user interface cannot preview or virtualize even when the table is not cached.

Causes

The issue occurs because of the size of the table definition that is being virtualized. When Data Virtualization prepares virtualization, it allocates a 32 K buffer to hold the data definition of the virtual table. If this data definition is greater than 32 K in size because of many columns or long column names, the data definition gets truncated and you see the GDB_ERROR: ENGINE_GET_ROWS_FOR_CONFIG_ERROR: Unable to get rows for config request message in the logs.

Resolving the problem

To virtualize a large table with large column names and complete most queries on the large table, follow these steps.

  1. Set the remote schema filter to a schema where the large table does not exist so that it does not get cached.
  2. Create the virtual table with a direct CREATE OR REPLACE NICKNAME statement that includes the SOURCELIST definition option as shown in the following example. The SOURCELIST value describes the remote location of the table to be virtualized in the format <CID>:<RemoteSchema>. The CID is the connection ID for the source and can be found in source details of the Data sources page, or in the result of select * from dvsys.listrdbc query.
    /* IBM_DVSYS */ CREATE OR REPLACE NICKNAME "ADMIN"."VERY_LARGE_TABLE" for QPLEX.gaiandb."VERY_LARGE_TABLE"
    ( 
        VERY_LONG_COLUMN_IDENTIFIER1,
        VERY_LONG_COLUMN_IDENTIFIER2,
        VERY_LONG_COLUMN_IDENTIFIER3,
        ...
        VERY_LONG_COLUMN_IDENTIFIER999,
        VERY_LONG_COLUMN_IDENTIFIER1000
) OPTIONS(SOURCELIST 'DB210000:DRV');
    An example of an SQL command to create a large virtual table.
  3. Query individual columns of the large virtual table.

    An example of an SQL statement to query a large table.

  4. Run aggregations and most other queries.
    Note:

    You cannot select all columns for the large table with a SELECT * statement. You see an error message that is similar to the following message.

    SQL5105N  The statement failed because a Big SQL component encountered an 
error.  Component receiving the error: "DV-FMP".  Component returning the 
error: "Virtualization Connector".  Log entry identifier: "GAI-001-NA".  
Reason: "".  SQLSTATE=58040