Does informix have a "materialized view" equivalent or DB-table syncing

Question

Question: Does Informix have a construct equivalent to Oracle's "materialized view" or is there a better way to synchronize two tables (not DB's) accross a DB link? I could write a sync myself (was asked to) but that seems like re-inventing the wheel.

Background: Recently we had to split (one part of DB one one server, the other part on the other server) a monolithic Informix 9.30 DB (Valent's MPM) since the combination of AppServer and DB server couldn't handle the load anymore.

In doing this we had to split a user defined table space (KPI Repository) aranged in a star shema of huge fact tables and well defined dimension tables.

Unfortunately a telco manager decided to centralize the dimension tables (Normalization, no data redundancy, no coding needed) on one machine and thus make them available as views over a DB-link on the other machine. This is both slow and unstable, as it every now and then crashes the DB server if the view is used in sub-queries (demonstrable), very uncool on a producton server

Answer 1

I may be getting your requirements but could you not just use enterprise replication to replicate the single table across the DB's?

Answer 2

IDS 9.30 is archaic (four main releases off current). Ideally, it should not still be in service; you should be planning to upgrade to IDS 11.50.

As MrWiggles states, you should be looking at Enterprise Replication (ER); it allows you to control which tables are replicated. ER allows update-anywhere topologies; that is, if you have 2 systems, you can configure ER so that changes on either system are replicated to the other.

Note that IDS 9.40 and 10.00 both introduced a lot of features to make ER much simpler to manage - more reasons (if the fact that IDS 9.30 is out of support is not sufficient) to upgrade.

(IDS does not have MQT - materialized query tables.)