Entity Extension

Cai

Document Maintained

The latest version of this document will be found in the <\\dell\>. All other softcopy or hardcopy versions are invalid and are not to be used. It is the responsibility of the document owner to update this document.

Revision History

Date of this revision: 2011-05-17 / Date of next revision (date)
Revision Number / Revision Date / Summary of Changes / Author / Reviewed by
1 / 2011-05-17 / Draft
2 / 2011-10-10 / Add how to turn off extension / Cai

Acronyms & Definitions

Acronym / Definition
MDM / Master Data Management

TABLE OF CONTENTS

1Assumptions

1.1Hardware/Software Information

1.2Notes

1.3Conventions

2Create Hub Module Project

3Modify module.mdmxmi

4Modify xsd files

5Modify properties files

6Run the SQL scripts

7Test Entity Extension

8Fix get Transaction Issue

9DSUI

10Turn off Optimized SQL

11Turn off extension

12References

13@Deprecated Compare withfresh workspace

1Assumptions

1.1Hardware/Software Information

OS:Windows 7 Ultimate 32 bit

MDM: Master Data Management v 10.0

1.2Notes

1.3Conventions

Italic

Indicates sample contents and references

Constant

Used for Linux/Windows command

…

Place holder, replace the content with your actual content among them

Bold

Stands for import tips

2Create Hub Module Project

Click FileNewOther, expand InfoSphere Master Data Management Server, choose Hub Module Project and click Next.

Populate the Project name and Base Java package name. The Service namespace URI will automatically generate according to Base Java package name value. You can also manually edit it.

Click Finish.

Note:

If this is the first time creating Hub Module Project, the Hub base name and Database schema name is required.

You can modify it later in MDM/application.mdmxmi, Code Generation tab.

3Modifymodule.mdmxmi

Open the module.mdmxmi file with the Hub Model Editor and create a folder ifneeded.

Click on the folder, and click New > Entity Extension to create anentity extensionimplementation.

  • Click on the new Entity Extension.
  • Populate the Name – The name of entity extension.
  • Documentation – Optional description of entityextension.
  • Documentation URL – Optional URL linking to documentation for entityextension.
  • Database table name – The database table name is all upcase of Name value.It is automacially popuplated.
  • Click the Edit button at the end of Extends, type the entity name you want to extends at the search box, and click OK.
  • Clear Override base query and leave Add fields to base table as default (cleared).

Right-click on MDM Entity Extension, and choose New > Attribute to create a new attribute.In MDM v10.0.0, the primary key is automatically generated by workbench.

  • Populate the Name – A name for the attribute.
  • Documentation – Optional description of the attribute.
  • Documentation URL – Optional URL that links to documentation for theattribute.
  • Type – String or other data type.
  • String length – 250 or other value, based on your Type value.
  • Check Nullable and leave Persistent as default (checked).
  • Entity field name –It is automatically populated with Name value.
  • Database field name –It is automatically populated with Name value, splitted by under score.

Save module.mdmxmi and click Validate model. If no error found, click Generate implementation.

4Modify xsd files

Expand <Project>\resources\snippets\xsd, there lists three snippets; they are automatically merged into original xsd files by workbench. If not, you can also manually merge them later.

<Project Name>_tcrmRequest_extension.xsd.snippet

<Project Name>_tcrmResponse_extension.xsd.snippet

<Project Name>_Hub Base Name.xsd.snippet

Three files are listed below:

tcrmRequest_extension.xsd

tcrmResponse_extension.xsd

<Hub Base Name>.xsd

You can find the merge part in them, the original file are backup in CustomerResources\xsd\backup.

5Modify properties files

Expend <Project>\resources\snippets\properties, there lists two snippets; they are automatically modified by workbench.If not, you can also manually merge them.

<Project Name>_DWLCommon_extension.properties.snippet

<Project Name>_TCRM_extension.properties.snippet

