2.128 INSERTALLRECORDS | NOINSERTALLRECORDS

Valid for

Replicat

Description

Use INSERTALLRECORDS to apply all record types as inserts in the target. Normally, Replicat applies inserts, updates and deletes to the target database as they occur on the original database. Consider the following sequence of transactions:

Sequence	Operation	Table	ID	BALANCE
1	INSERT	CUSTOMER	DAVE	1000
2	UPDATE	CUSTOMER	DAVE	900
3	UPDATE	CUSTOMER	DAVE	1250
4	DELETE	CUSTOMER	DAVE	1250

These operations, after replication, would leave no trace of the ID DAVE. No transaction information would be kept, only the ending balance. Therefore, we would have no knowledge that the first update reduced BALANCE by 100, or that the second update increased BALANCE by 350. Finally, we would have no idea that DAVE was ever deleted from the database, or what his ending BALANCE was.

INSERTALLRECORDS allows this information to be recorded. Instead of applying updates and deletes as they originally occurred, INSERTALLRECORDS forces Replicat to insert the information as a new record into the target table. INSERTALLRECORDS results in the storage of all images—before and after—into the target database.

Combining this information with special transaction information provides a way to create a database that contains more useful information. You can add special column values related to each transaction to the target data to make better reporting possible.

Using INSERTALLRECORDS increases the size of your target tables, so you should only enable it where complete records are required. INSERTALLRECORDS applies to all tables listed below it in the parameter file, until you turn it off again by specifying NOINSERTALLRECORDS. INSERTALLRECORDS can also be limited to a specific file or table by using it as an option under the MAP parameter.

Default

NOINSERTALLRECORDS

Syntax

INSERTALLRECORDS | NOINSERTALLRECORDS

Example

Example 1   

To build a more transaction-oriented view of customers, rather than the latest state of the database, enter the following into the parameter file:

INSERTALLRECORDS
MAP =CUSTOMER, TARGET =CUSTHIST,
COLMAP (USEDEFAULTS,
TS = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),
BEF_AFT = @GETENV ("GGHEADER", "BEFOREAFTERINDICATOR"),
OP_TYPE = @GETENV ("GGHEADER", "OPTYPE"),
ID = ID,
BALANCE = BALANCE);

This generates the net effect of each transaction, as in the following SQL query that returns the net sum of each transaction along with the time of the transaction and the customer ID.

SELECT A.ID, A.TS, A.BALANCE - B.BALANCE
FROM CUSTHIST A, CUSTHIST B
WHERE A.ID = B.ID AND A.TS = B.TS AND
A.OP_TYPE = 'A' AND B.OP_TYPE = 'B';

Example 2   

The following example applies all record types as inserts only for the $DATA3.TARGET.HISTORD order history file.

MAP $DATA.SOURCE.CUSTORD TARGET $DATA3.TARGET.HISTORD
INSERTALLRECORDS
COLMAP (USEDEFAULTS,
TRAN_TIME = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),
BEF_AFT = @GETENV ("GGHEADER", "BEFOREAFTERINDICATOR"),
OP_TYPE = @GETENV ("GGHEADER", "OPTYPE"));