Database transfer: Set up JCR collation

JCR collation is recommended when the language locales of the users do not natively collate correctly in the DB2 database, and when language locale correct ordering is important.

1. Stop the WebSphere Portal server.

2. From portal server, copy files to DB2 server...
   scp ${WpsInstallLocation}/jcr/wp.content.repository.install/lib/wp.content.repository.install.jar user@db2host:/tmp
   scp ${WasUserHome}/PortalServer/jcr/config/registerCollationUDFTemplate.sql user@db2host:/tmp

3. Log on to the JCR domain and set up collation
   cd $DB2_INSTANCE_HOME/sqllib/function
   $DB2_HOME/sqllib/java/jdk/bin/jar -xvf /tmp/wp.content.repository.install.jar icm/CollationUDF.class

4. From the DB2 server host, edit...
   /tmp/registerCollationUDFTemplate.sql

   ...and change all SCHEMA references to the value of jcr.DbSchema in wkplc_dbdomain.properties.

5. Connect to the JCR database and run the script.
   connect to jcrdb user ${jcr.DbUser} using ${jcr.DbPassword}
   db2 -tvf /tmp/registerCollationUDFTemplate.sql

6. Disconnect from the JCR database.

7. Restart the DB2 instance.

Verify the UDF is registered properly.

11. Log in as ${jcr.DbUser}.

12. Open a DB2 terminal window.

13. Connect to the database containing JCR domain:
    
    db2 connect to ${jcr.DbName} user ${jcr.DbUser} using ${jcr.DbPassword}

14. After connecting, to the JCR database, register the UDF.

    values ${jcr.DbName}.sortkeyj('abc','en')

15. Disconnect from DB2 terminal window:
    db2 disconnect all db2 terminate

16. Start the WebSphere Portal server.

17. Log in to WAS console and go to...
    Resources | Resource Environment | Resource Environment Providers | JCR ConfigService PortalContent | Custom properties

18. Add or update the following properties as necessary:

    Enable/Disable collation support for all DB2 platforms(LUW, Z, I), this is disabled (false) by default

    Name: jcr.query.collation.db2.enabled

    Value: true

    Type: java.lang.String

    Name: jcr.query.collation.en

    Value: en

    Type: java.lang.String

19. To apply the settings, stop WebSphere Portal, and then restart WebSphere Portal.

Parent Manual Steps: Database Transfer