Speed up loading of tables when you virtualize in Data Virtualization

Last updated: Nov 26, 2024

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 Data Virtualization, but the process takes too long.

Resolving the problem

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

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.
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>', ?, ?)
```
Data Virtualization 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
```
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.