Query of a virtualized table fails with Presto after upgrade from 4.8.x to Cloud Pak for Data 5.1

You can manually update the virtualized tables on your Presto connection if you encounter a GDB_ERROR.

Symptoms

If you have previous Presto connections and then you upgrade from Data Virtualization 4.8.x to Data Virtualization to Cloud Pak for Data 5.1, you might encounter a GDB_ERROR when you query a virtualized table.

Resolving the problem

  1. In the Data virtualization > Run SQL editor, run the dvsys.listrdbc query to identify the internal Connection ID (CID) for your Presto data source:
    select CID from dvsys.listrdbc

  2. Retrieve the set of virtualized tables for each Presto connection. Run the following query, replacing the (CID) value with the Presto CID value that you identified in step 1:

    select TABSCHEMA, TABNAME, OPTION, SETTING from SYSIBM.SYSTABOPTIONS where OPTION = 'SOURCELIST' and SETTING like '%<CID>%'
    The output of this query produces a virtualized table list for each Presto connection. Note the values for TABSCHEMA, TABNAME, and SETTING produced from this query.
  3. Run the following query, replacing the values for <TABSCHEMA>, <TABNAME>, and <SETTING> for each virtualized table list produced from the query in step 2. 
    alter NICKNAME "<TABSCHEMA>"."<TABNAME>" OPTIONS ( SET SOURCELIST  '<SETTING>')

    Modify the <SETTING> value before you run the query. Initially, the SETTING output has a format like PREST1000:"schema_name". For this query, modify your <SETTING> value to place the correct catalog name between the <SETTING> and the schema_name, with a period between the catalog name and schema_name.

    You can refer to the example query for guidance, where
    • <TABSCHEMA> has the value "CPADMIN",
    • <TABNAME> has the "presto_hive_sanity_string" value,
    • and the modified <SETTING> has the value PREST10000:"hive_data".tm_dv, with "hive_data" as the catalog name:
     alter NICKNAME "CPADMIN"."presto_hive_sanity_string" OPTIONS ( SET SOURCELIST 'PREST10000:"hive_data".tm_dv')
  4. If you are not able to run the virtualized table after you complete step 3, you need to virtualized that table again.