Hi

I have a problem, that I seem unable to solve. I need to extract a customer and all those who are in some way associated with him.

The table looks like this:
KUN_ASSOCID, - unique id for the row
CPRNR, - the customers id
ASS_CPRNR, - id of someone associated with the customer
ASS_TYPE, - na
LEVELNR -na

and has aprox. 1000000 rows

So for instance, if I were to search for cust. 1, a result could be.

1-2
2-3
3-4
3-5
...
and so on.

I thought of this a graph problem, and wrote the follow sql.


WITH GRP(KUN_ASSOCID, CPRNR, ASS_CPRNR,
ASS_TYPE, LEVELNR, PATH, COUNTER) AS
(
SELECT
STARTNODE.KUN_ASSOCID,
STARTNODE.CPRNR,
STARTNODE.ASS_CPRNR,
STARTNODE.ASS_TYPE,
STARTNODE.LEVELNR,
VARCHAR(VARCHAR(STARTNODE.CPRNR)||VARCHAR(STARTNOD E.ASS_CPRNR)||'Z', 1200),
1
FROM UPD.JB8C_KUN_ASSOC_S STARTNODE
WHERE ( STARTNODE.CPRNR = 15950714
OR STARTNODE.ASS_CPRNR = 15950714
)
UNION ALL
SELECT
CHILDNODE.KUN_ASSOCID,
CHILDNODE.CPRNR,
CHILDNODE.ASS_CPRNR,
CHILDNODE.ASS_TYPE,
CHILDNODE.LEVELNR,
CASE
WHEN
LOCATE(VARCHAR(CHILDNODE.CPRNR)||VARCHAR(CHILDNODE .ASS_CPRNR)||'Z', PARENTNODE.PATH) > 0
THEN
'C'
ELSE
PARENTNODE.PATH||VARCHAR(CHILDNODE.CPRNR)||VARCHAR (CHILDNODE.ASS_CPRNR)||'Z'

END,
PARENTNODE.COUNTER+1
FROM GRP PARENTNODE, UPD.JB8C_KUN_ASSOC_S CHILDNODE
WHERE PARENTNODE.PATH ^= 'C'
AND
(
(PARENTNODE.ASS_CPRNR =CHILDNODE.CPRNR)
OR
(PARENTNODE.CPRNR =CHILDNODE.ASS_CPRNR)
OR
(PARENTNODE.CPRNR = CHILDNODE.CPRNR AND PARENTNODE.ASS_CPRNR ^= CHILDNODE.ASS_CPRNR)
)
and
PARENTNODE.COUNTER < 10
)
SELECT DISTINCT G.CPRNR, G.ASS_CPRNR, G.ASS_TYPE,
G.LEVELNR, D.EJER_PCT, G.KUN_ASSOCID
FROM GRP G

In an effort to weed out cycles in the graph, I introduced a path string, which is checked to see if an edge has already been traversed.

Unfortunately, this does not work very well. I think the problem is this: The path which is build during the recursion is lost when a recursion branch ends. Thus, when another recursion branch is running, there is no information on the what parts of the graph has been visited.

Can anyone help solve this?

If it somehow was possible to build an edge list or something similar, the problem could be solved I think.

I am developing this for DB2 ver.8 on Z/OS.

Any comments would be very welcome (I should have been done by now )

Regards
Karsten

The "locate" & concat part of your query the "PATH" column) will be too heavy, I'm afraid.
Couldn't it be replaced by a "NOT EXISTS" condition such that you never add nodes that were visited before?

Hi Peter

It is true that the creation of the path is rather expensive, however, compared to a traversing a loop, it is cheap.

I will try to see if I can get the NOT EXISTS to do what I want.

Regards,
Karsten