Clean up data sources in Data Virtualization to improve performance

You can clean up your data sources to improve the time that it takes to load the Data sources page by removing duplicate or unnecessary connections.

These steps can be used to maintain virtual table dependencies and clean up orphaned or unlinked virtual tables. You also avoid loading these redundant tables on the Virtualize page.

Symptoms

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

Resolving the problem

You can find virtual table dependencies on data sources to determine which data source connections are most critical to your workload. This step helps you plan for removal of specific data source connections and to avoid orphaned virtual tables.

To view dependent virtual table counts, run the following command.

select cid, remotenodes, count(setting) numvts, removed, usr, url from dvsys.rdb_connections histconns left join syscat.taboptions opts on setting like '%'||cid||'%' and option = 'SOURCELIST' group by cid, remotenodes, url, usr, removed

To view individual dependent virtual tables, run the following command.

select cid, tabschema, tabname, cast( setting as VARCHAR(32672) ) sourcelist from dvsys.rdb_connections histconns left join syscat.taboptions opts on setting like '%'||cid||'%' and option = 'SOURCELIST'

Optionally, to view all virtual table dependencies, including views on base virtual tables, and on all source types, including object storage data sources and files, run the following command. This command might be slower to run.

select tabschema, tabname, owner, definer, cid, ext_connid, srctype, srcschema, srctabname, driver, url, user "USER", hostname, port, dbname, create_time, alter_time from SYSCAT.TABLES t, table(dvsys.get_vt_sources(t.tabschema, t.tabname)) s where t.tabschema not in ('DVSYS') and t.type = 'N'