Two files are listed below:

DWLCommon_extension.properties

TCRM_extension.properties

You can find the merge part in them, the original file are backup in CustomerResources\ properties\backup.

6Run the SQL scripts

After generating code,we can find several sql files in <Project>\resources\sql\db2. Open one of them,then we can see this execution order:

db2 -vf <Project Name>_SETUP_DB2.sql

db2 -vf <Project Name>_TRIGGERS_DB2.sql

db2 -vf <Project Name>_CONSTRAINTS_DB2.sql

db2 -vf <Project Name>_MetaData_DB2.sql

db2 -vf <Project Name>_CODETABLES_DB2.sql

Click Start > All Programs > IBM DB2DB2COPY1command-line toolcommand line window.

Change directiory to<your workspace>\Project\resources\sql\db2.

Conect to DB2 by typing:

db2 connect to <db_name> user <user_name> using <password>

Run SQL scripts in order:

Note:

If this is the first time creating Hub Module Project, below is required.

db2 -vf CONFIG_XMLSERVICES_RESPONSE_DB2.sql

7Test Entity Extension

Open <Project Name>_Extension Name>_SampleRequestObject.xml

Copy thexml snippetsinto normal AddXXX.xml

Make sure to put these snippets to the right place. You can refer to myTCRM.xsd for more details.In our case, put those snippets between <PersonLastUpdateUser> and <TCRMPartyAddressBObj

Run TestClient and check them in database.

8Fix get Transaction Issue

Issue Description (Abstract)

After creating extensions to Person, Organization, Contract, Product, or their child groups while running inquiry transactions on the parent group with Pluggable SQL feature turned ON, the additional fields created as part of extension do not appear in the response.

Cause

The issue occurs because Pluggable SQL for the extended object is not being regenerated automatically as part of data extension code generation process. As a result, the pluggable SQL remains unchanged and does not include the extended data columns. To get the correct response for the inquiry level with Pluggable SQL, regenerate the SQL by using the updateInqLevel transaction after the extension is deployed.

Solution

To resolve the issue, take the following steps:

1. Use the MDM Server Workbench to generate an extension related to Person, Organization, Contract, Product, or one of their child groups. See Chapter 3 - Developing InfoSphere MDM Server extensions andadditions in MDMWorkbenchUserGuide.pdf on how to create extensions using the wizard or model editor.

2. Run the SQL scripts generated by the workbench to update the InfoSphere MDM Server database table structure and metadata. Ensure that the metadata tables V_GROUP, V_ELEMENT, V_ELEMENTATTRIB and GROUPDWLTABLE are correct for the extended group. Ensure that the request and response XSD files named tcrmRequest_extension.xsd and tcrmResponse_Extension.xsd are updated.

3. The property file snippets created using workbench must be merged with the target property files. To make the pluggable SQL support available, merge <module_name>_TCRM_extension.properties with both the tcrm_extension.properties and the DWLCommon_extension.properties files. In MDM v10, this process is automatically completed by workbench, but it is still a good practice to check them.

4. Deploy the extension to the application server.

5. Restart the server.

6. Update the Inquiry Level of the corresponding group using an updateInqLevel transaction that includes the elements generateQuery=Y and BusinessTxType=<business transaction type>. The generateQuery flag must be set to "Y". BusinessTxType must point to the business transaction type to which the associated transaction pertains. This is an example of the transaction request:

The SQL statements will be generated and updated in INQLVLQUERY table. To validate this, check the table INQLVLQUERY and its field SQL_STATEMENT and make sure the extension attributes are included as part of SQL statement.

7. Restart the server

8. Now you can run the inquiry transactions and see the extended object in the response.

9. To detach the extended object from the parent object, roll back the database entries, remove the entries from the property files and XSD, restart the server and regenerate the SQL using the updateInqLevel transaction again.

10. For more details, please refer to www_304_ibm_com_support_entdocview_wss_uid_swg21442814.pdf

