Useful SQL Queries

Authors: VaughnRokosz, TimFeeney, BrianSteele, IanGreen, IanBarnard, WilliamChatham
Build basis: 7.x

This page contains some useful SQL queries to gather information from the DOORS Next (DN). Be advised that these queries can place load on the database so exercise caution and test these in a non-production environment first.
Note: The preferred output for the following scripts is CSV or TXT. No PDFs or XLS. Output each query result to its own file including query name in the name of the results file. Run these on systems that have been upgraded to 7.x.

Oracle

*The scripts will need to be changed to conform to your environment (e.g. change RMUSER to whatever user names are used for your environment).*

artifactsPerComponent.sql (number of artifacts per component):

select component_id, count (distinct item_id) as Comp 
from RMUSER.dngartifacts_db_artifact 
group by component_id order by Comp DESC;

-- Note: query below is optional: If you require the component name you can use the query below to display the component name using the item_id returned from previous query above.

select NAME from RMUSER.vvcmodel_component 
where item_id='an item_id from previous query';

artifactsPerFolder.sql (number of artifacts per folder):

select folder, count(folder) from
(
select distinct A.item_id as art, F.ITEM_ID as folder from RMUSER.dngartifacts_db_artifact A 
INNER JOIN RMUSER.DNGFOLDERS_DB_FOLDER F ON F.ITEM_ID = A.PARENT_FOLDER_ITEM_ID
) group by folder order by count(folder) desc fetch first 100 rows only

-- Note: queries below are optional: If you want to get from the folder ID to the title, something like this should work:

SELECT DISTINCT ITEM_ID, TITLE FROM RMUSER.DNGFOLDERS_DB_FOLDER F 
WHERE F.ITEM_ID = 'uuid from previous query'

This will get better info on where the folder is at. However it only works on a single item id at a time

select distinct t1.item_id, t1.title, t3.name_col as project, t2.name_col as component from 
(select item_id, component_id, process_area_id, title from RMUSER.DNGFOLDERS_DB_FOLDER 
where item_id='item_id'
) t1 
inner join rmuser.vvcmodel_component t2 on t2.item_id = t1.component_id 
inner join rmuser.process_process_area t3 on t3.item_id = t1.process_area_id

artifactsPerModule.sql (number of artifacts per module):

select module_item_id, count(distinct item_id) as count 
FROM rmuser.dngartifacts_db_binding_contnr TBL_2A 
group by module_item_id order by count desc;

-- Note: queries below are optional: How would I convert module_item_id to an identifiable module, see below, if we have multiple item_id values, instead of " item_id = 'id' " you can use -- " item_id in ('id', 'id', 'id') "?

select distinct title from RMUSER.DNGARTIFACTS_DB_ARTIFACT where item_id = 'value of module_item_id'

This will get better info on where the module is at. It only works on a single module id at a time

select distinct t1.item_id, t1.id, t1.title, t3.name as project, t2.name as component from 
(select item_id, component_id, process_area_id, title, id 
from rmuser.dngartifacts_db_artifact 
where item_id = 'module_item_id'
) t1 
inner join rmuser.vvcmodel_component t2 on t2.item_id = t1.component_id 
inner join rmuser.process_process_area t3 on t3.item_id = t1.process_area_id;

artifactsPerProject_v7.sql (number of artifacts per project):

SELECT COUNT(*), PA.NAME 
FROM RMUSER.DNGARTIFACTS_DB_ARTIFACT A 
INNER JOIN RMUSER.PROCESS_PROCESS_AREA PA ON A.CONTEXT_ID = PA.ITEM_ID 
GROUP BY PA.NAME order by count(*) desc;

artifactstates.sql (states per artifact):

select item_id, jz_discriminator, count(item_id) 
from rmuser.dngartifacts_db_artifact 
group by item_id, jz_discriminator 
having count(item_id) > 2 
order by count(item_id);

changesetsPerStream.sql (changesets per stream):

select S2.PARENT_STREAM_ITEM_ID, count(S2.PARENT_STREAM_ITEM_ID) 
from RMUSER.VVCMODEL_CHANGE_SET S2 
group by S2.PARENT_STREAM_ITEM_ID 
order by count(s2.PARENT_STREAM_ITEM_ID) desc

-- Note: query below is optional and can be used to convert PARENT_STREAM_ITEM_ID to something identifiable.

