To facilitate data replication from the source OpenEdge database to the target database, Pro2 uses two light weight databases that contain a series of replication tables. The replication tables manage

Pro2 Repl tables consist of minimal data and indexing, and a single sequence for process control. Replication triggers fire and capture the updated record information to any database replication table as the events take place. A replication table can be in a standalone Repl database or embedded into one of the source OpenEdge databases. To replicate Pro2 uses a replication processor that cycles through replication records periodically, based on user configuration, and replicates the table data directly to the target database.

As an alternate setup, the Repl tables can be embedded directly in the source database.

A ReplQueue is a table that is stored in the replication database. The ReplQueue table stores information on change events that occur on your source database. This information includes the Progress ROWID of the record changed, event date/time, and queue thread. Typically, ReplQueue records represent updates made to the source database that are waiting to be written to the target SQL database.

The Pro2 database and replication database are similar to one another in that they monitor data change events from the ReplQueue, and send those change events to the target database so that the information can be replicated there.

Repl database tables

Table 1. Sequences
Sequence Name Notes
NextReplNbr Increments the Sequence Number for each New ReplQueue Record
Table 2. Repl TablesThe following are a list of Replication database Tables:
Name Label
ReplControl Replication Control
ReplCustAsgn Custom Assignments
ReplCustDefs Custom Definitions
ReplCustFlds Custom Fields
ReplDBXRef Database Cross Reference
ReplFieldXref Column Cross-Reference
ReplProperties Properties
ReplQueue Replication Queue
ReplTableXRef Table Cross-Reference
Table 3. ReplControl – Replication control
Field name Data type Notes
GroupID Character Control Group
CodeID Character Group Sub Code
CodeVal1 Character First Sub Code Filter
CodeVal2 Character Second Sub Code Filter
CodeVal3 Character Record Value
Index name Components Unique Primary
idxControl GroupID Yes Yes
CodeID
CodeVal1
CodeVal2

ReplCustAsgn - Custom assignments

No fields or indexes are defined at this time.

