zOs/SQL/EXRID

set current sqlid = 'S100447';
select rid(c), strip(tbCreator) || '.' || strip(tbName)
               || '.' ||strip(name)
    from sysibm.sysColumns c
    where tbCreator = 'SYSIBM'
    order by name
    fetch first 10 rows only
;
create global temporary table session.r (r bigInt, nm char(100))
 --    with no data on commit preserve rows
;
insert into session.r
select rid(c)
    , strip(tbCreator) || '.' || strip(tbName) || '.' ||strip(name)
    from sysibm.sysColumns c
    where tbCreator = 'SYSIBM'
    order by name
    fetch first 10 rows only
;
select rid(c)
    , strip(tbCreator) || '.' || strip(tbName) || '.' ||strip(name)
    from sysibm.sysColumns c
    where rid(c) in (select r from session.r)
;
select rid(c)
    , strip(tbCreator) || '.' || strip(tbName) || '.' ||strip(name)
    , r.*
    from sysibm.sysColumns c join session.r r
        on rid(c) = r.r
;
--- 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 = 1   for
select rid(c)
    , strip(tbCreator) || '.' || strip(tbName) || '.' ||strip(name)
    from sysibm.sysColumns c
    where rid(c) =   234
;
explain plan set queryno = 2   for
select rid(c)
    , strip(tbCreator) || '.' || strip(tbName) || '.' ||strip(name)
    from sysibm.sysColumns c
    where rid(c) = 234 or rid(c) = 6789234
;
explain plan set queryno = 3   for
select rid(c)
    , strip(tbCreator) || '.' || strip(tbName) || '.' ||strip(name)
    from sysibm.sysColumns c
    where rid(c) in (select r from session.r)
;
explain plan set queryno = 7   for
select rid(c)
    , strip(tbCreator) || '.' || strip(tbName) || '.' ||strip(name)
    , r.*
    from sysibm.sysColumns c join session.r r
        on rid(c) = r.r
;
select * from  plan_view1
    order by -- collid, progName, version, bind_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2
    order by -- collid, progName, version, bind_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2det
    order by -- collid, progName, version, bind_time,
             queryNo, qblockno, planno, mixOpSeq
;
select *
    from plan_viewPred
    order by --collid, progName, applName, explain_time,
             queryNo, qBlockNo, planno,
             stage, predNo
    with ur
;
rollback
;;;;
select rid(t), hex(rid(t)), t.*
    from BUA.TXBC041 t
    where rid(t) = 4075884
    ;  x;
select rid(t), hex(rid(t)), name
    from sysibm.sysTables t
--  where name like 'TMF150%'
    where rid(t) = 421384
    ;
--- 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
select rid(t), hex(rid(t)), name
    from sysibm.sysTables t
--  where name like 'TMF150%'
    where rid(t) = 421384
;
select * from  plan_view1
    order by -- collid, progName, version, bind_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2
    order by -- collid, progName, version, bind_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2det
    order by -- collid, progName, version, bind_time,
             queryNo, qblockno, planno, mixOpSeq
;
select *
    from plan_viewPred
    order by --collid, progName, applName, explain_time,
             queryNo, qBlockNo, planno,
             stage, predNo
    with ur
;
rollback
;;;;