Topic
  • 6 replies
  • Latest Post - ‏2012-06-06T15:01:01Z by SystemAdmin
SystemAdmin
SystemAdmin
228 Posts

Pinned topic Join in CM bases result on old versions of secondary table

‏2012-06-04T12:46:42Z |
I am running a query against Content Manager v. 8.4.1 from a Java-program, where I am joining table A and B on attribute b:

/A[@a="x" AND (@b=(/B

I have additionally specified DKConstant.DK_CM_PARM_RETRIEVE, new Integer(DKConstant.DK_CM_CONTENT_ITEMTREE_NO_LINKS + DKConstant.DK_CM_VERSION_LATEST).

The result is that I get a list of A instances in the latest version. However A instances are returned based on information from B instances that are not nessecarily in latest version. This is a problem that is worsened by the fact that all B versions are retained in DB2, when we delete B instances via the CM API. This means that a) deleted B instances have impact on the resultset of the query, and b) older versions of B instances have impact on the resultset of the query.

1) How can I change my query/parameters so that I get A's from a join with B where B instances considered are only in latest version?
2) How can we make our delete operation delete all versions in DB2? (note: a solution to 2 will not omit the need for a solution to 1)

Brgds.
  • SystemAdmin
    SystemAdmin
    228 Posts

    Re: Join in CM bases result on old versions of secondary table

    ‏2012-06-04T12:48:42Z  
    the full query should read:

    /A[@a="x" AND (@b=(/B
  • SystemAdmin
    SystemAdmin
    228 Posts

    Re: Join in CM bases result on old versions of secondary table

    ‏2012-06-04T12:50:19Z  
    the full query should read:

    /A[@a="x" AND (@b=(/B
    /A[ @a="x" AND ( @b=( /B
  • SystemAdmin
    SystemAdmin
    228 Posts

    Re: Join in CM bases result on old versions of secondary table

    ‏2012-06-04T12:53:50Z  
    /A[ @a="x" AND ( @b=( /B
    I seems to have problems posting the query. It is attached in a file instead.

    Attachments

  • SystemAdmin
    SystemAdmin
    228 Posts

    Re: Join in CM bases result on old versions of secondary table

    ‏2012-06-04T15:08:51Z  
    I seems to have problems posting the query. It is attached in a file instead.
    This is the expected behavior, as the DK_CM_VERSION_LATEST option only applies to the final result. You can achieve what you want to achieve by adding an additional condition to the predicate on B in the query. It would look something like this:

    /A[ @a="x" AND ( @b=( /B[ @b="y" AND @VERSIONID = latest-version(.)] ) /@b) ]
    
  • SystemAdmin
    SystemAdmin
    228 Posts

    Re: Join in CM bases result on old versions of secondary table

    ‏2012-06-06T10:51:20Z  
    This is the expected behavior, as the DK_CM_VERSION_LATEST option only applies to the final result. You can achieve what you want to achieve by adding an additional condition to the predicate on B in the query. It would look something like this:

    <pre class="java dw" data-editor-lang="java" data-pbcklang="java" dir="ltr">/A[ @a="x" AND ( @b=( /B[ @b="y" AND @VERSIONID = latest-version(.)] ) /@b) ] </pre>
    Thanks - that solved 1 and was a really big help!
    Since we are implenting retention periods after which we want data to be removed entirely, could you also tell med how we ensure that all versions are removed from db2 when an instance is deleted via the CM API?
  • SystemAdmin
    SystemAdmin
    228 Posts

    Re: Join in CM bases result on old versions of secondary table

    ‏2012-06-06T15:01:01Z  
    Thanks - that solved 1 and was a really big help!
    Since we are implenting retention periods after which we want data to be removed entirely, could you also tell med how we ensure that all versions are removed from db2 when an instance is deleted via the CM API?
    To delete all versions of an item, you need to specify the DKConstantICM.DK_ICM_DELETE_ALL_VERSIONS option when you call DKDatastoreICM.deleteObject().

    Details may be found here:
    http://publib.boulder.ibm.com/infocenter/cmgmt/v8r4m0/topic/com.ibm.java.doc/com/ibm/mm/sdk/server/DKDatastoreICM.html#deleteObject_com.ibm.mm.sdk.common.dkDataObject_int_