Replication Guide and Reference

Target Tables

Table 41. Target Tables

Table name Internal name Description
Base aggregate target table userid.target_table This table is a target table that contains data aggregated from a source table.
Change aggregate target table userid.target_table This table is a target table that contains data aggregations based on changes from a source table.
Point-in-time target table userid.target_table This table indicates a specific commit time from the source server.
Replica userid.target_table This table contains a primary key identical to the primary key of the user table.
User copy target table userid.target_table This table is identical to a point-in-time table except that the IBMSNAP_LOGMARKER column is not included here.

Table name	Internal name	Description
Base aggregate target table	userid.target_table	This table is a target table that contains data aggregated from a source table.
Change aggregate target table	userid.target_table	This table is a target table that contains data aggregations based on changes from a source table.
Point-in-time target table	userid.target_table	This table indicates a specific commit time from the source server.
Replica	userid.target_table	This table contains a primary key identical to the primary key of the user table.
User copy target table	userid.target_table	This table is identical to a point-in-time table except that the IBMSNAP_LOGMARKER column is not included here.

+---------------------General-use programming interface----------------------+

Base Aggregate Target Table

userid.target_table

Base aggregate tables are target tables that contain data aggregated from a source table.

For base aggregate tables:

A descending unique index is suggested for IBMSNAP_LLOGMARKER to support query access.
Before-image user data columns must be nullable.
Null attributes of the after-image user data columns should match null attributes of the source, except for primary key columns, which should always be NOT NULL.

Table 42. Base Aggregate Target Table Columns

Column name Description
<user columns> Columns computed from the base table.
IBMSNAP_LLOGMARKER The current timestamp at the source server at the time of refresh.
IBMSNAP_HLOGMARKER The current timestamp at the source server at the time of refresh.

Column name	Description
<user columns>	Columns computed from the base table.
IBMSNAP_LLOGMARKER	The current timestamp at the source server at the time of refresh.
IBMSNAP_HLOGMARKER	The current timestamp at the source server at the time of refresh.

Figure 49. CREATE TABLE Statement

CREATE TABLE userid.BA_COPY( IBMSNAP_LLOGMARKER TIMESTAMP, IBMSNAP_HLOGMARKER TIMESTAMP, SUM_COL1 CHAR(1), SUM_COL3 CHAR(1)); CREATE INDEX BAyymmddhhmmssIX ON ba_copy (IBMSNAP_LLOGMARKER DESC);

+------------------End of General-use programming interface------------------+

+---------------------General-use programming interface----------------------+

Change Aggregate Target Table

userid.target_table

A change aggregate table is a target table that contains data aggregations based on changes from a source table.

For change aggregate tables:

A non-unique descending index is optional on IBMSNAP_LLOGMARKER.
Before-image user data columns must be nullable.
Computed user data columns should be NOT NULL, if your design can determine that the computation will never generate a null value, for example in a concatenation scenario.
Because CCD tables can contain duplicate rows, the DISTINCT clause should be used when a CCD table is the replication source table of a change aggregate target table.

Table 43. Change Aggregate Target Table Columns

Column name Description
<User columns, including computed columns> These columns are computed from change data related to the base table.
IBMSNAP_LLOGMARKER The oldest (lowest) IBMSNAP_LOGMARKER or (IBMSNAP_LLOGMARKER) value in the CD or CCD table rows being aggregated.
IBMSNAP_HLOGMARKER The most recent (highest) IBMSNAP_LOGMARKER or (IBMSNAP_LLOGMARKER) value in the CD or CCD table rows being aggregated.

Note: Since CCD tables may contain duplicates, use the DISTINCT clause when sourcing a change aggregate from a CCD table.

Column name	Description
<User columns, including computed columns>	These columns are computed from change data related to the base table.
IBMSNAP_LLOGMARKER	The oldest (lowest) IBMSNAP_LOGMARKER or (IBMSNAP_LLOGMARKER) value in the CD or CCD table rows being aggregated.
IBMSNAP_HLOGMARKER	The most recent (highest) IBMSNAP_LOGMARKER or (IBMSNAP_LLOGMARKER) value in the CD or CCD table rows being aggregated.

Figure 50. CREATE TABLE Statement

CREATE TABLE userid.CA_COPY( IBMSNAP_LLOGMARKER TIMESTAMP NOT NULL, IBMSNAP_HLOGMARKER TIMESTAMP NOT NULL); SUM_COL1 CHAR(1), SUM_COL3 CHAR(1) ), CREATE INDEX CAyymmddhhmmssIX ON ca_copy (IBMSNAP_LLOGMARKER DESC);

+------------------End of General-use programming interface------------------+

+---------------------General-use programming interface----------------------+

Consistent Change Data Table

userid.target_table

CCD tables contain committed change data.

The CCD table can optionally exist as:

A table of data.
A staging table maintained by one Apply program.
The result of a join between the CD table and the UOW table can be stored here, so that you perform the join step once only for "fan-out" copying.
A secondary staging table.
An external source table for nonrelational and multivendor data.
This allows IBM Replication to act as a "loading dock" and deliver transaction-consistent data from nonrelational sources.

For CCD tables:

A non-unique ascending index on IBMSNAP_COMMITSEQ is created. This index improves the performance of updating CCD table copies.
If the CCD table is condensed (CCD_CONDENSED = Y), a unique index is required for user data primary key columns to maintain the CCD table. CCD copies with TARGET_CONDENSED = N may contain duplicates as a result of reapplied changes following a failure of the Apply program.
Before-image user data columns must be nullable and therefore cannot be part of a primary key for a condensed CCD table.
An external CCD table is an alternate source for the original user table. The user table does not include computed columns; therefore, computed columns should not be included in the CCD subscriptions.
Null attributes of the after-image user data columns should match the null attributes of the source.
Views of change data tables can be included in view replication sources.
If an external program, other than the Apply program, maintains the external CCD table, the external program must initialize, maintain, and supply the correct values for the control columns.
Non-condensed CCD tables can contain duplicate rows as a result of changes reapplied after a failure during the previous copy operation.

