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.AD D(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. 11 13490
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
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
*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
Serge Rielau <sr*****@ca.e ye-be-em.com> wrote in message news:<c0******* ***@hanover.tor olab.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 ObjectRelationa l 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.
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.e ye-be-em.com> wrote in message news:<c1******* ***@hanover.tor olab.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
Paul,
Ok let's refine this:
CREATE V AS SELECT p1, p2, c1, c2)
FROM (SELECT p1, p2 FROM SYSIBM.SYSDUMMY 1) 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
I see, fat chance to convince Oracle to support DB2 Table functions, eh?
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Serge,
I have now a had chance to look/a try at this.
I don't think the FROM (select p1,p2 FROM SYSIBM.SYSDUMMM Y1) 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.e ye-be-em.com> wrote in message news:<c1******* ***@hanover.tor olab.ibm.com>.. . Paul,
Ok let's refine this:
CREATE V AS SELECT p1, p2, c1, c2) FROM (SELECT p1, p2 FROM SYSIBM.SYSDUMMY 1) 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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Lauren Quantrell |
last post by:
I did this thing with host_name(). I'm hoping someone can tell me if
I'm gonna get into trouble with this scheme before it's too late...
I have maybe 75 users.
They all call on a table of appointments (many thousands) and I dump
the ones being viewed into a temp table named TaskTEMP.
I am using a custom function within a View to grab a...
|
by: PaulR |
last post by:
Hi,
We have several inter-related SQL Table Functions, which all optimise
fine on their own and most of the time together. However, increasingly
as we are adding logic and making their logic more complex, the
optimiser seems to becoming more and more fragile on its ability to
optimise them effectively.
Let me give you our latest...
|
by: Michelle Keys |
last post by:
I am trying to call a print function to print a string from a database
using javascript.
Which is RC_DATA of Varchar2(2500).
This is a javascript is not being used. I have a thing that needs to be
modified:
if(e.CommandName =="Print")
{
string parsedreceipt = null;
parsedreceipt = DecodeReceipt (e.Item.Cells.Text);
Session =...
|
by: ratnakarp |
last post by:
Hi,
I have a search text box. The user enters the value in the text box and
click on enter button. In code behind on button click i'm writing the
code to get the values from the database and binding it to a repeater
control. This repeater control has multiple text boxes and buttons. Can
you please tell me how can i do paging in this case ?...
|
by: Serge Rielau |
last post by:
Hi all,
Following Ian's passionate postings on problems with ALTOBJ and the
alter table wizard in the control center I'll try to explain how to use
ALTOBJ with this thread. I'm not going to get into the GUI because it is
hard to describe in text.
First of all what is the purpose of ALTOBJ()?
This procedure was created mostly for ISVs who...
| |
by: Beowulf |
last post by:
I was just messing around with some ad hoc views and table returning
UDFs today so I could look at and print out data from a small table
and noticed something strange.
If I stick my select statement into a View the columns are returned in
the order I specify in the SELECT, but if the same statement is in a UDF
(so I can specify a...
|
by: lesperancer |
last post by:
it looks like this will save many versions of a relationship window,
but based on the fact that the same tables are displayed in the
relationship window
and it will restore versions of what was saved as long as all the
tables that were saved are currently shown in the relationship window
so if I have 2 tables, employee and customer
and...
|
by: Sibongile Khanyile |
last post by:
If anyone can help... I have a function that takes one parameter and
bring back quite a lot of records. but with a conventional function the
field size has a limit of 8000 and i can not use field type 'text'. the
limit has been reached and the view does not bring back results. an
option i found was to use a table function. but with my limited...
|
by: swethak |
last post by:
hi,
i have a code to disply the calendar and add events to that. It works fine.But my requirement is to i have to disply a weekly and daily calendar.Any body plz suggest that what modifications i have to made in my code
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |