zOs/WK/OPTHIHIT
-- insert history from user tables
-- into tqu080OptHintQuery
-- and tqu081OptHintPlan
update A540769.plan_table p set explain_time
= timestamp('2014-08-29-01.00.00')
+ (1e-6 * value((select queryNo
from A540769.dsn_userquery_table q
where p.progName = q.package
and p.collid = q.collection
and p.version = q.version
and p.queryNo = q.sjtables
), 0)) seconds
;
select min(explain_time), max(explain_time)
from A540769.plan_table
;
insert into oa1p.tqz081OptHintPlan
select * from A540769.plan_table
;
insert into oa1p.tqz080OptHintQuery
( EXPLAIN_TIME
, DESC
, QUERYNO
, SCHEMA
, HINT_SCOPE
, QUERY_TEXT
, USERFILTER
, OTHER_OPTIONS
, COLLECTION
, PACKAGE
, VERSION
, REOPT
, STARJOIN
, MAX_PAR_DEGREE
, DEF_CURR_DEGREE
, SJTABLES
, OTHER_PARMS
)
select
timestamp('2014-08-29-01.00.00')
+ (1e-6 * queryNo) seconds
, 'history 29.08.14 used rz2/dbof cmnbatch'
, sjTables
, SCHEMA
, HINT_SCOPE
, QUERY_TEXT
, USERFILTER
, OTHER_OPTIONS
, COLLECTION
, PACKAGE
, VERSION
, REOPT
, STARJOIN
, MAX_PAR_DEGREE
, DEF_CURR_DEGREE
, -1
, ''
from A540769.dsn_userquery_table q
;
select explain_time, strip(collection) || '.' || strip(package)
|| '#' || version , desc
from oa1p.tqz080OptHintQuery
order by explain_time desc
;rollback;x;
commit
;
select count(*)
-- delete
from A540769.plan_table
where not (
progName = 'NZDBM83'
and collid = 'NZ'
and version = 'ZVBI000028'
)
;
commit
;x;
select explain_time, strip(collection) || '.' || strip(package)
|| '#' || version
from oa1p.tqz080OptHintQuery q
order by explain_time desc
;x;
select explain_time, strip(collid) || '.' || strip(progName)
|| '#' || version
from oa1p.tqz081OptHintPlan q
order by explain_time desc
;x;
select explain_time, strip(collection) || '.' || strip(package)
|| '#' || version
from oa1p.tqz080OptHintQuery q
order by explain_time desc
;x;
select q.explain_time, p.explain_time
from ( select * from oa1p.tqz080OptHintQuery
where explain_time >= '2014-08-29-00.00.00' ) q
full outer join (select * from oa1p.tqz081OptHintPlan p
where explain_time >= '2014-08-29-00.00.00' ) p
on q.explain_time = p.explain_time
where q.explain_time is null or p.explain_time is null
or q.package <> p.progName
or q.collection <> p.collid
or q.version <> p.version
or q.queryNo <> p.queryNo
;x;
select count(*), explain_time
from oa1p.tqz081OptHintPlan
group by explain_time
order by explain_time desc
;x;
select count(*), explain_time
from oa1p.tqz080OptHintQuery q
group by explain_time
order by explain_time desc
;x;
delete
from A540769.dsn_userquery_table q
where exists (select 1
from A540769.dsn_userquery_table n
where q.package = n.package
and q.collection= n.collection
and q.version = n.version
and q.sjTables = n.sjtables
and q.queryNo < n.queryNo
)
;
select count(*), package
, collection
, version
, sjtables
from A540769.dsn_userQuery_table
where optHint = ''
group by package
, collection
, version
, sjtables
order by 1 desc
;x
= timestamp('2014-08-29-00.00.00') -- + 0.000001 seconds;
+ (1e-6 * (select queryNo from A540769.dsn_userquery_table q
where p.progName = q.package
and p.collid = q.collection
and p.version = q.version
and p.queryNo = q.sjtables
)) seconds
-- collect optHint history
-- from cmnbatch
;x;
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;