JCR Troubleshooting: Exception with JCR PAC tables SQLCODE: -803, SQLSTATE: 23505

Answer

There are occasional scenarios in which the PAC data is not completely removed from the database when content is removed from either WCM or Personalization. When this occurs to the point of stopping a current function, the data must cleaned up with the following methods:

WARNING:
The queries/process below are ok to use on Portal 6.1.x or 7.x but should NEVER be used for Portal 8.x or 8.5.x. It will wipe out ALL wcm roles for those versions, not just the stale entries.

NOTE: ALWAYS backup the database before making any SQL updates of this type.

DB2:

* SELECT COUNT(*) FROM schema.PROT_RES WHERE RES_TYPE IN (7003, 46, 51, 49, 48, 52) AND EXTERNAL_UID NOT IN (SELECT UUID FROM schema.ICMSTJCRWSNODES)
* DELETE FROM schema.PROT_RES WHERE RES_TYPE IN (7003, 46, 51, 49, 48, 52) AND EXTERNAL_UID NOT IN (SELECT UUID FROM schema.ICMSTJCRWSNODES)

A utility has been provided to clean up the table when using an Oracle database in the Attachments section below. Attached is a Java application that removes the unused entries from the PROT_RES table. This application directly connects to the database and queries both the PROT_RES and the ICMSTJCRWSNODES tables, listing the differences between the two tables.

When this application is executed in update mode, it also removes the unused entries from the PROT_RES table.

Because this application runs from the command-line, it has the following requirements:

• You must have a Java 1.5 JVM installed (note that this should be available via WebSphere).
• Since this application connects directly to the database, you must stop the Portal server while running this application.
• We recommend executing the application with a maximum amount of virtual memory available. This reduces the chance of running out of memory when processing large amounts of data.

Usage:
java com.ibm.jcr.db.pac.ProtResCleaner -db icmPropertiesFile [-update]

Instructions:
1. Download the dbapplications.jar file (in the Attachments section below) to your file system.

2. Stop all Portal servers that are connected to the database.

3. Back up the database.

4. Open a command prompt and set the following classpath:

<oracle-lib>/ojdbc14.jar;<applications-loc>/dbapplications.jar,

where <oracle-lib> is the location of the Oracle Java libraries and<applications-loc> is the location on the file system where you downloaded the dbapplications.jar file.

5. Back up and edit the <PortalServer>/jcr/lib/com/ibm/icm/icm.properties file and make sure that the following properties are set for the Oracle database:

jcr.database.type
jcr.database.name
jcr.database.userid
jcr.database.password
jcr.database.schema
jcr.database.driver
jcr.database.drivertype
jcr.database.hostname
jcr.database.port

6. Execute the application in report mode and save the output to the following file:
java -Xmx1500m com.ibm.jcr.db.pac.ProtResCleaner -db <PortalServer>/jcr/lib/com/ibm/icm/icm.properties

NOTE: You may change the value of "-Xmx1500m" as needed. Increase this value if you encounter an Out Of Memory error or decrease this value if you get an error that Java cannot create the virtual machine.

7. Verify the number of rows to be processed from the application output. This is the number of unused rows in PROT_RES that will be removed. Verify also that this value is much smaller than the total number of rows in the PROT_RES table.

8. Execute the application in update mode. Save the output to a new file:
java -Xmx1500m com.ibm.jcr.db.pac.ProtResCleaner -db <PortalServer>/jcr/lib/com/ibm/icm/icm.properties -update

9. Restart the Portal server.

Contact IBM Support if you encounter any problems in executing the above steps.

NOTE: This duplicate exception can be prevented by applying PK78517.