zOs/SQL/OPTHINTC

set current sqlid = 'S100447';
-- drop   TABLESPACE db2admin.AOPTHINT;
commit;
CREATE TABLESPACE AOPTHINT
  IN DB2ADMIN
  USING STOGROUP GSMS
  PRIQTY -1 SECQTY -1
  ERASE  NO
  FREEPAGE 0 PCTFREE 5
  GBPCACHE CHANGED
  TRACKMOD YES
  LOGGED
  BUFFERPOOL BP2
  LOCKSIZE ANY
  LOCKMAX SYSTEM
  CLOSE YES
  COMPRESS NO
  CCSID      Unicode
  DEFINE YES
  MAXROWS 255
;
create table s100447.tOptHint
    like cmnBatch.plan_table
    in db2Admin.aOptHint
;
-- Achtung (teil)copy aus planvw, aenderungen dort |||||||              00010000
-- Achtung (teil)copy aus planvw, aenderungen dort |||||||              00020000
-- Achtung (teil)copy aus planvw, aenderungen dort |||||||              00030000
-- SET CURRENT SQLID = 'S100447';                                       00040000
-- drop   VIEW S100447.vOptHint_VIEW1 ;                                 00050001
   CREATE VIEW S100447.vOptHint_VIEW1 as                                00060001
   SELECT SUBSTR(DIGITS(QUERYNO), 5) AS STMT,                           00070000
          QBLOCK_TYPE AS "TYPE",                                        00080000
                                                                        00090000
          CASE WHEN PRIMARY_ACCESSTYPE = 'D' then 'dirRow'              00100000
               WHEN ACCESSTYPE = 'I ' THEN 'ixScan'                     00110000
               WHEN ACCESSTYPE = 'I1' THEN 'ixOne '                     00120000
               WHEN ACCESSTYPE = 'R ' THEN 'tsScan'                     00130000
               WHEN ACCESSTYPE = 'N ' THEN 'ixSPin'                     00140000
               WHEN ACCESSTYPE = 'M ' THEN 'ixScMu'                     00150000
               WHEN ACCESSTYPE = 'MX' THEN 'ixScMX'                     00160000
               WHEN ACCESSTYPE = 'MI' THEN 'ixScIn'                     00170000
               WHEN ACCESSTYPE = 'MU' THEN 'ixScUn'                     00180000
               WHEN ACCESSTYPE = 'T ' THEN 'ixSPRS'                     00190000
               WHEN ACCESSTYPE = '  ' THEN '      '                     00200000
               ELSE                        '??' || accessType           00210000
          END AS ACCESS,                                                00220000
                                                                        00230000
          CASE WHEN ACCESSTYPE = 'R  ' THEN '       ' WHEN              00240000
          PRIMARY_ACCESSTYPE = 'D' THEN '       ' ELSE SUBSTR(          00250000
          ACCESSNAME, 1, 12) END AS "INDEX",                            00260000
          SUBSTR(TNAME, 1, 12) AS "TABLE",                              00270000
                                                                        00280000
          CASE WHEN TABLE_TYPE IS NULL THEN '      ' ELSE TABLE_TYPE    00290000
          END AS TTYP,                                                  00300000
          CASE WHEN METHOD = 3 THEN '      ' WHEN ACCESSTYPE = 'R '     00310000
          THEN '      ' ELSE SUBSTR(DIGITS(MATCHCOLS), 5, 1) CONCAT     00320000
          '  ' CONCAT INDEXONLY END AS MC_O,                            00330000
          CASE METHOD WHEN 0 THEN '      ' WHEN 1 THEN 'NLJOIN' WHEN    00340000
          2 THEN 'SMJOIN' WHEN 3 THEN 'SORT  ' WHEN 4 THEN 'HYJOIN'     00350000
          ELSE NULL END AS METHOD,                                      00360000
          CASE JOIN_TYPE WHEN 'F' THEN 'FULL ' WHEN 'L' THEN 'LEFT '    00370000
          WHEN 'S' THEN 'STAR ' ELSE ' ' END AS "JOIN",                 00380000
          SORTN_UNIQ CONCAT SORTN_JOIN CONCAT SORTN_ORDERBY CONCAT      00390000
          SORTN_GROUPBY AS UJOG,                                        00400000
          SORTC_UNIQ CONCAT SORTC_JOIN CONCAT SORTC_ORDERBY CONCAT      00410000
          SORTC_GROUPBY AS UJOC, TSLOCKMODE AS LCK,                     00420000
          SUBSTR(CORRELATION_NAME, 1, 4) AS CORR,                       00430000
          CASE PARALLELISM_MODE WHEN 'C' THEN 'CPU' WHEN 'I' THEN       00440000
          'I-O' WHEN 'X' THEN 'SYSPLEX' ELSE NULL END AS PARAL,         00450000
          STRIP(DIGITS(ACCESS_DEGREE), LEADING, '0') CONCAT ' '         00460000
          CONCAT STRIP(DIGITS(JOIN_DEGREE), LEADING, '0') AS AJ_DEG,    00470000
          STRIP(DIGITS(ACCESS_PGROUP_ID), LEADING, '0') CONCAT ' '      00480000
          CONCAT STRIP(DIGITS(JOIN_PGROUP_ID), LEADING, '0') AS         00490000
          PG_DEG, STRIP(DIGITS(MERGE_JOIN_COLS), LEADING, '0') AS MC,   00500000
          PREFETCH AS PRE,                                              00510000
          STRIP(DIGITS(QUERYNO), LEADING, '0') AS QNO,                  00520000
          SUBSTR(DIGITS(QBLOCKNO), 4, 2) CONCAT ' ' CONCAT SUBSTR(      00530000
          DIGITS(PLANNO), 4, 2) AS BL_PL, PARENT_QBLOCKNO,              00540000
          QBLOCKNO, PLANNO, TNAME, ACCESSNAME, OPTHINT,                 00550000
          HINT_USED, APPLNAME, "COLLID", PROGNAME, VERSION,             00560000
          TIMESTAMP, QUERYNO, MIXOPSEQ, BIND_TIME                       00570000
     FROM s100447.tOpthint A                                            00580001
;                                                                       00590000
------------------------------------------------------------------------00600000
insert into s100447.tOptHint
          select * from cmnbatch.plan_table where opthint <> ''
    union select * from cmnbatch.plan_table where opthint <> ''
    union select * from S100006.plan_table where opthint <> ''
    union select * from S100447.plan_table where opthint <> ''
    union select * from S103931.plan_table where opthint <> ''
 ;
 commit;
;yx
;
select count(*), opthint, length(opthint), hex(opthint)
    from cmnBatch.plan_table
    group by opthint
    order by opthint
    with ur