ONE Desktop ONE DQ&C ONE MDM ONE RDM ONE Runtime Server
latest (15.4.0) 15.3.0 15.2.0 15.1.0 14.5.x 13.9.x 13.8.x 13.7.x 13.6.x 13.5.0 13.4.x 13.3.x 13.2.x
User Community Service Desk Downloads
If you can't find the product or version you're looking for, visit support.ataccama.com/downloads

Internal Columns

This topic covers the columns not defined in the Model (see Model) and created or interpreted by the engine.

Database Columns

Database (DB) columns are part of each instance or master table maintained by the engine. The column names (both database columns and pseudocolumns) cannot be renamed or used for a different purpose than specified. Moreover, their values cannot be changed directly, but only by the engine processes or features.

The DB columns are added by the engine automatically. Some of the columns are also available in ONE plans serving as transitions under either database column name (DB) or audit pseudocolumn name (AP) - see Available in a plan as DB/AP column in the table below. There are also Audit Pseudocolumns (see Audit Pseudocolumns), which are (usually) not related to any database column, and they are available in ONE plans only.

There are no database types shown in the following table because they are dependent on the particular DB used.

Database column Instance table Master table Description Available in a plan as DB/AP Data type

id

x

  • Unique id automatically assigned to each incoming instance record by the MDM engine.

  • Serves as PK when DB persistence is used (see Persistence Layer, section DB Persistence).

DB

long_int***

id

x

  • Mapped in merge plan manually from master_id as a result of representative creator step.

  • Serves as PK when DB persistence is used (see Persistence Layer, section DB Persistence).

DB

long_int***

id

  • Id automatically assigned to each imported or created record.

long_int***

source_id*

x

+

  • Unique id of source system record; mapped in load plan/process delta service.

DB

string

master_id

x

+

  • Used to store the result of default matching; filled in match plan as a result of the unification step.

  • Also serves as a foreign key to the corresponding master entity.

DB

long_int***

match_isolate

x

  • Support for manual match operations (split_isolate).

DB

boolean

eng_origin

x

  • Stores the origin id of data origin, defined in [prefix]_ORIGIN table, CODE column (number).

  • Related AP contains a textual representation of the origin_id; equal to origin mapped in a load plan/process delta service.

AP

string

duplicate_id

eng_key

eng_active

x

x

AP

boolean

eng_existing

  • In general, when a record is deleted, it has eng_existing=false. These records are not deleted physically because they are used for the purposes of exporting.

  • Determines if the record is reported from the source system as existing (when using sourceDeletionStaregy=DELETE). If the record is deleted, eng_existing = false.

N/A

boolean

eng_existing

x

  • In general, when a record is deleted, it has eng_existing=false.

  • These records are not deleted physically, because they have to be there for export purpose.

  • Determines if the record is reported as deleted. It happens when two groups are merged into the one and eng_existing=false is set for the master record which was terminated.

N/A

boolean

eng_existing

  • In general, when a record is deleted, it has eng_existing=false.

  • Determines if the record is reported as deleted.

eng_modified_by

x

  • Displays the last user to have made changes to a record in the system of record hub.

string

eng_nature

x

  • Shows whether the record is authored or consolidated (data type).

boolean

eng_source_timestamp

x

see below

AP

datetime

eng_source_system

x

see below

AP

string

eng_creation_tid

x

x

see below

AP**

long_int***

eng_creation_date

x

x

see below

AP

datetime

eng_deletion_tid

x

x

see below

AP**

long_int***

eng_deletion_date

x

x

see below

AP

datetime

eng_last_update_tid

see below

AP**

long_int***

eng_last_update_date

x

x

see below

AP

datetime

eng_last_source_update_tid

x

see below

AP**

long_int***

eng_last_source_update_date

x

see below

AP

datetime

eng_activation_tid

x

x

see below

AP**

long_int***

eng_activation_date

x

x

see below

AP

datetime

eng_deactivation_tid

x

x

see below

AP**

long_int***

eng_deactivation_date

x

x

see below

AP

datetime

xid**

x

x

primary key of each table

N/A

long_int***

xctid**

x

x

internal technical column

N/A

long_int***

  • Now defined as a string with size 1000, mapped to DB as varchar(2000), or varch2(2000) for Oracle (2000 because of UTF). This is hardcoded in the MDM source code now. The maximum allowed content is 1000 chars.

    • Technically, the column might be available in ONE plan. In general, various TID/DATE appearances in ONE plans depend on a particular MMD definition.

      • Currently used in MDM only. It refers to a data type longer than integer and shorter than long, with its size limited to 8 bytes due to performance reasons (max value is 264, roughly 18 exabytes).

        • Present only when VLDB Persistence is used, see Persistence Layer, section VLDB Persistence.

Audit Pseudocolumns

The following pseudocolumns are available in various places across engine configuration, for example, ONE plans and WebServices. DB columns are described above.

Batch Load Pseudocolumns

Appear only in batch load plans, and then they are remapped into an eng_ prefixed columns and persisted as described above.

