Limitations and known issues for data sources in Data Virtualization

Data source issues

PREVIEWTABLE might return incorrect results or fail with SQL30020N when remote MySQL or Greenplum tables contain BIT or BINARY data types

Applies to: 5.4.0PREVIEWTABLE might fail with the SQL30020N error, and binary values might appear corrupted or contain unexpected data. In some cases, data from one column appears to spill into the next column, causing a malformed output.

This issue impacts:

• BIT columns in MySQL and Greenplum
• MySQL BINARY columns in PREVIEWTABLE

Workaround: Complete any of these possible workarounds.

• Use virtualized tables (nicknames) instead of PREVIEWTABLE where applicable.
• Use federated queries against virtualized objects rather than PREVIEWTABLE.
• If possible, avoid previewing tables that contain BIT/BINARY columns until the issue is resolved.

Adding a non SSL connection after an SSL connection with Personal Credentials makes the existing SSL connections invalid or display an error while querying the virtualized tables

Applies to: 5.4.0

If you add a non-SSL connection after an SSL connection was created with personal credentials, the non-SSL connection resets the dsTrustore, causing the existing SSL connection to become invalid. If you query the virtualized tables from the SSL connection, this error displays:

Unexpected error code "GDB_ERROR" received from data source "DV-FMP". Associated text
        and tokens are "Unable to get JDBC Connection before timeout - connec".. SQLCODE=-1822,
        SQLSTATE=560BD, DRIVER=4.35.15

Also, if you add an SSL connection after adding a non-SSL connection, and then later attempt to edit the non-SSL connection, the non-SSL connection re-triggers and causes the existing SSL connections to change to the Invaild state.

Workaround: Edit the SSL connection by just adding or modifying the description, re-triggering the connection flow, which turns the connection from the Invalid to Available state.

IBM Cloud Databases for PostgreSQL connector does not work if you provide a SSL certificate

Applies to: 5.4.0

If you provide a SSL certificate while you create a connection to PostgreSQL, by using the IBM Cloud Databases for PostgreSQL connector, the connection creation fails.

Workaround: Complete any of these possible workarounds.

• Create a PostgreSQL connection by using the normal PostgreSQL connector.
• Attempt to recreate the PostgreSQL connection without providing the SSL certificate.

Databricks connector does not work

Applies to: 5.4.0

You cannot connect to Databricks by using the Databricks connector due to connection issues.

Workaround: To create a Databricks connection, run the setRdbcX stored procedure.

Example call:

call dvsys.setrdbcx('Databricks', 'adb-2945880371306113.13.azuredatabricks.net', 443, 'default', 'transportMode=http;AuthMech=3;httpPath=/sql/1.0/warehouses/abaa9f18e77cc474;', 'token', '<password>', 1, 0, '', '', '', '', ?, ?, ?);

Creating a REST API connection with public CA authentication fails when passed through a proxy server

Applies to: 5.4.0

When you create a new REST API connector to virtualize REST APIs, the connection fails to be established if the target REST API has public CA authentication and has to be passed through a proxy server. This error message displays:

The data source could not be added. Ensure that the specified parameters are correct and try again.
qpendpoint_2:6416, failed with The exception 'java.lang.Exception: [IBM][AutoREST JDBC Driver][AutoREST]Failed to sample all endpoints in "" Caused by: com.ibm.jdbc.autorest.phoenix.sql.ae: I/O Error PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target Caused by: javax.net.ssl.SSLHandshakeException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target Caused by: javax.net.ssl.SSLHandshakeException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target' was thrown while evaluating an expression.;

Workaround: Complete these steps for REST API connections configured to use a proxy with SSL.

1. Upload the certificate by following the steps from Using a CA certificate to connect to internal servers from the platform.
2. Restart the c-db2u-dv-db2u-0 pod.
   Note: If the secret does not exist on the platform, then you can upload it to Data Virtualization by running this stored procedure through the Db2 CLI from within the c-db2u-dv-db2u-0 pod. Ensure you replace <certificate> with the CA certificate content in PEM format. Do not restart the c-db2u-dv-db2u-0 pod.

   call dvsys.MANAGEREMOTECACERT('UPDATE', '<certificate>', 'CCSCACERT', 'LOCATION=GAIANTRUSTSTORE', ?, ?);

3. Create the connection again in the Data Virtualization web client console. Do not pass the certificate in the SSL text field box, instead mark the box as SSL enabled.

Connections created using the cpd-cli connection create command can't be found or used

Applies to: 5.4.1

When you create connections by using the cpd-cli connection create command, the connections created cannot be found or used in Data Virtualization, but they are available and can be used in projects and catalogs. For more information on connection create, see connection create.

Workaround: Create the connection using the ID.

Connecting to Informix might initially undergo poor performance

Applies to: 5.4.0

