Customizing - User Columns Section Two

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 to track when the data was last updated.

This page allows you to specify the default user columns for all primary tables, secondary tables, and history tables. This configuration page has a table with 4 columns, the first column contains the name of the user column, the next 3 columns labeled Primary Tables, Secondary Tables and History Tables represent the corresponding bits for these columns and the configuration paramters for entries in this page are dflt_user_columns, sec_tab_column_mask and dflt_history_columns.

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

User Column Options

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

• Primary Tables: 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 using the Customize command.

• Secondary Tables: Includes the selected user column in any secondary tables.

• History Tables: Includes the selected user column in history tables.

Available User Columns

To add columns from the list below select the check boxes for the columns you wish to add to the corresponding types of tables (Primary Tables, Secondary Tables, and History Tables).

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 as 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 in which 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

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 preserve 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.