Web services data sources - example queries

You use materialized query tables to locally cache the results of a query and to improve the performance of queries. You can use nicknames from Web services data sources to create materialized query tables. For some queries, the database can automatically determine whether the materialized query table can answer a query without accessing the base tables. The following procedure shows how to create and populate a materialized query table:

1. Create a local or base table:

   CREATE TABLE mystocks(ticker VARCHAR(10));
   
   

   You can use the local table to maintain all the values that you want to cache.
2. Insert all of the values that you want to cache into the table:

   INSERT INTO mystocks VALUES('IBM');
   INSERT INTO mystocks VALUES('MSFT');
   ...
   

3. Create a Web services nickname:

   CREATE NICKNAME stockquote_nn ( 	
           ticker VARCHAR(40) OPTIONS (TEMPLATE   '&column'),        
           price VARCHAR(16) OPTIONS (XPATH   './Result/text()')
           )
   	FOR SERVER stock_server
   	OPTIONS (TEMPLATE '<ticker>&column</ticker>'
               XPATH './Result/text()' );

4. Create a view that consists of the nickname and the local table:

   CREATE VIEW  stock_quote_view (ticker, price) 
     AS (
       SELECT nn.ticker, nn.price 
        FROM stockquote_nn nn, mystocks s 
        WHERE nn.ticker=s.ticker
   );

5. Create a materialized query table:

   CREATE TABLE stockquote_MQT (ticker, ticker2, price)
           as (SELECT nn.ticker,s.ticker as ticker2, nn.price 
     FROM stockquote_nn nn, mystocks s
     WHERE nn.ticker=s.ticker ) 
     DATA INITIALLY DEFERRED REFRESH DEFERRED;
   

   Include all of the VARCHAR columns in the join predicate (nn.ticker and s.ticker) in the materialized query table output list to maximize the opportunities that the materialized query table is used by the federated database.

   To defer the refresh of the materialized query table, specify the REFRESH DEFERRED keyword. Materialized query tables that are specified with the REFRESH DEFERRED keyword do not reflect changes to the underlying base table. Use the clause DATA INITIALLY DEFERRED so that your data is not inserted into the table as part of the CREATE TABLE statement.

6. Issue a REFRESH TABLE statement to populate the table. The data in the table reflects the result of the query as a snapshot at the time that you issue the REFRESH TABLE statement. The following example populates the stockquote_MQT table and sets a value for the special register with the current refresh age.

   REFRESH TABLE stockquote_MQT;
   
   SET CURRENT REFRESH AGE any;
   

The queries that run on the data in the materialized query table are faster than the queries that run on the data in a base table. When you want to use the materialized query table, you refer to the view and not the nickname:

SELECT * FROM stock_quote_view
  WHERE  ticker='IBM';

If you issue a query to select a value that has not been cached, 0 rows are returned.

The following example shows how the Web services wrapper uses the PRIMARY_KEY and FOREIGN_KEY columns to associate parent and child nicknames.

CREATE NICKNAME zooport_getzooreport_nn ( 
   zooid VARCHAR (48) OPTIONS(TEMPLATE '&column'),
   zoo_id VARCHAR (48) OPTIONS(XPATH './ns1:Zoo/@id'),
   report_zoo_zooname VARCHAR (48) 
       OPTIONS(XPATH './ns1:Zoo/ns1:ZooName/text()'),
   report_zoo_count VARCHAR (48) 
       OPTIONS(XPATH './ns1:Zoo/ns1:Count/text()'),
   report_zoo_lastmodified VARCHAR (48) 
       OPTIONS(XPATH './ns1:Zoo/ns1:LastModified/text()'),
   nn_pk VARCHAR (16) NOT NULL OPTIONS(PRIMARY_KEY 'YES'),
   url VARCHAR (256) OPTIONS(URLCOLUMN 'Y'),
   soapaction VARCHAR (256) OPTIONS(SOAPACTIONCOLUMN 'Y')
) FOR SERVER "mytestsrvr" 
  OPTIONS(
    URL 'http://localhost:9080/MaelstromTest/services/ZooPort', 
    SOAPACTION 'http://myzoo.com/getZooReport' , 
    TEMPLATE '<soapenv:Envelope>
               <soapenv:Body>
                 <zooId>&zooId[1,1]</zooId>
               </soapenv:Body>
              </soapenv:Envelope>', 
    XPATH '/soapenv:Envelope/soapenv:Body' , 
    NAMESPACES ' soapenv="http://schemas.xmlsoap.org/soap/envelope/", 
                ns1="http://myzoo.com" ');

CREATE NICKNAME zooport_getzooreport_report_zookeeper_nn ( 
  nn_fk VARCHAR (16) NOT NULL 
      OPTIONS(FOREIGN_KEY 'ZOOPORT_GETZOOREPORT_NN'),
  zookeeper_id VARCHAR (48) OPTIONS(XPATH './@id'),
  report_zookeeper_name VARCHAR (48) OPTIONS(XPATH './ns1:Name/text()'),
  zookeeper_numbercages VARCHAR(48) 
       OPTIONS(XPATH './ns1:NumberOfCages/text()'),
  nn_pk VARCHAR (16) NOT NULL OPTIONS(PRIMARY_KEY 'YES')
 ) 
FOR SERVER "MYTESTSRVR" OPTIONS(
  XPATH './ns1:Zoo/ns1:Zookeeper' , 
  NAMESPACES ' soapenv="http://schemas.xmlsoap.org/soap/envelope/", 
       ns1="http://myzoo.com" ');

The following SQL statement joins the parent and child nicknames:

SELECT * 
FROM   zooport_getzooreport_nn a, 
       zooport_getzooreport_report_zookeeper_nn z, 
	  
WHERE   a.nn_pk  = z.nn_fk
    AND a.zooid  = 100
    ;