Types of Non DMSII Columns

The DATABridge Client offers several default non DMSII columns (user columns). You can add user columns to the relational database either by using user scripts, as described in this section, or by using the Client Configurator. For more information about the Client Configurator, see the DATABridge Client Console Help.

Note: The value for the Bit column in this table is equal to the value in the dms_subtype column of the DATAITEMS client control table. The exception is bit 14, which results in a dms_subtype of 0. Bits are numbered from right to left; the right-most bit is 1.

Bit

Value

User Column Name

Description

1

1

update_type

Database update type, as follows:

0 for extract
1 for create
2 for delete (bit 10 must also be enabled)
3 for modify

Note: This value cannot be used at the same time as bit 11.

2

2

update_time

Time the update was applied to the relational database (PC time)

3

4

update_ts

(SQL Server clients only) SQL Server timestamp data type. (The timestamp is a data type that exposes automatically-generated unique binary numbers within a database. It is not a true timestamp that contains a date and time value.)

4

8

audit_ts

DMSII audit file timestamp. This column is set to NULL during the initial clone.

Note: This bit cannot be used at the same time as bit 13.

5

16

audit_filenum

Audit file number

Note: If you use a decimal number, its precision must be at least 4. Otherwise, the value may be too large and result in a SQL error.

6

32

audit_block

Audit block serial number (ABSN)

7

64

source_name

Data source name

8

128

source_id

Data source identifier as defined in the DATASOURCES client control table

9

256

my_id

SQL SERVER IDENTITY column.

Updates have no effect on this number.

For Windows Clients only: This column won't appear on clients other than SQL Server, even if requested. The Oracle database provides the equivalent functionality with the ROWID pseudo-column, which is always present.

10

512

deleted_record

Delete indicator (key item). A nonzero value indicates that the record is deleted. This is actually the value of the client machine’s clock at the time of the deletion. Making this column part of the index allows multiple instances of a deleted record to coexist without being considered duplicate records.

Notes:

  • This bit cannot be used at the same time as bit 11. These types are compared in Preserving Deleted Records.
  • The granularity of this column is in seconds. If you have applications that perform many delete/insert operations, you may want to add a delete_seqno column to prevent the Client from getting duplicate deleted records. The Client recovers from this by waiting one second and retrying the operation, which can significantly slow the Client's performance.

11

1024

update_type

Expanded database update type as follows:

0 for extract
1 for create
2 for delete
3 for modify

Note:

  • If the key for this record is reused, the key is removed when the new, duplicate record is inserted.
  • This value cannot be used at the same time as bit 1 or bit 10. Bits 10 and 11 are compared in Preserving Deleted Records.
  • This bit and bit 1 work in the same way, except that this bit preserves the deleted image.

12

2048

source_id

Data source identifier as defined in the DATASOURCES client control table (key item)

13

4096

audit_ts

Expanded audit file time. This column contains the DMSII audit file timestamp during updates and the starting time of the data extraction during extraction.

Note: This bit cannot be used at the same time as bit 4.

14

8192

user_column1

Generic user column whose entry is left as NULL

15

16384

sequence_no

A sequence number used in history tables to determine the order of updates when they have the same update_time values

16

32768

delete_seqno

Augments the deleted_record column with a sequence number to provide higher granularity and avoid creating duplicate deleted records.