Edit
Attach
P
rintable
r6 - 2021-10-07 - 12:52:46 -
WillChatham
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. 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 dispaly 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 (Artifact States): <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: How would I convert S2.PARENT_STREAM_ITEM_ID to an identifiable stream? <verbatim> select name from RMUSER.VVCMODEL_CONFIGURATION where item_id='value of parent_stream_id' </verbatim> <br> ----+++ 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 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> *Warning* This query could run long so be sure to first test this in a non-production environment. <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 (Dispaly Table Space 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): <verbatim> SELECT COUNT(distinct item_id) FROM RMUSER.DNGARTIFACTS_DB_ARTIFACT </verbatim> <br> ----+++ totalArtifacts_v7.sql (Total Artifacts 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 (Versions by 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 (Versions by Config ID): <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* ----+++ 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 dispaly 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 (Artifact States): <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> ----+++ 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: How would I convert S2.PARENT_STREAM_ITEM_ID to an identifiable stream? <verbatim> select name from VVCMODEL.CONFIGURATION where item_id='value of parent_stream_id' </verbatim> <br> ---+++ 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 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> *Warning* This query could run long so be sure to first test this in a non-production environment. <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 (Dispaly Table Space Sizes): <verbatim> call get_dbsize_info(?, ?, ?, -1) </verbatim> <br> ----+++ totalArtifacts_v6.sql (Total Artifacts): <verbatim> SELECT COUNT(distinct item_id) FROM DNGARTIFACTS.DB_ARTIFACT A </verbatim> <br> ----+++ totalArtifacts_v7.sql (Total Artifacts 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 (Versions by 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 (Versions by Config ID): <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
|
r8
<
r7
<
r6
<
r5
|
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
.