For this we would be binding all the existing packages to new collection. Then we need to prepare a report of access path changes between old packages and new packages.
I was thinking to automate this process. By getting data from the plan table and comparing the access path and generating the report. But i am only concerned on how do i compare the data for the before package and after package (I mean what keys to use in Plan table)?
Can you guys let me know how to go about this process?
select p1.* , p2.*,
case when p1.method = p2.method
and (p1.tname = p2.tname)
and (p1.accessname = p2.accessname)
and (p1.sortn_uniq = p2.sortn_uniq)
and (p1.sortn_join = p2.sortn_join)
and (p1.sortn_groupby = p2.sortn_groupby)
and (p1.sortn_orderby = p2.sortn_orderby)
and (p1.sortc_uniq = p2.sortc_uniq)
and (p1.sortc_join = p2.sortc_join)
and (p1.sortc_groupby = p2.sortc_groupby)
and (p1.sortc_orderby = p2.sortc_orderby)
then ''
else '<======Change'
end as flag
from
(select * from plan_table where old_package_criteria) P1
ful outer join
(select * from plan_table where new_package_criteria) P2
on p1.progname = p2.progname
and p1.queryno = p2.queryno
and p1.qblockno = p2.qblockno
and p1.planno = p2.planno
and p1.mixopseq = p2.mixopseq
But what would be the old_package_criteria/new_package_criteria can you please elaborate more on this?
Example: The below are the contents of plan table for a single package
Code:
QUERYNO QBLOCKNO PROGNAME PLANNO METHOD CREATOR TNAME TABNO ACCESSTYPE MATCHCOLS ACCESSCREATOR ACCESSNAME INDEXONLY SORTN_UNIQ SORTN_JOIN SORTN_ORDERBY SORTN_GROUPBY SORTC_UNIQ SORTC_JOIN SORTC_ORDERBY SORTC_GROUPBY TSLOCKMODE TIMESTAMP PREFETCH MIXOPSEQ COLLID QBLOCK_TYPE BIND_TIME
535 1 TESTPGM1 1 0 AC TABLE02 1 I 1 AC XRESTRT1 N N N N N N N N N IS 2012040211545340 0 COLEXXXC001 SELECT 2012-04-02-11.54.53.436057
761 1 TESTPGM1 0 0 TC TABLE01 1 0 N N N N N N N N N IX 2012040211545340 0 COLEXXXC001 INSERT 2012-04-02-11.54.53.436057
891 1 TESTPGM1 1 0 TC TABLE01 1 I 3 TC XTABLE011 N N N N N N N N N IX 2012040211545340 L 0 COLEXXXC001 UPDATE 2012-04-02-11.54.53.436057
909 1 TESTPGM1 1 0 TC TABLE01 1 I 3 TC XTABLE011 N N N N N N N N N IX 2012040211545340 L 0 COLEXXXC001 UPDATE 2012-04-02-11.54.53.436057
1028 1 TESTPGM1 1 0 TC TABLE01 1 I 3 TC XTABLE011 Y N N N N N N N N IX 2012040211545340 0 COLEXXXC001 DELETE 2012-04-02-11.54.53.436057
1036 1 TESTPGM1 1 0 TC TABLE01 1 I 3 TC XTABLE011 Y N N N N N N N N IX 2012040211545340 0 COLEXXXC001 DELETE 2012-04-02-11.54.53.436057
1210 1 TESTPGM1 1 0 AC TABLE02 1 I 1 AC XRESTRT1 Y N N N N N N N N IX 2012040211545340 0 COLEXXXC001 UPDATE 2012-04-02-11.54.53.436057
1310 1 TESTPGM1 0 0 AC TABLE02 1 0 N N N N N N N N N IX 2012040211545350 0 COLEXXXC001 INSERT 2012-04-02-11.54.53.436057
I just want to confirm that is there a possibility that when we bind the existing packages in new collection, the values of QUERYNO QBLOCKNO and other columns in the plan table ,which are part of join query gets changed?
1) Queryno can only change if you start from a new dbrm. if you don't precompile but only rebind, queryno will not change.
2) qblockno, planno always start from 1
if there is a difference, f.e. an extra sort is added to the access path then it will be reported as a difference because of the FULL outer join : something <> null
3) I don't know how often your plan_table is cleaned up or which qualifier you use , but if you are interested in the last rebind of the last version of a package in a certain collection (and I'll add an extra table for estimated costs ) you'll need something like this :
Code:
select
coalesce(p1.progname,p2.progname)
, coalesce(p1.queryno,p2.queryno)
, p1.bind_time as prev_bind
, p2.bind_time as new_bind
, p1.procms as prev_procms
, p2.procms as new_procms
, p1.procsu as prev_procsu
, p2.procsu as new_procsu
, coalesce('(' !! strip(char(p1.qblockno)) !! ',' !! strip(char(p1.planno)) !! ',' !! strip(char(p1.mixopseq)) !! ')'
,'(' !! strip(char(p2.qblockno)) !! ',' !! strip(char(p2.planno)) !! ',' !! strip(char(p2.mixopseq)) !! ')'
) as Step
,
...
from
(select pl.*, st.procsu,st.procms from sysibm.syspackage PA
join xxx.plan_table PL
on PA.location = ''
and PA.COLLID = PL.COLLID
and PA.name = PL.progname
and PA.bindtime = PL.bind_time
left join xxx.dsn_statemnt_table ST
on st.queryno = pl.queryno
and st.explain_time = pl.bind_time
where PA.Collid = 'V8Collection'
and PA.name like '%'
and PA.pctimestamp = (select max(pctimestamp) from sysibm.syspackage P2 where p2.location = pa.location and p2.collid = pa.collid and p2.name = pa.name)
) P1
full outer join
(select pl.*, st.procsu,st.procms from sysibm.syspackage PA
join xxx.plan_table PL
on PA.location = ''
and PA.COLLID = PL.COLLID
and PA.name = PL.progname
and PA.bindtime = PL.bind_time
left join xxx.dsn_statemnt_table ST
on st.queryno = pl.queryno
and st.explain_time = pl.bind_time
where PA.Collid = 'V9Collection'
and PA.name like '%'
and PA.pctimestamp = (select max(pctimestamp) from sysibm.syspackage P2 where p2.location = pa.location and p2.collid = pa.collid and p2.name = pa.name)
) P2
on p1.progname = p2.progname
and p1.queryno = p2.queryno
and p1.qblockno = p2.qblockno
and p1.planno = p2.planno
and p1.mixopseq = p2.mixopseq
.jpg)
