Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Explain Comparision Report

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Tue Jun 05, 2012 5:48 pm    Post subject: Explain Comparision Report
Reply with quote

Hi,

We are doing a migration from DB2 V8 to DB2 V9.

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?
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Jun 05, 2012 6:25 pm    Post subject:
Reply with quote

I think these are the most important ones :
Code:
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
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Tue Jun 05, 2012 7:08 pm    Post subject:
Reply with quote

Thanks GuyC...

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?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Jun 05, 2012 7:43 pm    Post subject:
Reply with quote

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

order by coalesce(p1.progname,p2.progname)
     , coalesce(p1.queryno,p2.queryno)
     , 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)) !! ')' 
               )



I'm pretty sure this is ok , because I use this a lot.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Jun 06, 2012 2:50 pm    Post subject:
Reply with quote

Thanks GuyC...
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Detailed Transaction History report f... Kyle Carroll CICS 5 Tue Oct 18, 2016 12:42 am
No new posts Easytrieve - Report writing - Only 1 ... Abhi Nature CA Products 2 Wed Sep 14, 2016 11:40 am
No new posts Daily report to be extracted from CA ... polymathtarun DFSORT/ICETOOL 5 Mon Jul 11, 2016 4:33 pm
No new posts Report view in Mobile devices Robert Sample All Other Mainframe Topics 0 Thu Jun 23, 2016 7:05 pm
No new posts COBOL Report view in Mobile devices balaji81_k COBOL Programming 4 Thu Jun 23, 2016 6:41 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us