zOs/WK/OPTHIHIS

                                     -- collect optHint history
                                     -- from cmnbatch
delete from A540769.plan_table
;
commit;                              -- insert from cmnbatch
insert into A540769.plan_table
    select *
    from cmnbatch.plan_table
    where optHint <> ''
    or (hint_used <> '' and hint_used <> 'APREUSE')
;
commit
;
select count(*), optHint, hint_used
     from A540769.plan_table p
     group by optHint, hint_used
 ;
 select count(*)
     from A540769.plan_table p
 ;                                   -- delete duplicates
 delete
 --lect count(*)
     from A540769.plan_table p
     where exists (select 1
         from A540769.plan_table n
         where p.QUERYNO   = n.QUERYNO
           and p.APPLNAME  = n.APPLNAME
           and p.PROGNAME  = n.PROGNAME
           and p.VERSION   = n.VERSION
           and p.COLLID    = n.COLLID
           and p.OPTHINT   = n.OPTHINT
           and p.hint_used      = n.hint_used
           and p.explain_time   < n.explain_time
           and p.QBLOCKNO       = n.QBLOCKNO
           and p.PLANNO         = n.PLANNO
           and p.METHOD         = n.METHOD
           and p.CREATOR        = n.CREATOR
           and p.TNAME          = n.TNAME
           and p.TABNO          = n.TABNO
           and p.ACCESSTYPE     = n.ACCESSTYPE
           and p.MATCHCOLS      = n.MATCHCOLS
           and p.ACCESSCREATOR  = n.ACCESSCREATOR
           and p.ACCESSNAME     = n.ACCESSNAME
           and p.INDEXONLY      = n.INDEXONLY
           )
 ;
 commit
;                                    -- insert remaining explains
insert into A540769.plan_table
    select * from cmnbatch.plan_table p
    where opthint = '' and hint_used = '' and
        (QUERYNO
        ,APPLNAME
        ,PROGNAME
        ,VERSION
        ,COLLID
        ,explain_time) in (select
         QUERYNO
        ,APPLNAME
        ,PROGNAME
        ,VERSION
        ,COLLID
        ,explain_time
     from A540769.plan_table
     )
;
delete from A540769.dsn_userquery_table
;                                    -- insert query text
                                     -- queryNo -> sjTables
insert into A540769.dsn_userquery_table
      (  QUERYNO
      ,  SCHEMA
      ,  HINT_SCOPE
      ,  QUERY_TEXT
      ,  USERFILTER
      ,  OTHER_OPTIONS
      ,  COLLECTION
      ,  PACKAGE
      ,  VERSION
      ,  REOPT
      ,  STARJOIN
      ,  MAX_PAR_DEGREE
      ,  DEF_CURR_DEGREE
      ,  SJTABLES
      ,  OTHER_PARMS
      )
with a as
( SELECT unique queryno, collid, progName, version
    from A540769.plan_table a
)
select
        row_number() over ()
      , p.qualifier
      , -1
      , s.statement
      , ''
      , ''
      , a.collid
      , a.progName
      , a.version
      , ''
      , ''
      , -1
      , ''
      , a.queryNo
      , 'history 29.08.14 dbof cmnbatch'
    from A
      join sysibm.sysPackage p
        on     a.progName  = p.name
           and a.collid    = p.collid
           and a.version   = p.version
           and ''          = p.location
      join sysibm.sysPackStmt s
        on    p.name      = s.name
          and p.collid    = s.collid
          and p.conToken  = s.conToken
          and p.location  = s.location
          and a.queryno   = s.queryno
;
commit
;
                                     -- set explain_time
                                     --    to history datum
                                     -- orig explain_time is still
                                     --    in bind_time
update A540769.plan_table set explain_time = '2014-08-29-00.00.00'
;
                                     -- which stmts are lost?
with a as
( SELECT unique queryno, collid, progName, version
    from A540769.plan_table a
)
select substr(value(p.name, 'noPack'), 1, 8)
    ,  substr(value(s.name, 'noStmt'), 1, 8)
    , a.progName, a.collid, a.version
    from A
      left join sysibm.sysPackage p
        on     a.progName  = p.name
           and a.collid    = p.collid
           and a.version   = p.version
           and ''          = p.location
      left join sysibm.sysPackStmt s
        on    p.name      = s.name
          and p.collid    = s.collid
          and p.conToken  = s.conToken
          and p.location  = s.location
          and a.queryno   = s.queryNo
where s.name is null
;
                                     -- which stmts are lost?
SELECT unique queryno, collid, progName, version
    from A540769.plan_table a
    where not exists ( select 1
       from A540769.dsn_userquery_table q
       where        a.progName  = q.package
                and a.collid    = q.collection
                and a.version   = q.version
                and a.queryno   = q.sjtables
       )
;x;