select name from RMUSER.VVCMODEL_CONFIGURATION 
where item_id='value of parent_stream_id'

-- Note: this optional query can be used to convert PARENT_STREAM_ITEM_ID to something identifiable along with its component and project.

column project format a40
column stream format a40
column component format a40
select T3.item_id, T3.type, T3.archived, T3.component_item_id, T3.name as stream, T2.name as component, t4.name as project 
FROM(
select T1.item_id, T1.type, T1.archived, T1.component_item_id, T1.name 
FROM RMUSER.VVCMODEL_CONFIGURATION T1 where item_id IN
(select S2.PARENT_STREAM_ITEM_ID 
from RMUSER.VVCMODEL_CHANGE_SET S2 
group by S2.PARENT_STREAM_ITEM_ID
having count(S2.PARENT_STREAM_ITEM_ID) > 50000)
) T3
INNER JOIN RMUSER.VVCMODEL_COMPONENT T2 ON T3.component_item_id = T2.item_id
INNER JOIN RMUSER.PROCESS_PROCESS_AREA T4 on T2.project_area_id = T4.item_id

componentsPerProject.sql (total number of components per project):

SELECT COUNT(*), PA.NAME FROM RMUSER.VVCMODEL_COMPONENT C 
INNER JOIN RMUSER.PROCESS_PROCESS_AREA PA 
ON C.PROJECT_AREA_ID = PA.ITEM_ID 
GROUP BY PA.NAME order by count(*) desc

contributionsPerGC.sql (number of local DNG contributions per GC):

select composite_id, count(composite_id) from rmuser.repository_contributions 
group by composite_id order by count(composite_id) desc;

linksPerArtifact.sql (number of links per artifact):

select artifact, count(artifact) from 
(
select subject_item_item_id as artifact from rmuser.dnglinks_db_link 
union all
select object_item_item_id as artifact from rmuser.dnglinks_db_link
) group by artifact order by count(artifact) desc 

streamsPerComponent.sql (number of streams per component):

select component_item_id, count(component_item_id) 
from rmuser.vvcmodel_configuration where type = 0 
group by component_item_id 
order by count(component_item_id) desc fetch first 100 rows only

tablespaceSizes.sql (display tablespace sizes):

col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files 
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments 
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ; 

totalArtifacts_v6.sql (total artifacts in DNG repository):

SELECT COUNT(distinct item_id) FROM RMUSER.DNGARTIFACTS_DB_ARTIFACT

totalArtifacts_v7.sql (total artifacts in DNG repository inclusive of states):

SELECT COUNT(*) FROM RMUSER.DNGARTIFACTS_DB_ARTIFACT

totalComponents.sql (count of all components):

SELECT COUNT(*) FROM RMUSER.VVCMODEL_COMPONENT

versByComponent.sql (artifact versions in each component):

select component_id, count (component_id) as Comp 
from RMUSER.dngartifacts_db_artifact 
group by component_id order by Comp DESC;

versByConfigid.sql (artifact versions in each configuration):

-- Input parameter:  schema name

ACCEPT SCHEMA PROMPT 'Schema name:'

set linesize 999
set pagesize 999

select config_id, count (config_id) as configs 
from "&SCHEMA".REPOSITORY_VERSION 
group by config_id order by configs DESC;

configurationByDateTime.sql (Returns all configs created between the two dates specified in final two lines):

Note: This query can be scoped to run for Streams, Baselines or both by setting the conf."TYPE" as follows

• Streams: conf."TYPE" IN (0)
• Baselines: conf."TYPE" IN (1)
• Streams and baslines: conf."TYPE" IN (0,1)

SELECT conf.CREATION_TIME, conf.ITEM_ID, conf.NAME AS configuration_name, comp.NAME AS component_name FROM VVCMODEL_CONFIGURATION conf
INNER JOIN VVCMODEL_COMPONENT comp 
ON conf.COMPONENT_ITEM_ID = comp.item_id
WHERE conf."TYPE" IN (1)
AND conf.CREATION_TIME < timestamp '2019-03-19 17:04:20.941'
AND conf.CREATION_TIME > timestamp '2015-03-19 17:04:20.941'
ORDER BY conf.CREATION_TIME

identifyModuleWithRootBindingUUID.sql (Identify a Root Bindings associated Module UUID):

SELECT CONCAT('MD',MODULE_ITEM_ID)
FROM rmuser.dngartifacts_db_binding_contnr WHERE item_id='<Module_Root_Binding_UUID>'