Table 4. ReplCustDefs - Custom definitions
Field name Data type Notes
SrcDB Character Source Database Name
SrcTable Character Source Table Name
CustName Character Name
CustDefType Character Definition Type (Variable, Temp-Table, or Buffer)
CustMisc Character If set to CustDefType the Data Type is a Variable. Otherwise, is the name of the table that this temp-table or buffer corresponds to.
Table 5. ReplCustFlds - Custom fields
Field name Data type Notes
SrcDB Character Source Database Name
SrcTable Character Source Table Name
FldName Character Name of this field
FldDataType Character Data type
FldWidth Integer Maximum width of a field. It applies to Decimal and Varchar Fields only
FldDec Integer Maximum number of Decimals
FldMand Logical Indicates if a field is mandatory field. It is set to No by default.
Table 6. ReplDBXRef - Database cross-reference
Field name Data type Notes
SrcDB Character Source Database
SchHldr Character Schema Holder DB
SchImg Character Target Schema Image
TgtType Character Target DB Type
TgtConnName Character Target DB Connection
TgtPhysName Character Target DB Physical Name
GenQRec Logical Generate Queue Record
ProcQRec Logical Process Queue Record
SrcPhysName Character Source DB Physical Name
SchPhysName Character Schema Holder DB Physical
Index name Components Unique Primary
idxDBXRef SrcDB Yes Yes
idxDBType TgtType
Table 7. ReplFieldXRef - Field cross-reference
Field name Data type Notes
SrcDB Character Source Database
SrcTable Character Source table
SrcField Character Source field
SrcDataType Character Source data type
SrcOrder Integer Field Order
SchField Character Schema Field
SchDataType Character Schema Data Type
TgtField Character Target Field
TgtDataType Character Target Data Type
TgtPrec Integer Target Precision
TgtScale Integer Scale
SrcExtent Integer Source Extent
TgtExtent Integer Target Extent
OverrideDefs Logical Override Precision/Scale Defaults
Index name Components Unique Primary
idxFldXRef SrcDB Yes Yes
SrcTable
SrcField
idxSrcOrder SrcDB
SrcTable
SrcOrder
Table 8. ReplProperties – Replication properties
Field name Data type Notes
PropertyName Character Property Name
PropertyValue Character Property Value
Index name Components Unique Primary
idxProperties PropertyName Yes Yes
Table 9. ReplQueue - Replication Queue
Field name Data type Notes
Sequence Integer Sequence Number
EventType Character Event Type Single Letter – Create, Write, Delete
SrcDB Character Replication Record’s Source Database
SrcTable Character Replication Record’s Source Table
SrcRecord Character Source Record ROWID
EventDate Date Date Replication Record was Generated
EventTime Character Time Replication Record was Generated
SrcTransID Integer Source Database Transaction Number
Username Character User Id of Transaction
Applied Logical Replication Record Processed over to SQL
ApplDate Date Date Replication Record was Processed
ApplTime Character Time Replication Record was Applied
Audited Logical Audited? (Verification)
AudDate Date Audited Date of Verification
AudTime Character Audit Time of Verification
UserCust Character User Custom Data
RawData Raw Field to Store RAW information about the Record
QThread Integer Replication Queue Processing Thread #
Sequence Integer Sequence Number
Index name Components Unique Primary
idxApplied Applied No No
QThread
Sequence
idxAudited Audited No No
idxSeq Sequence Yes Yes
idxSrcRecord SrcRecord No No
idxSrcTable SrcTable No No
RQCompression Applied No No
SrcDB
SrcTable
SrcRecord
Sequence
idxAuditApplied Audited No No
Applied
QThread
Sequence
Table 10. ReplTableXRef - Table cross-reference
Field name Data type Notes
SrcDB Character Source Database
SrcTable Character Source table
SchTable Character Schema Table
TgtTable Character Target Table
GenQRec Logical Generate Queue Record
ProcQRec Logical Process Queue Record
QThread Integer Queue Thread #
UseInDiff Logical Include in Differential
TrigInst Logical Trigger Installed
MrgdTrig Logical Merged Triggers
OrigDTrigProc Character Original Delete Trigger
OrigWTrigProc Character Original Write Trigger
Index name Components Unique Primary
idxDiff UseInDiff No No
idxTblXRef SrcDB Yes Yes
SrcTable
idxThread QThread No No

Pro2 database tables

