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.