Customizing - SQL Data Types
Use the following parameters to control the default data types used by the Define/Redefine and Customize commands.
Configuration file parameters are included below with the following_font.
Default SQL data types
Use bigint for integers greater than 32-bits
Parameter: use_bigint
(SQL Server only) This option, when enabled, indicates that the Databridge Client will map DMSII numeric items that are too large to fit in an int data type (32-bit integer), to columns whose data type is bigint (64-bit integer). If this option is disabled, columns will have data types of decimal(n) instead. Items that are too large to fit in a bigint are still mapped to decimal(n). This option is designed to avoid customizations when you want to standardize the tables to use the bigint data type instead of decimal(n) whenever possible.
Use date data type
Parameter: use_date
(SQL Server only) This option, when enabled, indicates that the Databridge Client will map dates that have no time part to columns whose data type is date. If this option is disabled, these columns will have a data type of smalldatetime instead. This eliminates the need to customize such items when you want to standardize the tables to use the date data type instead of smalldattime.
The date data type has a much bigger range than both the smalldatatime and datetime data types. It is the ideal data type for dates that have no time parts as it uses less storage than smalldatetime.
Use datetime2 data type
Parameter: use_datetime2
(SQL Server only) This option, when enabled, indicates that the Databridge Client will map date/time items to columns whose data type is datetime2. If this option is disabled, these columns will have a data type of datetime instead. This eliminates the need customize such items when you want to standardize the tables to use the datetime2 data type instead of datetime.
The datetime2 data type has a larger range than the datetime data type, and offers increased precision in the time part. The datetime2 data type uses the same amount of storage as the datetime data type.
Use time data type
Parameter: use_time
(SQL Server only) This option, when enabled, indicates that the Databridge Client will map items that represent numeric times to columns whose data type is the time data type. If this option is disabled, such columns will have a data type of int instead. The value is formatted as "hhmiss". This option is designed to avoid customizations when you want to standardize the tables to use the time data type.
Note
This option is not applicable to DMSII TIME(12) and TIME(14) which are stored in REAL in DMSII. They cannot be replicated to a column of data type time, as they can contain values that are bigger than 24 hours. These items are replicated to columns whose data types is int.
Use varchar, minimum length (0 to 255)
Parameter: use_varchar min_varchar
This option, when enabled, indicates that the Databridge Client will map character data to columns whose data type is varchar (Microsoft SQL Server) or varchar2 (Oracle) instead of char. When you enable Use varchar, you can also specify a minimum length (0 to 255).
Example
When this option is enabled, and a value of 4 is set for the minimum length input field, all DMSII ALPHA items whose length is less than 4 will be replicated to columns with the char data type. Using the char data type for narrow columns saves storage, as varchar data has a two byte length.
Use clob
Parameter: use_clob
This option indicates that DMSII ALPHA data that is larger than the 4000-character limit of the varchar2 column should be mapped to a data type of clob instead of being truncated or split into two columns.
Note
Consider using the Enable extended types option, which is more efficient than this option.
Enable extended types
Parameter: enable_extended_types
(Oracle only) This option makes the Oracle Client read the Oracle database's
max_string_size parameter and check if it is set to "extended" (as opposed to "standard").
If it finds the parameter set to "extended", it allows varchar2 and raw columns to
have a maximum length of 32K.
Use this parameter to ensure that DMSII ALPHA columns that are longer than 4000 characters do not get split. This option is more efficient than setting the
parameter use_clob to True.
Caution
Before you can use this option you must alter the Oracle database and system to set the max_string_size parameter to extended. Consult the Oracle documentation for information on how to do this.
Beware that once you set the database's max_string_size parameter to "extended", this step cannot be undone.