Using a SQL Table function in a view ...

[Paul Reddin]

Hi,

This is a real hopeful one!

What we are trying to do:

1. We MUST present a mappable database object for our application
objects i.e a Table or a View

Some of the views are very complex, which raises another couple of
reqs.
1. The view complexity may need to be reduced. Using Table Functions,
could allow for
a.predicating parameters to obtain best performance
b.and the use of SQL/PL to help reduce complexity with the
inclusion of some procedural logic.

But, the heart of the problem is this,

How do we use Table functions within a view and still manage to pass
parameters in ?

One idea I have been looking at, is to create a 'parameter table'
which is populated with call parameters by the application, which the
view then performs a correlated join with
i.e
Calling App: (JABSTFP = table function parameter table for view)
insert into JABSTFP.ADD VALUES (1,1),(1,2),(1,3);
Select * from JABSTFV.ADD;

View definition (JABSTFV):
SELECT b.RESULT
FROM JABSTFP.ADD a, TABLE(JABSTF.ADD(a.v1,a.v2)) b

This works in the simple case but raises questions of
1. Multiple concurrent queries, how do we uniquely identify the
correct rows in the parameter table ?
2. How do we automatically delete the rows from the parameter table
after the select ? - (we can't do it within the Table function, as
MODIFIES SQL DATA is not valid when
referenced
inside a view).

If anybody has any input on any of this at all, then feel free.

Thanks.

Paul.

[Serge Rielau]

Paul,

Can you define "mappable" object?
Or in other words:
Why can't you expose a table-function? Is it for updatability?

One rtaher wild thought would be to expose the "parameters" in in the
signature of the view (as columns!) and of course in the RETURNS clause
of the table function
You would then use a WHERE clause to pin the values.
After that it's up to the optimizer to tunnel through the mess
(predicate push down right into the RETURN statement, transitive
closure, etc....)
You may want to try this in FP4. Taught the horse a few more tricks there)

No guarantees... Depends on just how complex the table functions are.
Keep me posted :-)
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Ford Desperado]

Serge,

One rtaher wild thought would be to expose the "parameters" in in the
signature of the view (as columns!) and of course in the RETURNS clause
of the table function
You would then use a WHERE clause to pin the values.
After that it's up to the optimizer to tunnel through the mess
(predicate push down right into the RETURN statement, transitive
closure, etc....)
You may want to try this in FP4. Taught the horse a few more tricks there)

not easy to understand. A working example would be helpful

[Serge Rielau]

*gulp*I was affraid of that....

CREATE TABLE T1(c1 INT, c2 INT);
CREATE FUNCTION foo() RETURNS TABLE(a INT, b INT)
RETURN SELECT * FROM T1;

CREATE VIEW v AS SELECT * FROM TABLE(foo()) AS F;

SELECT * FROM v WHERE a = 5;

DB2 will unravel all this and turn it into:
SELECT * FROM T1 WHERE c1 = 5;

This of course is a trivial example.
The point is that the table function is, at the end of the day just a
correlated subquery.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Paul Reddin]

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c0**********@hanover.torolab.ibm.com>...

Paul,

Can you define "mappable" object?
Or in other words:
Why can't you expose a table-function? Is it for updatability?

By 'mappable' I mean ..

We use a ObjectRelational mapping layer called TopLink(owned by Oracle
now).
This requires that we have physical Tables or Views to map the objects
too,
I don't believe we can map directly to a table function. Hence, we
need a way to wrap Table functions in views.

[Paul Reddin]

Serge,

Thanks a lot for this input.

The one area I'm still not sure of with this approach, is when
the table function gets more complex (which ours do & will).

i.e One of the main reasons for going this route would be to
pass parameters in to use those parameters throughout the function
to optimise ALL the queries within it. Would the predicate push-down
be as effective at achieving this throughout the function whereever the
parameter/predicate appeared, and not just in the final select ?

Thanks.

Paul.
Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c1**********@hanover.torolab.ibm.com>...

*gulp*I was affraid of that....

CREATE TABLE T1(c1 INT, c2 INT);
CREATE FUNCTION foo() RETURNS TABLE(a INT, b INT)
RETURN SELECT * FROM T1;

