Configure JCR collation support

View the steps to set up JCR collation to work with your IBM DB2 Universal Databaseā„¢ Enterprise Server Edition database. JCR collation is recommended when the language locales of your users do not natively collate correctly in the DB2 database and when language locale correct ordering is important.


Prerequisites

Installing DB2

Create users

Creating remote databases

Modifying database properties

JCR collation is not supported on IBM DB2 Universal Database for z/OS, IBM DB2 for iSeries, and Apache Derby. This means that if the JCR collation function sortkey() is invoked on one of these database platforms, the collation specified in the second argument to the function has no effect. The result of the function is the same as its first argument. The behavior is governed by the underlying database.

  1. Stop the WebSphere Portal server:

    • ./stopServer.sh WebSphere_Portal -username admin_userid -password admin_password

  2. If using a remote DB2 server, copy the following files from the WebSphere Portal server to a temporary directory on the remote DB2 server:

    PortalServer_root/jcr/prereq.jcr/config/collation.jar
    
    

    PortalServer_root/jcr/prereq.jcr/config/registerCollationUDFTemplate.sql
    
    

  3. Set up collation on the database where the JCR domain is located.

    1. Change to the directory db2_instance_owner_home/sqllib/function.

    2. Execute the command:

      Remote DB2:

      db2_instance_owner_home/sqllib/java/jdk/bin/jar -xvf temporary location/collation.jar icm/CollationUDF.class

      Local DB2: db2_instance_owner_home/sqllib/java/jdk/bin/jar -xvf PortalServer_root/jcr/prereq.jcr/config/collation.jar icm/CollationUDF.class

    3. If using a local DB2, change to the directory profile_root/PortalServer/jcr/config.

      If using a remote DB2, change to the temporary directory where you copied the files in a previous step.

    4. Open the file registerCollationUDFTemplate.sql and change all SCHEMA references to the JCR schema; for example, JCR.

      The value set for SCHEMA should match the value set for the jcr.DbSchema property.

      You specify jcr.DbSchema in the configuration file wkplc_comp.properties when you modify database properties. .

    5. Connect to the JCR database by running db2 connect to <jcrdb> user <userid> using <password>.

    6. Execute the script by running the command.

      Remote DB2:

      db2 -tvf temporary location/registerCollationUDFTemplate.sql

      Local DB2:

      db2 -tvf profile_root/PortalServer/jcr/config/registerCollationUDFTemplate.sql

    7. Disconnect from the JCR database.

    8. Restart the DB2 instance.

  4. Verify that the UDF is registered properly.

    1. Log in as the db2instanceID. Open a DB2 terminal window, and type db2. From the command line, type the command connect to JCRDB user userid using password. You must connect to the JCR database using the userid and password of the WebSphere Portal JCR data source.

    2. Execute the following command in the previous substep. . If the command completes successfully, the UDF is registered correctly as follows: values schema.sortkeyj('abc','en'), where schema is the schema used in the previous substep.

  5. Edit the icm.properties file, located in profile_root\PortalServer\jcr\lib\com\ibm\icm directory. Add the following section to the end of the file:

            # Enable/Disable collation support for all DB2 platforms
            # Disabled by default
            jcr.query.collation.db2.enabled = true
    
            # Database specific collation mappings
            # These mappings apply map a Java locale name into a collation name
            # supported by the underlying database.
            # Example mappings for DB2 platform
    
            # English
            jcr.query.collation.en = en
        
            # Swedish
            jcr.query.collation.sv = sv
    
            jcr.query.collation.zh = zh
            jcr.query.collation.de = de
            jcr.query.collation.da = da
            jcr.query.collation.hu = hu
            jcr.query.collation.jp = jp
    
    

  6. Enter the following command to start the WebSphere Portal server: ./startServer.sh WebSphere_Portal


Parent topic:

Prepare DB2 on AIX for a stand-alone production server


Previous topic:

Modifying database properties


Next topic:

Set up DB2