System-maintenance MQT created based on nicknames takes a long time to referesh

Question

You configured system-maintained MQT based on nicknames, when trying to retrieve latest data from remote data source, you found the elapsed time was very long. Why does it take so long when refreshing system-maintained MQT which is created on nicknames?

Cause

System-maintained MQT which is created on nicknames uses full refreshment.

Answer

From refreshing scale, there are 2 ways for system-maintained MQT. one is full refreshment, the other is incremental refreshment.

1) MQT defined with REFRESH IMMEDIATE option only uses incremental refreshment. REFRESH TABLE command actually doesn't do any operation on MQT. 
2) MQT defined with REFRESH DEFERRED option normally uses full refreshment, but not always, there is an exception. If you defined staging table for MQT, incremental refreshment will be used.

Only REFRESH DEFERRED MQT can be created on nickname, but staging table doesn't support nickname, that's why this kind of MQT uses full refreshment. If you want to use incremental refreshment, cache table is a choice.

닉네임에 대해서는 MQT(materialized query table)작성은 가능하지만 MQT에 대한스테이징 테이블(변경된 데이터만 저장한 테이블)은 작성할 수 없음. 대안인 캐시 테이블도 테이블 복제나 매한가지임, Capture and Apply. OTL

https://www-304.ibm.com/support/docview.wss?uid=swg21514881

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.swg.im.iis.fed.tuning.doc/topics/iiyvfed_tuning_cachetbls.html