1

Db2 11.1.4.6, pureScale.

Some database parameters & registry variables which could affect.

SELECT name, value
FROM SYSIBMADM.DBCFG
WHERE NAME IN ('stmt_conc', 'number_compat', 'varchar2_compat', 'date_compat')
AND MEMBER = 0
NAME VALUE
number_compat ON
varchar2_compat ON
date_compat OFF
stmt_conc LITERALS
SELECT REG_VAR_NAME, REG_VAR_VALUE
FROM TABLE (ENV_GET_REG_VARIABLES (0))
WHERE REG_VAR_NAME IN ('DB2_COMPATIBILITY_VECTOR', 'DB2_WORKLOAD')
REG_VAR_NAME REG_VAR_VALUE
DB2_WORKLOAD MAXIMO
DB2_COMPATIBILITY_VECTOR 70FBF

There is a huge number of the same statements - with the same (stmtid, semantic_env_id, stmt_text) - in the package cache.

SELECT member, stmtid, semantic_env_id, hash8 (stmt_text) AS STMT_HASH, COUNT (1) CNT
FROM TABLE (MON_GET_PKG_CACHE_STMT (NULL, NULL, NULL, -2))
GROUP BY member, stmtid, semantic_env_id, hash8 (stmt_text)
HAVING COUNT (1) > 1
ORDER BY CNT DESC
LIMIT 10
MEMBER STMTID SEMANTIC_ENV_ID STMT_HASH CNT
0 7026464291071940313 8094453505663225696 8080118808105692587 701
0 -4423404016290598011 8094453505663225696 -6964390111118859501 596
0 -2976305540666529598 8094453505663225696 -8432574562552866545 373
0 8497732506233804054 8094453505663225696 -1435683933981161312 298
1 7026464291071940313 8094453505663225696 8080118808105692587 285
0 980082547224038337 8094453505663225696 -8180107880424297015 247
0 9190953388972182367 8094453505663225696 780659571891354795 228
0 405608777460510610 8094453505663225696 4416087251436010086 199
0 -8786732462121039056 8094453505663225696 2949867518568303186 172
0 -5730754857364697872 8094453505663225696 796985381428379677 172

It's expected to have only 1 copy of these statements in the package cache instead of these hundreds.
What could be a reason of such a strange behavior, and how to avoid it?

Update

Actually, it seems that it doesn't depend on pureScale or connection concentrator.
For example, I got duplicate entries (not hunderds, but multiple groups with 2 identical statements) on my 11.5.7 sample database as well with the following statement, which anybody can run on their databases to check the same.
The query compares compilation environment as well. I see with this query, that 2 queries with the same (STMTID, SEMANTIC_ENV_ID) may really have different compilation environment.
In my case the difference is in the following fields: EXTENDED INDICATOR, ISOLATION, PATH.
But there are 16 distinct such statements only...

WITH M AS 
-- Duplicates
(
  SELECT *
  FROM
  (
    SELECT
      MEMBER
    , STMTID
    , SEMANTIC_ENV_ID
    , HASH8 (STMT_TEXT) AS STMT_HASH
    , COMP_ENV_DESC
    , INSERT_TIMESTAMP
    , STMT_EXEC_TIME
    , NUM_EXEC_WITH_METRICS
    , STMT_TEXT
    , COUNT (1) OVER (PARTITION BY MEMBER, STMTID, SEMANTIC_ENV_ID, HASH8 (STMT_TEXT))
        AS CNT
    FROM TABLE (MON_GET_PKG_CACHE_STMT ('D', NULL, NULL, -2))
  )
  WHERE CNT > 1
)
, DIFF AS 
(
  -- If duplicates have different compilation environment
  -- except 'RESOLUTION_TIMESTAMP'
  SELECT 
    M.MEMBER, M.STMTID, M.SEMANTIC_ENV_ID, M.STMT_HASH, E.NAME
  , MIN (E.VALUE)                           AS VALUE_MIN
  , MAX (E.VALUE)                           AS VALUE_MAX
  , COUNT (DISTINCT E.VALUE)                AS DIST_VALUES_CNT
  , COUNT (DISTINCT M.INSERT_TIMESTAMP) AS DIST_IT_CNT
  FROM 
    M
  , TABLE (COMPILATION_ENV (M.COMP_ENV_DESC)) E
  WHERE E.NAME <> 'RESOLUTION_TIMESTAMP'
  GROUP BY M.MEMBER, M.STMTID, M.SEMANTIC_ENV_ID, M.STMT_HASH, E.NAME
  HAVING COUNT (DISTINCT E.VALUE) > 1
)
/*
SELECT *
FROM DIFF
ORDER BY MEMBER, STMTID, SEMANTIC_ENV_ID, STMT_HASH, NAME
*/
SELECT COUNT (1)
FROM
(
  SELECT DISTINCT MEMBER, STMTID, SEMANTIC_ENV_ID, STMT_HASH
  FROM DIFF
)

