[opencms-dev] OpenCMS 7.0.5 and DB2 support

[Peter Long]

Hi all,

I have been trying to get OpenCMS 7.0.5 to work using DB2 9.5.3
running on AIX. I am new to OpenCMS so it has been quite a learning
experience. The first problem I have run into is that one of the
queries in org.opencms.db.generic/query.properties causes a SQL error
on DB2. The query in question is:

C_READ_RESOURCE_OUS=\
SELECT DISTINCT \
	${C_RELATIONS_SELECT_ATTRIBS} \
FROM \
	CMS_${PROJECT}_RESOURCE_RELATIONS \
WHERE \
    CMS_${PROJECT}_RESOURCE_RELATIONS.RELATION_TYPE = ? \
    AND ? LIKE CONCAT(CMS_${PROJECT}_RESOURCE_RELATIONS.RELATION_TARGET_PATH,
'%')

The query resulted in a SQL0132N error code from DB2, which results
because according to DB2 the LIKE predicate cannot have a column name
as the pattern-expression (right site/second parameter). According to
a SQL expert I spoke to, using a column in this manner is a non-ANSI
extension that MySQL and PostgreSQL support but DB2 does not.

The solution that we are using is to change the query to the following
in org.opencms.db.db2/query.properties:

C_READ_RESOURCE_OUS=\
SELECT DISTINCT \
	${C_RELATIONS_SELECT_ATTRIBS} \
FROM \
	CMS_${PROJECT}_RESOURCE_RELATIONS \
WHERE \
    CMS_${PROJECT}_RESOURCE_RELATIONS.RELATION_TYPE = ? \
    AND CMS_${PROJECT}_RESOURCE_RELATIONS.RELATION_TARGET_PATH = \
    SUBSTRING(?,1,
LENGTH(CMS_${PROJECT}_RESOURCE_RELATIONS.RELATION_TARGET_PATH),OCTETS)

and alternative would be:

C_READ_RESOURCE_OUS=\
SELECT DISTINCT \
	${C_RELATIONS_SELECT_ATTRIBS} \
FROM \
	CMS_${PROJECT}_RESOURCE_RELATIONS \
WHERE \
    CMS_${PROJECT}_RESOURCE_RELATIONS.RELATION_TYPE = ? \
    AND LOCATE(CMS_${PROJECT}_RESOURCE_RELATIONS.RELATION_TARGET_PATH,
?, 1, OCTETS) = 1

The first replaces the LIKE with a comparison of the the
RELATION_TARGET_PATH with a substring of the parameter where the
substring length is the RELATION_TARGET_PATH length. The comparison is
done in bytes not characters. I think that should be fine even if the
strings are UTF-8 or UTF-16 encoded.

The second query does something similar but looks neater. It locates
the string from RELATION_TARGET_PATH in the parameter string and
returns the starting position in the parameter where the string can be
found. If the result is 1 then RELATION_TARGET_PATH is a prefix of the
parameter.

Performance tests show the first example is about 1% faster than the second one.

Does anyone see a problem with using either of these queries as
replacements? I have just been guessing at the original meaning of the
query so I may have it all wrong. Also if someone has a better
alternative I would be interested to see it.

Lastly, given that this SQL issue exists I assume DB2 support is not
100%. Is that correct. If so are there other queries that will need
massaging?

Peter Long