When you connect to Informix by using the TCP/IP protocol, Informix experiences poor performance during the connection. After the connection has been established, the performance improves.

Workaround: After you establish the initial the connection to Informix, you can then improve the connection speeds by adding your local IP to the source file. For more information on this Informix issue, see the IBM Support article Slow connect time to Informix when using TCP/IP.

Previewtable and Virtualizetable stored procedures of some tables fail when you run them on Data Virtualization Manager remote tables

Applies to: 5.4.0

If you run the Previewtable and Virtualizetable stored procedures on DVM remote tables, then the actions might fail with the following error: 2025-12-04 07:15:04.853 ********** GDB_WARNING: ENGINE_STATEMENT_EXEC_ERROR: GDB_GTW_DVSQL_CB1A_TSDCV_TESTOTHER_DVM10020 Unable to EXECUTE statement (returning null result): com.rs.jdbc.dv.cmbu.CmbuSqlException: [DV][JDBC Driver][3.1.202403071758] SYSTEM ABEND X'0C3' OCCURRED ON THE REMOTE SYSTEM AND IS BEING PROPAGATED TO THIS SYSTEM [reasonCode=3 (0x00000003), sqlCode=-1 (0xffffffff), message1="SYSTEM ABEND X'0C3' OCCURRED ON THE REMOTE SYSTEM AND IS BEING PROPAGATED TO THIS SYSTEM"]. There is currently no workaround for this issue. Contact IBM Customer Support for more information, the Rocket reference number is APAR PH67068.

VIRTUALIZETABLE procedure fails on Presto if user only has partial column access

Applies to: 5.4.0

For Presto data sources, the VIRTUALIZETABLE stored procedure fails with a Query failed error when a user has access to only a subset of columns in the underlying table.

Workaround: Create a view in the source that includes only the columns the user can access, and then virtualize that view in Data Virtualization.

Double quotation marks used in fully capitalized tables names for Apache Cassandra or DataStax might encounter issues

Applies to: 5.4.0

If you create tables in Apache Cassandra or DataStax with fully capitalized names enclosed in double quotation marks (e.g., "PRODUCT"), then the resulting table name is created without the double quotation marks in the metadata (e.g., PRODUCT). This issue might cause problems when you subsequently preview or virtualize these tables in Data Virtualization.

Using the INSERT operation on a Microsoft Azure Data Lake Storage connection causes error

Applies to: 5.4.0

When you use the INSERT operation to insert data into a table on a Microsoft Azure Data Lake Storage connection, the operation might fail and cause an error resembling this example:

2025-08-06T20:08:16,210 WARN com.ibm.biginsights.bigsql.dfsrw.scheduler.DfsTempDirManager [TThreadPoolServer WorkerProcess-4] SId:32.0-6633: Error calling setPermission on abfs://bigdatacontainer2@bigdataqasa2.dfs.core.windows.net/FVT/dv_fvt_cos_data2124/COSBVT/path5571/_TEMP_1754499267690_320574492_20250906080915760

2025-08-06T20:08:26,720 ERROR com.ibm.biginsights.bigsql.dfsrw.scheduler.DfsBaseWriterCommitHandler [TThreadPoolServer WorkerProcess-4] {bigsql.COMMIT} SId:32.0-6633: Failed to commit for cosbvt.table5571
org.apache.hadoop.fs.FileAlreadyExistsException: Operation failed: "The specified path already exists.", 409, PUT, https://bigdataqasa2.dfs.core.windows.net/bigdatacontainer2/FVT/dv_fvt_cos_data2124/COSBVT/path5571/.COMMITTING___TEMP_1754499267690_320574492_20250906080915760?resource=file&timeout=90, rId: dbf44b18-601f-0090-6e0d-05e4c1000000, PathAlreadyExists,
"The specified path already exists. RequestId:dbf22b38-603f-0090-7f0d-07e8s1000000 Time:2025-08-06T20:08:26.6748181Z"

Workaround:

• Primary workaround

  Delete the temporary commit marker file manually and retry the insert. From the previous example, you would delete the .COMMITTING___TEMP_1754499267690_320574492_20250906080915760 file.

• Secondary workaround

  Bypass the use of temporary commit markers by setting bigsql.insert.temporary.storage.mode to BYPASS. For more information, see Bypassing temporary directories to increase the performance of insert operations into object stores.

Collecting statistics for a virtualized Presto table results in an error

Applies to: 5.4.0

When you try to collect the statistics of a virtualized Presto table using the COLLECT_STATISTICS stored procedure in Run SQL, you might encounter the error No statistics returned for table.

Workaround: Wait for an interval of 2-3 minutes and then attempt to collect the statistics again.

Note: You can apply a permanent fix on the data source side by setting the parameter http-server.max-request-header-size=<value up to 10TB> in the Presto coordinator configuration file. For more information, see Update Presto engine.

