powered by Jive Software

mucConversationLog LONG VARCHAR rejected by DB2 V8

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.

Bob,

Most likely, CLOB will work. I’'ve created JM-144 to track these DB2 script issues.

Regards,

Matt

Just wanted to let you know I am up and running with DB2 V8. I’'ll let you know if any issues arise.