zOs/SQL/T1

select '<' || (0+1) || '>', '<' || right(0-1*234, 3) || '>'
    from sysibm.sysDummy1
;
with ti as
(
  select s.*
      , value(i, instance) inst
      , char(case when clone = 'N' then  '     '
                  when instance = i then 'base '
                  else 'clone' end || value(i, instance), 6) instTx

  from sysibm.sysTablespace s
    left join  -- clone handling: add instances
        ( select           1 from sysibm.sysDummy1
          union all select 2 from sysibm.sysDummy1
        ) i (i)
      on  s.clone = 'Y'
)
select clone, instance, inst, instTx, count(*)
    from ti
 -- where dbName like 'Q%'
    group by clone, instance, inst, instTx
;x;
--- temporary explain --------------------------------------------------
set current sqlid = 'A540769';
delete from A540769.plan_table;
delete from A540769.DSN_STATEMNT_TABLE;
delete from A540769.DSN_DetCost_TABLE ;
delete from A540769.dsn_filter_Table  ;
delete from A540769.dsn_predicat_table;
explain plan set queryno = 3   for
with ti as
(
  select s.*
      , value(i, instance) inst
      , case when clone = 'N' then '     '
             when clone <> 'Y' then 'clo?' || clone
             when instance = i then 'base '
             else                   'clone' end instTxt
  from sysibm.sysTablespace s
    left join  -- clone handling: add instances
        ( select           1 from sysibm.sysDummy1
          union all select 2 from sysibm.sysDummy1
        ) i (i)
      on  s.clone = 'Y'
)
select clone, instance, inst, instTxt, count(*)
    from ti
    where dbName like 'Q%'
    group by clone, instance, inst, instTxt
;
select * from  plan_view1
    order by -- collid, progName, version, explain_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2
    order by -- collid, progName, version, explain_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2det
    order by -- collid, progName, version, explain_time,
             queryNo, qblockno, planno, mixOpSeq
;
select *
    from plan_viewPred
    order by collid, progName, explain_time,
             queryNo, qBlockNo, predNo, orderNo, mixOpSeqNo
    with ur
;
rollback
;;;;
SET CURRE  ( select           1 from sysibm.sysDummy1
create ta    union all select 2 from sysibm.sysDummy1
  (   typ  ) i (inst)
      PRIon s.instance = i.inst or s.clone = 'Y'
      appp by
      pgm        CHAR(8) NOT NULL default '*',
      rz         char(3) NOT NULL default '*',
      dbSy       char(4) NOT NULL default '*',
      location   char(16) NOT NULL  default '*',
      value      char(20) not null,
      installFr  date NOT NULL WITH DEFAULT,
      installTo  date NOT NULL WITH DEFAULT '31.12.9999',
      rowChg timestamp not null generated always
        for each row on update as row change timestamp
  )
  ;
rollback
;