Q replication filtering and transformations

Let's first look at what is possible when it comes to filtering rows and columns, and then move on to look at transformations.

Filtering rows/columns

Let's first look at row filtering. It is only possible to filter rows for replication in a unidirectional scenario, and this is done in the Q subscription. For an example, see the Creating a Q subscription section of Chapter 6.

What about the number of columns we want to replicate—can we replicate just a subset of the source table columns? For the latest release of code, we can subset the columns to be replicated. Note that we cannot replicate more columns than are defined at the target table or target stored procedure and that the column names must still match, which is shown in the following diagram:

For unidirectional replication only, the target table can have more columns than the source table as shown in the following diagram, but these "non-source" columns cannot be part of the target table key and must be defined as NULLABLE or NOT NULL WITH DEFAULT, as shown next.

Any filtering of rows or columns in unidirectional replication is specified at Q subscription definition time. At this time, we can specify:

• Which columns to replicate and how they map to columns at the target table (or to parameters in a stored procedure)

• A search condition to determine which rows from the source table are replicated

As stated at the beginning of this chapter, Q replication is built for speed with transformations not being a major factor. However, although Q replication does not have the transformation capabilities of SQL Replication, it does have some transformation capabilities, which are described in the following sections.

Before and After SQL—alternatives

In Q replication, there is no concept of before and after SQL, as there is in SQL Replication. In a unidirectional setup, we can use SQL expressions to transform data between the source and target tables. We can map multiple source columns to a single target column, or to create other types of computed columns at the target. An example is shown in the Q subscription for unidirectional replication section of Chapter 5 .

Stored procedure processing

If we want to perform transformations with Q replication, then we need to use stored procedure processing. This allows us to call external routines to perform all the transformations we want. The Replication to a stored procedure section of Appendix A shows an example of how to set up Q replication to a stored procedure.

We now move on to look at conflict detection in a Q replication environment.