zOs/SQL/DDLCH4

set current sqlid = 'S100447';
--  drop function A540769.fins;
    create global temporary table A540769.tIns
           (fl char(1), nu int);
--#SET TERMINATOR ?
create function A540769.fIns(c int) returns int
    modifies sql data
    begin
    delete from A540769.tIns;
    insert into A540769.tIns values('x', c);
    insert into A540769.tIns values('x', c-1);
    return c+ 1;
    end
?
--#SET TERMINATOR ;
select * from final table (
    update a540769.tIns set nu = A540769.fIns(12)
    ) x
;
select * from a540769.tIns
;
rollback
;
create function A540769.fChk(pty char(3)
                  , pqu varchar( 24) ccsid unicode for mixed data
                  , pnm varchar( 24) ccsid unicode for mixed data
                            )
           returns table    (ty char(3)
                            , qu varchar(128) --ccsid unicode
                            , nm varchar(128) --ccsid unicode
                            )
    return select 'ts', dbName, name
        from sysibm.sysTablespace
        where                dbName = pqu and name = pnm
    union all select 't', creator, name
        from sysibm.sysTables
        where               creator = pqu and name = pnm
;
--- 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 * from table(A540769.fChk('ts', 'MF01A1A', 'A150A')) a
;
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
;;;;
rollback
;x;
    modifies sql data
    begin
    delete from A540769.tIns;
    insert into A540769.tIns values('x', c);
    insert into A540769.tIns values('x', c-1);
    return c+ 1;
    end
?
--#SET TERMINATOR ;
select y.* from final table (
    insert into a540769.tIns values('a', A540769.fIns(12))
    ) x , a540769.tIns y
;
select * from a540769.tIns
;
rollback
;
--#SET TERMINATOR ?
create function A540769.fIns(c int) returns int
    modifies sql data
    begin
    delete from A540769.tIns;
    insert into A540769.tIns values('x', c);
    insert into A540769.tIns values('x', c-1);
    return c+ 1;
    end
?
--#SET TERMINATOR ;
select y.* from final table (
    insert into a540769.tIns values('a', A540769.fIns(12))
    ) x , a540769.tIns y
;
select * from a540769.tIns
;
rollback
;