origin (string)

Serves to map already defined origin, that is equal to textual representation eng_origin.

change_type (string)

Serves as input to the delta detection process. It’s mapped into eng_change_type.

source_timestamp (datetime)

Serves to provide external timestamp to the MDM. It’s mapped into eng_source_timestamp.

Transition Plans Pseudocolumns

The allowing pseudocolumns are available in DQC plans serving as transitions in MDM and in export plans.

Status Pseudocolumns

eng_active (boolean)

Instance records: determines if the record is reported from the source system as existing (when using sourceDeletionStaregy=DEACTIVATE).

Master records: Determines if at least one instance record corresponding to this master has eng_active=true.

eng_origin (string)

Instance records only. Contains the textual representation of the record origin. It is stored as origin_id column in a DB.

eng_source_system (string)

Instance records only. Contains the textual representation of the system corresponding to the record origin. DB column name is eng_system.

eng_source_timestamp (datetime)

Instance records only. The aim of this column is to prevent entering and overwriting the most recent record by the older ones based on the timestamp provided.

Modification Date Pseudocolumns

A set of pseudocolumn pairs (date + transaction id) describing when a particular record-level event took place. Date columns are of DATETIME type, transaction IDs are of LONG type. Only the date-related pseudocolumns are available in all transition and export plans, but all the columns ale persisted in a database as described.

The column values for new records are set by default during the Change Detection phase, however, for expanded records (for example CopyColumns, or aggregation) the values will be set during the Committing phase. This behavior means that expanded records always show previous values in the transition plans, and these values will be different from what you find in the MDM storage once the transaction completes.
Transaction ID column Date column Event

eng_creation_tid

eng_creation_date

Record creation. Always non-null.

eng_deletion_tid

eng_deletion_date

Record deletion. Null for existing records. Only makes sense in internal plans and delta exports.

eng_last_update_tid

eng_last_update_date

Last update of the record. Will be changed every time record is changed, including record I/U/D from input or loaded during processing. Always non-null.

eng_last_source_update_tid

eng_last_source_update_date

Only for instance records; last update of the record in delta detection phase (excludes internal calculations). Always non-null.

That is, last time the record was sent by source system and change was detected in the delta detection phase.

eng_activation_tid

eng_activation_date

(Last) record activation. Always non-null.

eng_deactivation_tid

eng_deactivation_date

(Last) record deactivation. Null for active records.

Inner Plan Pseudocolumns

The following pseudocolumns are not persisted in the database - they are only available in transition plans (computed for each loading operation):

eng_change_type (char)

Determines if the record was changed in the current load. The change type is evaluated against the eng_existing column.

  • N - No change (this normally appears only during retrofit operations).

  • I - Newly added record.

  • D - Deleted record.

  • U - An existing record that was modified (no insert/delete).

eng_activity_change_type (char)

Similar to eng_change_type, determines if the record was changed in the current load with respect to eng_active. See Status Pseudocolumns for reference.

  • N - No change (this normally appears only during retrofit operations).

  • I - Newly activated record.

  • D - Deactivated or inactive record.

  • U - An active record that was modified (no activation/deactivation).

eng_external_record (boolean)

True if the record is not an actual record in the hub, for example, a record sent in by the Identify Service (see Services).

Delta Export Pseudocolumns

Only appear during the delta data exports and they are not persisted in the database (but the information to be able to determine the state is preserved).

eng_change_type (char)

Determines the change between the state in reference time and the current state.

  • I - The record has been newly inserted since the reference time (present at the moment).

  • D - The record has been deleted since the reference time (not present at the moment).

  • U - The record has been modified since the reference time. No insert or delete operation took place.

  • If the record was deleted and then inserted (or vice versa) since the reference time, I (resp. D) will be reported - depending on whether the record is (I) or is not (D) present currently.

eng_activity_change_type (char)

Determines the change of the eng_active pseudocolumn.

  • I - The record has been newly activated since the reference time (active at the moment).

  • D - The record has been deactivated since the reference time (not active at the moment).

  • X - The record was inactive at the reference time, hasn’t been activated in the period. Only appears when the scope is EXISTING.

  • U - The record has been modified since the reference time. No change on eng_active flag took place. This also means that eng_active = true, as otherwise an X would be reported.

  • If the record was deactivated and then activated (or vice versa) since the reference time, I (resp. D) will be reported - depending on whether the record is (I) or is not (D) active currently.

Web Services Pseudocolumns

Appear only in web services.

Request Pseudocolumns

sourceId (string)

Used to map instance primary key to the source_id attribute for traversal/genericTraversal (see Services).

origin (string)

Serves as identification of requested record (together with sourceId) for some of the MDM Native Services (see Services).

change_type (string)

Serves as input to the delta detection process for RW web service only. It’s mapped into eng_change_type.

source_timestamp (datetime)

Serves as input to the change detection process for RW web service only. It’s mapped into eng_source_timestamp.

Was this page useful?