Limitations and known issues in Data Virtualization

When you virtualize many tables, for example more than 15 at the same time, your cart might fail to virtualize and you might see error messages that indicate failure to load tables.

Workaround: Reduce the number of tables to virtualize or specify a schema filter to speed up loading of metadata and tables. For more information, see Filtering data in Data Virtualization.

Applies to: 4.5.1 and later.

If the Data Virtualization service instance is shut down but the service is not, and you navigate to the Data Virtualization instance, you see the provisioning UI status page, which says Hang tight, we're deploying Data Virtualization. The user interface does not show the correct page since the service instance is shut down. To avoid seeing the provisioning user interface, shut down the Data Virtualization service as well.

Applies to: 4.5.0 and later.

If you remove a connection to a remote connector or the remote connector becomes unavailable because credentials expire, the Data sources page fails to load this connection.

Applies to: 4.5.0 and later.

If you try to virtualize a table that contains a column that is named id, preview shows only the column value of the first row in the table. This issue occurs because the keyword id is reserved for the user interface that displays the table.

In addition, if a table contains a column that is named id and the column contains identical values in the id column, these rows display only the values of the first row for each row that has the same value for the id column.

Workaround: Rename the column of the table to a unique name that is not id. If you cannot rename the column name, you must ensure that values in the column are unique.

Applies to: 4.5.0 and later.

If you schedule an online backup in Data Virtualization and if at the same time a cache refresh operation executes, the cache write operation can fail.

Online backup in Data Virtualization works by quiescing database write and update operations. Cache refresh is a write operation that updates Data Virtualization caches with fresh data from remote data sources. Both online backup and cache refresh operations can be scheduled to execute periodically. These schedules can conflict and every time the cache tries to refresh, an online backup might be in progress or it might start while the cache refresh is in progress. In these scenarios, cache refresh operations can fail with an error similar to the following message. The cache is active but it contains stale data.

The statement failed because a Big SQL component encountered an error. 
Component receiving the error: "BigSQL IO". Component returning the error: "UNKNOWN". 
Log entry identifier: "[BSL-1-b1bf428f]". Reason: "Cannot add block to /warehouse".. 
SQLCODE=-5105, SQLSTATE=58040, DRIVER=4.27.25

Cache auto-refresh might also fail and you can either manually refresh or wait for the next auto-refresh. However, if the online backup is scheduled such that it conflicts with any cache's refresh schedule, then every auto-refresh of those caches will fail.

Applies to: 4.5.0 and later.

Not all characters can be part of a table name. For example, the hashtag character (#) cannot be part of table names. You cannot use the hashtag character in table names even if you wrap this character in quotation marks.

Applies to: 4.5.0 and later.

When you virtualize a table in a MongoDB on Cloud data source that has a semicolon in the schema name, you will not be able to virtualize the table. The MongoDB on Cloud data source removes the semicolon from the schema name and queries of this virtual table fail with error SQL5105N.

Applies to: 4.5.0 and later.

You can virtualize a table in the remote data source but querying the virtual table fails with an SQL5105N error. See the following example.

ERROR] SQL error: [IBM][CLI Driver][DB2/LINUXX8664] 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-003-NA". SQLSTATE=58040 SQLCODE=-5105 (fvt_sql.py:157)

Applies to: 4.5.0 and later.

Applies to: 4.5.0 and later.

Error: CREATE OR REPLACE NICKNAME -- DB2 SQL Error: SQLCODE=-1229, SQLSTATE=40504, SQLERRMC=null, DRIVER=4.29.24

Error: CREATE OR REPLACE NICKNAME -- "<schema>"."<table>" already exists in Server "<server>" (use REPLACE=Y to overwrite)

The table <schema>.<table> already exists. Use REPLACE=Y to overwrite CallStack

This issue occurs even when you do not have tables with the same schema name and table name. The root cause is a timeout and retry sequence that is triggered by HAProxy timeouts. To work around this issue, ensure that your HAProxy settings meet the recommend values. For more information, see Configuring network requirements for Data Virtualization and Changing load balancer timeout settings. If virtualization of large files takes longer than 5 minutes, increase the HAProxy settings and retry the virtualization.

