Useful SQL Queries

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.

Number of Artifacts:

Oracle:

SELECT COUNT(*) FROM RMUSER.DNGARTIFACTS_DB_ARTIFACT

Db2:

SELECT COUNT(*) FROM DNGARTIFACTS.DB_ARTIFACT;

Number of Artifacts per Project:

Oracle:

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;

Db2:

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;

Number of Artifacts per Folder:

Oracle:

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

Db2:

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: -- If you want to get from the folder ID to the title, something like this should work: -- SELECT * FROM DNGFOLDERS.DB_FOLDER F WHERE F.ITEM_ID = 'uuid from previous query'

Number of Artifacts per component:

Oracle:

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

Db2:

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

Total number of Components:

Oracle:

SELECT COUNT(*) FROM RMUSER.VVCMODEL_COMPONENT

Db2:

SELECT COUNT(*) FROM VVCMODEL.COMPONENT;

Total number of Components per Project:

Oracle:

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

Db2:

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;

All Types:

Oracle:

column item_type format a80
select dbid, item_type from rmuser.repository_all_types;

Db2:

select dbid, substr(item_type,1,80) from repository.all_types

Binding States:

Oracle:

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

Db2:

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

Artifact States:

Oracle:

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);

Db2:

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)

Artifact states by component:

Oracle:

select component_id, count(component_id) from rmuser.dngartifacts_db_artifact 
group by component_id order by count(component_id);

Db2:

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

Large Streams:

Oracle:

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

Db2:

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

Changesets per stream:

Oracle:

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

Db2:

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

Top 100 Links:

Oracle:

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 fetch first 100 rows only

Db2:

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 fetch first 100 rows only

Top 100 Artifacts by Config ID:

Oracle:

set linesize 999
set pagesize 999
select config_id, count (distinct item_id) from RMUSER.REPOSITORY_VERSION group by config_id 
order by count(distinct item_id) DESC FETCH FIRST 100 ROWS ONLY;

Db2:

set linesize 999
set pagesize 999
select config_id, count (distinct item_id) from REPOSITORY.VERSION group by config_id 
order by count(distinct item_id) DESC FETCH FIRST 100 ROWS ONLY

Top 100 GCs:

Oracle:

select composite_id, count(composite_id) from rmuser.repository_contributions group by composite_id 
order by count(composite_id) desc fetch first 100 rows only;

Db2:

select composite_id, count(composite_id) from repository.contributions group by composite_id 
order by count(composite_id) desc fetch first 100 rows only;

Top 100 Modules V1:

Oracle:

select module_item_id, count(module_item_id) FROM   rmuser.dngartifacts_db_binding_contnr TBL_2A 
group by module_item_id order by count(module_item_id) desc fetch first 100 rows only;

Db2:

select module_item_id, count(module_item_id) FROM dngartifacts.db_binding_container TBL_2A 
group by module_item_id order by count(module_item_id) desc fetch first 100 rows only

Top 100 Modules V2:

Oracle:

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 fetch first 100 rows only;

Db2:

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 fetch first 100 rows only

Delivery Session:

Oracle:

select item_id, creator_item_id, STATE, SOURCE_ITEM_ID, TARGET_ITEM_ID, TITLE, TARGET_CHANGE_SET_ITEM_ID 
from rmuser.vvcmodel_deliver_session order by target_item_id

Db2:

select item_id, creator_item_id, STATE_COL, SOURCE_ITEM_ID, TARGET_ITEM_ID, TITLE, TARGET_CHANGE_SET_ITEM_ID 
from vvcmodel.deliver_session order by target_item_id

Primal By Config ID:

Oracle:

select config_id, count(config_id) from rmuser.vvcmodel_change_set_primal group by config_id;

Db2:

select config_id, count(config_id) from vvcmodel.change_set_primal group by config_id

Versions by Component:

Oracle:

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

Db2:

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

Versions by DBID:

Oracle:

--
-- Versions by config_id 
--
-- Input parameter:  schema name
--

ACCEPT SCHEMA PROMPT 'Schema name:'

set linesize 999
set pagesize 999

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

Db2:

select item_type_dbid, count (item_type_dbid) as Types from REPOSITORY.VERSION group by item_type_dbid order by Types DESC 

Versions by Config ID:

Oracle:

--
-- Versions by config_id 
--
-- 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;

Db2:

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

Row Counts:

Oracle:

--
-- List the row counts for the tables owned by the specifieid user
--
-- Input prameters:
--    Name of a user

ACCEPT OWNER PROMPT 'Owner:'
COLUMN table_name format a40
select  
   table_name,  
   num_rows counter  
from  
   dba_tables  
where  
   owner = '&OWNER' and num_rows > 0
order by  
   counter desc;

Db2:

select tabschema, tabname, card from syscat.tables order by card

Table Space Sizes:

Oracle:

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 ; 

Db2:

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