I found this bug today and wanted to share my solution to the problem. My scenario is I have Insight v18.104.22.168 deployed and integrated with 2 CLM deployments, one of which is version 4.0.5 and the other is version 4.0.3. The RTC Data Manager ETLs run fine against the 4.0.5 CCM server however, when running the 4.0.3 DM ETLs against the 4.0.3 CCM server, errors are thrown.
Viewing the ri_jdbc.log file, I was able to detect the first error in the “RTC_WorkItemApprovalState4.0.3” fact build (see below for full error). It is complaining about a column it is trying to query that does not exist. The column is ‘projectAreaArchived’.
When I checked the XML data configuration file in the XDC tool, the respective resource (WorkItemApprovalState) was indeed missing this data element.
The solution to this is to expose the required data element which indeed does exist in the RTC reportable REST API.
- Launch the XML Data Configuration tool
- Load the workitem403.xdc file
- Navigate to the data mapping table: “Resources” > “RTCWorkItem” > “WORKITEM_CCM” > “WorkItemApprovalState” > “WorkItemApprovalState
- Double-click on the data mapping table
- Select the “Columns” tab and then select “Modify”
- Navigate through the RTC reportable REST API to find the ‘/archived’ data element. Highlight it and select “Create”. This will add the new element to the data mapping template.
- Rename it to ‘projectAreaArchived’ by clicking in the “Table Column Name” cell and modifying the text
- Save the configuration
- Close the “WorkItemApprovaState” data mapping template tab
- You should receive a dialog that tells you the template has been changed and asks you to update it. Select “Ok”
- Save your configuration once again
- Your new column will appear faded. You have added the column but now need to load it. Highlight your new column and click on “Load”.
- Save your configuration again and this new column is now available made to Insight, hence the ETL will no longer complain about it.
There are 4 different places where this error will be thrown. You will only see one error at a time as the ETL halts upon the first error in this case. Here are the 4 fact builds this affects:
For reference, here is the full error found in the ri_jdbc.log:
query => SELECT “id”,
WHERE modifiedsince=’1899-12-31 00:00:00′ AND projectAreaArchived=false
03/06/2014 13:27:27,444 ERROR Thread-5 com.ibm.rational.drivers.jdbc.xml.internal.PageFetcherThread : CRRRE1203E: The column ‘projectAreaArchived’ does not exist.
03/06/2014 13:27:27,444 ERROR Thread-5 com.ibm.rational.drivers.jdbc.xml.internal.PageFetcherThread : com.ibm.rational.etl.common.exception.ETLException: CRRRE1203E: The column ‘projectAreaArchived’ does not exist.
com.ibm.rational.etl.common.exception.ETLException: com.ibm.rational.etl.common.exception.ETLException: CRRRE1203E: The column ‘projectAreaArchived’ does not exist.
Caused by: com.ibm.rational.etl.common.exception.ETLException: CRRRE1203E: The column ‘projectAreaArchived’ does not exist.
… 1 more
03/06/2014 13:27:27,459 INFO main com.ibm.rational.drivers.jdbc.xml.RDSStatement : CRRRE1228I: Close this statement