Docs
...
Speed up loading of tables when you virtualize

Speed up loading of tables when you virtualize in Data Virtualization

Last updated: Mar 17, 2025
Speed up loading of tables when you virtualize in Data Virtualization

Data sources larger than 100,000 tables slow down the loading of tables in the Virtualize > Tables page. You can reduce the scope of the data on from the Virtualize page by setting up a remote schema filter, and then viewing the filtered data from the List view. The remote schema filter excludes other schemas at the source, unlike filters that apply to the page, which only apply to the web client.

Symptoms

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

Resolving the problem

Complete the following steps to apply schema filters to reduce the scope of an existing Data Virtualization data source connection.

  1. Ensure that source connections are set up correctly. In Data virtualization > Run SQL, then 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, then 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. Data Virtualization loads tables from the newly added source schema when you go to the Virtualize page and select List view 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 running 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.