0 / 0
Speed up loading of tables when you virtualize in Watson Query

Speed up loading of tables when you virtualize in Watson Query

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.

Symptoms

You try to list or retrieve data sources with large tables in Watson Query, but the process takes too long.

Resolving the problem

To apply schema filters to reduce the scope of an existing Watson Query data source connection, use the following commands.

  1. Ensure that source connections are set up correctly. In Data virtualization > Run SQL, run the following command.
    CALL DVSYS.LISTRDBCDETAILS

    Check that all sources are present and that CPOOLSIZE and CPOOLALL are greater than 0.

  2. Set up schema filters for the new source by using the CID value assigned to the newly created source that you retrieved in step 1. In Data virtualization > SQL Editor, run the following commands.
    Replace these values with your own.
    • <cid>
    • <schema>
    • <agent_name>, labeled GDB_NODE in the output of step 1.
    Note: You must run both of the following SQL calls. You need two filters; one for table names and one for columns.
    CALL DVSYS.SETCONFIGPROPERTY('RELOADTABLES_ALLTABS_<cid>', 'null,<schema>,null,null', '<agent_name>', ?, ?)
    CALL DVSYS.SETCONFIGPROPERTY('RELOADTABLES_ALLCOLS_<cid>', 'null,<schema>,null,null', '<agent_name>', ?, ?)
  3. Watson Query loads tables from the newly added source schema when you go to the Virtualize page in the console. If the Virtualize page behavior hasn't changed, it might be because a previous reload is still in progress. You can interrupt it by using the following command:
    SELECT count(*) FROM DVSYS.RELOADTABLES WHERE CACHED>-100
  4. The Virtualize page now reloads tables from scratch. If you want to replace the schema filter, you can rerun step 2 and refresh the tables again in the console.

    To disable the filter, rerun step 2 with <schema> set to null.

Generative AI search and answer
These answers are generated by a large language model in watsonx.ai based on content from the product documentation. Learn more