Tips on Using db2advis with Multiple Queries

You may also like...

2 Responses

  1. thomas Koczon says:

    Hi,
    I have some sql I would like to explain/advise but it has multiple steps which confounds db2expln and db2advis :

    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMPTABLE1
    AS (SELECT DISTINCT ACTION_ID AS COL1
    FROM ACTION
    WHERE LTRIM(RTRIM(ACTION.ACTION_DESC)) LIKE ‘%Pend%’) DEFINITION ONLY
    ON COMMIT DELETE ROWS
    NOT LOGGED;

    INSERT INTO SESSION.TEMPTABLE1
    SELECT DISTINCT ACTION_ID
    FROM ACTION
    WHERE LTRIM(RTRIM(ACTION.ACTION_DESC)) LIKE ‘%Pend%’;
    –#SET FREQUENCY 4
    SELECT P.WORK_ITEM_FACT_ID,
    P.WORK_ITEM_ID,
    P.WORK_ITEM_ACTION_END_TS AS PEND_START_TIME,
    MIN(NP.WORK_ITEM_ACTION_BEGIN_TS) AS PEND_END_TIME,
    (SELECT COUNT(1)
    FROM CAL_DATE
    WHERE (CO_HOLIDAY_FL = ‘Y’
    OR WEEKDAY_FL = ‘N’)
    AND CAL_DT BETWEEN DATE(P.WORK_ITEM_ACTION_END_TS) AND DATE(MIN(NP.WORK_ITEM_ACTION_BEGIN_TS))) AS HOLIDAYS_COUNT
    FROM (SELECT WORK_ITEM_FACT1.WORK_ITEM_FACT_ID,
    WORK_ITEM_FACT1.WORK_ITEM_ID,
    WORK_ITEM_ACTION_FACT1.WORK_ITEM_ACTION_END_TS
    FROM SESSION.TEMPTABLE1,
    WORK_ITEM_FACT WORK_ITEM_FACT1,
    WORK_ITEM_ACTION_FACT WORK_ITEM_ACTION_FACT1
    WHERE WORK_ITEM_FACT1.LAST_MODIFIED_DATE = ANY (SELECT POPULATION_TS
    FROM POPULATION_INFO
    WHERE LOAD_STATUS_CD = ‘C’
    AND UNIQUE_ETL_PROCESS_ID = ‘SIW RETAIL FACT LOAD’)
    AND WORK_ITEM_FACT1.WORK_ITEM_ID = WORK_ITEM_ACTION_FACT1.WORK_ITEM_ID
    AND WORK_ITEM_ACTION_FACT1.ACTION_ID = SESSION.TEMPTABLE1.COL1) P
    left outer join (SELECT WORK_ITEM_FACT2.WORK_ITEM_FACT_ID,
    WORK_ITEM_FACT2.WORK_ITEM_ID,
    WORK_ITEM_ACTION_FACT2.WORK_ITEM_ACTION_BEGIN_TS
    FROM WORK_ITEM_FACT WORK_ITEM_FACT2,
    WORK_ITEM_ACTION_FACT WORK_ITEM_ACTION_FACT2
    WHERE WORK_ITEM_FACT2.LAST_MODIFIED_DATE = (SELECT POPULATION_TS
    FROM POPULATION_INFO
    WHERE LOAD_STATUS_CD = ‘C’
    DROP TABLE SESSION.TEMPTABLE1;

    Any ideas on how to get this to run through db2advis or db2expln ?

    thanks

    • Ember Crooks says:

      You really have to run each statement through individually. You can use db2advis to retain explain information, but then generating explain plans off of that information is not simple. It is easier to run each statement with your explain mode set to “yes” (which will both execute and store explain info), and then between each, run db2exfmt (with the -1 option) to get the formatted explain data.

Leave a Reply

Your email address will not be published. Required fields are marked *