Remap array fields to an alias table

To improve your query performance, you can move an existing array field or an array of structure field to an alias table (for example, a1).

  • The " field in alias" and "BLOB in alias" mapping formats are deprecated. The multi-row array mapping format is recommended.
  • You can revert your remapping changes by moving the field back to the main table and restoring the original SQL configuration.

Array

You can remap an array field to an alias table using the multi-row array mapping format.

To remap an array field using the multi-row array mapping format, follow these steps:

  1. Log in to the Service Manager Windows client as a system administrator.
  2. Type dbdict in the command line, and press Enter.
  3. Open the dbdict that you want to update by performing a search.
  4. Select the Fields tab.
  5. Select the field of the array type, and click Edit Field/Key.
  6. Enter a value in the fields as described in the following table.

    Note As an example, the following table lists the SQL Type values allowed for an array of characters. For other types of arrays, the allowed SQL Type values may differ.

    Mapping format SQL Name SQL Type SQL Table SQL RC
    Multi-row array MULTI_ROW_ARRAY TEXT, varchar, char, nvarchar, or nchar a1 False
  7. Click OK.

Array of structure

To remap an array of structure using the multi-row array mapping format, follow these steps:

  1. Log in to the Service Manager Windows client as a system administrator.
  2. Type dbdict in the command line, and press Enter.
  3. Open the dbdict that you want to update by performing a search.
  4. Select the Fields tab.
  5. In the array of structure, select the top level field (whose type is array), change its SQL Table field value to a1 and leave the rest of the fields empty.

  6. Select the middle level field (whose type is structure), and make sure its SQL Name, SQL Type, and SQL Table fields are empty.

  7. Select each element under the structure, and enter a value in the fields as described in the following table.

    Note As an example, the following table lists the SQL Type values allowed for a structure element of the character data type. For elements of other data types, the allowed SQL Type values may differ.

    SQL Name SQL Type SQL Table SQL RC
    <FIELD_NAME_1> TEXT, varchar, char, nvarchar, or nchar a1 False
  8. Click OK.

Related concepts

Existing array mapping
Existing array query performance
Prepare to remap the array