NUMTABSREMOTE view

The DVSYS.NUMTABSREMOTE view displays the number of tables in a remote RDBMS source.

Before you begin

Consider the following limitations:

  • This view does not usually call predicates, but you can use predicates on the CID and FILTER columns.
  • Only one thread can call the view at the remote data source at a time.
  • The default timeout for calling this view is 5 seconds, which can be configured using the SOURCE_CATALOG_API_CACHED_FOREGROUND_TIMEOUT_MS property.
  • If calling this view times out, the NUMTABS_FILTERED or NUMTABS_TOTAL columns return -1 and the call to the remote source will continue. After the call completes, the cache updates with the new value, which is returned in subsequent NUMTABSREMOTE calls.
  • If the NUMTABS_FILTERED or NUMTABS_TOTAL columns return -2, then this indicates that the data source connection is experiencing problems.
Note: In the following table, a pushed column refers to a column whose predicate value is used in a remote query or API running against the remote source to retrieve the total number of tables.
Table 1. DVSYS.NUMTABSREMOTE
Column Type Description
CID VARCHAR(2) Connection ID of the data source.
  • Pushed column: An equality predicate on this column will match the specified CID value, so the number of tables will be calculated only for that source.
FILTER VARCHAR(500) The composite CSV string with the same format as used for properties RELOADTABLES_ALLTABS_<CID> and RELOADTABLES_ALLCOLS_<CID>: '<CATALOG>,<SCHEMAPATTERN>,<TABLENAMEPATTERN>,<TABLETYPES>'.
  • Default behavior: If no equality predicate is specified on this column, then the individual filters for each source (as specified in the configuration) are applied. See the NUMTABS_FILTERED column.
  • Pushed column: An equality predicate on this column filters the data by matching the specified value against the column.
    For example, the following query resolves the number of tables in source DB20001 with schema ICP and table pattern TEST_75% (i.e. all table names starting with TEST_75). 
    select * from DVSYS.NUMTABSREMOTE where CID = 'DB20001' and FILTER = 'null,ICP,TEST_75%,null'
NUMTABS_TOTAL BIGINT The total number of tables in the source.
Note: If you attempt to run this view for multiple catalog sources, this column returns an output of -1 as the total number of tables, and this is expected. For more information, see Running the NUMTABSREMOTE view for multiple catalog sources returns -1 as the total number of tables.
NUMTABS_FILTERED BIGINT The number of tables in the source when applying filter constraints. If an equality predicate is specified against column FILTER then that value is used as a filter. Otherwise, the value of the configuration property is RELOADTABLES_ALLTABS_<CID>. If the value is not configured then the number will be the same as NUMTABS_TOTAL.

Example

This example call retrieves the total number of tables. 
SELECT * FROM DVSYS.NUMTABSREMOTE

Learn more

For more information on the SOURCE_CATALOG_API_CACHED_FOREGROUND_TIMEOUT_MS, RELOADTABLES_ALLTABS_<CID> and RELOADTABLES_ALLCOLS_<CID> properties, refer to SETCONFIGPROPERTY properties.