LarsBlog

Working with XmlAccess today I encountered of a DuplicateKeyException (see below), refering to a database constraint PAGE_INST(OID). My colleague had previously deleted some pages manually on the portal.

<message id="EJPDB0099E">com.ibm.wps.util.DataBackendException:
    EJPDB0099E: Error occurred during database access. Last SQL statement is
    [INSERT INTO release.PAGE_INST (OID, CREATED, MODIFIED,
    IS_ACTIVE, IS_SYSTEM, IS_SHARED, IS_PROVIDED, ALL_PORT_ALLOWED,
    ORDINAL, PARENT_OID, PARENT_SL, CONT_PARENT_OID, CONT_PARENT_SL,
    SKIN_DESC_OID, SKIN_DESC_SL, THEME_DESC_OID, THEME_DESC_SL,
    CREATE_TYPE, TYPE, TF_INST_OID, SERVER_INFO_OID, SERVER_INFO_SL) VALUES
    (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
    ?)]. Transaction has status [STATUS_ACTIVE], global ID
    [00000(really long number)]
    and local ID [22370].
</message>
<message>com.ibm.websphere.ce.cm.DuplicateKeyException:
    [IBM][CLIDriver][DB2/LINUX] SQL0803N One or more values in the INSERT statement,
    UPDATE statement, or foreign key update caused by a DELETE statement are not valid
    because the primary key, unique constraint or unique index identified by "1"
    constrains table "RELEASE.PAGE_INST" from having duplicate rows for those columns.
    SQLSTATE=23505
</message>

It seems that when resources are deleted on WebSphere Portal, they are flagged as logically deleted rather than physically deleted. Any attmept to re-create this object via XmlAccess will cause an error like the one above.

By default, a cleanup of this data is scheduled once a week. You can configure portal to clean up the data synchronously (ie as resources are deleted) according to these pages:
http://publib.boulder.ibm.com/infocenter/wpdoc/v6r0/topic/com.ibm.wp.ent.doc/wps/adclndel.html?resultof=%22%63%6c%65%61%6e%75%70%22%20
http://www.skywayradio.com/tech/WAS51/portal/adclndel.html

Its also possible to use XmlAccess to manually clean up all this data, using a simple script like the one below:

<?xml version="1.0" encoding="UTF-8"?>
<request xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        build="wp6001_072" type="update" version="6.0.0.1"
        xsi:noNamespaceSchemaLocation="PortalConfig_1.4.0.2.xsd">
    <portal action="locate">
        <task action="create" bean="ejb/wpsSchedulerTask"/>
    </portal>
    <status element="all" result="ok"/>
</request>

which can be run by executing:

C:\IBM\WPS60\PortalServer\bin\xmlaccess.bat
                 -in CleanUpDeletedResources.xml -out output.xml
                 -user wps6admin -password wps6admin
                 -url http://portalserver:10038/wps/config

The above script is from Portal v6.0.0.1 and may need changes for earlier versions of portal (if indeed this problem even affects earlier versions?)