Verifying table sync in watsonx.data

This topic provides details to verify that all expected tables have been successfully synced after the initial automatic as well as the manual sync operations in IBM® watsonx.data.

watsonx.data on IBM Software Hub

Before you begin

  1. Install and activate Query optimizer manager in watsonx.data. See Activating Query optimizer in IBM watsonx.data.
  2. Names of catalogs and schemas for the tables being synced must be known.
  3. The expected number of tables within each schema must be known.
  4. Run the following command and record the table properties (such as number of rows) for comparison in Step 5: 
    select count (*) from catalog.schema.table;
  5. If the session parameter is_query_rewriter_plugin_enabled is set to false, you will not be able to execute the ExecuteWxdQueryOptimizer commands.

Procedure

  1. Log in to watsonx.data console.
  2. Go to Query workspace and select Presto (C++) engine.
  3. Run the following command to retrieve a list of potential schema names: 
    ExecuteWxdQueryOptimizer 'select id_to_extsch(name) AS CATALOG_SCHEMA, name AS "SCHEMAID"  from SYSIBM.sysschemata where name != qualifier1';

    Select the correct schema name (case-sensitive) from the results.

  4. Run one of the following commands as needed:
    1. Run this command to count the number of tables within the specified schema: 
      ExecuteWxdQueryOptimizer 'select count(1) AS schema_table_count from SYSIBM.SYSTABLES WHERE CREATOR=EXTSCH_TO_ID('catalog_name', 'schema_name')';

      For example:

      ExecuteWxdQueryOptimizer 'select count(1) AS schema_table_count from SYSIBM.SYSTABLES WHERE CREATOR=EXTSCH_TO_ID('sample_data', 'TPCDS_10GB')';

      Compare the result of this query with the expected number of tables. If the counts match, it indicates that all expected tables have been synced.

    2. Run this command to list all tables within the specified schema: 
      ExecuteWxdQueryOptimizer 'SELECT NAME AS TABLE_NAME, id_to_extsch(creator) AS CATALOG_SCHEMA FROM SYSIBM.SYSTABLES WHERE CREATOR=EXTSCH_TO_ID('catalog_name', 'schema_name')';

      For example:

      ExecuteWxdQueryOptimizer 'SELECT NAME AS TABLE_NAME, id_to_extsch(creator) AS CATALOG_SCHEMA FROM SYSIBM.SYSTABLES WHERE CREATOR=EXTSCH_TO_ID('sample_data', 'TPCDS_10GB')';
  5. Run the following query to check table properties (such as number of rows): 
    ExecuteWxdQueryOptimizer 'select name AS tablename, card from SYSIBM.SYSTABLES WHERE CREATOR=EXTSCH_TO_ID('catalog_name', 'schema_name')';

    For example:

    ExecuteWxdQueryOptimizer 'select name AS tablename, card from SYSIBM.SYSTABLES WHERE CREATOR=EXTSCH_TO_ID('sample_data', 'TPCDS_10GB')';