Table 44 provides a list and a brief description of each of the CCD table columns.

Table 44. CCD Table Columns

Column name Description
IBMSNAP_INTENTSEQ Unique identifier for this change; it describes the sequence of a change within a transaction.
IBMSNAP_OPERATION Character value of I, U, or D, indicating an insert, update, or delete record.
IBMSNAP_COMMITSEQ The transaction commit sequencing value.
IBMSNAP_LOGMARKER The approximate commit time at the source server.
<user data columns> Columns from source tables specified while defining replication sources.

Column name	Description
IBMSNAP_INTENTSEQ	Unique identifier for this change; it describes the sequence of a change within a transaction.
IBMSNAP_OPERATION	Character value of I, U, or D, indicating an insert, update, or delete record.
IBMSNAP_COMMITSEQ	The transaction commit sequencing value.
IBMSNAP_LOGMARKER	The approximate commit time at the source server.
<user data columns>	Columns from source tables specified while defining replication sources.

Figure 51 shows the index for CCD tables.

Figure 51. CREATE TABLE Statement

CREATE TABLE userid.copy_table ( IBMSNAP_INTENTSEQ CHAR(10) FOR BIT DATA NOT NULL, IBMSNAP_OPERATION CHAR(1) NOT NULL, IBMSNAP_COMMITSEQ CHAR(10) FOR BIT DATA NOT NULL, IBMSNAP_LOGMARKER TIMESTAMP NOT NULL, KEY1 CHAR(1), DATA1 CHAR(1)) CREATE INDEX Zcopy_table ON copy_table(IBMSNAP_COMMITSEQ);

+------------------End of General-use programming interface------------------+

+---------------------General-use programming interface----------------------+

Point-in-Time Target Table

userid.target_table

This table indicates a specific commit time from the source server.

For point-in-time tables:

A unique index is automatically created and required for the primary key.
Before-image user data columns must be nullable.
Null attributes of the after-image user data columns should match null attributes of the source, except for primary key columns, which should always be NOT NULL.

Table 45. Point-in-Time Target Table Columns

Column name Description
<user key columns> The primary key of the target table, although it is not necessarily a component of the primary key of the base table. You can use predicates to prevent a null value from being assigned to the key fields of any copies.
<user nonkey columns> The nonkey data column from the base table.
IBMSNAP_LOGMARKER The approximate commit time at the source server. This column is null following a full refresh.

Column name	Description
<user key columns>	The primary key of the target table, although it is not necessarily a component of the primary key of the base table. You can use predicates to prevent a null value from being assigned to the key fields of any copies.
<user nonkey columns>	The nonkey data column from the base table.
IBMSNAP_LOGMARKER	The approximate commit time at the source server. This column is null following a full refresh.

Figure 52. CREATE TABLE Statement

CREATE TABLE PIT_COPY( COL1 CHAR(1) NOT NULL, COL3 CHAR(1), IBMSNAP_LOGMARKER TIMESTAMP, PRIMARY KEY(COL1) ); CREATE UNIQUE INDEX PITyymmddhhmmssIX ON pit_copy (COL1);

Note: The primary key requires a unique index.

+------------------End of General-use programming interface------------------+

+---------------------General-use programming interface----------------------+

Replica Target Table

userid.target_table

This table contains a primary key identical to the primary key of the user table.

Table 46. Replica Target Table Columns

Column name Description
<user key columns> The primary key of the target table, although it is not necessarily a component of the primary key of the base table. You can use predicates to prevent a null value from being assigned to the key fields of any copies.
<user nonkey columns> The nonkey data column from the base table.

Note: The replica must have the same primary key as the source table.

Column name	Description
<user key columns>	The primary key of the target table, although it is not necessarily a component of the primary key of the base table. You can use predicates to prevent a null value from being assigned to the key fields of any copies.
<user nonkey columns>	The nonkey data column from the base table.

Figure 53. CREATE TABLE Statement

CREATE TABLE REPLICA_COPY( COL1 CHAR(1) NOT NULL, COL3 CHAR(1), PRIMARY KEY(COL1) ); CREATE UNIQUE INDEX REPLICAyymmddhhmmssIX ON replica (COL1);

+------------------End of General-use programming interface------------------+

+---------------------General-use programming interface----------------------+

User Copy Target Table

userid.target_table

This table indicates a specific commit time from the source server.

For user copy target tables:

A unique index is automatically created and required for the primary key.
Before-image user data columns must be nullable.
Null attributes of the after-image user data columns should match null attributes of the source, except for primary key columns, which should always be NOT NULL.

Table 47. User Copy Target Table Columns

Column name Description
<user key columns> The primary key of the target table, although it is not necessarily a component of the primary key of the base table. You can use predicates to prevent a null value from being assigned to the key fields of any copies.
<user nonkey columns> The nonkey data column from the base table.

Column name	Description
<user key columns>	The primary key of the target table, although it is not necessarily a component of the primary key of the base table. You can use predicates to prevent a null value from being assigned to the key fields of any copies.
<user nonkey columns>	The nonkey data column from the base table.

Figure 54. CREATE TABLE Statement

CREATE TABLE USER_COPY_TARGET( COL1 CHAR(1) NOT NULL, COL3 CHAR(1), PRIMARY KEY(COL1) ); CREATE UNIQUE INDEX USER_COPY_TARGETyymmddhhmmssIX ON user_copy_target (COL1);

+------------------End of General-use programming interface------------------+

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]