CREATE VIEW v AS SELECT * FROM TABLE(foo()) AS F;

SELECT * FROM v WHERE a = 5;

DB2 will unravel all this and turn it into:
SELECT * FROM T1 WHERE c1 = 5;

This of course is a trivial example.
The point is that the table function is, at the end of the day just a
correlated subquery.

Cheers
Serge

[Serge Rielau]

Paul,

Ok let's refine this:

CREATE V AS SELECT p1, p2, c1, c2)
FROM (SELECT p1, p2 FROM SYSIBM.SYSDUMMY1) AS P,
TABLE(foo(P.p1, P.p2)) AS F(c1l, c2);

SELECT c1, c2 FROM V WHERE p1 = 5 and p2 = 6;

I think the optimizer has enough power to substitute p1 and p2 with 5
and 6 everywhere in the table function body.
Now I have never tried this and if it doesn't do it in all cases, too bad.

Note that the example above is, of course, not updatable anymore.
An instead of trigger may be advised here.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

I see, fat chance to convince Oracle to support DB2 Table functions, eh?

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Paul Reddin]

Serge,

I have now a had chance to look/a try at this.

I don't think the FROM (select p1,p2 FROM SYSIBM.SYSDUMMMY1) you
suggest can be made to work.
Since...
1. p1,p2 are not a valid columns for SYSIBM.SYSDUMMY.
- I have also tried (VALUES(p1,p2)) as P(p1,p2) but again the
compiler
complains that p1 not valid where it used.
Presumably, you cannot reference p1 on itself in this way?

Many Thanks

Paul.

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c1**********@hanover.torolab.ibm.com>...

Paul,

Ok let's refine this:

CREATE V AS SELECT p1, p2, c1, c2)
FROM (SELECT p1, p2 FROM SYSIBM.SYSDUMMY1) AS P,
TABLE(foo(P.p1, P.p2)) AS F(c1l, c2);

SELECT c1, c2 FROM V WHERE p1 = 5 and p2 = 6;

I think the optimizer has enough power to substitute p1 and p2 with 5
and 6 everywhere in the table function body.

[Serge Rielau]

Hmm, which version are you on? I dimply recall some name resolution
issues which ought to be fixed now.
Simply use:
from TABLE(......) as x
That will make suer DB2 can access all columns/variabels outside the
subquery.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Paul Reddin]

Serge,

I am running on V8.1 Fp4.

The following is the small test script I'm using. This works, but
note I have hard coded values in the view, (which I actually want to replace
with the "select p1,p2 from sysibm.sysdummy1" subquery - see later).

CREATE FUNCTION ADD(p_v1 INT, p_v2 INT )
RETURNS TABLE (RESULT INT)
F1: BEGIN ATOMIC
RETURN VALUES(p_v1 + p_v2);
END/

CREATE VIEW ADD_VIEW
(p1,
p2,
c1
)
AS SELECT p1,p2,c1
FROM
TABLE (VALUES(1,2),(2,3)) AS P(p1,p2),
TABLE (ADD(P.p1, P.p2)) AS F(c1)
/

The following version of the view can't be created

CREATE VIEW ADD_VIEW
(p1,
p2,
c1
)
AS SELECT p1,p2,c1
FROM
TABLE (select p1,p2 from sysibm.sysdummy1) AS P(p1,p2),
TABLE (ADD(P.p1, P.p2)) AS F(c1)
/
SQL0206N "P1" is not valid in the context where it is used. SQLSTATE=42703

I'm a little worried because this looks fundamentally strange to be referencing
p1 in both the main select and in the table subquery ?

Thanks again

Paul.

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c1**********@hanover.torolab.ibm.com>...

Hmm, which version are you on? I dimply recall some name resolution
issues which ought to be fixed now.
Simply use:
from TABLE(......) as x
That will make suer DB2 can access all columns/variabels outside the
subquery.

Cheers
Serge

[Serge Rielau]

OK, looking at this again, I was out for lunch with that example.
We seem to have a bit of a chicken-egg problem here.
If we start of with p1 and p2 being constants (just to have something to
start with), then the theorem prover will compare what you pass in with
the constnts chosen in the view definition and you end up with an empty
table because they are not the same.

I confess I don't see an out right now :-(

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab