Db2

 View Only
  • 1.  Federated_tool-maintained MQTs or cache tables (MAINTAINED BY FEDERATED_TOOL)

    IBM Champion
    Posted Mon March 20, 2023 09:29 AM

    I'm trying to set up some MQTs for a customer to cache data from Federated sources using the FEDERATED_TOOL option. I can get the syntax right but I need some help getting the thing working. The knowledge centre has some info but not much ( Creating sample cache tables

    Ibm remove preview
    Creating sample cache tables
    You can use a sample to set up distributed caching.
    View this on Ibm >

      and there is a link to what looks like a handy example (  DB2cacheTables.zip sample. ) however, if you follow that link it takes you to IBM Community home page and no sign of the advertised zip file. I don't suppose anyone has a copy stashed away somewhere do they?

    Regards

    Mark Gillis



    ------------------------------
    Mark Gillis
    ------------------------------


  • 2.  RE: Federated_tool-maintained MQTs or cache tables (MAINTAINED BY FEDERATED_TOOL)

    Posted Tue March 21, 2023 09:30 AM
    Not 100% sure where you are stuck, I've done it like this in the past for a federated Db2 database

    CREATE WRAPPER DRDA
            OPTIONS( DB2_FENCED 'N') @

    CREATE SERVER ${federated} TYPE DB2/UDB VERSION 9
            WRAPPER DRDA AUTHORIZATION "${user}" PASSWORD "${password}"
            OPTIONS( DBNAME '${federated}', PASSWORD 'Y') @

    CREATE USER MAPPING FOR "${user}" SERVER ${federated}
            OPTIONS ( REMOTE_AUTHID '${user}', REMOTE_PASSWORD '${password}') @

    CREATE NICKNAME TEST.T
            FOR ${federated}.TEST.T @

    Now it should be possible to create an MQT like:

    db2 "create table test.test_mqt as ( select x, count(1) cnt from TEST.T group by x ) DATA INITIALLY DEFERRED REFRESH DEFERRED"

    db2 "refresh table test.test_mqt"

    db2 "select count(*) from test.test_mqt"

    1          
    -----------
             37

      1 record(s) selected.






  • 3.  RE: Federated_tool-maintained MQTs or cache tables (MAINTAINED BY FEDERATED_TOOL)

    Posted Tue March 21, 2023 09:35 AM
    worth noting, you can't create an REFRESH immediate MQT for a nickname

    SQL20058N  The statement failed because the fullselect specified for the
          materialized query table "<table-name>" violates a restriction.
          Reason code = "<reason-code>".





  • 4.  RE: Federated_tool-maintained MQTs or cache tables (MAINTAINED BY FEDERATED_TOOL)

    IBM Champion
    Posted Thu March 23, 2023 10:26 AM

    Thanks for that Lennart.

    The problem is that I'm trying to use the FEDERATED_TOOL option of the MQT, to enable local data caching and take some of the load off the remote server referenced by the Nickname. My DDL is 

    Create Summary Table RT_SCHED.CARRIER_CODING_MQT 
    AS (SELECT * FROM WIP.CARRIER_CODING) 
    Data Initially DEFERRED
    REFRESH DEFERRED
    maintained BY FEDERATED_TOOL
    Enable Query Optimization

    and syntactically that is correct (i.e. the object is created). But I cannot get it populated. REFRESH doesn't work: "-- The REFRESH TABLE statement, which is used for system-maintained materialized query tables, cannot be invoked against federated_tool-maintained materialized query tables" and neither does SET INTEGRITY.



    ------------------------------
    Mark Gillis
    ------------------------------



  • 5.  RE: Federated_tool-maintained MQTs or cache tables (MAINTAINED BY FEDERATED_TOOL)

    Posted Sat March 25, 2023 03:46 AM
    I have not tried myself, but is not the replication supposed to do the refresh? I.e. is your replication transferring any data to the target? I can't find any examples but in: https://www.ibm.com/docs/en/db2/11.5?topic=statements-create-table#sdx-synid_federated_tool it says:

    "FEDERATED_TOOL
    Specifies that the data in the materialized query table is maintained by a federated replication tool. The REFRESH TABLE statement, which is used for system-maintained materialized query tables, cannot be invoked against federated_tool-maintained materialized query tables. Only a REFRESH DEFERRED materialized query table can be defined as MAINTAINED BY FEDERATED_TOOL.
    When specifying this option, the select clause in the CREATE TABLE statement cannot contain a reference to a base table (SQLSTATE 428EC)."

    which indicates that you should not have to refresh the table manually





  • 6.  RE: Federated_tool-maintained MQTs or cache tables (MAINTAINED BY FEDERATED_TOOL)

    Posted Fri March 24, 2023 09:02 AM
    Edited by Michael Klemmer Fri March 24, 2023 09:07 AM

    Hi Mark, try this:

    DB2cacheTables.zip | Powered by Box


    ------------------------------
    Michael Klemmer
    ------------------------------