Duplicates with absolutely the same compilation environment:

WITH M AS 
-- Duplicates
(
  SELECT *
  FROM
  (
    SELECT
      MEMBER
    , STMTID
    , SEMANTIC_ENV_ID
    , HASH8 (STMT_TEXT) AS STMT_HASH
    , COMP_ENV_DESC
    , INSERT_TIMESTAMP
    , STMT_EXEC_TIME
    , NUM_EXEC_WITH_METRICS
    , STMT_TEXT
    , COUNT (1) OVER (PARTITION BY MEMBER, STMTID, SEMANTIC_ENV_ID, HASH8 (STMT_TEXT))
        AS CNT
    FROM TABLE (MON_GET_PKG_CACHE_STMT ('D', NULL, NULL, -2))
  )
  WHERE CNT > 1
)
-- Statements with distinct environment
, D AS 
(
  SELECT DISTINCT M.MEMBER, M.STMTID, M.SEMANTIC_ENV_ID, M.STMT_HASH
  FROM 
    M
  , TABLE (COMPILATION_ENV (M.COMP_ENV_DESC)) E
  WHERE E.NAME <> 'RESOLUTION_TIMESTAMP'
  GROUP BY M.MEMBER, M.STMTID, M.SEMANTIC_ENV_ID, M.STMT_HASH, E.NAME
  HAVING COUNT (DISTINCT E.VALUE) > 1
)
SELECT
  MEMBER
, STMTID
, SEMANTIC_ENV_ID
, STMT_HASH
, INSERT_TIMESTAMP
, STMT_EXEC_TIME
, NUM_EXEC_WITH_METRICS
, CNT
, STMT_TEXT
FROM M
WHERE NOT EXISTS 
(
  SELECT 1
  FROM D
  WHERE 
    (D.MEMBER, D.STMTID, D.SEMANTIC_ENV_ID, D.STMT_HASH)
  = (M.MEMBER, M.STMTID, M.SEMANTIC_ENV_ID, M.STMT_HASH)
)
ORDER BY M.MEMBER, M.STMTID, M.SEMANTIC_ENV_ID, M.STMT_HASH, M.INSERT_TIMESTAMP
LIMIT 10
7
  • Are they all for section_type = 'D' ?
    – mao
    Commented Mar 22, 2023 at 15:11
  • Yes. The same result if I include section_type (which is equal to 'D') in the select statement. Commented Mar 22, 2023 at 15:16
  • It must be only a single entry for the same statement in the package cache disregarding of the statement concentrator setting. The only exception I can imagine could be if, say, some application still has opened cursor on one copy of the statement, while it was time to recompile the statement because of, say, runstats on participating tables. But it's not the case here - there are no corresponding activities in the database. What I see here looks like a clear defect. My intention is to understand, if it's some known "feature" and how to avoid it if possible. Commented Mar 22, 2023 at 15:29
  • @mao I've edited my post with a statemen, which anybody can run on their database to check. I've got nearly the same picture on my non-pureScale, non-Maximo, not-statement_concentrator sample 11.5 database. Commented Mar 22, 2023 at 15:53
  • On my linux x64 v11.5.0.8, I get no statements with >1 entries, all have 1, using simple different literals in multiple queries, stmt_conc off.
    – mao
    Commented Mar 22, 2023 at 16:54

2 Answers 2

2

I think you can see why inspecting the content of COMP_ENV_DESC. Say for example Maximo makes use of SET CURRENT TEMPORAL BUSINESS_TIME = with hundreds of distinct values

SELECT
  MEMBER
