Customizing - User Columns Section One

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 options below allow you to change the default names, sql types and sql lengths (when applicable) for the various types of user columns. The external columns are split into two pages, the second page allows you to select the default user columns for the various types of tables.

The corresponding configuration options for entries in this page are external_column[i] = "name", sql_type, sql_length), where i is the index of the user column, sql_type is the index of the SQL type, and sql_length is the length of the column when the SQL type has a length.

User Column Default Properties

This table of user columns has 3 columns that you can modify,

Use the following options to modify the default specifications for user columns in your relational database tables.

Column Name

This column represents the default column name. Use the input fields in this column to provide a new name for the Define/Redefine and Customize commands to use when creating the columns in the DATAITEMS Client control table.

Data Type

This column allows you to select a data type for the column from the provided list of data types. Available data types are dependent on the user column type and the type of the relational database.

Data Length

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 entered is outside the allowable range, you will get the error "Invalid SQL Length" and will then need to correct the length before the input will be accepted.

Available User Columns

The following is a list of the different types of user columns that are available. You can change the default name, data type, and data length using the controls on this page.

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 takes on the date/time value of when the data extraction started, instead of being NULL.

Create Time

The time when the record was created in the relational database (uses the data type date for Oracle and datetime or datatime2 for SQL Server).

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 it 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 avoids the creation of 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 a duplicate. The value of this column is a timestamp that represents the number of seconds elapsed since the beginning of the epoch (1/1/1970).

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

Identity Column

(SQL Server only) Identifies the column using the sequence number assigned to the record when the record was created. 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 Identity Column for history tables is preferable, as it doesn't have this problem. For optimal results, let the client choose the default user columns for history tables.

Server Update Time

The time in which the update was applied to the relational database.

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 preserves one deleted record per index value.

User Column 1

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. For more information, see the Databridge Client Administrator's Guide.

User Column 2

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. For more information, see the Databridge Client Administrator's Guide.

User Column 3

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. For more information, see the Databridge Client Administrator's Guide.

User Column 4

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. For more information, see the Databridge Client Administrator's Guide.