Read live RTC / Jazz repository data (work item attributes) using direct DB connection
![]()
I’m trying to determine the simplest way to read actual work item attribute information using SQL and a direct database (DB) connection to the Jazz DB.
Our JTS deployment is using an Oracle DB. We have configured a new work item type with two custom attributes. Our need is to select/retrieve the list of the two custom attributes based on the State (or Resolution) of the parent work item and only for work items of the new (custom) type we created. (We are in the process of determining whether State or Resolution will be used for the values in question.)
The reason we need to access these through direct SQL is we need to combine this information in an Oracle view in another database (via a DB Link) to make it available for a legacy process until we can get everything converted over to RTC.
The desired result list would look something like this:
I have investigated a number of articles including the following: But these seem more oriented to metrics reporting not direct access to RTC work item data.
1) Data Warehouse Snapshot Schemas (https://jazz.net/wiki/bin/view/Main/DataWarehouseSnapshotSchemas20)
2) Reports Component Home (https://jazz.net/wiki/bin/view/Main/ReportsMain)
3) Collaborative Lifecycle Management 2011: Reporting Workshop (https://jazz.net/forum/questions/52636/rtc-overview-data-model)
4) Exposing repository data (https://jazz.net/wiki/bin/view/Main/ExposingRepositoryData):
a. “in RTC 2.0, …we have added a new "LIVE_SNAPSHOT" which provides access to the queriable fields of all item types in the repository”
b. Would these “Live Snapshots” provide the information I am looking for?
I’m looking for physical data table definitions, physical models, or any other references that will help define a solution along these lines. Is it also possible for me to access this data in the Derby DB that is part of my RTC download POC environment? Thanks.
|
One answer
![]()
Ralph Schoon (63.3k●3●36●46)
| answered Jun 13 '13, 2:58 a.m.
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER
Michael,
my basic answer would be: you are not supposed to access the application DB directly. For one thing, the storage model - from what I have seen - is by no means trivial. If you want to access live data in CCM or any other application, use the available API's or other built in tool capabilities. Possibilities that come into mind with CCM/RTC are:
Comments Thanks for the information. Where can I find out more about OSLC and how to use it to get access to Work Item attributes? |
Your answer
Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.
Comments
Can someone help direct me to the best person to answer this question? Thanks!
Can someone help direct me to the best person to answer this question? Thanks!