As per our announcement, the last major version of Confluence to support DB2 was Confluence 4.2. Confluence 4.3 and later will not support DB2.

Therefore this issue will not be fixed. See my comment above for a workaround in existing instances.

Adding a generated column and index manually can resolve this problem on DB2.

See the instructions on this page, under "DB2 for Linux or Windows": Creating a Lowercase Page Title Index.

Adding the latest version of the patch with removed lower() function calls. This one removes the calls also from all the Java classes (these were giving us a hard time in production as well).

adding a patch against confluence 3.2 that removes all lower() calls and relies only on mysql case-insensitive collation

Confluence 3.0 includes some improved caching that alleviates this problem a lot for page lookups. We've also documented the instructions for creating lowercase indexes manually in several databases that support it (including DB2, thanks for the help – Patrick):

http://confluence.atlassian.com/display/DOC/Creating+a+Lowercase+Page+Title+Index

This issue will remain open, but should be much less of a problem if you can follow the instructions on that page.

rebased version compatible with confluence 2.10.1

Thanks Don. I'm not happy about the data redundancy, but if it means that you have only one db schema to support that is fast for everyone, then I think it's definitely worth it.

The benefit for us is that you'll do all your QA with the same schema as we use, minimizing the chance of something breaking for us in the future because of this change.

/i

I completely agree that the use of lower in all these places is going to hurt performance for some databases and that Igor's approach is the most pragmatic one for us to take in fixing this. (This is despite the obvious ickiness of replicated data). I only suggested using the MySQL insensitivity because if we don't manage to prioritise this bug, then you're going to need to maintain whatever workaround you do, and I'd think that a simple workaround would be easier to maintain than one that is database agnostic.

Don,

We took the risk and we deployed the patch to production yesterday. So far so good

The data was manually migrated using SQL, the queries should be in the comment section of the patch.

Thanks for the cluster update. I thought that just a simple serialization was used to push the objects over the wire to the second node, but I wasn't sure.

I'd prefer the solution proposed in the patch over changing the hibernate queries and exploiting MySQL insensitivity because the solution is the smallest common denominator for all of your supported databases, which would make it easier for you to test and support it.

Using this solution you could easily and safely provide this enhancements to all of your customers regardless of their database via an upgrade task (I can image that creating an upgrade task that would create custom indexes for all the supported databases would be much trickier).

Hello,
I want to reiterate
1. DB2 9 for z/OS supports index on column expressions.
2. DB2 LUW supports generated columns with index on generated columns - where the database maintains the generated column and will automatically rewrite queries to use the generated column. In DB2 LUW 9.5, generated columns can be created as hidden columns (not returned for SELECT *).

So this problem is solvable in current releases of DB2 - but it requires user action to solve it.

This issue is a major problem for any site that has large tables. Basically, any customer that's going to have a large wiki installation - this issue is a show-stopper. The lower is used all over the place. Not just on content. The biggest issue is on the SPACES table - since it's used to look up content, blog posts, etc. So it's either a unique lookup on SPACES, or a table space scan to find 1 row.

We also saw this issue on LINKS.
select outgoingli0_.LINKID as LINKID, outgoingli0_.DESTPAGETITLE as DESTPAGE2_
, outgoingli0_.DESTSPACEKEY as DESTSPAC3_, outgoingli0_.CONTENTID as CONTENTID
, outgoingli0_.CREATOR as CREATOR, outgoingli0_.CREATIONDATE as CREATION6_
, outgoingli0_.LASTMODIFIER as LASTMODI7_, outgoingli0_.LASTMODDATE as LASTMODD8_
from LINKS outgoingli0_ CARD 1457006 NPAGES 57402
where (lower(outgoingli0_.DESTSPACEKEY)=? ) COLCARD 11320
and(lower(outgoingli0_.DESTPAGETITLE)=? ) COLCARD 356000

BLOGPOST Title also has the issue.

select blogpost0_.CONTENTID as CONTENTID, blogpost0_.SPACEID as SPACEID
, blogpost0_.TITLE as TITLE, blogpost0_.VERSION as VERSION
, blogpost0_.CREATOR as CREATOR, blogpost0_.CREATIONDATE as CREATION6_
, blogpost0_.LASTMODIFIER as LASTMODI7_, blogpost0_.LASTMODDATE as LASTMODD8_
, blogpost0_.VERSIONCOMMENT as VERSIONC9_, blogpost0_.PREVVER as PREVVER
, blogpost0_.CONTENT_STATUS as CONTENT11_
from CONTENT blogpost0_
, SPACES space1_
where blogpost0_.CONTENTTYPE='BLOGPOST'
and ((lower(space1_.SPACEKEY)=?
and blogpost0_.SPACEID=space1_.SPACEID)
and(lower(blogpost0_.TITLE)=? )
and(blogpost0_.CREATIONDATE>=? )
and(blogpost0_.CREATIONDATE<? )
and(blogpost0_.PREVVER is null )
and(blogpost0_.CONTENT_STATUS='current' ))

CONTENT_LABEL table.
select distinct label0_.LABELID as LABELID
, label0_.NAME as NAME, label0_.OWNER as OWNER
, label0_.NAMESPACE as NAMESPACE, label0_.CREATIONDATE as CREATION5_
, label0_.LASTMODDATE as LASTMODD6_
from LABEL label0_ CARD 22253 NPAGES 424
, CONTENT_LABEL labelling1_ CARD 73483 NPAGES 1711
where (lower(labelling1_.SPACEKEY)=? ) COLCARD 7813
AND(labelling1_.LABELID=label0_.LABELID ) COLCARD 22253/22253
AND(label0_.NAMESPACE=? ) COLCARD 4
order by label0_.NAME

ATTACHMENTS table.
SELECT attachmentid, title, contenttype, creator, creationdate
, lastmodifier, lastmoddate, filesize, attachment_comment
, attversion, prevver
FROM WIKI_RW.attachments CARD 403449
where pageid = ? COLCARD 94288
and lower(title) = ? COLCARD 253952
order by attversion desc with UR

Our wiki has a lot of content, a lot of attachements, a lot of spaces. Any big site - this issue is an absolute performance killer . The wiki quickly becomes useless.