Using Transaction History

Oracle GoldenGate enables you to retain a history of changes made to a target record and to map information about the operation that caused each change. This history can be useful for creating a transaction-based reporting system that contains a separate record for every operation performed on a table, as opposed to containing only the most recent version of each record.

For example, the following series of operations made to a target table named CUSTOMER would leave no trace of the ID of Dave. The last operation deletes the record, so there is no way to find out Dave's account history or his ending balance.

Table 8-5 Operation History for Table CUSTOMER

Sequence	Operation	ID	BALANCE
`1`	`Insert`	`Dave`	`1000`
`2`	`Update`	`Dave`	`900`
`3`	`Update`	`Dave`	`1250`
`4`	`Delete`	`Dave`	`1250`

Retaining this history as a series of records can be useful in many ways. For example, you can generate the net effect of transactions.

To Implement Transaction Reporting

To prepare Extract to capture before values, use the GETUPDATEBEFORES parameter in the Extract parameter file. A before value (or before image) is the existing value of a column before an update is performed. Before images enable Oracle GoldenGate to create the transaction record.
To prepare Replicat to post all operations as inserts, use the INSERTALLRECORDS parameter in the Replicat parameter file. Each operation on a table becomes a new record in that table.
To map the transaction history, use the return values of the GGHEADER option of the @GETENV column conversion function. Include the conversion function as the source expression in a COLMAP statement in the TABLE or MAP parameter.

Using the sample series of transactions shown in **INTERNAL XREF ERROR** the following parameter configurations can be created to generate a more transaction-oriented view of customers, rather than the latest state of the database.

Process	Parameter statements
Extract	GETUPDATEBEFORES TABLE ACCOUNT.CUSTOMER;
Replicat	INSERTALLRECORDS MAP SALES.CUSTOMER, TARGET SALES.CUSTHIST, COLMAP (TS = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'), BEFORE_AFTER = @GETENV ('GGHEADER', 'BEFOREAFTERINDICATOR'), OP_TYPE = @GETENV ('GGHEADER', 'OPTYPE'), ID = ID, BALANCE = BALANCE);

Process

Parameter statements

Extract

GETUPDATEBEFORES
TABLE ACCOUNT.CUSTOMER;

Replicat

INSERTALLRECORDS
MAP SALES.CUSTOMER, TARGET SALES.CUSTHIST,
COLMAP (TS = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'),
BEFORE_AFTER = @GETENV ('GGHEADER', 'BEFOREAFTERINDICATOR'),
OP_TYPE = @GETENV ('GGHEADER', 'OPTYPE'),
ID = ID,
BALANCE = BALANCE);

Note:

This is not representative of a complete parameter file for an Oracle GoldenGate process. Also note that these examples represent a case-insensitive database.

This configuration makes possible queries such as the following, which returns the net sum of each transaction along with the time of the transaction and the customer ID.

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