Requirements for redirecting queries
Posted: Mon Jan 27, 2025 9:17 am
In order for the DB2 optimizer to even consider transparently redirecting SELECT statements (query rerouting) to the mirror tables, further requirements must be met:
All tables addressed in the query must have a corresponding mirror table, i.e. mirror tables must also be defined for small, static lookup tables.
All columns referenced in the query must exist in the mirror tables.
The following system settings must be active at either the session or database level:
The intra-partition brazil telegram screening parallelism must be activated
For the current session: CALL ADMIN_SET_INTRA_PARALLEL('YES')
Generally in the DBM CFG: INTRA_PARALLEL YES
The degree of parallelism must be set to a value greater than 1. The value ANY lets the system choose the actual value
Session: SET CURRENT DEGREE 'ANY'
DB CFG: DFT_DEGREE ANY
The DB2 optimizer must be allowed to use mirror tables as a special type of Materialized Query Tables (MQT) for query optimization
Session: SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION = REPLICATION
DB CFG: DFT_MTTB_TYPES REPLICATION
Special register CURRENT REFRESH_AGE must be set to a value greater than zero or ANY.
All tables addressed in the query must have a corresponding mirror table, i.e. mirror tables must also be defined for small, static lookup tables.
All columns referenced in the query must exist in the mirror tables.
The following system settings must be active at either the session or database level:
The intra-partition brazil telegram screening parallelism must be activated
For the current session: CALL ADMIN_SET_INTRA_PARALLEL('YES')
Generally in the DBM CFG: INTRA_PARALLEL YES
The degree of parallelism must be set to a value greater than 1. The value ANY lets the system choose the actual value
Session: SET CURRENT DEGREE 'ANY'
DB CFG: DFT_DEGREE ANY
The DB2 optimizer must be allowed to use mirror tables as a special type of Materialized Query Tables (MQT) for query optimization
Session: SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION = REPLICATION
DB CFG: DFT_MTTB_TYPES REPLICATION
Special register CURRENT REFRESH_AGE must be set to a value greater than zero or ANY.