Applies to: 4.5.0 and later.

On the Configure connection page, when you click Download Linux driver package, the drivers must be downloaded from an external website. This download is not supported in an air-gapped environment currently.

Applies to: 4.5.0 and later.

When you follow these instructions to use a custom SSL or TLS certificate, you might encounter SSL verification errors, and Data Virtualization might not work. Custom CA-signed certificates are not supported for Data Virtualization. Self-signed certificates do not cause this issue and are supported for Data Virtualization. To work around this issue, revert to the default certificate that was provided with Cloud Pak for Data or use a self-signed certificate.

Applies to: 4.5.0 and later.

If you get a timeout error when you try to connect to Data Virtualization, increase timeout values. To increase timeout values, update the /etc/haproxy/haproxy.cfg file and set the client and server timeout value to 10m. For more information, see Changing load balancer timeout settings.

Applies to: 4.5.0 and later.

Workaround: Reload the remote table's cache by using the refresh button in the List view of the Virtualize page.

If you add a connection to a data source in cloud object storage, change your credentials, and then try to re-add the connection, you might see an error that is similar to the following message.

The data source could not be added. From the Platform connections page, modify the parameters of the selected existing connection and try again.

Or, the connection status for the re-added connection shows a status of Invalid. You must reenter and validate your credentials.

Workaround: Edit the connection and enter the correct credentials after the connection is re-added to Data Virtualization. You can reenter credentials after the connection is added to Data Virtualization.

Applies to: 4.5.0 and later.

• Db2®
• Denodo
• SAP HANA
• SAP S/4HANA
• Spark

Workaround: Edit these connections from the Data sources page only.

Applies to: 4.5.0 and later.

When you try to create a connection to a data source that has already been connected to, you see the message The data source could not be added. Ensure that the specified parameters are correct and try again.

Applies to: 4.5.0 and later.

Some connections in Cloud Pak for Data 4.5.0 default to use the TLSv1.3 protocol when they initiate SSL connections to remote data sources. If the remote data source does not support TLSv1.3, the SSL handshake fails.

This change affects the following connection types.

• Amazon Redshift
• Apache Hive
• Google BigQuery
• Greenplum
• PostgreSQL
• Microsoft Azure SQL Database
• Microsoft SQL Server
• Oracle
• Salesforce.com
• SAP S/4HANA (Using the SAP OData connection type)

Applies to: 4.5.0 and later.

If a connection uses a custom SSL certificate that has been stored in a vault, the connection must first be created in Platform connections by using the connections service REST API before it can be added on the Data sources page in Data Virtualization with Add connection > Existing platform connection.

The SSL certificate must either be input as a bare certificate in plain text format or be entered by using the following secret reference format as a singleton secret instead of a JSON array as shown in the following example.

"ssl_certificate": "{\"secretRefID\":\"secret_urn:MySSLCert\",\"key\":\"password\"}"

POST "${CPDHOST}/v2/connections?test=false&catalog_id=${CATALOG_ID}" \
-H "content-type: application/json" \
-H "Authorization: Bearer ${CPDTOKEN}" \
-d '{ "datasource_type": "${DATASOURCE_TYPE}",
    "name": "${CONNECTION_NAME}",
    "properties": {
        "host": "mydatabase.mycompany.com",
        "port": "5500",
        "connection_mode": "sid",
        "sid": "mydatabase",
        "username": "{\"secretRefID\":\"secret_urn:MyCreds\",\"key\":\"username\"}",
        "password": "{\"secretRefID\":\"secret_urn:MyCreds\",\"key\":\"password\"}",
        "ssl": "true",
        "properties": "CryptoProtocolVersion=TLSv1.2",
        "ssl_certificate": "{\"secretRefID\":\"secret_urn:MySSLCert\",\"key\":\"password\"}"
    },
    "origin_country": "US",
    "flags": [
        "personal_credentials"
    ] } '

Applies to: 4.5.0 and later.

Applies to: 4.5.0 and later.

db2 connect to bigsql
db2 "call DVSYS.setRdbcX('SAPS4Hana', '<Data source IP:port>', '0', '', 'CreateSchema=ForceNew', '<username>', '<password>', '0', '0', '', '', '<internal connector instance and port>', ?,?,?);"
db2 terminate

