It's all about the answers!

Ask a question

Read live RTC / Jazz repository data (work item attributes) using direct DB connection


Michael Taylor (8865764) | asked Jun 07 '13, 1:43 p.m.

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:

 

Planned For (based on Parent item)

State [or Resolution]

(based on Parent item)

Custom Attribute 1 (from child)

Custom Attribute 2 (from child)

Parent ID

Child ID

8.7

Waiting String

FRM_DEMO

1.2

64

102

8.6

Waiting String

FRM_CLST

1.13

25

93

8.6

Waiting ST

FRM_CLST

1.5

25

76

8.6

Waiting UAT

FRM_NOTE

1.9

13

85

 

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.


Comments
Michael Taylor commented Jun 11 '13, 12:18 p.m.

Can someone help direct me to the best person to answer this question?  Thanks!


Michael Taylor commented Jun 12 '13, 12:44 p.m.

Can someone help direct me to the best person to answer this question?  Thanks!

One answer



permanent link
Ralph Schoon (63.1k33645) | 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:

  1. Use the CSV export to export the data, include the work item ID and any data you need to correlate that to data in external tools.
  2. Use the Plain Java Client Libraries to access the data e.g. run a query and store the data in any media or format you prefer. See https://rsjazz.wordpress.com/2012/10/29/using-work-item-queris-for-automation/ or https://rsjazz.wordpress.com/2012/11/19/using-expressions-for-automation/ and https://rsjazz.wordpress.com/2013/01/02/working-with-work-item-attributes/ for an introduction to the API.
  3. Use OSLC to get the data and store it similar to 2.

Comments
Michael Taylor commented Jun 13 '13, 1:57 p.m.

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


Register or to post 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.