, STMTID
, SEMANTIC_ENV_ID
, INSERT_TIMESTAMP
, STMT_EXEC_TIME
, NUM_EXEC_WITH_METRICS
, STMT_TEXT
, CNT
, NAME
, VALUE
FROM
(
SELECT
*
, CASE WHEN MIN(VALUE) OVER (PARTITION BY MEMBER, STMTID, SEMANTIC_ENV_ID, HASH8 (STMT_TEXT), NAME) 
        IS DISTINCT FROM MAX(VALUE) OVER (PARTITION BY MEMBER, STMTID, SEMANTIC_ENV_ID, HASH8 (STMT_TEXT), NAME)
THEN TRUE ELSE FALSE END AS DIFFERENCE_IN_COMP_ENV_DESC
FROM
(
SELECT
  MEMBER
, STMTID
, SEMANTIC_ENV_ID
, INSERT_TIMESTAMP
, STMT_EXEC_TIME
, NUM_EXEC_WITH_METRICS
, STMT_TEXT
, COMP_ENV_DESC
, COUNT (1) OVER (PARTITION BY MEMBER, STMTID, SEMANTIC_ENV_ID, HASH8 (STMT_TEXT))
    AS CNT
FROM TABLE (MON_GET_PKG_CACHE_STMT ('D', NULL, NULL, -2))
)
CROSS JOIN TABLE(COMPILATION_ENV(COMP_ENV_DESC)) ce
WHERE CNT > 1 and NAME != 'RESOLUTION_TIMESTAMP'
)
WHERE DIFFERENCE_IN_COMP_ENV_DESC = TRUE
AND stmt_text = 'SELECT * FROM SYSDUMMY1'
ORDER BY CNT DESC, MEMBER, STMTID, SEMANTIC_ENV_ID, hash8(COMP_ENV_DESC) 
LIMIT 10

EDIT: I just tried with this script and I have 4 rows wtih the same MEMBER, STMTID, SEMANTIC_ENV_ID, HASH8 (STMT_TEXT)

SET schema SYSIBM;
SET CURRENT temporal business_time = CURRENT timestamp - 1 day;
SELECT * FROM SYSDUMMY1;
SET CURRENT temporal business_time = CURRENT timestamp - 2 days;
SELECT * FROM SYSDUMMY1;
-- - 2 days again, I did not see it but current timestamp was different
SET CURRENT temporal business_time = CURRENT timestamp - 2 days; 
SELECT * FROM SYSDUMMY1;
SET CURRENT temporal business_time = null;
SELECT * FROM SYSDUMMY1;

gives

MEMBER  STMTID                SEMANTIC_ENV_ID       INSERT_TIMESTAMP            STMT_EXEC_TIME        NUM_EXEC_WITH_METRICS  STMT_TEXT                                           CNT          NAME                                                VALUE                                               
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0       5264689468733577611   3464141837497415252   2023-03-22 18:15:38.531108  0                     1                      SELECT * FROM SYSDUMMY1                             4            CURRENT TEMPORAL BUSINESS_TIME                      2023-03-21-18.15.38.523288000000                    
0       5264689468733577611   3464141837497415252   2023-03-22 18:15:38.600465  0                     1                      SELECT * FROM SYSDUMMY1                             4            CURRENT TEMPORAL BUSINESS_TIME                      0000-00-00-00.00.00.000000000000                    
0       5264689468733577611   3464141837497415252   2023-03-22 18:15:38.578281  0                     1                      SELECT * FROM SYSDUMMY1                             4            CURRENT TEMPORAL BUSINESS_TIME                      2023-03-20-18.15.38.570260000000                    
0       5264689468733577611   3464141837497415252   2023-03-22 18:15:38.55515   0                     1                      SELECT * FROM SYSDUMMY1                             4            CURRENT TEMPORAL BUSINESS_TIME                      2023-03-20-18.15.38.547350000000                    
3
  • COMP_ENV_DESC is absolutely the same for duplicate entries. If it was different, SEMANTIC_ENV_ID must be different, but it's the same. The difference you might see with your statement is because of difference in the RESOLUTION_TIMESTAMP field of COMP_ENV_DESC only. Commented Mar 22, 2023 at 16:48
  • @MarkBarinstein Just made an edit, I have the same SEMANTIC_ENV_ID with different COMP_ENV_DESC and I removed RESOLUTION_TIMESTAMP, just to say. I'm sorry that doesn't show any difference in your case
    – nfgl
    Commented Mar 22, 2023 at 17:36
  • I've edited the last statement, which really shows, that compilation environment may be different for the same (STMTID, SEMANTIC_ENV_ID) not only in RESOLUTION_TIMESTAMP. But still most of my duplicate queries have no difference in the compilation environment. Commented Mar 22, 2023 at 18:37
0

Not much of an answer, but I spotted a number of "duplicates" in a database. The only thing that seems to differ (including COMPILATION_ENV - RESOLUTION_TIMESTAMP) is the metrics and the executable_id, which indicates that there has to be something else than COMPILATION_ENV that causes a new executable_id. One thing I noted is that two randomly picked executable_id for the same stmt have different section_env, not sure if it's relevant:

with se1 as (
    select section_env 
    from table(MON_GET_SECTION(<executable_id 1>))
) , se2 as (
    select section_env 
    from table(MON_GET_SECTION(<executable_id 2>))
) 
select dbms_lob.compare(se1.section_env, se2.section_env) 
from se1 
cross join se2

1          
-----------
          1 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.