When you have aggregate clones across multiple aggregate persistence targets, you can use double buffering to avoid downtime when refreshing the aggregates.
To set up double buffering, you manually call the aggregate create and delete SQL statements in a way that controls the refresh.
You start by deleting aggregates on the first target. Next, you create the aggregates on the first target, specifying the targets where aggregates have not yet been deleted as inactive schemas, so that the old data is not used in the refresh. Then, you repeat this process for each target. Note that you do not need to specify inactive schemas when refreshing the last target because by that point, the data in the other schemas has already been refreshed.
To specify inactive schemas, set the request variable INACTIVE_SCHEMAS before the create aggregates statement. For example:
set variable INACTIVE_SCHEMAS='"tgt2".."double2"' :
Only specify schemas that have not yet been refreshed as inactive schemas. Do not specify a schema that has already been refreshed or that you have just deleted.
To specify multiple inactive schemas, use a comma-separated list. Make sure there are no spaces in the list.
The example illustrates how to use double buffering to refresh aggregates on two targets.
Note:
When you have aggregate clones across multiple aggregate persistence targets, the additional instances are hot-spares that take over the query load while the initial instance is being refreshed. The aggregate clones are not used for load balancing the incoming queries.
Example 13-1 Refreshing Aggregate Clones on Two Targets
Assume that you have the following aggregate clones on targets tgt1 and tgt2:
"myfactaggr"
for "FACT_1"("MEASURE_1")
at levels ("INCR"."DIM1_LEVEL1Dim"."DIM1_LEVEL1 Detail")
using connection pool "tgt1"."cp"
in "tgt1".."double1",
"myfactaggr"
for "FACT_1"("MEASURE_1")
at levels ("INCR"."DIM1_LEVEL1Dim"."DIM1_LEVEL1 Detail")
using connection pool "tgt2"."cp"
in "tgt2".."double2";
To make sure that at least one aggregate clone is available during the refresh, follow these steps:
Delete the aggregate clone for the first target:
set variable LOGLEVEL=7 : delete aggregates "tgt1".."double1"."myfactaggr";
Create the aggregate for the first target, making sure to specify the second target as an inactive schema so that the data is not used in the refresh:
set variable LOGLEVEL=7, INACTIVE_SCHEMAS='"tgt2".."double2"' : create aggregates
"myfactaggr"
for "FACT_1"("MEASURE_1")
at levels ("INCR"."DIM1_LEVEL1Dim"."DIM1_LEVEL1 Detail")
using connection pool "tgt1"."cp"
in "tgt1".."double1";
Delete the aggregate clone for the second target:
set variable LOGLEVEL=7 : delete aggregates "tgt2".."double2"."myfactaggr";
Create the aggregate for the second target. Because the first target has already been refreshed, do not specify any inactive schemas:
set variable LOGLEVEL=7 : create aggregates
"myfactaggr"
for "FACT_1"("MEASURE_1")
at levels ("INCR"."DIM1_LEVEL1Dim"."DIM1_LEVEL1 Detail")
using connection pool "tgt2"."cp"
in "tgt2".."double2";