In messenger_db2.sql, table mucConversationLog defines a column ‘‘body’’ as LONG VARCHAR - this data type is not available under DB2 V8.1.2. Since DB2 defines a LONG VARCHAR as a string longer up to 32700 bytes in length, I opted to change ‘‘body’’ to a CLOB (character large object max 2GB), as opposed to a maximum VARCHAR (32672).
Will this work? I have included an excerpt from the DB2 docs that speak about varying length strings and it includes some warning about the way the CLOB field can be used.
-Bob
FROM THE DB2 DOCS-----
Varying-length character strings:
There are three types of varying-length character strings:
-
A VARCHAR value can be up to 32 672 bytes long.
-
A LONG VARCHAR value can be up to 32 700 bytes long.
-
A CLOB (character large object) value can be up to 2 gigabytes (2 147 483 647 bytes) long. A CLOB is used to store large SBCS or mixed (SBCS and MBCS) character-based data (such as documents written with a single character set) and, therefore, has an SBCS or mixed code page associated with it.
Special restrictions apply to expressions resulting in a LONG VARCHAR or CLOB data type, and to structured type columns; such expressions and columns are not permitted in:
-
A SELECT list preceded by the DISTINCT clause
-
A GROUP BY clause
-
An ORDER BY clause
-
A column function with the DISTINCT clause
-
A subselect of a set operator other than UNION ALL
-
A basic, quantified, BETWEEN, or IN predicate
-
A column function
-
VARGRAPHIC, TRANSLATE, and datetime scalar functions
-
The pattern operand in a LIKE predicate, or the search string operand in a POSSTR function
-
The string representation of a datetime value.
In addition to the restrictions listed above, expressions resulting in LONG VARCHAR or CLOB data types or structured type columns are not permitted in:
-
A basic, quantified, BETWEEN, or IN predicate
-
A column function
-
VARGRAPHIC, TRANSLATE, and datetime scalar functions
-
The pattern operand in a LIKE predicate or the search string operand in the POSSTR function
-
The string representation of a datetime value.
The functions in the SYSFUN schema taking a VARCHAR as an argument will not accept VARCHARs greater than 4 000 bytes long as an argument. However, many of these functions also have an alternative signature accepting a CLOB(1M). For these functions, the user may explicitly cast the greater than 4 000 VARCHAR strings into CLOBs and then recast the result back into VARCHARs of desired length.
NUL-terminated character strings found in C are handled differently, depending on the standards level of the precompile option.
Each character string is further defined as one of:
Bit data
Data that is not associated with a code page.
Single-byte character set (SBCS) data
Data in which every character is represented by a single byte.
Mixed data
Data that may contain a mixture of characters from a single-byte character set and a multi-byte character set (MBCS).
SBCS data is supported only in an SBCS database. Mixed data is only supported in an MBCS database.