Db2

Db2

Where DBAs and data experts come together to stop operating and start innovating. Connect, share, and shape the AI era with us.


#Data


#Data
#Databases
#Operatingsystems
#Db2
#Databasesolutions
 View Only

Wrong result with an "ANY aggregation function emulation" attempt

  • 1.  Wrong result with an "ANY aggregation function emulation" attempt

    Posted 10 days ago

    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
    ------------------------------