General data restrictions for SQL Replication
SQL Replication has specific restrictions for certain data types including data encryption restrictions and data type restrictions.
- Data encryption restrictions
- SQL Replication can replicate some types of encrypted data.
- SQL Replication supports Db2® for z/OS® source tables that are defined with an edit routine (EDITPROC) to provide additional data security. To use these tables as sources for replication, the Db2 subsystem that contains the tables must be at Version 8 or higher with APAR PK13542 or higher.
- Encrypt scalar function in Db2 for Linux®, UNIX, and Windows
- Column data can be encrypted and decrypted by using the encrypt scalar function in Db2 for Linux, UNIX, and Windows. To use this with replication, the data type must be VARCHAR FOR BIT DATA at the source. This data replicates successfully as long as the source and target use the same code page and the decrypt functions are available. Replication of columns with encrypted data should only be used with servers that support the DECRYPT_BIN or DECRYPT_CHAR function.
- FIELDPROC (z/OS)
- SQL Replication supports columns that are defined on Db2
for z/OS tables with field procedures (FIELDPROC) to
transform values. The Db2 subsystem that contains the tables
with FIELDPROC columns must be at APAR PK75340 or higher.
If possible, you should create the following index on your SYSIBM.SYSFIELDS table to improve performance:
CREATE INDEX "SYSIBM"."FIELDSX" ON "SYSIBM"."SYSFIELDS" (TBCREATOR ASC, TBNAME ASC, NAME ASC) USING STOGROUP SYSDEFLT PRIQTY 100 SECQTY 100 CLOSE NO; COMMIT;
- Data type restrictions
- SQL Replication cannot replicate the following data types:
- LOB columns from non-Db2 relational sources
- You can replicate BINARY or VARBINARY data types when the source and target are on z/OS. Replication of these data types from a z/OS source to a Db2 for Linux, UNIX, and Windows target or federated target is not supported. BINARY and VARBINARY data types are supported as targets of source expressions only if the source datatype is CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, or ROWID.
- SQL Replication can replicate the following data types under certain
- Long variable graphic (LONG VARGRAPHIC) data if the source and target tables reside in Db2 for z/OS.
- Long variable character (LONG VARCHAR and LONG VARGRAPHIC) data requires either that the source database tables be in Db2 for z/OS or both the source and target tables be in Db2 for Linux, UNIX, and Windows. When you specify DATA CAPTURE CHANGES for a source table when the table is created, any LONG VARCHAR and LONG VARGRAPHIC columns are automatically enabled for replication. If you add LONG VARCHAR columns to the table after it is registered as a source and the table previously had no LONG columns, you must use the ALTER TABLE statement to enable DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS for the new LONG VARCHAR or LONG VARGRAPHIC columns.
- SQL Replication cannot replicate a table that contains abstract data types.
- SQL Replication can replicate tables with spatial data type columns but cannot replicate the actual spatial data type columns.
- User-defined data types (distinct data types in Db2) are converted to the base data type in the change-data (CD) table before replication. In addition, if SQL Replication creates the target table as part of the subscription-set member definition, user-defined types are converted to the base data type in the target table as well as in the CD table.