Precomputed Result Sets

Replication Server supports the replication of precomputed result sets DDL commands between primary and replicate Adaptive Server databases that support precomputed result sets.

Adaptive Server 15.7 ESD #2 and later supports precomputed result sets. See Precomputed Result Sets in the Adaptive Server Enterprise New Features Guide version 15.7 ESD #2.

In Adaptive Server, you can create a precomputed result set on any query expression, together with a set of policies to maintain the preocomputed result set. Replication Server supports the replication of precomputed result set data definition language (DDL) commands between Adaptive Server databases in all environments, including multisite availability (MSA) and warm standby environments. However, you cannot mark precomputed result sets for replication, and the update, delete, insert, and select into data manipulation language (DML) commands cannot directly update the precomputed results set. This is because unlike a regular table, Replication Server does not replicate any maintenance changes occurring on data stored in a precomputed result set, whether the changes are:

Initiated from a precomputed result set DDL, although the DDL can also be replicated, or
From part of the base table update transaction initiated from the immediate refresh policy.

Replication Server only supports the replication of the Adaptive Server precomputed result set DDL commands:

alter {precomputed result set | materialized view}
create {precomputed result set | materialized view}
drop {precomputed result set | materialized view}
refresh {precomputed result set | materialized view}
truncate {precomputed result set | materialized view}

Precomputed result sets are also called materialized views. See Commands in the Adaptive Server Reference Manual: Commands.

Adaptive Server requires four session-level set options to have the relevant settings before you can create and use precomputed result sets:

set ansinull on
set arithabort on
set arithignore off
set string_rtruncation on

To replicate the precomputed result set DDL commands, Replication Server includes the rs_ddlsession_setting system function to automatically set the Adaptive Server session-level options whenever the DSI thread processes these DDL commands, and therefore allow Replication Server to apply the commands to the replicate Adaptive Server database. To avoid affecting DML replication, Replication Server provides the rs_ddlsession_resetting system function to automatically reset the session-level options to their default values after processing the precomputed result set DDL commands:

set ansinull off
set arithabort on
set arithignore off
set string_rtruncation off

Remember: You do not have to execute the functions manually.

Tip: The rs_ddlsession_resetting function resets the options to the default values overwriting any values you previously set. You can create customized a function string for rs_ddlsession_resetting to ensure that the values revert to what you previously set.

See the descriptions of rs_ddlsession_setting and rs_ddlsession_resetting in the Replication Server Reference Manual.

Earlier versions of Replication Server that do not include the two functions can replicate precomputed result set DDL commands as long as both primary and replicate Adaptive Server databases support precomputed result sets and as long you can apply the relevant settings for the Adaptive Server session-level set options. To ensure that precomputed result set DDL commands can replicate even if your version of Replication Server version does not include the system functions, you can create a procedure in the default database that automatically configures the relevant set options when the original user for the replicate database logs into the database.