Using pluggable SQL for MDM extensions

To leverage the benefits of pluggable SQL in extensions to the Person, Organization, Contract, or Product objects or their child groups, you must perform some additional configuration.

Procedure

Use the MDM Workbench to generate an extension for a Person, Organization, Contract or Product object or their child groups.
For more information about creating extensions using the Workbench, see ../../com.ibm.mdmhs.wb.tools.models.doc/c_developingmdmserverextensionsandadditions.html.
Run the SQL scripts that the MDM Workbench generates to update the InfoSphere® MDM database table structure and metadata.
Ensure that the metadata tables V_GROUP, V_ELEMENT, V_ELEMENTATTRIB, and GROUPDWLTABLE were updated correctly for the extended group.
Optional: If you have enabled legacy schema files to be generated by the Workbench, ensure that the request and response XSD files tcrmRequest_extension.xsd and tcrmResponse_extension.xsd were updated correctly.
This is not necessary if you have not enabled legacy schema generation in the Workbench, because these legacy schemas have been replaced by MDMCommon.xsd, MDMDomains.xsd and xsd-snippet files provided by bundles and registered as OSGi services. For more information, see ../../com.ibm.mdmhs.wb.tools.models.doc/c_CreatingAReqOrRespObjForACustomTxn.html.
Optional: Merge the property file snippets generated by the Workbench with the target property files:
- To provide pluggable SQL support, merge the snippet <module_name>_TCRM_extension.properties with the file tcrm_extension.properties.
This is not necessary if you have not enabled legacy schema generation in the Workbench because these legacy schemas have been replaced by MDMCommon.xsd, MDMDomains.xsd and xsd-snippet files provided by bundles and registered as OSGi services. For more information, see ../../com.ibm.mdmhs.wb.tools.models.doc/c_CreatingAReqOrRespObjForACustomTxn.html.
Deploy the extension to the application server and restart the server.
Use the updateInqLevel transaction to update the inquiry level of the group that corresponds to the group for which your extension applies. Remember to:
- Set the value of the GenerateQuery element to Y:
```
<GenerateQuery>Y</GenerateQuery>
```
- Set the value of the BusinessTxType element to the business transaction type that pertains to the associated transaction:
```
<BusinessTxType>[business transaction type]</BusinessTxType>
```
The SQL statements will be generated and updated.
To validate that the SQL statements were properly generated and updated, check the INQLVLQUERY table and its SQL_STATEMENT field to ensure that the extension attributes are included as part of the SQL statements.
For more information, see Configuring inquiry levels for pluggable SQL.
Restart the server.
Test the new inquiry level by running inquiry transactions. You should see the extended object in the response.
Optionally, if you wish to detach the extended object from the parent object:
1. Roll back the database entries.
2. Remove the entries from the properties files and XSD files.
3. Restart the server.
4. Regenerate the SQL using the updateInqLevel transaction.

Example

This example request XML shows how to use the updateInqLevel transaction to update Inquiry Level 4 of the Person group.

<?xml version="1.0" encoding="UTF-8"?>
<DWLAdminService xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="DWLAdminService.xsd">
  <RequestControl>
    <requestID>1000</requestID>
    <DWLControl>
      <requesterName>cusadmin</requesterName>
      <requesterLanguage>100</requesterLanguage>
    </DWLControl>
  </RequestControl>
  <DWLTx>
    <DWLTxType>updateInqLevel</DWLTxType>
    <DWLTxObject>DWLInqLevelBObj</DWLTxObject>
    <DWLObject>
		<DWLInqLevelBObj>
			<InquiryLevelId>1008</InquiryLevelId>
			<Application>TCRM</Application>
			<GroupName>Person</GroupName>
			<InquiryLevel>4</InquiryLevel>
			<Description>Level 4 person updated</Description>
			<InquiryLevelLastUpdateDate>2010-07-26 10:09:47.468</InquiryLevelLastUpdateDate>			
			<GenerateQuery>Y</GenerateQuery>
			<BusinessTxType>44</BusinessTxType>
		</DWLInqLevelBObj>   
    </DWLObject>
  </DWLTx>
</DWLAdminService>