DbSequence
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, 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.
Commands hvrcompare and hvrrefresh also affect any database sequences matched by action DbSequence, unless option –Q is defined. Database sequences which are only in the 'write' database are ignored.
- This action cannot be used with parameter Burst in action Integrate.
- 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.
Parameters
This section describes the parameters available for action DbSequence.
Following are the two tabs/ways, which you can use for defining action parameters in this dialog:
- Regular: Allows you to define the required parameters by using the UI elements like checkbox and text field.
- Text: Allows you to define the required parameters by specifying them in the text field. You can also copy-paste the action definitions from Fivetran HVR documentation, emails, or demo notes.
| Parameter | Argument | Description |
|---|---|---|
| CaptureOnly | Only capture database sequences, do not integrate them. | |
| IntegrateOnly | Only integrate database sequences, do not capture them. | |
| Name | seq_name | Name of database sequence. Only capture or integrate database sequence named seq_name. By default, this action affects all sequences. |
| Schema | db_schema | Schema which owns database sequence. By default, this action only affects sequences owned by the current user name. |
| BaseName | seq_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 (parameter Name) or for all sequences in a schema (parameter 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 parameter Schema but not Name. Instead either define lots of DbSequence actions with both parameters Schema and Name or use multiple capture locations or channels, each with its own action DbSequence defined with parameter Schema.
Bidirectional Replication
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.