Edit
Attach
P
rintable
r7 - 2021-10-07 - 14:26:05 -
TimFeeney
You are here:
TWiki
>
Deployment Web
>
DeploymentMonitoring
>
UsefulSQlQueries
<div id="header-title" style="padding: 10px 15px; border-width:1px; border-style:solid; border-color:#FFD28C; background-image: url(<nop>https://jazz.net/wiki/pub/Deployment/WebPreferences/TLASE.jpg); background-size: cover; font-size:120%"> ---+!! Useful SQL Queries <img src="https://jazz.net/wiki/pub/Deployment/WebPreferences/todo.png" alt="todo.png" width="50" height="50" align="right"> %DKGRAY% Authors: Main.VaughnRokosz, Main.TimFeeney, Main.BrianSteele, Main.WilliamChatham<br> Build basis: 7.x %ENDCOLOR%</div></sticky> <!-- Page contents top of page on right hand side in box --> <sticky><div style="float:right; border-width:1px; border-style:solid; border-color:#DFDFDF; background-color:#F6F6F6; margin:0 0 15px 15px; padding: 0 15px 0 15px;"> %TOC{title="Page contents"}% </div></sticky> <sticky><div style="margin:15px;"></sticky> 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. <br> *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. <br><br> ---++ *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): <verbatim> select component_id, count (distinct item_id) as Comp from RMUSER.dngartifacts_db_artifact group by component_id order by Comp DESC; </verbatim> -- *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. <verbatim> select NAME from RMUSER.vvcmodel_component where item_id='an item_id from previous query'; </verbatim> <br> ----+++ artifactsPerFolder.sql (number of artifacts per folder): <verbatim> 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 </verbatim> -- *Note:* queries below are optional: If you want to get from the folder ID to the title, something like this should work: <verbatim> SELECT DISTINCT ITEM_ID, TITLE FROM RMUSER.DNGFOLDERS_DB_FOLDER F WHERE F.ITEM_ID = 'uuid from previous query' </verbatim> This will get better info on where the folder is at. However it only works on a single item id at a time <verbatim> 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 </verbatim> <br> ----+++ artifactsPerModule.sql (number of artifacts per module): <verbatim> 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; </verbatim> -- *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') "? <verbatim> select distinct title from RMUSER.DNGARTIFACTS_DB_ARTIFACT where item_id = 'value of module_item_id' </verbatim> This will get better info on where the module is at. It only works on a single module id at a time <verbatim> 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; </verbatim> <br> ----+++ artifactsPerProject_v7.sql (number of artifacts per project): <verbatim> 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; </verbatim> <br> ----+++ artifactstates.sql (states per artifact): <verbatim> 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); </verbatim> <br> ----+++ changesetsPerStream.sql (changesets per stream): <verbatim> 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 </verbatim> -- *Note:* query below is optional and can be used to convert PARENT_STREAM_ITEM_ID to something identifiable. <verbatim> select name from RMUSER.VVCMODEL_CONFIGURATION where item_id='value of parent_stream_id' </verbatim> <br> -- *Note:* this optional query can be used to convert PARENT_STREAM_ITEM_ID to something identifiable along with its component and project. <verbatim> 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 </verbatim> ----+++ componentsPerProject.sql (total number of components per project): <verbatim> 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 </verbatim> <br> ----+++ contributionsPerGC.sql (number of local DNG contributions per GC): <verbatim> select composite_id, count(composite_id) from rmuser.repository_contributions group by composite_id order by count(composite_id) desc; </verbatim> <br> ----+++ linksPerArtifact.sql (number of links per artifact): <verbatim> 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 </verbatim> <br> ----+++ streamsPerComponent.sql (number of streams per component): <verbatim> 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 </verbatim> <br> ----+++ tablespaceSizes.sql (display tablespace sizes): <verbatim> 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 ; </verbatim> <br> ----+++ totalArtifacts_v6.sql (total artifacts in DNG repository): <verbatim> SELECT COUNT(distinct item_id) FROM RMUSER.DNGARTIFACTS_DB_ARTIFACT </verbatim> <br> ----+++ totalArtifacts_v7.sql (total artifacts in DNG repository inclusive of states): <verbatim> SELECT COUNT(*) FROM RMUSER.DNGARTIFACTS_DB_ARTIFACT </verbatim> <br> ----+++ totalComponents.sql (count of all components): <verbatim> SELECT COUNT(*) FROM RMUSER.VVCMODEL_COMPONENT </verbatim> <br> ----+++ versByComponent.sql (artifact versions in each component): <verbatim> select component_id, count (component_id) as Comp from RMUSER.dngartifacts_db_artifact group by component_id order by Comp DESC; </verbatim> <br> ----+++ versByConfigid.sql (artifact versions in each configuration): <verbatim> -- 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; </verbatim> <br><br><br> ---++ *DB2* ** For each of these, login to the appropriate RM database and run the query. ** ----+++ artifactsPerComponent.sql (number of artifacts per component): <verbatim> select component_id, count (distinct item_id) as Comp from dngartifacts.db_artifact group by component_id order by Comp DESC </verbatim> -- *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. <verbatim> select NAME_COL from vvcmodel.component where item_id='an item_id from previous query'; </verbatim> <br> ----+++ artifactsPerFolder.sql (number of artifacts per folder): <verbatim> 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 </verbatim> -- *Note:* queries below are optional: If you want to get from the folder ID to the title, something like this should work: <verbatim> SELECT DISTINCT ITEM_ID, TITLE FROM DNGFOLDERS.DB_FOLDER F WHERE F.ITEM_ID = 'uuid from previous query' </verbatim> This will get better info on where the folder is at. It only works on a single item id at a time <verbatim> 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 </verbatim> <br> ----+++ artifactsPerModule.sql (number of artifacts per module): <verbatim> 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 </verbatim> -- *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') "? <verbatim> select distinct item_id,title from DNGARTIFACTS.DB_ARTIFACT where item_id = 'value of module_item_id' </verbatim> This will get better info on where the module is at. It only works on a single module id at a time <verbatim> 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 </verbatim> <br> ----+++ artifactsPerProject_v7.sql (number of artifacts per project): <verbatim> 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; </verbatim> <br> ----+++ artifactstates.sql (states per artifact): <verbatim> 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) </verbatim> <br> ----+++ changesetsPerStream.sql (changesets per stream): <verbatim> 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 </verbatim> -- *Note:* query below is optional and can be used to convert PARENT_STREAM_ITEM_ID to something identifiable. <verbatim> select name from VVCMODEL.CONFIGURATION where item_id='value of parent_stream_id' </verbatim> <br> -- *Note:* this optional query can be used to convert PARENT_STREAM_ITEM_ID to something identifiable along with its component and project. <verbatim> 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 </verbatim> ---+++ componentsPerProject.sql (total number of components per project): <verbatim> 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; </verbatim> <br> ----+++ contributionsPerGC.sql (number of local DNG contributions per GC): <verbatim> select composite_id, count(composite_id) from repository.contributions group by composite_id order by count(composite_id) desc </verbatim> <br> ----+++ linksPerArtifact.sql (number of links per artifact): <verbatim> 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 </verbatim> <br> ----+++ streamsPerComponent.sql (number of streams per component): <verbatim> 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 </verbatim> <br> ----+++ tablespaceSizes.sql (display tablespace sizes): <verbatim> call get_dbsize_info(?, ?, ?, -1) </verbatim> <br> ----+++ totalArtifacts_v6.sql (total artifacts in DNG repository): <verbatim> SELECT COUNT(distinct item_id) FROM DNGARTIFACTS.DB_ARTIFACT A </verbatim> <br> ----+++ totalArtifacts_v7.sql (total artifacts in DNG repository inclusive of states): <verbatim> SELECT COUNT(*) FROM DNGARTIFACTS.DB_ARTIFACT A </verbatim> <br> ----+++ totalComponents.sql (count of all components): <verbatim> SELECT COUNT(*) FROM VVCMODEL.COMPONENT C </verbatim> <br> ----+++ versByComponent.sql (artifact versions in each component): <verbatim> select component_id, count (component_id) as Comp from dngartifacts.db_artifact group by component_id order by Comp DESC </verbatim> <br> ----+++ versByConfigid.sql (artifact versions in each configuration): <verbatim> select config_id, count (config_id) as configs from REPOSITORY.VERSION group by config_id order by configs DESC </verbatim> <br> <sticky></div></sticky>
Edit
|
Attach
|
P
rintable
|
V
iew topic
|
Backlinks:
We
b
,
A
l
l Webs
|
H
istory
:
r10
<
r9
<
r8
<
r7
<
r6
|
More topic actions...
Deployment
Deployment web
Planning and design
Installing and upgrading
Migrating and evolving
Integrating
Administering
Monitoring
Troubleshooting
Community information and contribution guidelines
Create new topic
Topic list
Search
Advanced search
Notify
RSS
Atom
Changes
Statistics
Web preferences
NOTE: Please use the Sandbox web for testing
Status icon key:
To do
Under construction
New
Updated
Constant change
None - stable page
Smaller versions of status icons for inline text:
Copyright © by IBM and non-IBM contributing authors. All material on this collaboration platform is the property of the contributing authors.
Contributions are governed by our
Terms of Use.
Please read the following
disclaimer
.
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
.