Details Table

The details table contains all the primary key fields and any ancillary data the user wants to carry along for use by the operator when reviewing potential duplicates or for reporting purposes. For duplicate checking of payments, the data may include a unique identifier for the document, the source of the document, and the routing number of the originator of the data. When clients pass data to the Duplicate Detect engine, they do so using fields, which are name and value pairs. The name specified for each field must match exactly with a column in the details table. This is how the engine determines which field to insert into which database column.

The user can also define secondary key fields. Secondary key fields are used to avoid false positives. If a match is found in the database based on the primary key fields, the Duplicate Detect engine manually goes through the secondary key fields to determine if it can eliminate the entry as a potential duplicate. The item can only be eliminated from being a potential duplicate if the original entry and the new entry have values for one of the secondary key fields, and those values do not match. If one of the entries has a value and the other does not have a value for a secondary key field, the entry is still considered a potential duplicate and the next secondary key is checked.

The user may also define a field as part of a removal key. Primary and secondary keys can also be used as removal keys. In the example payment namespace, the document identifier or the item sequence number (ISN) are removal keys. The removal keys are used to uniquely identify an entry the user wants to remove. The combination of removal, primary, and secondary keys uniquely identifies the entry.

The definition of fields as primary, secondary, and removal keys is retrieved from the database metadata. The user is responsible for adding comments to the column definitions to designate the key fields. If a field can be defined as more than one type of key, use commas to separate the key definitions.

Table 1 shows the valid values for column comments.

Table 1. Column Definition Comments
Column Comment	Corresponding Key
PKEY	Primary key
SKEY	Secondary key
RKEY	Removal key

In addition to the user columns defined in the DDL file, the user’s DDL must create additional system columns in the details table. The additional columns are shown in Table 2.

Table 2. Additional System Columns for Details Table
Column Name	Data Type	Description
SYS_ID	BIGINT	DB2® generated unique system identifier for the entry
SYS_UOW_ID	BIGINT	DB2 generated unique system identifier for the unit of work
SYS_CLIENT_ADDR	VARCHAR (128)	Server address and port where the data is coming from (set by the client)
SYS_DUP	CHAR (1)	Duplicate flag Y Duplicate N Not a duplicate S Suspended P Potential duplicate (review pending)
SYS_ENTERED_DATE	TIMESTAMP	Date the entry was created
SYS_EXPIRY_DATE	TIMESTAMP	Date the entry expires
SYS_DELETED	TIMESTAMP	Date the entry was soft deleted (NULL if not deleted)

All columns are used by Duplicate Detect and must be defined as shown in the example. For payment duplicate checking table, the user’s details table DDL might look like the following example:

CREATE TABLE DETAILS
(
   SYS_ID             BIGINT          NOT NULL
                                      GENERATED ALWAYS AS IDENTITY,
   SYS_UOW_ID         BIGINT          NOT NULL,
   ACCT_NO            CHAR(20)       NOT NULL,
   ROUTING_NO         CHAR(20)        NOT NULL,
   AMOUNT             CHAR(14)        NOT NULL,
   ITEM_ID            CHAR(32),
   SRC                CHAR(32),
   MICR2              CHAR(32),
   MICR4              CHAR(32),
   MICR6              CHAR(32),
   SERIAL             CHAR(32),
   ORIG_RT            CHAR(20),
   SYS_CLIENT_ADDR    VARCHAR(128),
   SYS_DUP            CHAR(1),
   SYS_ENTERED_DATE   TIMESTAMP,
   SYS_EXPIRY_DATE    TIMESTAMP,
   SYS_DELETED        TIMESTAMP,

   CONSTRAINT DETAILS_PK PRIMARY KEY (SYS_ID)
);

CREATE INDEX DETAILS_EXPIRY ON DETAILS ( SYS_EXPIRY_DATE ) ALLOW REVERSE SCANS;
CREATE INDEX DETAILS_PKEY ON DETAILS   ( ACCT_NO, ROUTING_NO, AMOUNT );

COMMENT ON DETAILS ( ACCT_NO IS 'PKEY' );
COMMENT ON DETAILS ( ROUTING_NO IS 'PKEY' );
COMMENT ON DETAILS ( AMOUNT IS 'PKEY' );
COMMENT ON DETAILS ( SERIAL IS 'SKEY' );
COMMENT ON DETAILS ( MICR2 IS 'SKEY' );
COMMENT ON DETAILS ( ITEM_ID IS 'RKEY' );

Note: For performance reasons, the columns composing the primary key should be the first columns in the details table definition after the SYS_ID and SYS_UOW_ID columns. The user’s payload columns should follow and, finally, the system level columns. The user should only include those payload fields that are absolutely necessary, as additional nonessential fields degrade performance and use additional disk resources.