In EWM 7.0.1, new indexes would help DB2 performance on the following tables on z/OS:
**)2 on table CCM_REPOSITORY.DELETED_ITEMS, for columns
+- "CONTENT_DELETED" (ASC)
+- and "STATES_DELETED" (ASC)
SELECT ITEM_UUID , ITEM_TYPE_DBID FROM CCM_REPOSITORY.DELETED_ITEMS
WHERE CONTENT_DELETED = 0 OR STATES_DELETED = 0 FETCH FIRST 20 ROWS ONLY
**)2 on CCM_REPOSITORY.ITEM_STATES for columns
+- ITEM_TYPE_DBID, KEY_UUID, MODIFIED (ASC)
+- and ITEM_TYPE_DBID, MODIFIED, KEY_UUID (ASC)
SELECT T.ITEM_UUID , T.KEY_UUID , T.MODIFIED , T.VAL_ENCODING , T.ITEM_VALUE
FROM CCM_REPOSITORY.ITEM_STATES T
LEFT OUTER JOIN CCM_BUILDMAP.BUILD_MAP Q ON T.KEY_UUID = Q.STATE_ID
WHERE T.ITEM_TYPE_DBID = ? AND T.MODIFIED < ? AND T.KEY_UUID > ?
ORDER BY T.KEY_UUID ASC FETCH FIRST 1000 ROWS ONLY
**)1 on CCM_BUILDMAP.INPUT_BUILD_FILE for
+- columns STATE_ID, MODIFIED (ASC)
SELECT T.ITEM_UUID , T.KEY_UUID , T.MODIFIED , T.VAL_ENCODING , T.ITEM_VALUE , Q.MODIFIED
FROM CCM_REPOSITORY.ITEM_STATES T
LEFT OUTER JOIN CCM_BUILDMAP.INPUT_BUILD_FILE Q ON T.KEY_UUID = Q.STATE_ID
WHERE T.ITEM_TYPE_DBID = ? AND T.MODIFIED < ?
ORDER BY T.KEY_UUID ASC
FETCH FIRST 965 ROWS ONLY
**)2 on CCM_SCD.STREAM_DIRECTORY_COMPONENT_DIRECTORIES for columns
+- KEY_COL, JZ_PARENT_ID , VALUE_ITEM_ID (ASC)
+- and JZ_PARENT_ID , VALUE_ITEM_ID (ASC)
SELECT DISTINCT T6.LOGICAL_NAME , T6.FILE_TYPE , T6.REFERENCE_TYPE , T6.PATH_COL
FROM CCM_SCD.STREAM_DIRECTORY T1
INNER JOIN CCM_SCD.STREAM_DIRECTORY_COMPONENT_DIRECTORIES T2 ON ( T1.ITEM_ID = T2.JZ_PARENT_ID )
INNER JOIN CCM_SCD.COMPONENT_DIRECTORY T3 ON ( T2.VALUE_ITEM_ID = T3.ITEM_ID )
INNER JOIN CCM_SCD.COMPONENT_DIRECTORY_FILE_SOURCE_CODE_DATAS T4 ON ( T3.ITEM_ID = T4.JZ_PARENT_ID )
INNER JOIN CCM_SCD.FILE_SOURCE_CODE_DATA T5 ON ( T4.VALUE_ITEM_ID = T5.ITEM_ID )
INNER JOIN CCM_SCD.FILE_SOURCE_CODE_DATA_DEPENDENCIES J1 ON ( T5.ITEM_ID = J1.JZ_PARENT_ID )
INNER JOIN CCM_SCD.DEPENDENCY T6 ON ( J1.ITEM_ID = T6.ITEM_ID )
WHERE ( ( T1.ITEM_ID = ? ) AND ( T4.KEY_COL = ? ) AND ( T6.IGNORE_IN_BUILD = 0 ) )
**)1 on CCM_BUILD.BUILD_RESULT for columns
+- BUILD_DEFINITION_ITEM_ID, CONTEXT_ID (ASC)
select COUNT(*)from CCM_BUILD.BUILD_RESULT r1
inner join ctx on ctx.ctx_uuid = r1.CONTEXT_ID
where BUILD_DEFINITION_ITEM_ID = ?
;
**)1 on CCM_REPOSITORY.CONTEXT_MEMBERS for columns
+- MEMBER_UUID, CONTEXT_UUID (ASC)
with ctx(ctx_uuid) as
(SELECT distinct CCM_REPOSITORY.CONTEXT_SUBCONTEXTS.CONTEXT_UUID
from CCM_REPOSITORY.CONTEXT_SUBCONTEXTS
join CCM_REPOSITORY.CONTEXT_MEMBERS
on CCM_REPOSITORY.CONTEXT_MEMBERS.CONTEXT_UUID =
CCM_REPOSITORY.CONTEXT_SUBCONTEXTS.SUBCONTEXT_UUID
where CCM_REPOSITORY.CONTEXT_MEMBERS.MEMBER_UUID in(?, ?)
union select distinct CONTEXT_UUID from CCM_REPOSITORY.CONTEXT_MEMBERS
where MEMBER_UUID in(?, ?)
union (SELECT cast(? as char(23))
from sysibm.sysdummy1 )
union (SELECT cast(? as char(23))
from sysibm.sysdummy1 ) )
**)1 on CCM_REPOSITORY.CHANGE_EVENT for columns
JZ_ITEM_DISCRIMINATOR, MODIFIED, ITEM_ID (ASC)
SELECT C.ITEM_ID, C.MODIFIED FROM (SELECT ITEM_ID, MODIFIED FROM CCM_REPOSITORY.CHANGE_EVENT
WHERE MODIFIED >= ? AND JZ_ITEM_DISCRIMINATOR = ? AND EVENT_AUTHOR_ITEM_ID = ?
UNION SELECT A.ITEM_ID, A.MODIFIED FROM CCM_REPOSITORY.CHANGE_EVENT A, CCM_REPOSITORY.CHANGE_EVENT_EVENT_CONTRIBUTORS B
WHERE A.ITEM_ID = B.JZ_PARENT_ID AND A.MODIFIED >= ? AND A.JZ_ITEM_DISCRIMINATOR = ? AND B.ITEM_ID = ? ) ASC
order by C.MODIFIED desc ;
Thank you for recommending improvements to DB performance in ELM on z/OS. At this time it's unclear to our development team what impact the recommended index changes will have, so this item is being rejected. If you have specific performance insights, benchmarks, or other information you feel would justify the enhancement please feel free to open an RFE that includes that information. We'd be happy to re-evaluate it at that time.
Due to processing by IBM, this request was reassigned to have the following updated attributes:
Brand - Internet of Things
Product family - Engineering Lifecycle Management (ELM)
Product - ELM Common Services
Component - Administration
Operating system - Multiple
Source - None
For recording keeping, the previous attributes were:
Brand - Internet of Things
Product family - Engineering Lifecycle Management (ELM)
Product - IBM Engineering Workflow Management (formerly Rational Team Concert- RTC)
Component - Enterprise Extensions
Operating system - Multiple
Source - None
Due to processing by IBM, this request was reassigned to have the following updated attributes:
Brand - Internet of Things
Product family - Engineering Lifecycle Management (ELM)
Product - ELM Common Services
Component - Administration
Operating system - Multiple
Source - None
For recording keeping, the previous attributes were:
Brand - Internet of Things
Product family - Engineering Lifecycle Management (ELM)
Product - IBM Engineering Workflow Management (formerly Rational Team Concert- RTC)
Component - Enterprise Extensions
Operating system - Multiple
Source - None
**)1 on CCM_REPOSITORY.CHANGE_EVENT for columns
JZ_ITEM_DISCRIMINATOR, MODIFIED, ITEM_ID (ASC)
SELECT C.ITEM_ID, C.MODIFIED FROM (SELECT ITEM_ID, MODIFIED FROM CCM_REPOSITORY.CHANGE_EVENT
WHERE MODIFIED >= ? AND JZ_ITEM_DISCRIMINATOR = ? AND EVENT_AUTHOR_ITEM_ID = ?
UNION SELECT A.ITEM_ID, A.MODIFIED FROM CCM_REPOSITORY.CHANGE_EVENT A, CCM_REPOSITORY.CHANGE_EVENT_EVENT_CONTRIBUTORS B
WHERE A.ITEM_ID = B.JZ_PARENT_ID AND A.MODIFIED >= ? AND A.JZ_ITEM_DISCRIMINATOR = ? AND B.ITEM_ID = ? ) ASC
order by C.MODIFIED desc ;
**)1 on CCM_REPOSITORY.CONTEXT_MEMBERS for columns
+- MEMBER_UUID, CONTEXT_UUID (ASC)
with ctx(ctx_uuid) as
(SELECT distinct CCM_REPOSITORY.CONTEXT_SUBCONTEXTS.CONTEXT_UUID
from CCM_REPOSITORY.CONTEXT_SUBCONTEXTS
join CCM_REPOSITORY.CONTEXT_MEMBERS
on CCM_REPOSITORY.CONTEXT_MEMBERS.CONTEXT_UUID =
CCM_REPOSITORY.CONTEXT_SUBCONTEXTS.SUBCONTEXT_UUID
where CCM_REPOSITORY.CONTEXT_MEMBERS.MEMBER_UUID in(?, ?)
union select distinct CONTEXT_UUID from CCM_REPOSITORY.CONTEXT_MEMBERS
where MEMBER_UUID in(?, ?)
union (SELECT cast(? as char(23))
from sysibm.sysdummy1 )
union (SELECT cast(? as char(23))
from sysibm.sysdummy1 ) )
**)1 on CCM_BUILD.BUILD_RESULT for columns
+- BUILD_DEFINITION_ITEM_ID, CONTEXT_ID (ASC)
select COUNT(*)from CCM_BUILD.BUILD_RESULT r1
inner join ctx on ctx.ctx_uuid = r1.CONTEXT_ID
where BUILD_DEFINITION_ITEM_ID = ?
;
**)2 on CCM_SCD.STREAM_DIRECTORY_COMPONENT_DIRECTORIES for columns
+- KEY_COL, JZ_PARENT_ID , VALUE_ITEM_ID (ASC)
+- and JZ_PARENT_ID , VALUE_ITEM_ID (ASC)
SELECT DISTINCT T6.LOGICAL_NAME , T6.FILE_TYPE , T6.REFERENCE_TYPE , T6.PATH_COL
FROM CCM_SCD.STREAM_DIRECTORY T1
INNER JOIN CCM_SCD.STREAM_DIRECTORY_COMPONENT_DIRECTORIES T2 ON ( T1.ITEM_ID = T2.JZ_PARENT_ID )
INNER JOIN CCM_SCD.COMPONENT_DIRECTORY T3 ON ( T2.VALUE_ITEM_ID = T3.ITEM_ID )
INNER JOIN CCM_SCD.COMPONENT_DIRECTORY_FILE_SOURCE_CODE_DATAS T4 ON ( T3.ITEM_ID = T4.JZ_PARENT_ID )
INNER JOIN CCM_SCD.FILE_SOURCE_CODE_DATA T5 ON ( T4.VALUE_ITEM_ID = T5.ITEM_ID )
INNER JOIN CCM_SCD.FILE_SOURCE_CODE_DATA_DEPENDENCIES J1 ON ( T5.ITEM_ID = J1.JZ_PARENT_ID )
INNER JOIN CCM_SCD.DEPENDENCY T6 ON ( J1.ITEM_ID = T6.ITEM_ID )
WHERE ( ( T1.ITEM_ID = ? ) AND ( T4.KEY_COL = ? ) AND ( T6.IGNORE_IN_BUILD = 0 ) )
**)1 on CCM_BUILDMAP.INPUT_BUILD_FILE for
+- columns STATE_ID, MODIFIED (ASC)
SELECT T.ITEM_UUID , T.KEY_UUID , T.MODIFIED , T.VAL_ENCODING , T.ITEM_VALUE , Q.MODIFIED
FROM CCM_REPOSITORY.ITEM_STATES T
LEFT OUTER JOIN CCM_BUILDMAP.INPUT_BUILD_FILE Q ON T.KEY_UUID = Q.STATE_ID
WHERE T.ITEM_TYPE_DBID = ? AND T.MODIFIED < ?
ORDER BY T.KEY_UUID ASC
FETCH FIRST 965 ROWS ONLY
**)2 on CCM_REPOSITORY.ITEM_STATES for columns
+- ITEM_TYPE_DBID, KEY_UUID, MODIFIED (ASC)
+- and ITEM_TYPE_DBID, MODIFIED, KEY_UUID (ASC)
SELECT T.ITEM_UUID , T.KEY_UUID , T.MODIFIED , T.VAL_ENCODING , T.ITEM_VALUE
FROM CCM_REPOSITORY.ITEM_STATES T
LEFT OUTER JOIN CCM_BUILDMAP.BUILD_MAP Q ON T.KEY_UUID = Q.STATE_ID
WHERE T.ITEM_TYPE_DBID = ? AND T.MODIFIED < ? AND T.KEY_UUID > ?
ORDER BY T.KEY_UUID ASC FETCH FIRST 1000 ROWS ONLY
**)2 on table CCM_REPOSITORY.DELETED_ITEMS, for columns
+- "CONTENT_DELETED" (ASC)
+- and "STATES_DELETED" (ASC)