zOs/SQL/CATCOLC2

$#@
call sqlConnect DP4G
$*( generate sql to compare the columns of two tables
--cr1 = CMNBATCH
--cr2 = A540769 $*)
$<>
$>. fEdit('~tmp.sql(catcolco)')
$<=[
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 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 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 $crL.$tb l
       full outer join $crR.$tb 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 $crL.$tb
    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 $crR.$tb
    group by 0
     $do cx=1 to m.col.0 $@[
         parse var m.col.cx n1    c1
         if n1 == '=' then
           $$- '      ,' c1
         $]
)
select 'left', '$crL.$tb'
      , sum(cnt), count(*)
    from lu
union all  select 'right', '$crR.$tb'
      , sum(cnt), count(*)
    from ru
union all select lr, cmp, count(*), 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                                              20150211 19:00:15
$#out                                              20150211 18:56:22
$#out                                              20150211 18:53:14
$#out                                              20150211 18:49:23
$#out                                              20150211 18:47:02
$#out                                              20150211 17:14:03
$#out                                              20150211 17:12:56
$#out                                              20150211 17:09:18