Use Double Buffering to Refresh Highly Available Aggregates
When you've aggregate clones across multiple aggregate persistence targets, you can use double buffering to avoid downtime when refreshing the aggregates.
You manually call the aggregate create and delete SQL statements in a way that controls the refresh to set up double buffering.
You start by deleting aggregates on the first target. Next, you create the aggregates on the first target, specifying the targets where aggregates haven't yet been deleted as inactive schemas, so that the old data isn't used in the refresh. Then, you repeat this process for each target. You don't need to specify inactive schemas when refreshing the last target because by that point, the data in the other schemas has already been refreshed.
When specifying 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 haven't yet been refreshed as inactive schemas. Don't specify a schema that has already been refreshed or that you've just deleted.
When specifying multiple inactive schemas, use a comma-separated list. Make sure there are no spaces in the list.
The Refreshing Aggregate Clones on Two Targets example show to use double buffering to refresh aggregates on two targets. When you've 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 aren't used for load balancing the incoming queries.
Refreshing Aggregate Clones on Two Targets
Assume that you've 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";
-
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 isn't 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, don't 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";