Array mapping options

In a database dictionary record, an arrays contain one or more elements of a single data type or a structure field. If an array contains a structure element it is referred to as an array of structure. Arrays of structure contain one or more fields that can be of any data type.

You can map your array data in the following five formats.

• Field in main table
• Field in alias table (Deprecated)
• BLOB in main table
• BLOB in alias table (Deprecated)
• Multi-row array table

The array mapping format you choose determines the following factors about your array data.

• Can your RDBMS directly query your array data?
• Can third-party tools report against your array data?
• Is your array data stored with other fields in one or more main tables?
• Is your array data stored separately in one or more dedicated alias tables?
• Can your array support complex arrays of structure?
• How efficiently does your system retrieve and write data to your array?

Field in main table

The field in main table format maps a Service Manager array field as a character large object (CLOB) within a main table. This array format is good for situations where the following conditions apply:

• You want to be able to use third-party tools to run reports against your array data
• You want the option to store very large array elements
• You do not want to remap an existing array

The following table summarize the advantages of using this array mapping format.

Advantages

Advantage	Description
Third-party tools can report against the array	Service Manager stores array data as a character string that third-party reporting tools can read. The system separates each element of the array with a \n character delimiter.
The array can store very large array elements	Since the array is mapped to a character large object column, the column can occupy a large amount of space. Most RDBMS vendors support at least 2 gigabytes of large object data in such a column.
The array has access to other fields in the main table	There is a greater probability that other fields reside in the same main table as the array. Having all or most of your fields in a main table reduces the number of SELECT and JOIN statements your system has to make to fetch a record. The less RDBMS traffic required, the better performance your queries and reports will have.
There is no need to remap if you use the default array mapping format	The field in main table format is Service Manager's out-of-box array mapping format. Unless someone has changed the default array mapping format, all your array data will automatically be in this format.

The following table summarize the disadvantages of using this array mapping format.

Disadvantages

Disadvantage	Description
The RDBMS cannot directly query the array	Most RDBMS vendors cannot query a column containing a reference to character large object data.
Any query against the array is inefficient	The RDBMS cannot directly query this array format because the column is mapped to a large object data type. Instead, the RDBMS must scan the table to return all rows of the large object column, and then forward the results to Service Manager for evaluation.
The array shares table space with other fields in the table	The array does not have a dedicated table in which to store data. Each RDBMS vendor has its own rules to determine the maximum number of columns allowed in a table, the maximum size of any row, and the maximum table size. Any array in a main table will consume some of these RDBMS resources. If an array requires more resources than any one main table can provide, Service Managerwill move the array to another main table.

To set up an array in a field in main table mapping, you must create the following mappings in a database dictionary record.

Database dictionary element type	RDBMS mapping required
Array data type	Not mapped
Sub-field of the array	Mapped as a CLOB column in a main table
RC flag	False

Field in alias table (Deprecated)

The field in alias table format maps a Service Manager array field as a character large object (CLOB) within an alias table. This array format is deprecated. In earlier versions, this format was intended to support the following features:

• Your RDBMS vendor could only support one "long" data type per table (This is still a restriction on Oracle databases but can be worked around by using CLOB and BLOB data types instead.)
• You wanted the option to store very large array elements
• You wanted to be able to use third-party tools to run reports against your array data

You should use one of the following array formats instead of the field in alias table format.

• Field in main table
• BLOB in main table
• Multi-row array table

BLOB in main table

The BLOB in main table format maps a Service Manager array field as a binary large object (BLOB) within a main table. This array format is good for situations where the following conditions apply:

• Your array data is in a binary format (such as attachment data or RAD language expressions)
• You want the option to store very large array elements
• You want to support complex arrays of structure
• You do not need to use third-party tools to run reports against your array data

The following table summarize the advantages of using this array mapping format.

Advantages

