Action DbSequence allows database sequences to be replicated.
If a single DbSequence action is defined without any parameters for the entire channel (i.e. location group '*') then operations on all database sequences in the capture location(s) which are owned by the current schema will be replicated to all integrate locations. This means that if a nextval is done on the capture database then after replication a nextval on the target database is guaranteed to return a higher number. Note that however that if database sequence 'caching' is enabled in the DBMS, then this nextval on the target database could display a 'jump'.
SQL statement create sequence is also replicated, but drop sequence is not replicated.
- This action cannot be used with Integrate /Burst.
- This action is only supported for certain DBMSs, for more information see Replicate database sequences (using action DbSequence) in Capabilities.
- Capture of database sequence requires log–based capture (Capture).
- For an Oracle RAC cluster, sequences should be defined with parameter ORDER (default is NOORDER), unless the next values are only generated on one node.
This section describes the parameters available for action DbSequence.
Only capture database sequences, do not integrate them.
Only integrate database sequences, do not capture them.
Name of database sequence. Only capture or integrate database sequence named seq_name. By default, this action affects all sequences.
Schema which owns database sequence. By default, this action only affects sequences owned by the current user name.
Name of sequence in database, if this differs from the name used in HVR. This allows a single channel to capture multiple database sequences that have the same sequence name but different owners.
Replication can be defined for a specific sequence (/Name) or for all sequences in a schema (/Schema without /Name) or for all sequences owned by the current user (neither /Name nor /Schema).
To capture all sequences from multiple schemas it is not allowed to just define multiple DbSequence actions with /Schema but not /Name. Instead either define lots of DbSequence actions with both /Schema and /Name or use multiple capture locations or channels, each with its own DbSequence /Schema action.
Bidirectional replication of sequences causes problems because the sequence change will 'boomerang back'. This means that after the integrate job has changed the sequence, the HVR capture job will detect this change and send it back to the capture location. These boomerangs make it impossible to run capture and integrate jobs simultaneously. But it is possible to do bidirectional replication for a failover system; i.e. when replication is normally only running from A to B, but after a failover the replication will switch to run from B to A. Immediately after the switchover a single boomerang will be sent from B to A, but afterwards the system will consistent and stable again.
If bidirectional replication is defined, then HVR Refresh of database sequences will also cause a single 'boomerang' to be captured by the target database's capture job.
Session names cannot be used to control bidirectional replication of database sequences in the way that they work for changes to tables. For more information, see Managing Recapturing Using Session Names.
Replication of Sequence Attributes
Database sequence 'attributes' (such as minimum, maximum, increment, randomness and cycling) are not replicated by HVR. When HVR has to create a sequence, it uses default attributes and only the value is set accurately. This means that if a database sequence has non–default attributes, then sequence must be manually created (outside of HVR) on the target database with the same attributes as on the capture database. But once these attributes are set correctly, then HVR will preserve these attributes while replicating the nextval operations.