zOs/WK/T

CREATE VIEW OA1P.PLAN_VIEW1 AS
select  substr(right('     ' || strip(char(queryNo))   , 6)
            || right('  '    || strip(char(qBlockNo))  , 2)
            || right('  '    || strip(char(planNo))    , 2)
            || right('  '    || strip(char(mixOpSeq)), 1)
            ,1 ,11) "Queryn B PM",
        QBLOCK_TYPE AS "TYPE",
        CASE WHEN PRIMARY_ACCESSTYPE = 'D' then 'dirRow'
             WHEN ACCESSTYPE = 'I ' THEN 'ixScan'
             WHEN ACCESSTYPE = 'I1' THEN 'ixOne '
             WHEN ACCESSTYPE = 'R ' THEN 'tsScan'
             WHEN ACCESSTYPE = 'RW' THEN 'woScan'
             WHEN ACCESSTYPE = 'N ' THEN 'ixSPin'
             WHEN ACCESSTYPE = 'M ' THEN 'ixMult'
             WHEN ACCESSTYPE = 'MX' THEN 'ixMSca'
             WHEN ACCESSTYPE = 'MI' THEN 'ixMInt'
             WHEN ACCESSTYPE = 'MU' THEN 'ixMUni'
             WHEN ACCESSTYPE = 'T ' THEN 'ixSPRS'
             WHEN ACCESSTYPE = 'V ' THEN 'insBuf'
             WHEN ACCESSTYPE = '  ' THEN '      '
             ELSE                        '??' || accessType
        END AS ACCESS,
        CASE WHEN ACCESSTYPE = 'R  ' THEN '       '
             when PRIMARY_ACCESSTYPE = 'D' THEN '       '
             ELSE SUBSTR(ACCESSNAME, 1, 12) END AS "INDEX",
        SUBSTR(TNAME, 1, 12) AS "TABLE",
        CASE WHEN TABLE_TYPE IS NULL THEN '      '
             ELSE TABLE_TYPE END AS TTYP,
        CASE WHEN METHOD = 3 THEN '      '
             WHEN ACCESSTYPE = 'R ' THEN '      '
             ELSE SUBSTR(DIGITS(MATCHCOLS), 5, 1) || '  ' || indexOnly
        END AS MC_O,
        CASE METHOD WHEN 0 THEN '      '
            WHEN 1 THEN 'NLJOIN'
            when 2 THEN 'SMJOIN'
            WHEN 3 THEN 'SORT  '
            WHEN 4 THEN 'HYJOIN'
            ELSE NULL END AS METHOD,
        CASE JOIN_TYPE WHEN 'F' THEN 'full'
            WHEN 'L' THEN 'left'
            WHEN 'S' THEN 'star'
            ELSE ' ' END AS "joiT",
        SORTN_UNIQ CONCAT SORTN_JOIN CONCAT SORTN_ORDERBY CONCAT
            SORTN_GROUPBY AS UJOG,
        SORTC_UNIQ CONCAT SORTC_JOIN CONCAT SORTC_ORDERBY CONCAT
            SORTC_GROUPBY AS UJOC,
        TSLOCKMODE AS LCK,
        SUBSTR(CORRELATION_NAME, 1, 4) AS CORR,
        CASE PARALLELISM_MODE WHEN 'C' THEN 'CPU'
            WHEN 'I' THEN 'I-O'
            WHEN 'X' THEN 'SYSPLEX'
            ELSE NULL END AS PARAL,
        STRIP(DIGITS(ACCESS_DEGREE), LEADING, '0') || ' '
            || STRIP(DIGITS(JOIN_DEGREE), LEADING, '0') AS AJ_DEG,
        STRIP(DIGITS(ACCESS_PGROUP_ID), LEADING, '0')|| ' '
            || STRIP(DIGITS(JOIN_PGROUP_ID), LEADING, '0') PG_DEG,
        STRIP(DIGITS(MERGE_JOIN_COLS), LEADING, '0') AS MC,
        PREFETCH AS PRE,
        page_range as pgRa,
        substr(opthint, 1, 10) optHint,
        substr(hint_used, 1, 10) hint_used,
            -- full length names
        TNAME, ACCESSNAME, accessType,
            -- package identifikation
        collid, progName, applName, version, bind_time,
            -- query node identificaten
        queryno, qBlockNo, planno, mixopSeq, timestamp,
            PARENT_QBLOCKNO
     FROM OA1P.PLAN_TABLE A
;