Format of interface tables

An interface table has the same format as its corresponding object structure, including persistent and nonpersistent columns and excluding any columns that are excluded from the object structure.

The interface table includes additional columns that identify the sequence in which the sending system writes, and the receiving system processes the records in the various interface tables.

Key columns

If the interface table represents a hierarchical object structure with parent-child object relationships, the table does not include any part of the child object key columns that are included in the parent object key columns. For example, PERSONID is a key column in the PERSON, PHONE, EMAIL, and SMS records. The PERSONID column appears only at the parent (PERSON) level in the MXPERSON_IFACE interface table.

Duplicate columns and aliases

The XML representation of a hierarchical object structure contains duplicate column names, but interface table and flat file representation do not. If an object structure has duplicate non-key column names in both a parent object and a child object, a duplicate column name error occurs when the interface table or a flat file record is generated.

To resolve this issue, change the alias name for one of the duplicate column names. Every system database column can have an alias alternate name. When an alias exists, the system uses the alias when interface tables and flat files are generated. Change the alias to eliminate the duplicate column name error.

Most columns do not have an alias, but some columns have aliases that support the predefined enterprise services or publish channels.

A column within an object structure can have a single alias. If the object structure is used by multiple publish channels and enterprise services, a change to an alias affects every interface table that is associated with the object structure.

The columns in the predefined object structures have system-assigned aliases. Check for duplicates when you create a hierarchical object structure or when you add an object to a predefined object structure. The Add/Modify Alias window shows the fields and aliases for the objects in a selected object structure, and identifies any duplicate alias names with a check in the duplicate column. If a duplicate alias exists, overwrite its value in the ALIASNAME column.

Restricted columns

The HASLD field is an internal system column that is excluded from all object structures. Do not include this column in any object structure that is associated with an interface table. The LANGCODE field is also excluded from the predefined object structures.

Integration processing columns

The following table shows the columns that are used in the interface table sequencing, retrieval, and processing. Some columns are in either the interface queue tables or the interface tables; some are in both places.

Table 1. Integration processing columns
Column name Interface queue table Interface table Description
IFACENAME Yes No

The IFACENAME column contains the name of the enterprise service or the publish channel that is used in a transaction. The column is populated in outbound transactions. For inbound transactions, the external system must populate the column with the name of the enterprise service or the publish channel that corresponds to the row that is inserted into an interface table.

TRANSID Yes Yes

The TRANSID column in an interface queue table is a sequential number that uniquely identifies an integration transaction. The TRANSID and the interface table name, identifies a unique transaction. The interface queue table can contain one record with a TRANSID value. The corresponding interface table can have one or more records with the TRANSID, depending on the number of records that are written to that interface table as part of that enterprise service or the publish channel.

If a transaction writes to multiple interface tables, the interface queue table contains a separate record with a unique TRANSID value for each interface table.

Each interface queue table maintains its own TRANSID counter. The outbound TRANSID value is initialized when the interface queue table records are generated. You must create and maintain the TRANSID counters that populate the inbound queue tables and the interface table records.

If the external systems do not correctly manage the inbound TRANSID counters, sequential processing is not guaranteed. Ensure that the TRANSID values that the external system generates does not duplicate the TRANSID value that is generated. Errors occur if duplicate TRANSID values exist and when you process the same object structure in both an inbound and an outbound direction by using a single interface table.

Each endpoint has its own set of interface queue tables and its own outbound TRANSID counter.

TRANSSEQ No Yes

When multiple records in an interface table share the same TRANSID value, the TRANSSEQ column provides a secondary sequence number that indicates the sequence in which those records should be processed.

EXTSYSNAME Yes No

The EXTSYSNAME column in the interface queue tables can contain inbound or outbound data. For inbound transactions, the column contains the name of a valid and enabled external system that is defined in the integration framework. For outbound transactions, the column contains the name of the external system that is the destination of the transaction.

ACTION Yes No

The ACTION column contains the action to perform on the external system (outbound) or on the integration framework (inbound). The following actions can be used:

  • Add: Inserts the data that is provided in the message.
  • Delete: Deletes the parent data, and any related child data, from the database.
  • Change: Updates parent and child data contents of the message, but does not delete existing child data that is not explicitly specified in the message.
  • Replace: Replaces the existing records with the contents of the message and deletes existing child data that is not referenced in the message.
  • AddChange: If the primary record does not exist, processes as an add action and, otherwise, processes as a change action.
  • Null: If the primary record does not exist, processes as an add action and, otherwise, process as a replace action.
IMPORTMESSAGE Yes (used inbound only) No

The IMPORTMESSAGE column holds any error message that was produced when the interface table row was moved to the inbound queue.

TRANSLANGUAGE Yes No

The TRANSLANGUAGE column identifies the language of the transaction. For an outbound transaction, this value indicates the language of the user who initiated the transaction. For an inbound transaction, this value indicates the language of the transaction. Any attributes that support a multilanguage environment are expected to be in the language that the TRANSLANGUAGE value defines.

MESSAGEID Yes (used outbound only) No

The MESSAGEID column is a unique identifier that the system assigns to every outbound transaction.

IFACETBNAME Yes (used outbound only) No

The IFACETBNAME column is the name of the interface table that corresponds to the IFACENAME column. This column applies to outbound transactions only.

Long description columns in an Oracle Database

Long description columns are stored in a CLOB (character large object) column in an Oracle Database. Interface tables contain two versions of each CLOB column, one with data type CLOB, and one with data type ALN with a character length of 4000. In the following example, the name of the CLOB column is the column alias. The name of the alphanumeric column is the column alias with the suffix 2.

Table 2. Long description columns in an Oracle Database
Data type Name of description column
CLOB PERSON_DESCRIPTION_LD
ALN PERSON_DESCRIPTION_LD2

The system populates both columns in outbound transactions. For inbound transactions, the integration framework uses the value in the ALN column if it is not null; otherwise, the value in the CLOB column is used.