SQL, LISTAGG(), QSYS2.SYSCOLUMNS2, and IS NOT DISTINCT all team up here to generate a table level comparision query.

SQL generated table comparison query

--
-- Imagine that you have 2 versions of the same table.
-- The tables have the same format.
-- This example provides an SQL function that generates a table compare query.
-- 
create schema coolstuff;
create table coolstuff.table_master as (select * from qsys2.syslimtbl limit 100) with data;
create table coolstuff.table_secondary as (select * from qsys2.syslimtbl limit 100) with data;
select * from coolstuff.table_secondary;
update coolstuff.table_secondary set limit_category = 555 limit 10;
insert into coolstuff.table_master  select * from qsys2.syslimtbl  limit 2 offset 5000;
insert into coolstuff.table_master  select * from qsys2.syslimtbl  limit 2 offset 5000;

create or replace function coolstuff.generate_file_compare (
      source_schema varchar(128), 
      source_table varchar(128), 
      target_schema varchar(128),
      target_table varchar(128)
  )
  returns clob(2m) ccsid 1208
  language sql
  not deterministic
  reads sql data
  called on null input
  set option commit = *none, dbgview = *source, dynusrprf = *user
  return
    (
      select 'select ''' concat source_schema concat '.' concat source_table concat ''' as table, 
  rrn(a) as rrn, a.* from ' concat source_schema concat '.' concat source_table concat
          ' a
  left exception join   ' concat target_schema concat '.' concat target_table concat ' b on ' concat
          listagg(cast('A.' concat qsys2.delimit_name(column_name) concat ' IS NOT DISTINCT FROM B.'
            concat qsys2.delimit_name(column_name) as clob(1m)), ' AND ') within group (order by
          ordinal_position) concat '   UNION ALL   ' concat 'select ''' concat target_schema concat
          '.' concat target_table concat ''', 
    rrn(b), b.* from ' concat source_schema concat '.' concat source_table concat ' a
  right exception join  ' concat target_schema concat '.' concat target_table concat ' b on ' concat
          listagg(cast('A.' concat qsys2.delimit_name(column_name) concat ' IS NOT DISTINCT FROM B.'
            concat qsys2.delimit_name(column_name) as clob(1m)), ' AND ') within group (order by
          ordinal_position) concat ' order by 2 '
        from qsys2.syscolumns2 c
        where table_name = source_table
              and table_schema = source_schema);


stop;
values coolstuff.generate_file_compare(
                     source_schema => 'COOLSTUFF',
                     source_table  => 'TABLE_MASTER',
                     target_schema => 'COOLSTUFF',
                     target_table  => 'TABLE_SECONDARY') ;
stop;

--
-- Paste the result for the generate_file_compare() function here, add a semicolon on the end and click on format
--
select 'COOLSTUFF.TABLE_MASTER' as table, rrn(a) as rrn, a.*
  from coolstuff.table_master a
       left exception join coolstuff.table_secondary b
         on a.last_change_timestamp is not distinct from b.last_change_timestamp
           and a.limit_category is not distinct from b.limit_category
           and a.limit_type is not distinct from b.limit_type
           and a.limit_id is not distinct from b.limit_id
           and a.job_name is not distinct from b.job_name
           and a.user_name is not distinct from b.user_name
           and a.current_value is not distinct from b.current_value
           and a.system_schema_name is not distinct from b.system_schema_name
           and a.system_object_name is not distinct from b.system_object_name
           and a.system_table_member is not distinct from b.system_table_member
           and a.object_type is not distinct from b.object_type
           and a.asp_number is not distinct from b.asp_number
           and a.ifs_path_name is not distinct from b.ifs_path_name
union all
  select 'COOLSTUFF.TABLE_SECONDARY', rrn(b), b.*
    from coolstuff.table_master a
         right exception join coolstuff.table_secondary b
           on a.last_change_timestamp is not distinct from b.last_change_timestamp
             and a.limit_category is not distinct from b.limit_category
             and a.limit_type is not distinct from b.limit_type
             and a.limit_id is not distinct from b.limit_id
             and a.job_name is not distinct from b.job_name
             and a.user_name is not distinct from b.user_name
             and a.current_value is not distinct from b.current_value
             and a.system_schema_name is not distinct from b.system_schema_name
             and a.system_object_name is not distinct from b.system_object_name
             and a.system_table_member is not distinct from b.system_table_member
             and a.object_type is not distinct from b.object_type
             and a.asp_number is not distinct from b.asp_number
             and a.ifs_path_name is not distinct from b.ifs_path_name
  order by 2;


--
-- Can EXCEPT get the job done? (no)
--
((select 'COOLSTUFF.TABLE_MASTER' as table, rrn(a) as rrn, a.* from coolstuff.table_master a)
  except distinct
(select 'COOLSTUFF.TABLE_SECONDARY', rrn(b), b.* from coolstuff.table_secondary b))
union all
((select 'COOLSTUFF.TABLE_SECONDARY', rrn(b), b.* from coolstuff.table_secondary b)
except distinct
(select 'COOLSTUFF.TABLE_MASTER' as table, rrn(a) as rrn, a.* from coolstuff.table_master a))
order by 2;

Small code fix AND addition of an attempt to use EXCEPT to accomplish the same.

The simple answer is no, EXCEPT can't accomplish the same feat.
EXCEPT is really EXCEPT DISTINCT, so it discards duplicate rows.
Also, EXCEPT treats a NULL column comparison as a mismatch, where the example uses IS NOT DISTINCT to avoid this situation.

Hi Scott, can you please explain me the meaning of "stop" statement?

Hi, sure.
ACS's Run SQL Script recognizes stop; and highlights it in red.
stop; is intended to be used in your scripts where you want the execution to "stop".