Limitations and known issues in Data Virtualization

When you add a large number of tables to your cart, for example more than 120, and try to virtualize them, you might see HTTP status code 500 Internal Server Error and your cart will fail to virtualize.

The persistent volume (PV) on the Data Virtualization head node becomes full because archived transaction logs in the embedded Db2® database are taking up a significant amount of space on the PV.

To work around this issue, see Persistent volume on Data Virtualization head pod becomes full.

Data Virtualization stops accepting connections when your SSL certification expires. You might see an error message that indicates The server encountered an error followed by a Load configured data source failed: HttpError500 message.

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.

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.

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)

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

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

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.

When you create virtual tables, some of the table statistics are not collected correctly. To solve this issue, see Table statistics are not collected. For more information, Collecting statistics in Data Virtualization.

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 to 10m. For more information, see Load balancer timeout settings.

Some connections in Cloud Pak for Data 4.0.8 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.

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 connection.

The SSL certificate must either be input as a bare certificate in plain text format or be entered 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\"}"

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.

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.

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

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.

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

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.

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.

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.

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.

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.

When you try to add Db2 Hosted , Db2 Event Store, and 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 and Db2 Event Store data sources, data source and the MySQL connection type for the Compose for MySQL data source.

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.

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

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.

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

Workaround: SeeUser management in Managing users.

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 Virtualizationusers 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 Error SQL0727N when you query view results in Data Virtualization.

When you virtualize files in Data > Data virtualization > Virtualize, the Business terms column is not available for data sources on the Files tab. These data sources do not support business terms.

In Cloud Pak for Data version 4.0.7 or 4.0.8, profiling of Data Virtualization data assets in Watson Knowledge Catalog fails with the following error The property [auth_method] is not supported. Also, you cannot preview virtual objects in Watson Knowledge Catalog when a data masking rule applies.

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.

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.

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.

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.

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.

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.

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.

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.

To set a cache refresh rate, you can select an Hourly frequency and then choose the minute of the hour when the cache refresh is run. If you increase this minute beyond 59, the refresh minute becomes blank. If you leave this page with the refresh minute blank and then return to this page, you cannot set the hourly refresh rate for a cache because the minute remains blank.

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.

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

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.

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.

To work around the problem, restart the Data Virtualization operator pod by running the following command.

oc -n <project> delete pod $(oc -n <project> get pods | grep -i dv | cut -d' ' -f 1)

Data sources larger than 100,000 tables slow down the loading of tables in the Virtualize > Tables page. You can reduce their scope by setting remote schema filters. For more information, see Speed up loading of tables when you virtualize in Data Virtualization.

When you add a Data Virtualization connection to an existing platform connection to Cloud Object Storage, you must add all of the required credentials. For more information, see Connecting to Cloud Object Storage in Data Virtualization.

To add credentials, you must zoom out in your browser to see the entire Add required credentials dialog box. The Secret key field is not enabled until you enter an Access key. The Update button does not appear until you zoom out to see the entire dialog box.

After you connect to data sources and virtualize group tables, you can review the grouped tables and clear some entries before you virtualize. On the Edit grouped tables page, if you have a long list and you need to scroll through the page, the scroll bar might disappear after you clear some selections. You cannot scroll up and click Apply and continue.

On Data > Data virtualization > Virtualization > Cache management > Add new cache, the cache can be created and managed by users. However, if you click Test queries with cache, you see a Failed to test queries error message. You cannot use matching and recommendations capabilities in caching for Cloud Object Storage.

1. On all remote connector hosts, edit the file: <DV endpoint directory>/sysroot/data/gaiandb_config.properties.
2. Search for and replace all instances of com.ibm.db2j.GExcel with com.ibm.db2j.QExcel. For troubleshooting, see Queries on virtualized flat files fail with incorrect results in Data Virtualization.
3. Save the file and exit the editor. The changes are automatically loaded after you save the file. Confirm that large Excel files can be queried.
4. If virtual tables for files on a host still don't show content, try restarting the remote connector agent. For more information, see Managing connectors on remote data sources.

You might experience SQL operations intermittently failing, and you might also see SQLCODE=-1229 errors:

Error message The current transaction has been rolled back because of a system error.. SQLCODE=-1229, SQLSTATE=40504, DRIVER=4.27.25

To work around the issue, see Data Virtualization service on Cloud Pak for Data appears to be unstable on a Red Hat OpenShift on Kubernetes cluster.

When you virtualize a table that has a double quotation mark in the schema name, you will see an error message and you will not be able to virtualize the table. See the following example.

Error The assets request failed: CDICO2060E: The metadata for the select statement could not be retrieved Sql error: The statement failed because a Big SQL component encountered an error. Component receiving the error: "SCHEDULER". Component returning the error: "SCHEDULER". Log entry identifier: "[SCL-0-3a08009d4]". Reason: "".. SQLCODE=-5105, SQLSTATE=58040, DRIVER=4.28.11. The statement text is: SELECT * FROM """ABC"""."SHIP_MODE" FOR READ ONLY

This issue can occur on Netezza® on Cloud, MongoDB, and Db2 Warehouse data sources.

If you create a connection by using an uploaded IBM Informix JDBC driver, this connection will not support SSL.

You have the Data Virtualization Admin or Engineer roles and you try to add a Ceph data source connection with incorrect parameters. The error message indicates The data source could not be added. Ensure that the specified parameters are correct and try again, however, when you click Edit in the message, the page hangs and you cannot edit your parameters.

In Cloud Pak for Data version 4.0.1 or earlier, the Data Virtualization caching pod does not initialize successfully if Db2 Data Management Console does not initialize successfully.

• Number of worker nodes
• Cores per worker node
• Memory per worker node
• Storage summary

In Cloud Pak for Data 4.0.1, a user who does not explicitly have a Data Virtualization Admin role but is assigned to a group and the group has a Data Virtualization Admin role, will not be able to access the cache dashboard page.