Note: The Module_Root_Binding_UUID used in the query above is the Root Binding UUID with the "RB" stub removed example Root binding is "RB_tOWWISU6Ee65jpJDsH-48Q" so for Module_Root_Binding_UUID insert "_tOWWISU6Ee65jpJDsH-48Q". You can open the module using the query result by adding it to the resource URL : https://jazz.ibm.com:9443/rm/resources/MD_ssY4mCU6Ee65jpJDsH-48Q' and open in a Browser.

DB2

* For each of these, login to the appropriate RM database and run the query. *

artifactsPerComponent.sql (number of artifacts per component):

select component_id, count (distinct item_id) as Comp 
from dngartifacts.db_artifact 
group by component_id order by Comp DESC

-- Note: query below is optional: If you require the component name you can use the query below to display the component name using the item_id returned from previous query above.

select NAME_COL from vvcmodel.component 
where item_id='an item_id from previous query';

artifactsPerFolder.sql (number of artifacts per folder):

select folder, count(folder) from
(
select distinct A.item_id as art, F.ITEM_ID as folder from dngartifacts.db_artifact A 
INNER JOIN DNGFOLDERS.DB_FOLDER F ON F.ITEM_ID = A.PARENT_FOLDER_ITEM_ID
) group by folder order by count(folder) desc fetch first 100 rows only

-- Note: queries below are optional: If you want to get from the folder ID to the title, something like this should work:

SELECT DISTINCT ITEM_ID, TITLE 
FROM DNGFOLDERS.DB_FOLDER F 
WHERE F.ITEM_ID = 'uuid from previous query'

This will get better info on where the folder is at. It only works on a single item id at a time

select distinct t1.item_id, t1.title, t3.name_col as project, t2.name_col as component from 
(select item_id, component_id, process_area_id, title 
from DNGFOLDERS.DB_FOLDER where item_id='item_id'
) t1 
inner join vvcmodel.component t2 on t2.item_id = t1.component_id 
inner join process.process_area t3 on t3.item_id = t1.process_area_id

artifactsPerModule.sql (number of artifacts per module):

select module_item_id, count(distinct item_id) as count 
FROM dngartifacts.db_binding_container TBL_2A 
group by module_item_id order by count desc

-- Note: queries below are optional: How would I convert module_item_id to an identifiable module, see below, if we have multiple item_id values, instead of " item_id = 'id' " you can use -- " item_id in ('id', 'id', 'id') "?

select distinct item_id,title 
from DNGARTIFACTS.DB_ARTIFACT 
where item_id = 'value of module_item_id'

This will get better info on where the module is at. It only works on a single module id at a time

select distinct t1.item_id, t1.id, t1.title, t3.name_col as project, t2.name_col as component from 
(select item_id, component_id, process_area_id, title, id 
from dngartifacts.db_artifact where item_id='module_item_id'
) t1 
inner join vvcmodel.component t2 on t2.item_id = t1.component_id 
inner join process.process_area t3 on t3.item_id = t1.process_area_id

artifactsPerProject_v7.sql (number of artifacts per project):

SELECT COUNT(*), PA.NAME_COL FROM DNGARTIFACTS.DB_ARTIFACT A 
INNER JOIN PROCESS.PROCESS_AREA PA ON A.CONTEXT_ID = PA.ITEM_ID 
GROUP BY PA.NAME_COL order by count(*) desc;

artifactstates.sql (states per artifact):

select item_id, jz_discriminator, count(item_id) from dngartifacts.db_artifact 
group by item_id, jz_discriminator having count(item_id) > 2 order by count(item_id)

changesetsPerStream.sql (changesets per stream):

select S2.PARENT_STREAM_ITEM_ID, count(S2.PARENT_STREAM_ITEM_ID) 
from VVCMODEL.CHANGE_SET S2 
group by S2.PARENT_STREAM_ITEM_ID 
order by count(s2.PARENT_STREAM_ITEM_ID) desc

-- Note: query below is optional and can be used to convert PARENT_STREAM_ITEM_ID to something identifiable.

select name from VVCMODEL.CONFIGURATION where item_id='value of parent_stream_id'

-- Note: this optional query can be used to convert PARENT_STREAM_ITEM_ID to something identifiable along with its component and project.