Applies to: 4.5.0 and later.

When you query virtualized tables in SAP HANA data sources that select certain columns and use the NOT operator as a filter on those columns, you might see incorrect results.

Applies to: 4.5.0 and later.

When you create a connection to MongoDB, you see only tables that were created in the MongoDB data source before the connection was added.

For example, if you have 10 tables in your MongoDB data source when you create a connection, you see 10 tables when you start to virtualize the table. If a user adds new tables into the MongoDB data source after the connection is added and before you click Virtualize, Data Virtualization won't display the new tables under the Virtualize tab.

Workaround: To see all recently added virtualized MongoDB tables, delete the connection to MongoDB and re-create the connection.

Applies to: 4.5.0 and later.

The current MongoDB JDBC Driver does not support connection to database names that contain special characters.

Applies to: 4.5.0 and later.

If you preview SAP HANA data sources that contain data with a data type of TINYINT, you see inaccurate data for some rows of type TINYINT. However, you can virtualize the data source. Only preview is affected.

Applies to: 4.5.0 and later.

After you virtualize the table, in Virtualized data, the data is available for the columns that contain LOB or Long Varchar data types.

Applies to: 4.5.0 and later.

Applies to: 4.5.0 and later.

For example, you might see the following message. DB_WARNING: ENGINE_JDBC_CONN_ATTEMPT_ERROR: Failed JDBC Connection attempt in XXX ms for: YYY;sslTrustStoreLocation=/path/.dsTempTrustStore;, cause: .... Message: java.lang.RuntimeException: Unexpected error: java.security.InvalidAlgorithmParameterException: the trustAnchors parameter must be non-empty.

To work around this issue, see Errors when you edit an SSL-based connection in Data Virtualization.

Applies to: 4.5.0 and later.

Data Virtualization supports the timestamp data type up to nanoseconds. When a remote data source's timestamp type is with a scale greater than 9 nanoseconds, Data Virtualization returns truncated timestamp column values up to nanoseconds. Additionally, for timestamp predicates, Data Virtualization compares only up to nanoseconds.

Applies to: 4.5.0 and later.

When you try to preview data from the Db2 for i, an error message is displayed. Db2 for i might be a remote data source and you don't have the required access permissions. For more information, see the Db2 product documentation.

Applies to: 4.5.0 and later.

Applies to: 4.5.0 and later.

You can specify any additional JDBC properties when you add data source connections to Data Virtualization. However, you cannot change these additional JDBC properties after you add data source connections.

Applies to: 4.5.0 and later.

You can add Db2 on Cloud data source connections to Data Virtualization. However, you cannot specify any port information when you add these data source connections; by default, port 50001 is used. Additionally, you cannot specify whether the port has SSL enabled, so all Db2 on Cloud connections have SSL enabled by default. If you want to add a Db2 on Cloud connection with a different port or without enabling SSL, specify the Db2 connection type.

Applies to: 4.5.0 and later.

When you try to add Db2 Hosted , Db2 Event Store, and IBM Cloud Compose for MySQL data source connections, you cannot specify whether to enable SSL or not. This limitation prevents you from adding these data source connections to Data Virtualization. To solve this issue, if you want to add these connections to Data Virtualization, specify the Db2 connection type for Db2 Hosted data source and the MySQL connection type for the IBM Cloud Compose for MySQL data source.

Applies to: 4.5.0 and later.

When you run a query against Db2 data sources, you might encounter performance issues. For example, these performance issues might appear if your SQL statement uses a string comparable, such as c1 = 'abc', c2=c3, where c1, c2, and c3 are string data types such as char or varchar. To avoid these performance issues, you must modify the collating sequence (COLLATING_SEQUENCE) server option of the data source. For more information about this server option, see Collating sequence.

Applies to: 4.5.0 and later.

To solve this issue, ensure that your Data Virtualization connections are on a single Cloud Pak for Data cluster.

Applies to: 4.5.0 and later.

When you run the script to upgrade a remote connector on a Windows data source, you see some error messages. For example,

