Detection Methodologies
Update Capture
Updates to both the remote and central databases must be detected so that they can be collected and sent to the other databases. This section does not deal with the deletion of rows from the database. That subject will be handled in the Delete Processing section. The methods that can be used include row marking and update tables.
A single row table called DIST$CONTROL_TABLE that has a column named MASTER_DB and UPDATE_TX_ID is used to control the update capture.
Control Table Creation and loading
If MASTER_DB is TRUE, the database that the triggers are running on is the central database. UPDATE_TX_ID is used to control processing in the triggers such as
Row Marking
The row marking technique would add two columns to distributed tables. The first column would be a row status field that would contain a status indicator for the row update state and the second would contain the transaction id for either the last or first update of the row. In the remote databases, the transaction id field would be cleared when the row is updated from the central database.
The following is the SQL that could be used to add these columns to a table as well as a row version column:
Row Marking Alter Table Command – Oracle
Update Tables
An alternative method would be to have an update table. This could be in one of two forms, a table that would hold pointers to the updated rows or a table that contains formatted update information. Both of these tables would have columns for the table name or identifier, primary key value, row status and transaction id. If formatted data is to be included, a column would be included for that data.
The following SQL are examples of how to create this table. The first example points to the actual data that has been updated.
Update Table Creation – Oracle
The following example is for an update table that includes the update information formatted for transmission.
Update Table with Update Data Creation – Oracle
Trigger Based Detection
If feasible, the actual detection of updates should be done in a database trigger. The trigger code would either update the row status and transaction id columns of the update row or would populate the update. The following is an example of trigger code for row marking for an update.
Row Marking Trigger Update– Oracle
The following is an example of trigger code for row marking for an insert.
Row Marking Trigger Insert– Oracle
The following is an example of trigger code using an update table for an update.
Update Table Trigger Update– Oracle
The following is an example of trigger code using an update table for an insert.
Update Table T
rigger Insert– Oracle
The following is an example of trigger code using a formatted update table for an update.
Formatted Update Table Trigger Update– Oracle
The following is an example of trigger code using a formatted update table for an insert.
Formatted Update Tab
le Trigger Insert– Oracle
These triggers rely on a few stored procedures. LOCAL_TRANSACTION_ID is in the Oracle provided package DBMS_TRANSACTION. The procedures DIST$NEW_ STATUS and DIST$NEXT_ROW_VERSION would be created for the application. The following is an example of the DIST$NEW_STATUS function:
DIST$NEW_VERSION Stored Procedure – Oracle
The following examples are for the function DIST$NEXT_ROW_VERSION. You will note that these examples return the old row version except if the database is the central database. In the first example, the current row version is incremented.
DIST$NEXT_ROW_VERSION Stored Procedure – Oracle
The following is an example of the same function that returns the next value from a sequence called DITS$ROW_VERSION.
DIST$NEXT_ROW_VERSION Stored Procedure – Oracle
The following example of DIST$NEXT_ROW_VERSION returns the current date for new row version.
DIST$NEXT_ROW_VERSION Stored Procedure – Oracle
Application Based Detection
Application based detection can be used if the database that is being used doesn’t support triggers and stored procedures and the application is customizable. At AMAT, we were running a heterogeneous environment with the remote sites using a Microsoft Access database and the central database being an Oracle database. Though we were able to use triggers on the central database, we had to modify the application to support the remote databases.
The same database customizations will need to be performed to the database that was performed for trigger-based detection. If the database does not support the reading of the transaction id, a column will have to be added to the DIST$CONTROL_TABLE as follows:
Control Table Creation and loading
The above example also has a column for CURRENT_ROW_VERSION. This would be needed if the ROW_VERSION stored in the database is generated from a pseudo sequence.
If row marking is to be implemented, the easiest way to mark the row is to add hidden controls linked to the DIST$ROW_STATUS, DIST$UPDATE_TX_ID and ROW_VERSION columns. The following is an Access Basic example of how to perform this action:
Row Marking – Access Basic
The following example implements an Update table.
Update Table – Access Basic
The following example would be used if the Update table contains the preformatted data.
Formatted Update Table – Access Basic
As with triggers, these routines require some global functions, gblGetNewRowVersion, gblGetNewRowStatus, gblGetRowStatus and gblGetNewTxnID. The following is an example of how to implement gblGetNewRowVersion.
Get New Row Status Function – Access Basic
The following is an example to implement the function gblGetRowStatus.
Get Row Status Function – Access Basic
The following example implements the gblGetNewRowVersion function that just increments the current row version.
Get New Row Version – Access Basic
The following example implements the gblGetNewRowVersion function that uses a pseudo-counter in the control table..
Get New Row Version – Access Basic
The following example implements the gblGetNewRowVersion function that uses the current date.
Get New Row Version – Access Basic
The following example is an implementation of the gblGetNewTxnID function.
Get New Transaction ID – Access Basic
The following example implants the gblIsMaster function.
Is Master – Access Basic
Handling Calculated Fields
Some fields in the database can hold calculated values or summaries from other tables. An example would be in a time logging function, each associated time entry would be added to a total time field on a parent record. A Trouble Notes field in a incidence record could be the accumulation of the text from attached notes entries. If we ignore these fields when we capture updates to the database and have them recalculated in the other databases, it would save on the number of updates that we would have to send to the master database and return to the remote databases.
Ignoring these fields in an application modification example is trivial as these fields should not be updateable by the user and we wouldn’t get a save event unless the user modified another column in the row. Since the values will be recalculated anyway, if the row is updated, we can ignore the column.
If triggers are being used, they will have to determine if another column on the table has been updated before taking action. The following is an example of the Row Marking Update trigger modified to check if a column is updated that needs to be marked. Note that inserts don’t need this checking as they will need to be marked in any case.
Row Marking Trigger Checking For Update – Oracle