zOs/WK/OPTHICTE

       -- optHint testcase
       -- mit testCase wk403 - source 3 (gdb9998.twk403p1)

       -- 1. clean up planTable
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

select * from  plan_view1
    order by -- collid, progName, version, bind_time,
             queryNo, qblockno, planno, mixOpSeq
;
       -- 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
;