'#' is not recognized as an internal or external command, operable program or batch file. '#export' is not recognized as an internal or external command, operable program or batch file.

You can ignore these error messages as the remote connector was upgraded successfully.

Applies to: 4.5.0 and later.

Applies to: 4.5.0 and later.

In the default virtualization mode (where you can see all tables, irrespective of business term assignments), when you navigate to the Virtualize page, the console appears to be loading the table list for a while when data sources added to Data Virtualization have tables with nonstandard types, such as NULL or OTHER. However, you can wait for the loading to complete to see a list of all tables, and you can preview, add to cart, edit columns, and virtualize any of the listed tables. Refresh is disabled, but you can refresh the page to trigger the reload of the available tables cache.

Applies to: 4.5.0 and later.

Data Virtualization does not deactivate the connection pool for that data source when your instance runs a continuous workload against virtual tables from a particular data source. Instead, Data Virtualization waits for a period of complete inactivity before it deactivates the connection pool. The waiting period can create stale connections in the connection pool that get closed by the data source service and lead to query failures.

• Consider disabling the keep-alive parameter inside any data sources that receive continuous workload from Data Virtualization.
• You can also decrease the settings for corresponding Data Virtualization properties, RDB_CONNECTION_IDLE_SHRINK_TIMEOUT_SEC and RDB_CONNECTION_IDLE_DEACTIVATE_TIMEOUT_SEC, as shown in the following examples: 


  CALL DVSYS.SETCONFIGPROPERTY('RDB_CONNECTION_IDLE_SHRINK_TIMEOUT_SEC', '10', '', ?, ?);    -- default 20s, minimum 5s
  CALL DVSYS.SETCONFIGPROPERTY('RDB_CONNECTION_IDLE_DEACTIVATE_TIMEOUT_SEC, '30', '', ?, ?);    -- default 120s, minimum 5s

  Decreasing the RDB_CONNECTION_IDLE_SHRINK_TIMEOUT_SEC and RDB_CONNECTION_IDLE_DEACTIVATE_TIMEOUT_SEC settings might help if there are small gaps of complete inactivity that were previously too short for the Data Virtualization shrink and deactivate timeouts to take effect.

Applies to: 4.5.0 and later

In Cloud Pak for Data, you can define attribute-based dynamic group assignments. There is a known platform limitation where attributes are synchronized with the external active directory (for example, LDAP) only when the user signs in to Cloud Pak for Data using the web client. Until the user signs in, the attributes known to the platform and the attribute-based dynamic group assignments will remain static. Users might get unexpected decision outcomes for the data protection rules that are based on the dynamic groups.

Applies to: 4.5.0 and later.

A new session (authentication) must be established for the user group assignments to take effect.

Workaround: See User management in Managing users.

Applies to: 4.5.0 and later.

Applies to: 4.5.0 and later.

This limitation is a result of a Db2 limitation on groups. For more information, see Restrictions on the use of group privileges when executing DDL statements or binding packages.

You can also grant public access on your objects for all roles or all Data Virtualization users and then restrict access by using data protection rules that are defined on groups. For more information, see Governing virtual data with data protection rules in Data Virtualization.

See also SQL0727N error when you query view results in Data Virtualization.

Applies to: 4.5.0 and later.

Applies to: 4.5.0 and later.

Workaround: Switch to the List view to see business term assignments and grouped tables.

Applies to: 4.5.0 and later.

Only objects that are created in the user interface are automatically published to the catalog. Objects that are created using SQL are not published automatically and must be published to the catalog manually or by using the API.

Applies to: 4.5.0 and later.

Data Virtualization enforces data protection rules even if the data asset is cataloged in a catalog that does not have the Enforce data protection rules option enabled. This behavior is subject to change in future releases. To ensure a predictable behavior in future releases, add the virtual data assets to the catalogs with the Enforce data protection rules option enabled only.

Applies to: 4.5.0 and later.

To avoid double masking when you use preview in Watson services, access control in Data Virtualization is not applied when you preview a data asset (table or view) that comes from Data Virtualization. This happens only when data masking or row-level filtering applies to the preview in Watson services. Access control does not apply in this circumstance.

You must define your rules to manage access to the catalogs, projects, data assets, or connections for access control in Watson services.

Applies to: 4.5.0 and later.

In the strict virtualization mode (where you can see tables only if they have at least one column with business term assignment), when you navigate to the Virtualize page, the console appears to be loading the table list for a while without showing any tables. The loading can be much slower compared to the default virtualization mode while the console evaluates the list of eligible tables that can be virtualized, depending on term assignments to data source table and column names.

Applies to: 4.5.0 and later.

You cannot access a table but according to the data policies and authorizations, you are authorized to access this table. This issue occurs only if Watson Knowledge Catalog policy enforcement is enabled in Data Virtualization. To solve this issue, see Access to a table is denied by policies in Data Virtualization.

Applies to: 4.5.0 and later.

The policy service is unable to decide which of the duplicated assets to use for policy enforcement and does not aggregate the rules. You must avoid duplicate assets across governed catalogs as this might lead to issues with policy enforcement behavior in Data Virtualization.

Applies to: 4.5.0 and later.

You are virtualizing a data asset in Watson Knowledge Catalog, but you cannot see a business term that is assigned to this data asset. To solve this issue, see Cannot see business term that is assigned to data asset in Data Virtualization.

Applies to: 4.5.0 and later.

If you did not enter a username and password when you created the connection to the Data Virtualization data source, you see the following error: Missing personal credentials. If you are not assigned the Admin or Steward role for this object, you see the following error: Unable to access this asset.

To work around this issue, see A virtualized object cannot be used in Cognos Dashboards without credentials and an appropriate role in Data Virtualization.

Applies to: 4.5.0 and later.

400: Missing ResultSet:java.sql.SQLSyntaxErrorException: Long column type column or parameter 'COLUMN2' not permitted in declared global temporary tables or procedure definitions.

Column heading names are case-insensitive and converted to uppercase in the API response, which is exposed by the console. Therefore, a column named ABC is considered the same as a column named abc. However, the columns can be renamed to mixed case when you virtualize your data source.

Applies to: 4.5.0 and later.

ERROR 400 'Your InputStream was neither an OLE2 stream nor an OOXML stream' was thrown while evaluating an expression.

1. Load the file data into a table on a supported data source. For more information, see Supported data sources.
2. Virtualize the table. For more information, see Creating a virtualized table.
3. Optionally, you can change the format of the file; for example, to CSV format before you virtualize it again.

Applies to: 4.5.0 and later.

For text files exposed by remote connectors, Cloud Pak for Data automatically detects the encoding scheme of flat data files, such as CSV and TSV files. However, to avoid decoding issues, it is recommended that you set the encoding scheme manually for flat data files. For more information, see Setting encoding scheme.

Applies to: 4.5.0 and later.

For text files in Cloud Object Storage data sources with CSV, TSV, and JSON formats, only UTF-8 character encoding is supported in Data Virtualization. Cloud Object Storage binary formats such as Optimized Row Columnar (ORC) or Parquet are unaffected because they transparently encode character types.

Applies to: 4.5.0 and later.

Workaround: On the Virtualize page, click to reload tables.

Applies to: 4.5.0, 4.5.1, and 4.5.2.

Fixed in: 4.5.3.

Edit the schema names before you virtualize. Remove periods from the schema name.

Alternatively, go to the project and add the connected asset from within the project by using the Data Virtualization connection.

Applies to: 4.5.1.

Fixed in: 4.5.3.

Workaround: Set no more than 200 tables with business terms in one catalog.

Applies to: 4.5.0 and 4.5.1.

Fixed in: 4.5.3.

Fixed in: 4.5.2.

Workaround: Add the user with the Engineer role to thePlatform assets catalog as an editor or administrator.

Applies to: 4.5.0.

Fixed in: 4.5.1.

Applies to: 4.5.0.

Fixed in: 4.5.1.

Workaround: Add the Access governance artifacts Platform permission for the current user. If you do not have a catalog other than the Platform assets catalog, create a catalog. For more information, see Managing business terms (Watson Knowledge Catalog).

Applies to: 4.5.0.

Fixed in: 4.5.1.