Edit
Attach
P
rintable
r5 - 2021-09-29 - 08:20:44 -
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><br> ---++ Number of Artifacts: Oracle: <verbatim> SELECT COUNT(*) FROM RMUSER.DNGARTIFACTS_DB_ARTIFACT </verbatim> Db2: <verbatim> SELECT COUNT(*) FROM DNGARTIFACTS.DB_ARTIFACT; </verbatim> <br> ---++ Number of Artifacts per Project: Oracle: <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> Db2: <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> ---++ Number of Artifacts per Folder: Oracle: <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> Db2: <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: -- 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' <br> ---++ Number of Artifacts per component: Oracle: <verbatim> select component_id, count (distinct item_id) as Comp from RMUSER.dngartifacts_db_artifact group by component_id order by Comp DESC; </verbatim> Db2: <verbatim> select component_id, count (distinct item_id) as Comp from dngartifacts.db_artifact group by component_id order by Comp DESC </verbatim> <br> ---++ Total number of Components: Oracle: <verbatim> SELECT COUNT(*) FROM RMUSER.VVCMODEL_COMPONENT </verbatim> Db2: <verbatim> SELECT COUNT(*) FROM VVCMODEL.COMPONENT; </verbatim> <br> ---++ Total number of Components per Project: Oracle: <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> Db2: <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> ---++ All Types: Oracle: <verbatim> column item_type format a80 select dbid, item_type from rmuser.repository_all_types; </verbatim> Db2: <verbatim> select dbid, substr(item_type,1,80) from repository.all_types </verbatim> <br> ---++ Binding States: Oracle: <verbatim> 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); </verbatim> Db2: <verbatim> 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) </verbatim> <br> ---++ Artifact States: Oracle: <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> Db2: <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> ---++ Artifact states by component: Oracle: <verbatim> select component_id, count(component_id) from rmuser.dngartifacts_db_artifact group by component_id order by count(component_id); </verbatim> Db2: <verbatim> select component_id, count(component_id) from dngartifacts.db_artifact group by component_id order by count(component_id) </verbatim> <br> ---++ Large Streams: Oracle: <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> Db2: <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> <br> ---++ Changesets per stream: Oracle: <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> Db2: <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> <br> ---++Top 100 Links: Oracle: <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 fetch first 100 rows only </verbatim> Db2: <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 fetch first 100 rows only </verbatim> <br> ---++Top 100 Artifacts by Config ID: Oracle: <verbatim> 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; </verbatim> Db2: <verbatim> 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 </verbatim> <br> ---++Top 100 GCs: Oracle: <verbatim> 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; </verbatim> Db2: <verbatim> select composite_id, count(composite_id) from repository.contributions group by composite_id order by count(composite_id) desc fetch first 100 rows only; </verbatim> <br> ---++Top 100 Modules V1: Oracle: <verbatim> 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; </verbatim> Db2: <verbatim> 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 </verbatim> <br> ---++Top 100 Modules V2: Oracle: <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 fetch first 100 rows only; </verbatim> Db2: <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 fetch first 100 rows only </verbatim> <br> ---++ Delivery Session: Oracle: <verbatim> 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 </verbatim> Db2: <verbatim> 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 </verbatim> <br> ---++ Primal By Config ID: Oracle: <verbatim> select config_id, count(config_id) from rmuser.vvcmodel_change_set_primal group by config_id; </verbatim> Db2: <verbatim> select config_id, count(config_id) from vvcmodel.change_set_primal group by config_id </verbatim> <br> ---++ Versions by Component: Oracle: <verbatim> select component_id, count (component_id) as Comp from RMUSER.dngartifacts_db_artifact group by component_id order by Comp DESC; </verbatim> Db2: <verbatim> select component_id, count (component_id) as Comp from dngartifacts.db_artifact group by component_id order by Comp DESC </verbatim> <br> ---++ Versions by DBID: Oracle: <verbatim> -- -- 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; </verbatim> Db2: <verbatim> select item_type_dbid, count (item_type_dbid) as Types from REPOSITORY.VERSION group by item_type_dbid order by Types DESC </verbatim> <br> ---++ Versions by Config ID: Oracle: <verbatim> -- -- 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; </verbatim> Db2: <verbatim> select config_id, count (config_id) as configs from REPOSITORY.VERSION group by config_id order by configs DESC </verbatim> <br> ---++ Row Counts: Oracle: <verbatim> -- -- 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; </verbatim> Db2: <verbatim> select tabschema, tabname, card from syscat.tables order by card </verbatim> <br> ---++ Table Space Sizes: Oracle: <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> Db2: <verbatim> call get_dbsize_info(?, ?, ?, -1) </verbatim> <br> <sticky></div></sticky>
Edit
|
Attach
|
P
rintable
|
V
iew topic
|
Backlinks:
We
b
,
A
l
l Webs
|
H
istory
:
r10
|
r7
<
r6
<
r5
<
r4
|
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
.