Table 11. Sequences
Sequence name Notes
CustDefIDSeq Increments the Sequence Number for each New Repl_CustDefs Record
SchemaSeq Increments the Sequence Number for each New ReplP_SchemaHist Record
ThreadSeq Currently not assigned
JobTaskIDSeq Increments the Sequence Number for each New ReplP_JobTask Record
JobIDSeq Increments the Sequence Number for each New ReplP_Job Record
CompanyIDSeq Currently not assigned
AlertSequenceID Increments the Sequence Number for each New ReplP_Alerts Record
NextSyncNbr Increments the Sequence Number for each New Repl_Sync Record
ThreadErrorSeq Increments the Sequence Number for each New ReplP_ThreadErrors Record
Table 12. Pro2 tables
Name Label
ReplP_Alerts Location to store reportable alerts
ReplP_AlertType Alert types
ReplP_Company Instance info
ReplP_EntInfo Enterprise info
ReplP_Job Job details
ReplP_JobLobs Job attachments
ReplP_JobMessage Job Messages
ReplP_JobTask Job Task info
ReplP_JobTemplate Job Templates
ReplP_SchemaHist History of replicated database tables
ReplP_ThreadActDet Thread activity data detail
ReplP_ThreadActivity Thread Activity
ReplP_ThreadErrors Thread Errors
ReplP_ThreadHist List of threads used for replication
ReplP_User User info
ReplP_WBDef Watch box details
Repl_Control Replication Control
Repl_CustDefs Custom Definitions
Repl_CustFlds Global or table specific custom fields for the SQL schema
Repl_DBXRef Database Map info
Repl_FieldXref Field Mapping
Repl_Properties Properties
Repl_Sync Sync messages
Repl_TableXRef Table Mapping
Repl_ThreadControl Thread Control
Table 13. ReplP_Alerts - Location to store reportable alerts
Field name Data type Notes
AlertID Integer Alert ID
AlertTypeID Integer Alert type ID
AlertTxt Character Alert text
AlertTime Datetime Time of alert
AlertReporter Character Alert reporter
Index name Components Unique Primary
idxAlert AlertID No Yes
AlertTypeID
Table 14. ReplP_AlertType - Alert types
Field name Data type Notes
AlertTypeID Integer Alert type ID
AlertType Character Alert ID
AlertCategory Character Alert category
Index name Components Unique Primary
idxAlertType AlertTypeID No Yes
Table 15. ReplP_Company - Instance info
Field name Data type Notes
COMPANYID Integer Company ID
INSTALLTYPE Character Installation type
COMPANYNAME Character Company name
ADDRESS Character Address1
ADDRESS2 Character Address2
CITY Character City
STATE Character State
ZIPCODE Character Zip code
WEBADDRESS Character Company web address
EMAIL Character Email
PHONE Character Phone
Index name Components Unique Primary
idxCompany COMPANYID No Yes
Table 16. ReplP_EntInfo - Enterprise information
Field name Data type Notes
InstNum Integer Instance number
InstName Character Instance name
InstAbbr Character Instance abbreviation
InstURL Character Instance URL
EntInstanceNum Integer Enterprise number
EntASHost Character Enterprise host
EntASName Character Enterprise AppServer name
EntASService Character Enterprise AppServer service name or port number
EntSuspendTransfers Logical Suspend data transfer (yes or no). Default is "no"
EntDeleted Logical Delete (yes or no). Default is "no"
CREATEDATE Datetime-tz Creation date
InstOS Character Instance OS
InstOEVer Character Instance OpenEdge version
InstPro2Ver Character Instance Pro2 version
InstOEArch Character Instance OpenEdge architecture
InstMachine Character Instance machine
InstIP Character Instance IP
InstTgtType Character Target instance type
InstConfig Character Instance configuration
InstASName Character Instance AppServer name
UPDATEDATE Datetime-tz Update date
Index name Components Unique Primary
InstKey InstNum Yes Yes
Table 17. ReplP_Job - Job details
Field name Data type Notes
JOBID Integer Job ID
DESCRIPTION Character Job description
REPEATABLE Logical Repeatable (yes or no)
TASKNAME Character Task name
SCHEDULETIME Integer Schedule time
SCHEDULEDATE Date Schedule date
SCHEDULEDAY Character Schedule day
CREATEDATE Datetime-tz Creation date
UPDATEDATE Datetime-tz Update date
RUNEND Datetime Task run end date
RUNSTART Datetime Task run start date
JOBRESULTSBLOB Blob Job result
SCHEDULEFREQUENCY Character Schedule frequency
PARENTID Integer Parent ID
EXPIREDATE Date Expiry date
TASKFREQUENCY Character Task frequency
TASKINTERVAL Integer Task interval
JOBENABLED Logical Job enabled (yes or no)
JOBTEMPLATEID Integer Job template ID
JOBPARAMETERS Character Job parameters
JOBSTATUS Character Job status (Pending, Canceled, Running, Done)
JOBRESULTSCLOB Clob Job result
SCHEDULEINTERVAL Character Schedule interval
EXPIRETIME Integer Expiry time
Index name Components Unique Primary
JobID JobID Yes Yes
CreateDate CreateDate No No
JobStatus JobStatus No No
JOBID
JOBTEMPLATEID JOBTEMPLATEID No No
ParentID ParentID No No
Table 18. ReplP_JobLobs - Job attachments
Field name Data type Notes
JOBID Integer Job ID
JOBTASKID Integer Job task ID
DESCRIPTION Character Job description
JOBRESULTSBLOB Blob Job result
JOBRESULTSCLOB Clob Job result
CREATEDATE Datetime-tz Creation date
Index name Components Unique Primary
JobLob JOBID Yes Yes
JOBTASKID
CREATEDATE
Table 19. ReplP_JobMessage - Job Messages
Field name Data type Notes
JOBID Integer Job ID
JOBMESSAGE Character Job message
CREATEDATE Datetime-tz Creation date
JOBTASKID Integer Job task ID
DISPLAYED Logical Display (yes or no)
MESSAGETYPE Character Job message type
Index name Components Unique Primary
JobTaskID JOBID No Yes
JOBTASKID
CREATEDATE
CreateDate CREATEDATE No No
Displayed DISPLAYED No No
JOBID
JOBTASKID
Table 20. ReplP_JobTask - Job Task info
Field name Data type Notes
JOBID Integer Job ID
JOBSTATUS Character Job status
JOBPARAMETERS Character Job parameters
RUNSTART Datetime Job run start date
RUNEND Datetime Job run end date
JOBTASKID Integer Job task ID
TASKNAME Character Task name
CREATEDATE Datetime-tz Creation date
UPDATEDATE Datetime-tz Update date
JOBRESULTSBLOB Blob Job result
JOBRESULTSCLOB Clob Job result
Index name Index name Components Unique Primary
JobTaskID JOBID No Yes
JOBTASKID
CREATEDATE
CreateDate CREATEDATE No No
JobStatus JOBSTATUS No No
JOBID
JOBTASKID
Table 21. ReplP_JobTemplate - Job Templates
Field name Data type Notes
JOBTEMPLATEID Integer Job template ID
DESCRIPTION Character Job description
TASKNAME Character Task name
USER_FIELDNAME Character User field name
USER_FIELDTYPES Character User field type
USER_FIELDVALUES Character User field value
USER_FIELDLABELS Character User field label
USER_FIELDMINVAL Integer User field minimum value
USER_FIELDMAXVAL Integer User field maximum value
PROGNAME Character Program name
CMDSTRING Character Command string to hold the task name, thread number and script extension
JOBPARAMETERS Character Job parameters
CREATEDATE Datetime-tz Creation date
UPDATEDATE Datetime-tz Update date
USER_FIELDTOOLTIP Character User field tool tip
Index name Components Unique Primary
JobTemplateID JOBTEMPLATEID Yes Yes
Table 22. ReplP_SchemaHist - History of replicated database tables
Field name Data type Notes
S_RELID Integer Source record ID
SRCDB Character Source database
SRCTABLE Character Source table
Index name Components Unique Primary
idxSchema S_RELID Yes Yes
idxSchemaDbTbl SRCDB No No
SRCTABLE
Table 23. ReplP_ThreadActDet - Thread activity data detail
Field name Data type Notes
ACTIVITYDATE Date Thread activity date
S_RELID Integer Source record ID
THREADID Integer Thread ID
ACTIVITYCOUNT Integer Thread activity count
ERRORCOUNT Integer Error count
LOCKEDCOUNT Integer Locked count
SKIPPEDCOUNT Integer Skipped count
THREADTYPE Character CDC, Replication or Audit
ACTIVITYTIME Integer Activity time
DETHOUR Integer Hour unit of time for thread activity
DETUNIT Integer Minute unit of time for thread activity. In HH:MM, DETUNIT is calculated as quotient of MM divided by 30
Index name Components Unique Primary
ThreadActDetIdx THREADTYPE No Yes
ACTIVITYDATE
DETHOUR
DETUNIT
SRCTABLE
Table 24. ReplP_ThreadActivity - Thread Activity
Field name Data type Notes
S_RELID Integer Source record ID
THREADID Integer Thread ID
THREADTYPE Character Thread type
ACTIVITYCOUNT Integer Activity count
ERRORCOUNT Integer Error count
LOCKEDCOUNT Integer Locked count
SKIPPEDCOUNT Integer Skipped count
ACTIVITYDATE Date Activity date
SRCDB Character Source database
Index name Components Unique Primary
ThreadActIdx SRCDB No Yes
THREADID
THREADTYPE
ACTIVITYDATE
ActivityDateIdx ACTIVITYDATE No No
ThreadIdx THREADID No No
THREADTYPE
Table 25. ReplP_ThreadErrors - Thread Errors
Field name Data type Notes
S_RELID Integer Source record ID
THREADID Integer Thread ID
ERRORDESCRIPTION Character Error description
CREATEDATE datetime-tz Thread creation date
THREADTYPE Character Thread type
THREADERRORID Integer Thread error ID
ERRORCOUNT Integer Error count
Index name Components Unique Primary
ThreadErrorIdx THREADERRORID Yes Yes
CreateTypeIDIdx CREATEDATE No No
THREADTYPE
THREADID
TypeIDDateIdx S_RELID No No
THREADTYPE
THREADID
CREATEDATE
Table 26. ReplP_ThreadHist - List of threads used for replication
Field name Data type Notes
THREADID Integer Thread ID
QTHREAD Integer Queue thread number
Index name Component Unique Primary
idxThread THREADID No Yes
Table 27. ReplP_ThreadLag -
Field name Data type Notes
THREADID Integer Thread ID
THREADTYPE Character CDC, Replication or Audit
AVGLAG Integer Average lag
MAXLAG Integer Maximum lag
MINLAG Integer Minimun lag
LAGDATE Date Lag date
LAGHOUR Integer Lag hour
RECCOUNT Integer Record count
Index name Components Unique Primary
ThreadLagIdx THREADID No Yes
THREADTYPE
LAGDATE
LAGHOUR
LagDateIdx LAGDATE No No
THREADTYPE
Table 28. ReplP_User - User information
Field name Data type Notes
Pro2UserID Integer Pro2 user ID
USERNAME Character Username
ADDRESS Character Address1
ADDRESS2 Character Address2
CITY Character City
STATE Character State
ZIPCODE Character Zip code
PHONE Character Phone
EMAIL Character Email
COMPANYID Integer Company ID
PASSWORD Character Password
Index name Components Unique Primary
ReplP_P2UserIDX USERNAME No Yes
Table 29. ReplP_WBDef - Watch box details
Field name Data type Notes
wbID Character Watch box ID
wbMode Integer Watch box mode
wbTitle Character Watch box title
wbValue Character Watch box value
wbvalTxt Character Tool tip text for each watch box
wblistTxt Character Unique value of each watch box
wbType Character Watch box type
wbFnxid Character Watch box function ID
Index name Components Unique Primary
idxWbdef wbID No Yes
Table 30. Repl_Control - Replication Control
Field name Data type Notes
GroupID Character Control Group
CodeID Character Group Sub Code
CodeVal1 Character First Sub Code Filter
CodeVal2 Character Second Sub Code Filter
CodeVal3 Character Record Value
Index name Components Unique Primary
idxControl GroupID Yes Yes
CodeID
CodeVal1
CodeVal2
Table 31. Repl_CustDefs - Custom Definitions
Field name Data type Notes
SrcDB Character Source database name
SrcTable Character Source table name
CustName Character Definition name
CustDefType Character Definition type (variable, temp-table, or buffer)
CustMisc Character Data type
CustDefID Integer Definition ID
Index name Components Unique Primary
idxCustDefs CustDefID Yes Yes
Table 32. Repl_CustFlds – Global or table specific custom fields for the SQL schema
Field name Data type Notes
SrcDB Character Source database name
SrcTable Character Source table name
FldName Character Field name
FldDataType Character Data type
FldWidth Integer Maximum width of a field. It applies to decimal and varchar Fields only
FldDec Integer Maximum number of decimals
FldMand Logical Indicates if a field is mandatory field. It is set to No by default.
Index name Components Unique Primary
idxCustFlds SrcDB Yes Yes
SrcTable
FldName
Table 33. Repl_DBXRef – Database cross-reference
Field name Data type Notes
SrcDB Character Source database
SchHldr Character Schema holder database
SchImg Character Target schema image
TgtType Character Target database type
TgtConnName Character Target database connection
TgtPhysName Character Target database physical name
GenQRec Logical Generate Queue Record (yes or no)
ProcQRec Logical Process Queue Record (yes or no)
SrcPhysName Character Source database physical name
SchPhysName Character Schema holder database physical name
SrcConnectType Character Source connection type
SrcDBMode Character Source database mode
SrcHostName Character Source host name
SrcHostPort Character Source host port
SrcUserName Character Source database username
SrcUserPWD Character Source database password
AppsrvName Character AppServer name
TgtUserName Character Target database username
TgtUserPWD Character Target database password
TgtDBHost Character Target database host
TgtDBPort Character Target database port
AppsrvConnectStr Character AppServer connection string
SrcLastJobID Integer Source DB last job ID
TgtLastJobID Integer Target DB last job ID
SrcDBPath Character Source database path
TgtDBPath Character Target database path
SchDBPath Character Schema database path
SrcLogical Character Source logical
TgtLogical Character Target logical
Index name Components Unique Primary
idxDBXRefc SrcDB Yes Yes
idxDBType TgtType No No
Table 34. Repl_FieldXref - Replication Cross-Reference
Field name Data type Notes
SrcField Character Source field
SrcDataType Character Source data type
TgtField Character Target field
TgtDataType Character Target data type
SrcOrder Integer Field order
TgtPrec Integer Target precision
TgtScale Integer Decimal places in the target field
SrcDB Character Source database
SrcTable Character Source table
SchField Character Schema field
SchDataType Character Schema data type
SrcExtent Integer Source extent
TgtExtent Integer Target extent
OverrideDefs Logical Override precision/scale defaults
Index name Components Unique Primary
idxFldXRef SrcDB Yes Yes
SrcTable
SrcField
idxSrcOrder Field No No
SrcDB
SrcTable
SrcOrder
idxTgtfld SrcDB No No
SrcTable
TgtField
Table 35. Repl_Properties- Properties
Field name Data type Notes
PropertyName Character Property name
PropertyValue Character Property value
PropertyCategory Character Property category
Index name Components Unique Primary
idxProperties PropertyName Yes Yes
Table 36. Repl_Sync – Sync messages
Field name Data type Notes
Sequence Integer Sequence ID
SyncTable Character Table name you want to sync
EventDate Date Event date
EventTime Character Event time
Username Character Username
Applied Logical Applied record (yes or no)
ApplDate Date Applied date
ApplTime Character Applied time
Table 37. Repl_TableXRef – Table mapping
Field name Data type Notes
SrcTable Integer Source table
TgtTable Character Target table
GenQRec Logical Generate queue record (yes or no)
ProcQRec Logical Process queue record (yes or no)
QThread Integer Queue thread number
UseInDiff Logical Include in differential (yes or no)
SrcDB Character Source database
SchTable Character Schema table
TrigInst Logical Trigger installed (yes or no)
MrgdTrig Logical Merged triggers (yes or no)
OrigDTrigProc Character Original delete trigger procedure
OrigWTrigProc Character Original write trigger procedure
Index name Components Unique Primary
idxTblXRef SrcDB Yes Yes
SrcTable
idxDiff UseInDiff No No
idxThread QThread No No
Table 38. Repl_ThreadControl - Thread Control
Field name Data type Notes
ThreadID Integer Thread ID
Updtime datetime Update time of the thread status
ThreadStatus Character Thread status
LogLevel Character Log level
Disposition Character Delete record/mark as applied
RepControl Character CDC, replication, or audit
ThreadType Character Thread type
ControlType Character Control type
SrcDB Character Source database
Index name Components Unique Primary
ThreadCtrlIdx SrcDB Yes Yes
ThreadID
ThreadType
ControlType