Unable to connect to an SSL-enabled data source in Data Virtualization by using a remote connector if you use custom certificates

Applies to: 5.4.0

After you create your own custom certificates to use when Data Virtualization connects to the IBM® Software Hub platform, you are not able to connect to a remote SSL-enabled data source.

For more information about remote data sources and connectors in Data Virtualization, see Accessing data sources by using remote connectors in Data Virtualization.

Workaround:

Complete the following steps to resolve this issue and connect to remote data sources:

1. Download the CA certificate files from /etc/pki/ca-trust/source/anchors in the Data Virtualization head pod c-db2u-dv-db2u-0.
2. Upload the CA certificate files that you downloaded in step 1 to each remote connector computer.
3. If a file has more than one CA certificate, split the certificates into individual files
4. Run the following command to set AGENT_JAVA_HOME to the Java home that is used by the remote connectors:

   AGENT_JAVA_HOME=$(tac <Remote connector install path>/datavirtualization.env| grep -i java_home | grep -v "#" -m )

5. For each file that you created in step 3, run the following command to add the certificates to the Java cacerts truststore in AGENT_JAVA_HOME/lib/security/cacerts. Make sure that you provide a unique alias value for the -alias command parameter for each file.

   keytool -storepass changeit -keystore $AGENT_JAVA_HOME/lib/security/cacerts -importcert -alias <pick a alias> -rfc -file <absolute path to cert generated in step 3> -noprompt

6. Restart the remote connector. For more information, see Managing connectors on remote data sources.

Querying a virtualized table in a Presto catalog with a matching schema from a previous catalog might result in an error

Applies to: 5.4.0

When you use a new catalog and then query a virtualized table from a previous catalog with an identical schema name, the query might run if the schema exists in the new catalog, but the outcome might result in an error. The resulting error might be caused by the differences in column definitions within the tables under the same schema name across catalogs.

Special characters are not supported in MongoDB database names

Applies to: 5.4.0

You cannot use special characters such as semicolons and single quotes in a MongoDB database name.

Limited file types are supported with the Microsoft Azure Data Lake Storage Gen2 data source connection

Applies to: 5.4.0

You can connect to Microsoft Azure Data Lake Storage Gen2 data source from Data Virtualization. However, only the following file types are supported with this connection in Data Virtualization:

• CSV
• TSV
• ORC
• Parquet
• JSON Lines

Special characters are not preserved in databases or schemas with MongoDB connections after you upgrade to Data Virtualization on IBM Software Hub

Applies to: 5.4.0

After you upgrade from an earlier version of Data Virtualization on IBM Software Hub to version 5.4.0, with a MongoDB connection, special characters in the database or schema are not preserved even though SpecialCharBehavior=Include is set in the data source connection. The updated MongoDB driver (version 6.1.0) that is used in Data Virtualization on IBM Software Hub does not recognize special characters in tables by default. This might cause issues with your results when you query a virtual table that has special characters.

The DECFLOAT data type is not supported in Data Virtualization

Applies to: 5.4.0

The DECFLOAT data type is not supported in Data Virtualization. As a result, the type DECFLOAT is converted to DOUBLE and the special numeric values NaN, INF, and -INF are converted to NULL.

The Data sources page might fail to load data sources when remote connectors are added, edited, or removed

Applies to: 5.4.0

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.

Unable to add a connection to SAP S/4HANA data source with a SAP OData connection

Applies to: 5.4.0

If you try to connect to an SAP S/4HANA data source that contains many tables, that connection might time out and the connection might fail. Increasing timeout parameters has no impact.

To work around this issue, run the following commands.

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

You cannot connect to a MongoDB data source with special characters in a database name

Applies to: 5.4.0

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

Remote data sources - Performance issues when you create data source connection

Applies to: 5.4.0

You try to create a data source by searching a different host, but the process takes several minutes to complete. This performance issue occurs only when these two conditions are met:

• The remote data source is connected to multiple IBM Software Hub clusters.
• Data Virtualization connects to multiple data sources in different IBM Software Hub clusters by using the remote connectors.

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

Query fails due to unexpectedly closed connection to data source

Applies to: 5.4.0

Data Virtualization does not deactivate the connection pool for the 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.

Workaround: Check the properties for persistent connection (keep-alive parameter) for your data sources. You can try two workarounds:


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

Schema map refresh in-progress message appears for reloaded connections that do not require a refresh schema map

Applies to: 5.4.0

The Schema map refresh in-progress message appears when you reload connections in Data Virtualization, even when the data source does not require a refresh schema map.

Only connections from data sources such as Google BigQuery, MongoDB, SAP S/4HANA, and Salesforce.com require a refresh schema map to update any changes in tables and columns for existing connections.