If you are using hibernate to generate a database for you via HBM2DDL, and are using long strings in your mapping files, Hibernate has to make a choice between generating a varchar and a clob.

I have an issue using JBPM against DB2 for iSeries, in that there is a limit of approx 32k for the total length of all non LOB columns retrieved in a SELECT statement, and JBPM is generating SQL via hibernate that blows this limit.

The reason this occurs is that the JBPM mapping files have loads of strings defined with lengths of 4000. As DB2 for iSeries allows varchars up to 32k, the schema generated by hibernate contains loads of 4000 long varchars rather than CLOBS.

When you then do any sort of query, this quickly blows the DB2 32k retrieved in any one result set row limit.

To get around this we changed the mapping files to have the lengths of the strings greater than the varchar limit of 32k, so that when the schema creation ran all these long string fields were clobs not varchars.

Question: how does hibernate know how to generate a CLOB rather than a VARCHAR, and is there anyway we can control this (in other words, if we could, we could have just said, make all strings of 4000 or longer clobs, and therefore would not have had to mess with the various mapping files).

Thanks for any input on this.

I am pretty sure you can set the column type to "text" and the generation utility will create a CLOB data type in the DB. I know it creates a TEXT for SQL Server and supposedly it does the same for other DBs that use CLOB.

Thanks, I realised that. The point is in the JBPM mapping files fields are mapped like this:

<property name="message" column="MESSAGE_" length="4000"/>

i.e. no type sepcified (gleamed via introspsection), and resolving to java.lang.String, together with a column name and a length.

I do not want to modify the JBPM source code unless I really have to.

My point to summarise is this:

If hibernate ends up generating really complicated SQL, which ends up blowing the DB2 limit, then is there any way of getting hibernate to generate CLOBS at a lower cut off point then whatever the current default is, so that complex queries end up returning smaller total column widths, thereby fitting inside the DB2 32k limit.