zOs/SQL/PLANDACO

$#@
call sqlConnect DBOL
$*( generate sql to compare the data two set of explain tables
         for LOBs only length is compared
$*)
$=qFr = -9e99
$=qTo = +9e99
$<>
$>. fEdit('~tmp.sql(planDaCo)')
$<=[
with t (tb, crL, crR) as
(
   select l.name, l.creator, r.creator
      from sysibm.sysTables l
        join sysibm.sysTables r
          on l.name = r.name
      where l.creator = 'CMNBATCH'
         and r.creator = 'A540769'
         and r.dbName = 'DB2PLAN'
         and r.type   = 'T'
)
, c as
(
   select c.name col, colType ct, tb, crL, crR, c.*
     from t
      join sysibm.sysColumns c
        on c.tbCreator = crL and c.tbName = t.tb
)
select * from c order by tb, colNo
$] call sqlSel
$|
tb = ''
$forWith i $@[
     if tb <> $TB & tb \== '' then
         $@tbEnd
     if tb <> $TB then $@[
         tb = $TB
         $@tbBegin
         $]
     if $COLTYPE = 'ROWID' then
         iterate
     else if pos('LOB', $COLTYPE) > 0 then
         t1 = 'l'
     else if $NULLS = 'Y' then
         t1 = 'n'
     else
         t1 = '='
     call mAdd col, t1 $COL
     if t1 \== '=' then $@=[
        || case when l.$COL is null
                 and r.$COL is null then ' '
                when l.$COL is null then 'r'
                when r.$COL is null then 'l'
        $@ if t1 \== 'l' then $@=[
                when l.$COL
                  <> r.$COL then '|'
        $]
        $@ if t1 == 'l' then $@=[
                when length(l.$COL)
                  <> length(r.$COL) then '|'
        $]
                else ' '
           end
         $]
     $]
$@tbEnd
$proc $@=/tbBegin/
    $@ m.col.0 = 0
    $= crL = $CRL
    $= crR = $CRR
    $= tb  = $TB
with l as
(
  select * from $crL.$tb
$@ if $COL = 'QUERYNO' then $@=[
           where queryNo between $qFr and $qTo
     $]
)
, r as
(
  select * from $crR.$tb
$@ if $COL = 'QUERYNO' then $@=[
           where queryNo between $qFr and $qTo
     $]
)
, j as
(
  select case when l.$COL is null
             and r.$COL is null then 'null'
            when l.$COL is null then 'righ'
            when r.$COL is null then 'left'
            else 'both' end  lr
      , ''
$/tbBegin/
$proc $@=/tbEnd/
                cmp
     $do cx=1 to m.col.0 $@=[
         $@ parse var m.col.cx n1    c1
         $=col =- c1
         , value(l.$col
                ,r.$col
                )  $col
         $]
     from l
       full outer join r
     $= op = on
     $do cx=1 to m.col.0 $@[
         parse var m.col.cx t1    c1
         $=col =- c1
         if t1 == '=' then $@=[
      $op l.$col
        = r.$col
      $=op=and
         $]
         $]
)
, lu as
(
  select count(*) cnt
     $do cx=1 to m.col.0 $@[
         parse var m.col.cx n1    c1
         if n1 == '=' then
           $$- '      ,' c1
         $]
    from l
    group by 0
     $do cx=1 to m.col.0 $@[
         parse var m.col.cx n1    c1
         if n1 == '=' then
           $$- '      ,' c1
         $]
)
, ru as
(
  select count(*) cnt
     $do cx=1 to m.col.0 $@[
         parse var m.col.cx n1    c1
         if n1 == '=' then
           $$- '      ,' c1
         $]
    from r
    group by 0
     $do cx=1 to m.col.0 $@[
         parse var m.col.cx n1    c1
         if n1 == '=' then
           $$- '      ,' c1
         $]
)
--lect 'left', '$crL.$tb'
--      , count(*), cast(null as int)
--  from l
--ion all select 'left', '$crR.$tb'
--      , count(*), cast(null as int)
--  from r
select 'left', '$crL.$tb'
      , sum(cnt*cnt), sum(cnt), count(*)
    from lu
union all  select 'right', '$crR.$tb'
      , sum(cnt*cnt), sum(cnt), count(*)
    from ru
union all select lr, cmp, count(*), cast(null as int), cast(null as int)
    from j
    group by lr, cmp
;
-- select *
--  from j
--  where not (lr = 'both' and cmp = '')
--  order by 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17 ;
$/tbEnd/
$#out                                              20150223 08:11:44
$#out                                              20150223 08:09:14
$#out                                              20150223 07:46:57
$#out                                              20150223 07:37:16
$#out                                              20150223 07:37:05
*** run error ***
SQLCODE = -924: DB2 CONNECTION INTERNAL ERROR, 00000002,
    0000000C, 00F30006
sql = connect DE0G
$#out                                              20150216 15:23:02