9Fix DSUIIssue

After adding entity extension, if you still want to use DSUI, some modification is required.

For more details, see

TW_Generate_SDO.doc

Chapter 16 - Understanding SDO objects in MDMUIDevelopersGuide.pdf

10Turn off OptimizedSQL

Problem Description (Abstract)

You are upgrading from InfoSphere MDM Server version 8.5 to InfoSphere MDM Server version 9.0.1 or higher, and all the steps required for this migration have been run. Now when you invoke a get transaction (let’s say getPerson), the application returns only the base TCRMPersonBObj instead of including the extension in the response. The extension was done to the TCRMPersonBObj object in the earlier version. You expect this to work in the new release after migration.

Symptom

The get transaction is working successfully without including the extension data in response. This happens to all the extensions.

Cause

This issue was caused by employing a new feature called Optimized SQL. Optimized SQL is a new feature in InfoSphere MDM Server version 9.0.1 or higher version. The INQLVLQUERY table will be populated by upgrade script. If you have "optimized.sql=true" in your tcrm_extension.properties file and the SQL statements in INQLVLQUERY table doesn't include the extension, the extension data will not return in a get transaction.

Solutions

There are three options to resolve this issue:

Solution One:

1. If you don't need the feature, it can be disabled and the extension will return in the response. See Chapter 16 - Defining inquiry levels in MDMDevelopersGuide.pdf.

2. Simply modifyset "optimized.sql=false" in your tcrm_extension.propertiesfile will disable Optimized SQL.

Solution Two:

1. Solution one has wide affects to MDM. The scenario to apply solution two is you need Optimized SQL for some domain (like Product), but do NOT need it for another domain (let’s say Person). So you can just disable Person part’s Optimized SQL.

2. Use below SQL script to update the database.

UPDATE INQLVLQUERY SET END_DT = CURRENT_TIMESTAMP, LAST_UPDATE_DT = CURRENT_TIMESTAMP WHERE BUSINESS_TX_TP_CD = '44';

Solution Three:

If you want to keep using the feature, you will need to regenerate the SQL:

See Chapter 8Fix get Transaction Issue for more details.

11Turn off extension

Sometimes in test environment, you will want to rollback entity extension to isolate problems. In this scenario, following steps are required.

  • Rollback SQL scripts
  • Under <Project>\resources\sql\db2, you will find several SQL scripts in this naming convention, Rollback_xxx_xxx_DBTYPE.sql.
  • Open one of them, and you will see the execution order.
  • Run each SQL file as shown below and in the same order.

db2 -vf Rollback_PartyAddressExtension_MetaData_DB2.sql

db2 -vf Rollback_PartyAddressExtension_CONSTRAINTS_DB2.sql

db2 -vf Rollback_PartyAddressExtension_TRIGGERS_DB2.sql

db2 -vf Rollback_PartyAddressExtension_SETUP_DB2.sql

  • Comment properties files
  • The <Project>\resources\snippets\properties will tell you which properties files are modified by workbench, what you need to do is rollback those modification.
  • In Convenience, we list them below:

DWLCommon_extension.properties

TCRM_extension.properties

  • Comment xsd files
  • The <Project>\resources\snippets\xsd will tell you which xsd files are modified by workbench, what you need to do is rollback those modification.
  • In Convenience, we list them below:

tcrmRequest_extension.xsd

tcrmResponse_extension.xsd

tw.xsd

  • Remove Entity Extension from MDM Deployment Assembly
  • Delete the Entity Extension Module Project is not recommended.
  • You can right click on MDM project > Properties > Deployment Assembly, and remove module project from MDM Deployment Assembly.

12References

MDMWorkbenchUserGuide.pdf

MDMUIDevelopersGuide.pdf

TW_Generate_SDO.doc

www_304_ibm_com_support_entdocview_wss_uid_swg21442814.pdf

Page 1