Dmitry Yemanov wrote:

> Ann et al,
>
> Regarding temporary datasets vs PSQL.
>
> First, I think that everything DECLAREd within a procedure body should
> behave similarly. It means that either local temporary table or transient
> data set must be created at the declaration and destroyed upon procedure's
> exit. And also be visible only on a per-procedure basis. Exactly like
> DECLAREd variables and cursors behave. We don't need different semantics for
> the same syntax.

Fine, DECLARE was a bad choice of keyword - Maybe a syntax like this
would be better ..

create procedure ....
as
declare variable a integer;
extern transient data set y (f1 integer, f2 integer);
begin
<whatever>
end

This is why I dislike working backward from examples to ideas - my
choice of a dumb keyword has completely confused the thought I was
trying to convey - that there would be a mechanism in a procedure for
describing the structure of something that does not yet exists.

>
> Second, I don't support a proposal to map existing DLTT/transient dataset to
> its local declaration. I believe it tends to cause structure mismatches that
> will bite everyone at runtime. Eevery structural change will require a lot
> of PSQL to be corrected as well. Too complex and risky to be used without
> worries.

So you'd argue that transient data sets and procedures are simply
incompatible and can not be made to work together.

>
> Third, I think it's too early to talk about passing temporary datasets to
> the nested calls, either implicitly via scope inheritance or explicitly via
> formal parameters.

Nested references are absolutely necessary for modular programming. We
already see too many 1000+ line procedures. Adding a useful construct
like a transient data set that can be used only in monolithic procedures
pushes developers in the wrong direction.

> Personally, I don't like a concept of nested visibility
> of local objects (declared at upper levels),

err... isn't that a fairly common programming language convention?

> If we want something to be used in the nested
> calls, just pass it via the stack (read: using formal parameters). This
> works for variables now. We should decide whether it's desirable for cursors
> and how it could be done (a tricky question). Then we should think how
> temporary datasets fit this semantics.

Transient data sets are just another kind of table - restricted in scope
to the connection, but otherwise just a table. Procedures see tables
now without having them passed as formal parameters. I confused the
issue by picking a bad key word and associating transient data sets with
variables and cursors which are quite different objects.

>
> Since my personal opinion is that PSQL-scoped datasets is a valuable thing
> to have, I see two possible solutions:
>
> 1) temporary dataset can be created/declared in PSQL, but it's still an
> independent session-scope object which is not destroyed upon procedure's
> exit. It's visible in nested calls, but this is okay as it's not a local
> object, it's just initialized in a procedure.

Why do you restrict this to those transient data sets created in
procedures? Why is using a transient data set from a procedure invoked
by another procedure less fragile that using the same thing invoked from
a application?

>
> 2) temporary dataset is declared in PSQL and have a procedure-scope
> visibility and lifecycle. It can be optionally initialized with a select
> expression and/or can be later changed,

What happens if it is created by an EXECUTE STATEMENT? How can you
create procedure blr for something whose structure you don't know?

> but its structure must be fixed for
> the given procedure.

Agreed.

> It can be passed to nested calls as an actual parameter
> value (realistically thinking, this feature is a separate one).

I'm not sure what you gain by passing the thing, rather than making it
part of the name space, but sure, OK.

> Local
> temporary dataset name overrides every session-level table-type object, be
> it persistent table, GTT or another session-level temporary dataset.

OK. Part of the utility of transient data sets is that they can be
easily refined, essentially replacing themselves... e.g.

select e.name, e.age, e.salary from employees e yielding emp;
select e.name, e.age, e.salary from emp e where e.age > 50 yielding emp;
select e.name, e.age, e.salary from emp e
where e.salary < (select average (e1.salary) from employees e1)
yielding emp

How does this sort of transformation work in your second model - where
does the transient data set replaced itself, and where does it create a
new version, visible only within the scope of the procedure?

>
> It's also possible to have both these technics, but it looks too much
> complicated at the first glance.

Interesting thought. Which variant is more useful?

Regards,


Ann