User Columns

User columns let you add non-DMSII information to the relational database tables. For example, you can add an Audit Timestamp column to store the audit file timestamp and track when the data was last updated. (The configuration parameter for user columns is external_column[i] = "name", sql_type, sql_length.)

The following is a list of the different types of user columns that are available. Options for each column (such as Add, Secondary Tables, History Tables) are described in detail below.

Non-DMSII Column

Audit Block Serial Number

A column that contains the Audit Block Serial Number (ABSN) of the block in the audit trail from which updates are currently being processed. If you use a decimal number its precision must be at least 10.

Audit File Number

A column that contains the Audit File Number (AFN). 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.

Audit Timestamp

A column that contains the audit file timestamp of the block from which updates are currently being processed. This value is stored using a date/time data type. For extract records this column is NULL.

Audit & Extract Timestamp

A column that contains the audit file timestamp of the block from which updates are currently being processed. This value is stored using a date/time data type. For extract records this column is NULL.

For extract records this column takes on the date/time value of when the data extraction started, instead of being NULL.

Data Source ID

A column that contains the data source identifier, as defined in the data_source_id column of the DATASOURCES client control table.

Data Source ID Key

This column is identical to Data Source ID except that this column is used as a key.

Data Source Name

A column that contains the data source name.

Delete Sequence Number

This column augments the Deleted Record column with a sequence number to provide higher granularity and avoid creating duplicate deleted records.

Deleted Record

When this column is added to a table, deleted records are marked as deleted and left in the table. The client makes this column part of the index, which allows multiple instances of a deleted record to exist in the table and not be considered duplicates. The value of this column is measured in seconds.

Include the Delete Sequence Number column if this column does not provide enough detail to avoid duplicate records (for example, a record is deleted twice in the same second).

Identity Column

Identifies the column using the sequence number assigned to the record when the record was created (in the case of SQL Server). Updates have no effect on this number.

Sequence Number

A sequence number used in history tables to determine the order of updates when they have the same update_time values. For SQL Server, the Identify Column for history tables is preferable, as it doesn't have this problem. For optimal results, let the client choose the default column for history tables.

Server Update Time

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

SQL Server Timestamp

Use this column (or the Identity column) for compatibility with older SQL Server databases. SQL timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.

Update Type

In history tables, this column indicates the type of update involved (insert, delete, or update). For non-history tables, this column indicates the type of the last update performed. (A value of 0 is assigned to this column during data extraction.)

Update Type (Logical Delete)

A column used in tables that preserve one deleted record per index value.

User Column

A generic user column whose entry is NULL. To add a default value to this type of column, use the Data table creation user script. See the DATABridge Client Administrator's Guide.

User Column options

Use the following options to include and modify user columns in your relational database tables.

Add

Adds the selected user column to your relational database.

If you change the name, SQL type, or SQL length using these settings, those changes will be in effect if you later add user columns in the data set properties via the Client Configurator.

Secondary Tables

Includes the user column in any secondary tables

History Tables

Includes the user column in history tables.

Column Name

Shows the default column name. Type in the box to rename the column.

Data Type

Allows you to specify the data type for the column. Available data types are determined by the type and version of the relational database.

Data Length

Allows you to specify the length for the data type if a length is required. Most user columns impose a minimum and maximum length for the data type. If the value you enter does not fall into the allowable range, you will get the error "Invalid SQL Length" and will then need to correct the length before input will be accepted.