previous
next
User columns let you add non-DMSII information to the relational database tables. For example, you can add an 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 , , ) are described in detail below.
Non-DMSII Column
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.
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.
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.
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.
Time the record was created in the relational database (relational database time).
A column that contains the data source identifier, as defined in the data_source_id column of the DATASOURCES client control table.
This column is identical to except that this column is used as a key.
A column that contains the data source name.
This column augments the column with a sequence number to provide higher granularity and avoid creating duplicate deleted records.
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 column if this column does not provide enough detail to avoid duplicate records (for example, a record is deleted twice in the same second).
(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.
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 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.
Time the update was applied to the relational database.
Use this column (or the ) 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.
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.)
A column used in tables that preserve one deleted record per index value.
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.
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.
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.
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.
Use the following options to include and modify user columns in your relational database 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 via the Client Configurator.
Includes the user column in any secondary tables
Includes the user column in history tables.
Shows the default column name. Type in the box to rename the column.
Allows you to specify the data type for the column. Available data types are determined by the type and version of the relational database.
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.