Advantage	Description
Preservation of any native binary data	The RDBMS does not have to do any code page translations to store binary data in the array. Since there is no translation from a binary format to a character-based format, there is no chance that the translation will result in data corruption such as high-bit characters becoming question marks.
The array can store very large array elements	Since the array is mapped to a binary large object column, the column can occupy a large amount of space. Most RDBMS vendors support at least 2 gigabytes of large object data in such a column.
The array has access to other fields in the main table	There is a greater probability that other fields reside in the same main table as the array. Having all or most of your fields in a main table reduces the number of SELECT and JOIN statements your system has to make to fetch a record. The less RDBMS traffic required, the better performance your queries and reports will have.
Support for complex arrays of structure	This array format supports complex arrays of structure such as an array of structure within an array of structure.

The following table summarize the disadvantages of using this array mapping format.

Disadvantages

Disadvantage	Description
Third-party tools cannot report against the array	Service Manager stores array data in a proprietary binary format that third-party reporting tools cannot read.
The RDBMS cannot directly query the array	Most RDBMS vendors cannot query a column containing a reference to character large object data.
Any query against the array is inefficient	The RDBMS cannot directly query this array format because the column is mapped to a large object data type. Instead, the RDBMS must scan the table to return all rows of the large object column, and then forward the results to Service Manager for evaluation.

To set up an array in a field in main table mapping, you must create the following mappings in a database dictionary record.

Database dictionary element type	RDBMS mapping required
Array data type	Mapped as a BLOB column in a main table
Sub-field of the array	Not mapped
RC flag	True

BLOB in alias table (Deprecated)

The BLOB in alias table format maps a Service Manager array field as a binary large object (BLOB) within an alias table. This array format is deprecated. In earlier versions, this format was intended to support the following features:

• Your RDBMS vendor could only support one "long" data type per table (This is still a restriction on Oracle databases but can be worked around by using CLOB and BLOB data types instead.)
• You want the option to store very large array elements
• You want to support complex arrays of structure
• You do not need to use third-party tools to run reports against your array data

You should use one of the following array formats instead of the BLOB in alias table format.

• Field in main table
• BLOB in main table
• Multi-row array table

Multi-row array table

The multi-row array table format maps a Service Manager array field as a character string within one or more rows in an alias table. This array format is good for situations where the following conditions apply:

• You want to be able to run RDBMS queries on your array data
• You want to be able to use third-party tools to run reports against your array data
• You want the option to store very large array elements
• You want to support simple arrays of structure

The following table summarize the advantages of using this array mapping format.

Advantages

Advantage	Description
The RDBMS can directly query the array	In this array format, Service Manager stores each array element as a simple character string in one or more rows. This format allows RDBMS queries to read each individual array element without needing to forward the query to Service Manager for evaluation.
Third-party tools can report against the array	Service Manager stores each element of the array in its own separate row. Third-party reporting tools can read the row and extract the element value.
There is no risk of data truncation for new array elements	Each array element occupies as many rows as necessary to store the full element. This means that the length of the array element is not limited by the length of the column. Any array element that exceeds the column length for one row continues onto the next row. This feature prevents Service Manager from truncating array element data.
Support for simple arrays of structure	This array format supports simple arrays of structure such as an array of structure within an array of structure.

The following table summarize the disadvantages of using this array mapping format.

Disadvantages

Disadvantage	Description
Service Manager must maintain a separate table	In order to fetch a record, the system must make SELECT and JOIN statements between the main and alias tables. These statements require more RDBMS traffic and can lower your RDBMS performance.
The array uses more table rows	Each array element will require one or more table rows. Each time you fetch an array record, the system must select multiple rows from an alias table. The more rows the system has to select, the more costly your record retrieval is in terms of RDBMS performance.

To set up an array in a field in main table mapping, you must create the following mappings in a database dictionary record.

Database dictionary element type	RDBMS mapping required
Array data type	Mapped to an alias table
Sub-field of the array	Mapped as a character column in a main table
RC flag	False