zOs/WK/OPTHIST1

select max(bind_time)
    from S100447.plan_table
;
set current sqlid = 'A540769';
set current schema = 'A540769';
       -- statement level access path hints
       -- 1. mit optHjCte opthints erstellen und anschauen
select * from  plan_view1
    order by -- collid, progName, version, bind_time,
             queryNo, qblockno, planno, mixOpSeq
;
delete from A540769.dsn_userquery_table;
insert into A540769.dsn_userquery_table
     (query_Text, schema, hint_Scope, queryNo)
     values (
        ' select * '
        ||  ' from gdb9998.twk403p1 '
        ||  ' where  id =3 and name = ''david1'' and parent = 1 '
        , 'A540769'
        , 0
        ,2)
;
select * from A540769.dsn_userquery_table
;
commit
;
insert into S100447.plan_table
            select * from A540769.plan_table
;
insert into S100447.dsn_userquery_table
          (query_Text, schema, hint_Scope, queryNo)
    select query_Text, schema, hint_Scope, queryNo
        from A540769.dsn_userquery_table
;
commit
;;;
set current sqlid = 'A540769';
delete from A540769.plan_table;
delete from A540769.DSN_STATEMNT_TABLE;
delete from A540769.DSN_DetCost_TABLE ;
delete from A540769.dsn_filter_Table  ;
delete from A540769.dsn_predicat_table;
explain plan set queryno = 0    for
select *
    from gdb9998.twk403p1
        where  id =3 and name = 'david1' and parent = 1
;
       -- cte Opthints, 1 fuer jeden der drei indexe
explain plan set queryno = 1    for
with DSN_INLINE_opt_Hint (access_type, access_creator, access_name) AS
( values ('INDEX', 'GDB9998', 'IWK403P11')
)
select *
    from gdb9998.twk403p1
        where  id =3 and name = 'david1' and parent = 1
;
explain plan set queryno = 2    for
with DSN_INLINE_opt_Hint (access_type, access_creator, access_name) AS
( values ('INDEX', 'GDB9998', 'IWK403P1CHANGING')
)
select *
    from gdb9998.twk403p1
        where  id =3 and name = 'david1' and parent = 1
;
explain plan set queryno = 3    for
with DSN_INLINE_opt_Hint (access_type, access_creator, access_name) AS
( values ('INDEX', 'GDB9998', 'IWK403P13')
)
select *
    from gdb9998.twk403p1
        where  id =3 and name = 'david1' and parent = 1
;
       -- planTable anschauen

       -- planTable modifizieren: opthint setzen
update A540769.plan_table set optHint = 'OH' || strip(queryno)
;
select * from  plan_view1
    order by -- collid, progName, version, bind_time,
             queryNo, qblockno, planno, mixOpSeq
;
commit
;
       -- mit current optHint und queryno wird opthint verwendet
set current optimization hint = 'OH2';
select *
    from gdb9998.twk403p1
        where  id =3 and name = 'david1' and parent = 1
        queryno 2
;
set current optimization hint = 'OH3';
select *
    from gdb9998.twk403p1
        where  id =3 and name = 'david1' and parent = 1
        queryno 3
;
       -- ohne queryNo nicht
set current optimization hint = 'OH2';
select *
    from gdb9998.twk403p1
        where  id =3 and name = 'david1' and parent = 1
;
       -- ohne current optHint auch nicht
set current optimization hint = '';
select *
    from gdb9998.twk403p1
        where  id =3 and name = 'david1' and parent = 1
        queryno 2
;
       -- mit optHint <> queryno auch nicht
set current optimization hint = 'OH2';
select *
    from gdb9998.twk403p1
        where  id =3 and name = 'david1' and parent = 1
        queryno 3
;
       -- ab hier mit wsh, also DSNREXX ||||
       -- das geht auch nicht, program ist falsch|
       -- explain statt nur select, sonst stört dynamic statemtent cache
set current sqlid = 'A540769';
set current optimization hint = 'OH2';
explain plan for
select *
    from gdb9998.twk403p1
        where  id =3 and name = 'david1' and parent = 1
        queryno 2
;
update A540769.plan_table
    set collid = 'DSNREXX', progName = 'DSNREXX'
    where progName <> 'DSNREXX'
;
set current optimization hint = 'OH2';
explain plan for
select *
    from gdb9998.twk403p1
        where  id =3 and name = 'david1' and parent = 1
        queryno 2
;
update A540769.plan_table
    set version = 'UK55753'
 -- where progName <> 'DSNREXX'
;
set current optimization hint = 'OH2';
explain plan for
select *
    from gdb9998.twk403p1
        where  id =3 and name = 'david1' and parent = 1
        queryno 2
;
select * from  plan_view1
    order by -- collid, progName, version, bind_time,
             queryNo, qblockno, planno, mixOpSeq
;
rollback
;