[opencms-dev] OpenCMS 7.0.5 and DB2 support

[Peter Long]

A quick update. DB2 returns SQL0418N with these two alternate queries
unless you change them as follows:
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(CAST(? AS VARCHAR(1024)),1,
LENGTH(CMS_${PROJECT}_RESOURCE_RELATIONS.RELATION_TARGET_PATH),OCTETS)

and

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,
CAST(? AS VARCHAR(1024)), 1, OCTETS) = 1

DB2 does not seem to like the parameter to be untyped in these
queries. The CAST tells it the type of the parameter at prepare time.

Peter Long

On Wed, Feb 4, 2009 at 11:53 AM, Peter Long <opencms at petelong.com> wrote:
> 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
>