Code sample for handling re-assignment of ROWID to target record when using CDC and a Table Partitioning Policy column value changes? - Progress Community

Loading

Skip to Main Content

Code sample for handling re-assignment of ROWID to target record when using CDC and a Table Partitioning Policy column value changes?

When using Change Data Capture (CDC) and Table Partitioning (TP) together, is it possible capture rows whose ROWID was changed due to the record change and see the before/after ROWID values?

Dec 23, 2020
Knowledge

Title

Code sample for handling re-assignment of ROWID to target record when using CDC and a Table Partitioning Policy column value changes?

URL Name

code-sample-for-handling-re-assignment-of-rowid-to-target-record-when-using-cdc-and-a-table-partitioning-policy-column-value-changes

Article Number

000198641

Environment

Product: OpenEdge Version: 11.7.x, 12.x OS: All Supported Platforms

Question/Problem Description

When using Table Partitioning in a database, the concept of ROWID changes is very real.

Let's say you've got a LIST partition setup on the Country and Salesrep fields in the customer table, and the value of Country and/or Salesrep changes in the record.

When this happens, the record is deleted internally and re-created. The net effect of this is that the ROWID of the record is now different.

If you are using CDC in order to maintain a copy of the record in another table/database, and are using ROWID to index the records in the target table, you need a mechanism for proliferating the change in ROWID into the target.

Steps to Reproduce

Clarifying Information

Error Message

Defect Number

Enhancement Number

Cause

Resolution

The below code demonstrates

USING OpenEdge.DataAdmin.Util.CDCTrackingHelper.

DEFINE VARIABLE iColumn AS INTEGER             NO-UNDO.

DEFINE VARIABLE hBefore AS HANDLE              NO-UNDO.
DEFINE VARIABLE hAfter  AS HANDLE              NO-UNDO.

DEFINE VARIABLE oHelper AS CDCTrackingHelper   NO-UNDO.

DEFINE BUFFER bFile   FOR _file.
DEFINE BUFFER bPolicy FOR _partition-policy.
DEFINE BUFFER bCDC    FOR _cdc-change-tracking.

oHelper = NEW CDCTrackingHelper("Customer", BUFFER bCDC:HANDLE ).
hAfter = oHelper:CreateChangeBuffer().
CREATE BUFFER hBefore FOR TABLE hAfter.

FOR EACH bCDC 
    WHERE bCDC._source-table-number EQ oHelper:SourceTableNumber:
    
    IF (oHelper:IsUpdate()) THEN DO:
        FIND bFile WHERE bFile._file-name EQ "Customer" NO-LOCK NO-ERROR.
        IF AVAILABLE bFile AND
           bFile._file-attributes[3] /* The table is partitioned*/ THEN
            FIND FIRST bPolicy 
                WHERE bPolicy._object-number EQ bFile._file-num NO-LOCK NO-ERROR.
        
        IF AVAILABLE bPolicy THEN 
            PolicyColLoop: DO iColumn = 1 TO EXTENT(bPolicy._column-name):
            IF (bPolicy._column-name[iColumn] GT "") NE TRUE THEN
                LEAVE PolicyColLoop.
            IF oHelper:FieldChanged(bPolicy._column-name[iColumn]) THEN DO:
                oHelper:FetchChangeRecord(hBefore,TRUE).
                oHelper:FetchChangeRecord(hAfter).
                /* place logic here to re-assign the rowid in the target 
                    e.g. 
                    FIND FIRST targetRecord 
                            WHERE targetRecord.RowIdent EQ 
                                    STRING(hBefore:ROWID) 
                            EXCLUSIVE-LOCK NO-ERROR.
                    IF AVAILABLE targetRecord THEN 
                        targetRecord.RowIdent = STRING(hAfter:ROWID) */
            END.
        END.
        /* Release the _file and _buffer records so they do not contaminate 
           next iteration */
        RELEASE bPolicy NO-ERROR.
        RELEASE bFile   NO-ERROR.
    END.
END.

Workaround

Notes

Keyword Phrase

Last Modified Date

12/23/2020 2:05 PM

Drop Files

Upload FilesOr drop files

Disclaimer

The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.

Loading

Code sample for handling re-assignment of ROWID to target record when using CDC and a Table Partitioning Policy column value changes? - Progress Community