Checking whether a query was routed to shadow tables by using monitoring interfaces

Use monitoring table functions to view details on which tables were used in the query plan for statements that are in cached memory. You can use monitoring functions to identify the objects that are used in a query plan or to identify the list of statements that are currently cached in memory. By viewing the list of objects and statements, you can check whether a query was routed to a shadow table. If the statement is no longer cached in memory, you must use the EXPLAIN utility.

To check whether a query was routed to a shadow table:

1. View a list of all the objects (tables or indexes) that are used in the query plan for a statement by running the following query:

   SELECT DISTINCT
     stmt.EXECUTABLE_ID, OBJECT_TYPE,
     substr(OBJECT_NAME,1,25) as OBJECT_NAME,
     substr(OBJECT_SCHEMA,1,25) as OBJECT_SCHEMA
   FROM
     table(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1)) AS stmt,
     table(MON_GET_SECTION_OBJECT(stmt.EXECUTABLE_ID,NULL,NULL,NULL,NULL,-1))
       AS object
   WHERE
     STMT_TEXT = 'statement-text';

   The following example is an edited version of the output that you might receive from running the preceding query:

   EXECUTABLE_ID                            OBJECT_TYPE OBJECT_NAME OBJECT_SCHEMA
   ---------------------------------------- ----------- ----------- -------------
   x'0100..7F00..0020020140711113104198741' TA          T1_SHADOW   ONLINE
   
     1 record(s) selected.

2. Run a query to view the list of statements that are currently cached in memory where a particular shadow table was used.
   The following query shows the list of statements that are currently cached in memory for the table TRADE_SHADOW that belongs to the schema DTW:

   SELECT DISTINCT
     object.EXECUTABLE_ID,
     substr(STMT_TEXT,1,200) as STMT_TEXT
   FROM
     table(MON_GET_SECTION_OBJECT(NULL,NULL,'DTW',NULL,'TRADE_SHADOW',-1))
       AS object,
     table(MON_GET_PKG_CACHE_STMT(object.EXECUTABLE_ID,NULL,NULL,-1)) AS stmt