Database replication definition

To replicate SQL statements in an MSA environment, you must include the replicate SQLDML clause with the create replication definition or alter replication definition commands.

This code segment displays the syntax for create and alter database replication definitions:

[[not] replicate setname [in (table list)] ]

where:

setname = DDL | tables | functions | transactions | system procedures | SQLDML | ‘options’.

The ‘options’ parameter is a combination of:

• U – update

• D – delete

• I – insert select

• S – select into

The SQLDML parameter is also defined as a combination of U, D, I, and, S statements.

This example shows how to use the ‘options’ parameter to replicate SQLDML on tables tb1 and tb2:

replicate 'UDIS' in (tb1,tb2)

This example shows how to use the SQLDML parameter that produces the same result as the ‘options’ parameter in the previous example:

replicate SQLDML in (tb1,tb2)

You can use multiple replicate clauses in a create database replication definition. However, for an alter database replication definition, you can use only one clause.

If you do not specify a filter in your replication definition, the default is the not replicate clause. Apply alter database replication definition to change the SQLDML filters. You can either specify one or multiple SQLDML filters in a replicate clause.

This example shows how to filter out the select into statement for all tables. The second clause, not replicate 'U' in (T), filters out updates on table T:

create database replication definition dbrepdef
       with primary at ds1.pdb1
       not replicate ‘S’
       not replicate ‘U’ in (T)
go

This example enables update and delete statements on all tables using the replicate 'UD' clause:

create database replication definition dbrepdef_UD
       with primary at ds2.pdb1
       replicate 'UD'
go

You can use multiple clauses to specify a table multiple times in the same definition. However, you can use each of U, D, I, and S only once per definition.

create database replication definition dbrepdef
       with primary at ds2.pdb1
       replicate tables in (tb1,tb2)
       replicate 'U' in (tb1)
       replicate 'I' in (tb1,tb2)
go

This example applies update and delete statements for tables tb1 and tb2:

alter database replication definition dbrepdef
      with primary at ds1.pdb1
      replicate 'UD' in (tb1,tb2)
go