How do I write queries on the RTC DB to extract some data?

Yash Dixit (11●1●1) | asked Mar 07 '14, 6:52 p.m.
edited Mar 08 '14, 5:27 p.m. by Geoffrey Clemm (30.0k●2●30●35)

I have access to RTC DB but i have no clue how the data is being stored in it. Can some one provide me the tables and linkages for the RTC Database so that i may write queries on the DB to extract some data?

Comments

sam detweiler commented Mar 07 '14, 8:19 p.m.

why do you want to go direct to the DB? the structure is not documented for this kind of access. there are apis to get to the data

3 answers

Most liked answers ↓|Newest answers|Oldest answers

permanent link

Yash Dixit (11●1●1) | answered Mar 09 '14, 7:51 p.m.

I do understand that CLM DB is blackbox but there are some essential features that are missing in RTC which force the user to get into DB to extract that kind of data. There is no report which you can run to identify a defect's status change from one state to another on the periodical basis(daily/weekly). In CQ, history had a filter to run the query on the defects to figure out the status changes if any. There is no way we can figure out anything related to History in RTC. Is there a way that someone can provide what are the main tables that store the defect work item data at least?

Comments

Matthias Buettgen commented Mar 11 '14, 3:05 a.m.

Did you consider using BIRT to get these kind of data. I developed various reports which displayed the kind of information you're looking for. Best point to start I assume would be the WORKITEMS_SNAPSHOT. There are a couple of tables which refer to the data warehouse.
If you use the advanced BIRT data source it's also possible to create some kind of SQL-like queries.

1	permanent link sam detweiler (12.5k●6●185●201) \| answered Mar 09 '14, 8:02 p.m. Install RRDi free reporting system.. then you can get that data.

2	permanent link Charlie Seo (221●2●7) \| answered Mar 09 '14, 7:45 p.m. You should consider CLM DB as a blackbox since any wrong injection can cause damage and due to complex reference it won't be easy to fix things. As mentioned, majority of data can be retrieved by REST and Plain API.

It's all about the answers!

Related questions

How do I write queries on the RTC DB to extract some data?

3 answers

Your answer