A problem that is sometimes seen with ARRAY/VARRAY type columns is unexpected row length errors when dealing with UTF-8 character data. This problem only occurs when the ARRAY/VARRAY type is defined with a CHARACTER or VARCHAR element type. For example, suppose you create table arrayc4 that includes an ARRAY data type column. You insert data into the table that contains data imported from a UTF-8 client session character set. No problems occur when you insert these rows into the table. But then when you attempt to select the entire row, again with a UTF-8 session character set, the system returns the following error.
SELECT * FROM arrayc4; *** Failure 3577 Row size or Sort Key size overflow. Statement# 1, Info =0 *** Total elapsed time was 1 second.
This error does not occur in all cases. It depends on how large the arrays are and how much internal storage they require.
To summarize, the impact of this is that you can create a table, insert data into it, and then have client utilities such as FastExport fail when they attempt to export all of the columns in the table to a client system.
Similar problems can sometimes be seen in other situations in Vantage with both character and non-character data. It is not exclusive to UTF-8 character data.
- You can modify the default export width for the user who is experiencing this problem. The new export width must be one that does not increase the row size from its server storage size when it exports data to a client.
A possible problem with this workaround is that the export width applies to each array element individually, so elements that require expansion might be truncated even though other elements that contract are present so the complete row size would be correct.
- There are two approaches to working around the problem for character string data.
- Reduce the number of columns the request generates.
This includes reducing or eliminating the number of columns that Vantage generates internally, but does not return to the user. An example is the data rows to which the system appends the BYNET sort key. While Vantage does append the sort key to the row physically, generating a temporary column that exists in a spool, that sort key is no longer appended to the row when the system returns the sorted data to the requestor.
If appending the BYNET sort key to a row causes a row length error to occur, you could try to sort by the first n characters of the character string instead of sorting by all of the characters in the string. This applies to the transformed format for the array, which is VARCHAR.
- Reduce the size of some or all of the columns being generated.
Whether this is possible or not depends on how strictly your physical database design must follow your logical design. If experience indicates that smaller data types (such as SMALLINT in place of INTEGER) or narrower character data type widths can be used, then experiment with that to reduce or eliminate row size errors.
- Reduce the number of columns the request generates.
- For non-character data, the optimal solution to row size errors is to reduce the number of columns returned by queries that are experiencing problems with unexpected row size errors.
It is not possible to suggest more explicit workarounds to this problem without knowing the details of your database schema.