select T3.item_id, T3.type_col, T3.archived, T3.component_item_id, T3.name_col as stream, T2.name_col as component, t4.name_col as project 
FROM(select T1.item_id, T1.type_col, T1.archived, T1.component_item_id, T1.name_col FROM VVCMODEL.CONFIGURATION T1 
where item_id IN 
(select S2.PARENT_STREAM_ITEM_ID 
from VVCMODEL.CHANGE_SET S2 
group by S2.PARENT_STREAM_ITEM_ID 
having count(S2.PARENT_STREAM_ITEM_ID) > 50000)) T3 INNER JOIN VVCMODEL.COMPONENT T2 
ON T3.component_item_id = T2.item_id INNER JOIN PROCESS.PROCESS_AREA T4 on T2.project_area_id = T4.item_id

componentsPerProject.sql (total number of components per project):

SELECT COUNT(*), PA.NAME_COL FROM VVCMODEL.COMPONENT C 
INNER JOIN PROCESS.PROCESS_AREA PA ON C.PROJECT_AREA_ID = PA.ITEM_ID 
GROUP BY PA.NAME_COL order by count(*) desc;

contributionsPerGC.sql (number of local DNG contributions per GC):

select composite_id, count(composite_id) from repository.contributions 
group by composite_id order by count(composite_id) desc

linksPerArtifact.sql (number of links per artifact):

select artifact, count(artifact) from (select subject_item_item_id as artifact 
from dnglinks.db_link union all select object_item_item_id as artifact from dnglinks.db_link) 
group by artifact order by count(artifact) desc

streamsPerComponent.sql (number of streams per component):

select component_item_id, count(component_item_id) 
from vvcmodel.configuration where type_col = 0 
group by component_item_id 
order by count(component_item_id) desc fetch first 100 rows only

tablespaceSizes.sql (display tablespace sizes):

call get_dbsize_info(?, ?, ?, -1)

totalArtifacts_v6.sql (total artifacts in DNG repository):

SELECT COUNT(distinct item_id) FROM DNGARTIFACTS.DB_ARTIFACT A

totalArtifacts_v7.sql (total artifacts in DNG repository inclusive of states):

SELECT COUNT(*) FROM DNGARTIFACTS.DB_ARTIFACT A

totalComponents.sql (count of all components):

SELECT COUNT(*) FROM VVCMODEL.COMPONENT C

versByComponent.sql (artifact versions in each component):

select component_id, count (component_id) as Comp 
from dngartifacts.db_artifact 
group by component_id order by Comp DESC

versByConfigid.sql (artifact versions in each configuration):

select config_id, count (config_id) as configs from REPOSITORY.VERSION 
group by config_id order by configs DESC

configurationByDateTime.sql (Returns all configs created between the two dates specified in final two lines):

Note: This query can be scoped to run for Streams, Baselines or both by setting the conf.TYPE_COL as follows

• Streams: conf.TYPE_COL IN (0)
• Baselines: conf.TYPE_COL IN (1)
• Streams and baslines: conf.TYPE_COL IN (0,1)

SELECT conf.CREATION_TIME, conf.ITEM_ID, conf.NAME_COL AS configuration_name, comp.NAME_COL AS component_name FROM VVCMODEL.CONFIGURATION conf
INNER JOIN VVCMODEL.COMPONENT comp 
ON conf.COMPONENT_ITEM_ID = comp.item_id 
WHERE conf.TYPE_COL IN (1) 
AND timestamp(conf.CREATION_TIME) < '2019-03-19 17:04:20' 
AND timestamp(conf.CREATION_TIME) > '2015-03-19 17:04:20'
ORDER BY conf.CREATION_TIME

identifyModuleWithRootBindingUUID.sql (Identify a Root Bindings associated Module UUID):

SELECT CONCAT('MD',MODULE_ITEM_ID)
FROM dngartifacts.db_binding_container WHERE item_id='<Module_Root_Binding_UUID>'

Note: The Module_Root_Binding_UUID used in the query above is the Root Binding UUID with the "RB" stub removed example Root binding is "RB_tOWWISU6Ee65jpJDsH-48Q" so for Module_Root_Binding_UUID insert "_tOWWISU6Ee65jpJDsH-48Q". You can open the module using the query result by adding it to the resource URL : https://jazz.ibm.com:9443/rm/resources/MD_ssY4mCU6Ee65jpJDsH-48Q' and open in a Browser.