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
------------------------------
Original Message:
Sent: Tue March 21, 2023 09:34 AM
From: Lennart Jonsson
Subject: Federated_tool-maintained MQTs or cache tables (MAINTAINED BY FEDERATED_TOOL)
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>".
Original Message:
Sent: 3/20/2023 9:29:00 AM
From: Mark Gillis
Subject: Federated_tool-maintained MQTs or cache tables (MAINTAINED BY FEDERATED_TOOL)
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
------------------------------