IBM Support

Database Truncation Exceptions in the Logs Caused by Character Sets and Locales

Technote (troubleshooting)


Problem(Abstract)

When a Process Engine's database is configured with a character set such that the database representation of a character requires more bytes-per-character than the in-memory representation of that character, workflows may receive unexpected database exceptions.

Symptom

Unexpected "Error writing Log record" exceptions appear in the Process Engine elogs with an accompanying “data doesn’t fit in the column” exception from the database (e.g., ORA-12899)


Cause

The cause of this issue is a result of having a mismatch in the character sets defined in the locales of the server operating systems (PE and Database) and the character set defined in the database itself. If the settings between the OS locale character sets and the database character set are different, the database will convert the incoming data to match the character set and byte size of the character set that is configured for the database.

The Process Engine currently supports a byte based field definition process and uses the system locale for its handling of characters. When the database needs to convert the incoming characters (depending on the locale character set) to database character set, these characters could be expanded to a number of bytes that would overflow the defined field size in the process engine's data base.

For example, let's assume we have a PE environment where the PE server is configured with the Western European character set WE8ISO8859-P15 in the OS locale, while the database is configured with the UTF-8 character set.

If the Process Engine is going to write an "Ä" (umlaut A), it will send the character to the database as one byte. The database, however, will convert the "Ä" into two bytes as it stores the data in the database because it is configured with UTF-8.

Because of the misconfiguration of the character sets, and depending upon the data being stored, the database may need to expand the number of bytes being stored. If the expanded number of bytes being stored in the database exceeds the declared size of the field in the database, the database will then return an error (exception). In this example, if the declared size of the database field was one byte, the attempt to store the one byte "Ä" would result in an expansion to two bytes in the database, and that would result in an exception.


Diagnosing the problem

If using Oracle, you will see Process Engine elog records like these:

2009/11/11 12:04:25.655 121,1,12899 <fnsw> VW/Process (308) ...
VW (vwisi) (Region=2): Error writing Log record (e=120, tm=1257930266, sq=72820)

2009/11/11 12:04:25.662 121,1,12899 <fnsw> VW/Process (308) ...
VW (vwisi) (Region=2): ISI Exception processing work object number F0CB01A3CEA034DFBC3B031E0BFC6FA1 : [Err=79013263]

If using DB2, you will see Process Engine elog records similar to these:

2010/03/12 13:58:41.176 121,9,302 <fnsw> VW/Process (356574) ... [SERIOUS]
Error in GDBD_exec: SQLExecute, STMT 65537 (&200c7d80) (../src/GDBD.c, VERSION 4
.1.1.0, @3394).
SQLSTATE = 22001, NativeError = -302,
ErrorMsg = '[IBM][CLI Driver][DB2/AIX64] SQL0302N The value of a host variable
in the EXECUTE or OPEN statement is too large for its corresponding use. SQLSTATE=22001

2010/03/12 13:58:41.176 121,9,302 <fnsw> VW/Process (356574) ...
VW (VWKs.0) (Region=1): Error writing Log record (e=165, tm=1268430725, sq=64)

If using Microsoft SQL Server, you will see Process Engine elog records similar to the above examples.


Resolving the problem

Best practice to avoid these database exceptions is to configure the locale (or code page) of the Process Engine Server to match the character set of the database and the locale or code page of the server that hosts the database. These settings should be chosen so that the database never requires more bytes (on disk) to store data than the PE's "in memory" representation of that same data.

For details concerning setting the character sets of the servers and the databases, please read following documents:

For PE 4.5.x, please read the "PE 4.5.x Non-English Support Guide"
ftp://public.dhe.ibm.com/software/data/cm/filenet/docs/p8doc/45x/p8_45x_non_english_install.pdf

And the "Plan and Prepare Your Environment for Filenet P8" document:
ftp://public.dhe.ibm.com/software/data/cm/filenet/docs/p8doc/451/plan_and_prepare.pdf

For PE 4.0.x, please read the "Installing Non-English Requirements Technical Notice"
ftp://public.dhe.ibm.com/software/data/cm/filenet/docs/p8doc/40x/p8_40x_non_english_install.pdf

For PE 3.5.x, please read the "Installation Guide for Non-English Environments"
ftp://public.dhe.ibm.com/software/data/cm/filenet/docs/p8doc/35x/p8_351_non_english_install.pdf

In all cases, make sure that you choose appropriate character sets for the Process Engine Server, the Application Engine Server, the Content Engine Server, the database server(s), and the databases themselves.

Make sure the character sets you choose are size-compatible so that you don’t receive truncation errors from the database on the Process Engine Server.

And, on a related note, make sure the character sets or locales of all servers and the character sets of all databases can handle the characters you intend to use. For example, do not use a basic US 7-bit ASCII character set on any server or for the database character set if you intend to store non-English characters such as "o with an umlaut" or "n with a tilde."

If any server or database is not properly configured to handle the characters you intend to use, you will see exceptions or you will see the unsupported characters being translated to 0x1a characters (the standard substitution character). And, the 0x1a characters in the data will result in downstream exceptions when the data is used in XML documents.

Document information

More support for: Case Foundation
Process Engine

Software version: 3.5.0, 4.0.3, 4.5.0, 4.5.1

Operating system(s): AIX, HP-UX, Solaris, Windows

Reference #: 1423858

Modified date: 13 May 2010


Translate this page: