How to copy WCM v6.x JCR database ?

As we have a big WCM content repository (more than 18.000 content items now) we have tried to write a procedure in order to be able to quickly copy an existing v6.x WCM database and to restore it on a target portal system.

This experience was interesting, and I discover that the process is quite different than the one we used in WCM v5.x.Basically, in v5.x, we simply had to do a backup/restore of the (DB2) database, then delete WCM index on the target, and after restarting the portal, everything was working fine...but now with v6.x JCR database format it's a little bit more complex...

The problem is that v6 Portal (or WCM ?) system creates dynamically JCR table view (and tables ?) at startup. Moreover, it seems that standard
database backup process (to copy database) does not copy this tables and tables views. I have no idea of why exactly, but this problem is described
at leat on z/OS (See : Workspace Validation utility to verify JCR Workspace database views are in sync for DB2 on z/OS).

So after restoring WCM database copy, and starting the portal, we had the following errors in the logs:
Caused by: com.ibm.content.exception.ServiceException: javax.jcr.RepositoryException: DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC:
JCR.ICMSTJCRL00001Thread 430447016 : lException: DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: JCR.ICMSTJCRL00001

Basically, this means that the table "JCR.ICMSTJCRL00001" was missing on the target portal...

After checking the list of existing tables on the target portal, and compare it with the source, I have seen that a lots of tables were missing on
v6.x.On the target portal, the following kind of tables was not available:
ICMSTJCRN00001, ICMSTJCRN00002, ICMSTJCRN00003, ICMSTJCRN0000x, etc
ICMSTJCRL00001, ICMSTJCRL00002, ICMSTJCRL00003, ICMSTJCRL0000x, etc

Running the command "db2 list tables for schema jcr" reported about 2988 records tables in the v6 source, and only 875 record(s) in the v6 target.
We have asked the DBA why this table was not copied by the DB2 backup/copy process ? (but I have no reply so far, he is still doing some tests).

So I have tried to fix it by myself :-)
Here is the solution I found (please note that this solution might NOT be supported by IBM, I still have to open a PMR to confirm it is):

Based on the previous article (see), I add the option:

jcr.workspace.view.validation=FIX

in the "/portal/WebSphere/wp_profile/PortalServer/jcr/lib/
com/ibm/icm/icm.properties" config file (I first tried with value "REPORT"), and then restart the portal.
(please not that our portal is running on AIX, not z/OS, but this option seems to work anyway)...

In the log file I have seen that the portal (WCM ?) was creating table views at startup:
[3/12/09 11:40:46:915 CET] 00000019 WorkspaceUtil I com.ibm.icm.da.portable.data.WorkspaceUtils validate Workspace Validation is starting. [3/12/09 11:40:47:439 CET] 00000019 WorkspaceUtil W com.ibm.icm.da.portable.data.WorkspaceUtils validate Workspace Validation has discovered the
following Database View(s) are missing. Please run the Validation utility with the FIX option to recreate: ICMSTJCRN00548 ICMSTJCRL00548
ICMSTJCRLR00548 ICMSTJCRLV00548 [3/12/09 11:40:47:452 CET] 00000019 WorkspaceUtil W com.ibm.icm.da.portable.data.WorkspaceUtils validate Workspace Validation has discovered the
following Database View(s) are missing. Please run the Validation utility with the FIX option to recreate: ICMSTJCRN00538 ICMSTJCRL00538
ICMSTJCRLR00538 ICMSTJCRLV00538


And after startup, I was able to see that all the missing tables (JCR.ICMSTJCRL00001, ICMSTJCRL00002, ICMSTJCRL00003, and also ICMSTJCRLV00001, etc,
etc) has been created.
The command "db2 list tables for schema jcr" now report a similar number of tables on the target v6 portal.

Now, I can see that WCM Content library is available in the portal admin interface, and that WCM Portlet rendering is working fine.


However, just a "minor" issue: after starting the portal I had a lots of "JURU" or "indexing" traces in the logs: I think it's because the JCR text search indexer was trying to rebuild WCM data index.
So I did set this option to false, to stop this process (and restart the portal):

jcr.textsearch.enabled=false(in /portal/WebSphere/wp_profile/PortalServer/jcr/lib/
com/ibm/icm/icm.properties)


That's all. Hope this will help you....If some of you also did try a similar process to copy WCM v6 database, do not hesitate to give your feedback.

------------------------
Also, for more information about this topic:

See this IBM article : Java Content Repository (JCR) information and Frequently Asked Questions (FAQ) for IBM Web Content Management (WCM)

See also this post on Vivek Agarwal's Blog, who had a quite similar issue on Quickr : Resolved an exception stacktrace related to Quickr/WebSphere Portal search tables