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
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';
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"));