Last updated: Nov 26, 2024
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'