Hi, I have some problem in using SQL3 recursive queries on DB2
database system (8.1 and 8.2 UDB).
I need to compute the transitive closure of a (possibly) ciclic graph
using SQL3 on DB2.
I MUST use pure SQL3 and I cannot use cursors, indexes and the like.
The graph is represented by a set of arcs with the table "arc"
having two attributes "a1" and "a2" for source and target nodes resp.
The SQL3 query I'm using is the following:
with path (att1, att2) as (
select e.a1, e.a2
from arc e
union all
select e.a1, p.att2
from arc e, path p
where e.a2 = p.att1 and p.att2 <> e.a1)
select distinct att1, att2
from path
group by att1
I have that:
1)using the following graph (represented by the "arc" table), the SQL3
query above works fine
"arc"
1, 2
2, 3
3, 4
4, 5
5, 1
i.e. if the overall graph is a cycle, the query works well. However:
2) if I use the following graph, the SQL3 computation "fails", i.e.
DB2 doesn't send any error but the query does not return any result
within one hour (after this I stopped the query)
"arc"
1, 2
2, 1
2, 3
3, 4
3) Similarly, if I use the following graph, the SQL3 computation
"fails" (as in point 2)
"arc"
1, 2
2, 3
3, 2
3, 4
I tried also other graph configurations but I have the same problems
as in 2) and 3).
I tried to change the query in (I capitalize modifications to
highlight them)
with path (att1, att2) as (
select e.a1, e.a2
from arc e
union all
select DISTINCT e.a1, p.att2
from arc e, path p
where e.a2 = p.att1 and p.att2 <> e.a1)
select distinct att1, att2
from path
group by att1
or
with path (att1, att2) as (
select e.a1, e.a2
from arc e
union all
select e.a1, p.att2
from arc e, path p
where e.a2 = p.att1 and p.att2 <> e.a1
AND (e.a1, p.att2) NOT IN (SELECT * FROM path) )
select distinct att1, att2
from path
group by att1
but the system gives syntactical errors.
Could you help me to solve this problem? So far I found that DB2 is
the only database working with cyclic graphs and recursive queries
over them (point 1) but I need to solve queries over graphs of type 2)
and 3) .
Could you tell me where (and if) the query is wrong?
Regards 3 4133
Vincenzino wrote: Hi, I have some problem in using SQL3 recursive queries on DB2 database system (8.1 and 8.2 UDB). I need to compute the transitive closure of a (possibly) ciclic graph using SQL3 on DB2. I MUST use pure SQL3 and I cannot use cursors, indexes and the like.
The graph is represented by a set of arcs with the table "arc" having two attributes "a1" and "a2" for source and target nodes resp.
The SQL3 query I'm using is the following:
with path (att1, att2) as ( select e.a1, e.a2 from arc e union all select e.a1, p.att2 from arc e, path p where e.a2 = p.att1 and p.att2 <> e.a1) select distinct att1, att2 from path group by att1
I have that: 1)using the following graph (represented by the "arc" table), the SQL3 query above works fine "arc" 1, 2 2, 3 3, 4 4, 5 5, 1 i.e. if the overall graph is a cycle, the query works well. However:
2) if I use the following graph, the SQL3 computation "fails", i.e. DB2 doesn't send any error but the query does not return any result within one hour (after this I stopped the query) "arc" 1, 2 2, 1 2, 3 3, 4
3) Similarly, if I use the following graph, the SQL3 computation "fails" (as in point 2) "arc" 1, 2 2, 3 3, 2 3, 4
I tried also other graph configurations but I have the same problems as in 2) and 3).
I tried to change the query in (I capitalize modifications to highlight them)
with path (att1, att2) as ( select e.a1, e.a2 from arc e union all select DISTINCT e.a1, p.att2 from arc e, path p where e.a2 = p.att1 and p.att2 <> e.a1) select distinct att1, att2 from path group by att1
or
with path (att1, att2) as ( select e.a1, e.a2 from arc e union all select e.a1, p.att2 from arc e, path p where e.a2 = p.att1 and p.att2 <> e.a1 AND (e.a1, p.att2) NOT IN (SELECT * FROM path) ) select distinct att1, att2 from path group by att1
but the system gives syntactical errors.
Could you help me to solve this problem? So far I found that DB2 is the only database working with cyclic graphs and recursive queries over them (point 1) but I need to solve queries over graphs of type 2) and 3) .
Could you tell me where (and if) the query is wrong?
Regards
Since the graph can be cyclic the simplest fix would be to add an
artificial "timeout".
E.g. add a column "level" which starts with 0
recurse using level + 1 as level
and then add an "AND level < 10000"
That will limit DB2 to 10000 levels of recursion (which ought to be
plenty for most apps - even my reports to chain is shorter ;-)
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Vincenzino wrote: Hi, I have some problem in using SQL3 recursive queries on DB2 database system (8.1 and 8.2 UDB). I need to compute the transitive closure of a (possibly) ciclic graph using SQL3 on DB2. I MUST use pure SQL3 and I cannot use cursors, indexes and the like.
That sounds very much like an exercise. ;-)
The graph is represented by a set of arcs with the table "arc" having two attributes "a1" and "a2" for source and target nodes resp.
The SQL3 query I'm using is the following:
with path (att1, att2) as ( select e.a1, e.a2 from arc e union all select e.a1, p.att2 from arc e, path p where e.a2 = p.att1 and p.att2 <> e.a1) select distinct att1, att2 from path group by att1
I have that: 1)using the following graph (represented by the "arc" table), the SQL3 query above works fine "arc" 1, 2 2, 3 3, 4 4, 5 5, 1 i.e. if the overall graph is a cycle, the query works well. However:
2) if I use the following graph, the SQL3 computation "fails", i.e. DB2 doesn't send any error but the query does not return any result within one hour (after this I stopped the query) "arc" 1, 2 2, 1
Here you have a cycle already.
Either you choose the approach suggested by Serge and implement an explicit
termination, or you must collect the path to each vertex and before
extending the path you will check if the vertex already exists in the path
so far.
2, 3 3, 4
--
Knut Stolze
Information Integration
IBM Germany / University of Jena
"Vincenzino" <li*@mat.unical.it> wrote in message
news:95**************************@posting.google.c om... Hi, I have some problem in using SQL3 recursive queries on DB2 database system (8.1 and 8.2 UDB). I need to compute the transitive closure of a (possibly) ciclic graph using SQL3 on DB2. I MUST use pure SQL3 and I cannot use cursors, indexes and the like.
The graph is represented by a set of arcs with the table "arc" having two attributes "a1" and "a2" for source and target nodes resp.
The SQL3 query I'm using is the following:
with path (att1, att2) as ( select e.a1, e.a2 from arc e union all select e.a1, p.att2 from arc e, path p where e.a2 = p.att1 and p.att2 <> e.a1) select distinct att1, att2 from path group by att1
I have that: 1)using the following graph (represented by the "arc" table), the SQL3 query above works fine "arc" 1, 2 2, 3 3, 4 4, 5 5, 1 i.e. if the overall graph is a cycle, the query works well. However:
2) if I use the following graph, the SQL3 computation "fails", i.e. DB2 doesn't send any error but the query does not return any result within one hour (after this I stopped the query) "arc" 1, 2 2, 1 2, 3 3, 4
3) Similarly, if I use the following graph, the SQL3 computation "fails" (as in point 2) "arc" 1, 2 2, 3 3, 2 3, 4
I tried also other graph configurations but I have the same problems as in 2) and 3).
I tried to change the query in (I capitalize modifications to highlight them)
with path (att1, att2) as ( select e.a1, e.a2 from arc e union all select DISTINCT e.a1, p.att2 from arc e, path p where e.a2 = p.att1 and p.att2 <> e.a1) select distinct att1, att2 from path group by att1
or
with path (att1, att2) as ( select e.a1, e.a2 from arc e union all select e.a1, p.att2 from arc e, path p where e.a2 = p.att1 and p.att2 <> e.a1 AND (e.a1, p.att2) NOT IN (SELECT * FROM path) ) select distinct att1, att2 from path group by att1
but the system gives syntactical errors.
Could you help me to solve this problem? So far I found that DB2 is the only database working with cyclic graphs and recursive queries over them (point 1) but I need to solve queries over graphs of type 2) and 3) .
Could you tell me where (and if) the query is wrong?
Regards
See http://tinyurl.com/3zb36
--
JAG This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Varun Kacholia |
last post by:
Hello All,
I wanted to know whether recursive selects are supported in the latest version
of postgres. I checked out the unimplemented sql constructs and came to know
that "with recursive" is...
|
by: Mikito Harakiri |
last post by:
I wonder if
WITH RECURSIVE MaryAncestor(anc,desc) AS
( (SELECT parent as anc, child as desc FROM ParentOf WHERE desc =
"Mary")
UNION
(SELECT A1.anc, A2.desc
FROM MaryAncestor A1, MaryAncestor...
|
by: Mikito Harakiri |
last post by:
Transitive closure (TC) of a graph is
with TransClosedEdges (tail, head) as
( select tail, head from Edges
union all
select e.tail, ee.head from Edges e, TransClosedEdges ee
where e.head =...
|
by: David |
last post by:
Hi there,
I'm having some trouble identifying transitive dependencies in the student
table below
StudId Name CourseCode CourseDesc Lecturer Grade Office
'S1234', 'Jack', ...
|
by: birchb |
last post by:
While working on type expressions I am rather stuck for a
way to express recursive types. A simple example of this is a
singly-linked list of integers. In some languages you have compiler
syntax...
|
by: streamkid |
last post by:
hello..
i have the following problem to solve, which is part of mst problem.
i have some nodes (ie cities if you like), and each city is connected
with some other. we don't know which city is...
|
by: karpalmera |
last post by:
Hello! If there’s anyone who can help with this problem…
We created two working environments, creating the same table (TABLEX), same indexes (INDEX1, INDEX2 & INDEX3) and executing the same SQL...
|
by: from.future.import |
last post by:
Hi,
I encountered garbage collection behaviour that I didn't expect when
using a recursive function inside another function: the definition of
the inner function seems to contain a circular...
|
by: Davy |
last post by:
Hi all,
Sometimes I need to pass same parameter in recursive function. From my
point of view, the style is redundant, and I don't what to use some
global style like self.A, self.B, Is there any...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |