Database dictionary data types

Every field in a database dictionary record requires a data type value to determine the format of the data in the field. Database dictionary records use an abstract data type that the Service Manager server uses to determine how to map your system data to the SQL data type required by your RDBMS. For example, the database dictionary data type "number" maps to FLOAT in Microsoft SQL server and maps to NUMBER in Oracle. For a complete listing of how the database dictionary data types map to RDBMS data types, use the Database Manager utility to view the records in the sqldbinfo table.

Caution Do not change the data type mappings in the sqldbinfo table unless directed to do so by customer support. Changing these values can result in data loss or corruption.

Note Starting with Service Manager 9.40, the sqlserver record in the sqldbinfo table contains a read-only flag named Use Unicode Data Type to indicate if Microsoft SQL Server uses the Unicode data type (nvarchar).

The Service Manager server does not automatically validate field data to ensure that it matches the data type defined in the database dictionary record. If you want to validate your data prior to submitting it to the RDBMS, you have several options:

  • You can customize your forms so that the input field matches the data type. By default, Forms Designer uses the data type to determine the input object to use when you drag a field on to a form.
  • You can create a data policy record for the table and then create validation rules for each field in the table you want to validate.
  • You can create a format control record for the table and then create validation rules for each particular record state such as add, update, delete, or initial display.

Once you save data to Service Manager, the RDBMS controls the data type and any validation. If you change the data type on the RDBMS side, Service Manager can update the database dictionary record to match the RDBMS data type with a system restart of the server. We recommend you control data types from the database dictionary so you do not have to restart your system every time you make a change to a field.

Service Manager data types also allow you to take advantage of the following advanced features.

  • You can move your system data from one RDBMS type to another
  • You can map system data to multiple RDBMS types at the same time

You can use the SQL to SQL Conversion utility to enable one or both of these advanced features. The SQL to SQL Conversion utility allows you to move or map data on a table by table basis. It also allows you to customize how you want data in array fields to be mapped in the new RDBMS as well as offering several export options such as creating DDL or creating duplicate tables.

Related topics

Automatic update of database dictionary records
Database dictionary
Splitting SQL tables
Database dictionary records
SQL data type for localizable fields

Add a field to an existing database dictionary record
Create an active database dictionary record
Create an inactive database dictionary record
Update the data type of a database dictionary field

Database dictionary data types