Hello.
It's reproduced on all DB2 for LUW versions: 12.1, 11.5, 11.1.
I'm not able to open a Case with IBM Support, so I'd like someone to do it just because the technique used here could be useful for others...
Briefly:
Here is an example of DB2 package cache analysis.
Sometimes it's useful to group the entries for "similar" statements: the same statements with constants instead of parameter markers, the statements like 'in (?), in (?, ..., ?)', etc. All of them have the same values for (member, semantic_env_id, stmtid), but different stmt_text and other field values.
But it's convenient to show the statement text of whatever statement (just for referencd) in such a group along with aggregated metrics.
There is no ANY aggregation function in DB2, but it can be emulated with XMLROW & XMLAGG functions like in the example below, which one may run as is to look at the wrong result - the output rows are erroneously duplicated.
But, if I uncomment the '--, M.XML_DOC' in the SELECT list or the '--WHERE X.STMT_TEXT IS NOT NULL' clause (these actions must not change the result set), I get the correct result.
SELECT
M.MEMBER, M.semantic_env_id, M.stmtid, M.num_diffferent_stmts
--, M.XML_DOC
, X.STMT_TEXT
FROM
(
SELECT
N.member, N.semantic_env_id, N.stmtid
, COUNT(1) AS num_diffferent_stmts
, XMLQUERY('$D[1]' PASSING XMLAGG(
XMLROW(
N.query_cost_estimate
, N.stmt_text
)
) AS "D") AS XML_DOC
FROM TABLE
(
--MON_GET_PKG_CACHE_STMT(NULL, NULL, NULL, -2)
SELECT *
FROM
(
VALUES
(0, 1, 1, 1, 'values 1')
, (0, 1, 1, 2, 'values 2')
) T (member, semantic_env_id, stmtid, query_cost_estimate, stmt_text)
) N
GROUP BY N.member, N.semantic_env_id, N.stmtid
HAVING COUNT(1) > 1
) M
CROSS JOIN XMLTABLE
(
'$D/row' PASSING M.XML_DOC AS "D"
COLUMNS
QUERY_COST_ESTIMATE BIGINT PATH 'QUERY_COST_ESTIMATE'
, STMT_TEXT CLOB(2M) PATH 'STMT_TEXT'
) X
--WHERE X.STMT_TEXT IS NOT NULL
ORDER BY M.member, M.semantic_env_id, M.stmtid
Here is a fiddle link to test.
------------------------------
Mark Barinstein
------------------------------