Prepare to remap the array

Before you remap your array, you need to determine the answers to following questions.

• What is the next free alias table for the database dictionary record?
• What SQL data type will the array sub-element require?
• What length should your character fields have?

Determine the next free alias table

To remap the array as a multi-row array table, you must first determine what the next free alias table is for the database dictionary record. You can see what alias tables are already in use from the SQL Tables tab of the database dictionary record.

In this case, the incidents database dictionary already has five existing alias tables, so the next free alias table is a6. We shall create a new alias table called a6 that will store our remapped array data.

Determine the data type of the array sub-element

Next, determine what data type the array sub-element will require in SQL terms. You can use the Service Managersqldbinfo table as a guideline for mapping a Service Manager data type to a SQL data type. For example, the assignment.groups sub-element uses the character data type. According to the sqldbinfo table, a character data type maps to a varchar data type on Microsoft SQL server.

Verify that your SQL data type mapping meets your data needs. Ideally, you want to identify the source of your data and match its SQL mapping. For example, the source of the assignment.groups field in the operator table is the name field in the assignment table. When you look at the database dictionary for the source data, you see that the source data is mapped as a varchar(60) data type.

In this example, we shall remap the array sub-element to use the varchar(60) data type to match the source data type.

Determine the length of character fields

If you want to remap your array sub-element with a simple character data type, you must choose a length for the character field. Remember that multi-row array tables cannot use character large object data types. There are two methods to determine an appropriate length for your character data.

• Match the length of the data source (preferred)
• Review your existing data and add a padding

Ideally, you want to identify the source of your data and match its SQL mapping. For example, the source of the assignment.groups field is the name field of the assignment table. Since the name field is mapped as a varchar(60) data type, we will map our array to the same length of varchar(60).

You can also use your existing data to determine the proper size of your character fields. For example, if you review the source data of the assignment.groups field, you will see text strings from 20 to 40 character long.

You could therefore remap this data as a varchar(40) data type and likely not lose any data. However, you may want to provide the option to store assignment groups more than 40 characters long without having to store the array elements in separate rows. In this case, you may want to use a larger value such as varchar(50) or varchar(60).

When choosing a length value, you want to avoid selecting a SQL length shorter than the maximum length of your existing data. The system will wrap any existing data that exceeds the SQL length of the character field into a new row. Wrapping data into a new row can result in data corruption and broken queries. For example, if you select a length of varchar(10), then you any existing assignment group longer than ten characters will wrap into two or more rows. Using this mapping, the system would change an existing assignment group of "Application" to have one array element row with the value "Applicatio" and another array element row with the value "n". Any RDBMS query searching for the value "Application" would fail because there is no longer one element with this value.