[opencms-dev] OpenCMS 7.0.5 and DB2 support

[Georgi Naplatanov]

Hello, Peter. I'm developer of new JPA driver for OpenCms. At this time
this driver use OpenJPA implementation.

I tested this driver with Apache Derby, HSQLDB, H2, MS SQL, PostgreSQL
and MySQL.

It will nice to test JPA driver with DB2.

You can download patched OpenCms 7.0.5 from
http://sourceforge.net/projects/modulesforopenc/, because at this time
JPA driver is not part of official release.

Best regards
Georgi

Peter Long 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
> 
> _______________________________________________
> This mail is sent to you from the opencms-dev mailing list
> To change your list options, or to unsubscribe from the list, please visit
> http://lists.opencms.org/mailman/listinfo/opencms-dev