Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...
Database Forum / DB2 Topics / April 2008

Tip: Looking for answers? Try searching our database.

NEED HELP: How to convert Joe Celko  SQL IN DB2 without Stored Procedure

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
lenygold - 11 Apr 2008 15:29 GMT
I am tryieng to convert our time consuming  recursive queries too very
efficient queries based
on nested set model.
The only problem is to convert an adjacency list model into a nested set
model, with push down stack algorithm to DB2 query. The client does not want
to use Stored  Procedure.
Please any  Recurcive or CWE ideas.
Thank's in advance.

-- Tree holds the adjacency model
CREATE TABLE Tree
(emp CHAR(10) NOT NULL,
boss CHAR(10));

INSERT INTO Tree
SELECT emp, boss FROM Personnel;

-- Stack starts empty, will holds the nested set model
CREATE TABLE Stack
(stack_top INTEGER NOT NULL,
emp CHAR(10) NOT NULL,
lft INTEGER,
rgt INTEGER);

BEGIN ATOMIC
DECLARE counter INTEGER;
DECLARE max_counter INTEGER;
DECLARE current_top INTEGER;

SET counter = 2;
SET max_counter = 2 * (SELECT COUNT(*) FROM Tree);
SET current_top = 1;

INSERT INTO Stack
SELECT 1, emp, 1, NULL
 FROM Tree
WHERE boss IS NULL;

DELETE FROM Tree
WHERE boss IS NULL;

WHILE counter <= (max_counter - 2)
LOOP IF EXISTS (SELECT *
                  FROM Stack AS S1, Tree AS T1
                 WHERE S1.emp = T1.boss
                   AND S1.stack_top = current_top)
    THEN
    BEGIN -- push when top has subordinates and set lft value
      INSERT INTO Stack
      SELECT (current_top + 1), MIN(T1.emp), counter, NULL
        FROM Stack AS S1, Tree AS T1
       WHERE S1.emp = T1.boss
         AND S1.stack_top = current_top;

       DELETE FROM Tree
        WHERE emp = (SELECT emp
                       FROM Stack
                      WHERE stack_top = current_top + 1);

       SET counter = counter + 1;
       SET current_top = current_top + 1;
    END
    ELSE
    BEGIN  -- pop the stack and set rgt value
      UPDATE Stack
         SET rgt = counter,
             stack_top = -stack_top -- pops the stack
       WHERE stack_top = current_top
      SET counter = counter + 1;
      SET current_top = current_top - 1;
    END IF;
END LOOP;
END;
Reply to this Message
Lennart - 11 Apr 2008 16:55 GMT
> I am tryieng to convert our time consuming  recursive queries too very
> efficient queries based
[quoted text clipped - 4 lines]
> Please any  Recurcive or CWE ideas.
> Thank's in advance.

As an alternative to nested set you could implement the closure of the
tree in a separate relation. You would gain at least the same
performance boost as in nested set, and IMO ends up with a easier to
grasp model. Note that I'm not arguing against nested set, just
pointing out another possability. Once up in the time I created some
notes to convince myself that the idea would work. Sloppy, but you
might find some interest in them:

http://fungus.teststation.com/~jon/treehandling/TreeHandling.htm

> -- Tree holds the adjacency model
> CREATE TABLE Tree
> (emp CHAR(10) NOT NULL PRIMARY KEY,
>  boss CHAR(10));

CREATE TABLE TreeClosure
(emp CHAR(10) NOT NULL,
 boss CHAR(10) NOT NULL,
 PRIMARY KEY (emp, boss));

CREATE TRIGGER ADD_EMP
AFTER INSERT ON TREE
REFERENCING NEW AS N
FOR EACH ROW
MODE DB2SQL
WHEN (N.boss IS NOT NULL)
BEGIN ATOMIC
  insert into TreeClosure (emp, boss)
      values (N.emp, N.boss)
      union all
      select N.emp, X.boss from TreeClosure X where X.emp = N.boss;
--
END;

-- implent triggers for del and move, see link above
-- CREATE TRIGGER DEL_EMP
--
--CREATE TRIGGER MOVE_EMP

--Add some sample data

[lelle@53dbd181 ~]$ db2 "insert into tree (emp) values 'A'"
DB20000I  The SQL command completed successfully.
[lelle@53dbd181 ~]$ db2 "insert into tree (emp, boss) values
('B','A')"
DB20000I  The SQL command completed successfully.
[lelle@53dbd181 ~]$ db2 "insert into tree (emp, boss) values
('C','A')"
DB20000I  The SQL command completed successfully.
[lelle@53dbd181 ~]$ db2 "insert into tree (emp, boss) values
('D','B')"
DB20000I  The SQL command completed successfully.
[lelle@53dbd181 ~]$ db2 "insert into tree (emp, boss) values
('E','D')"
DB20000I  The SQL command completed successfully.
-- Some silly questions
-- number of employees under each boss

[lelle@53dbd181 ~]$ db2 "select boss, count(1) from treeclosure group
by boss"

BOSS       2
---------- -----------
A                    4
B                    2
D                    1

-- number of "bosses" over each emp

[lelle@53dbd181 ~]$ db2 "select emp, count(1) from treeclosure group
by emp"

EMP        2
---------- -----------
B                    1
C                    1
D                    2
E                    3

 4 record(s) selected.

HTH
/Lennart

[...]
Reply to this Message
--CELKO-- - 11 Apr 2008 23:12 GMT
There is a VIEW with a recursive CTE that turns the adjacency list
model into a nested sets model, but I don't have a copy right now.  I
think it was done by Ben-Gan.  I will if I can find.
Reply to this Message
--CELKO-- - 11 Apr 2008 23:15 GMT
> There is a VIEW with a recursive CTE that turns the adjacency list
> model into a nested sets model, but I don't have a copy right now.  I
> think it was done by Ben-Gan.  I will if I can find.

got it:

http://www.simple-talk.com/opinion/geek-of-the-week/database-geek-of-the-week-it
zik-ben-gan/
Reply to this Message
lenygold - 12 Apr 2008 11:48 GMT
Hi Joe.
Thank You  very much for your help. I am trying to convert this vew in DB2,
but i have a problem
with expression: SELECT empid, 0 AS lvl, n,
CAST(n AS VARBINARY(MAX)) AS sortpath - DB2 does not accsept this expression
Any Idea how to replace it? Thank's in advance Leny G.
   

>> There is a VIEW with a recursive CTE that turns the adjacency list
>> model into a nested sets model, but I don't have a copy right now.  I
[quoted text clipped - 3 lines]
>
>http://www.simple-talk.com/opinion/geek-of-the-week/database-geek-of-the-week-it
zik-ben-gan/
Reply to this Message
Serge Rielau - 12 Apr 2008 13:32 GMT
> Hi Joe.
> Thank You  very much for your help. I am trying to convert this vew in DB2,
[quoted text clipped - 10 lines]
>>
>> http://www.simple-talk.com/opinion/geek-of-the-week/database-geek-of-the-week-it
zik-ben-gan/

Use VARCHAR(something) FOR BIT DATA instead of VARBINARY(MAX)

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message
lenygold - 13 Apr 2008 00:55 GMT
Thank you Serge for your help.
But i am still fighting with this conversion.  I am trying step by step and
got the following
error:

WITH T0(ROOT) AS
      (SELECT 1 FROM SYSIBM.SYSDUMMY1),
   TwoNumsCTE(N) AS
    (SELECT 1 FROM SYSIBM.SYSDUMMY1
    UNION ALL
     SELECT 2 FROM SYSIBM.SYSDUMMY1
     ORDER BY 1),
 SortPathCTE(empid,lvl,n,sortpath) AS
    (SELECT empid, 0, n,INTEGER(CAST(CHAR(n) AS varchar(300))) AS sortpath
       FROM dbo_Employees,T0
        CROSS JOIN TwoNumsCTE
       WHERE empid = ROOT
 UNION ALL
   SELECT C.empid, P.lvl + 1, TN.n,
   P.sortpath + ROW_NUMBER() OVER(PARTITION BY C.mgrid
     ORDER BY C.empname, C.empid, TN.n)
     FROM SortPathCTE AS P
   JOIN dbo_Employees AS C
   ON P.n = 1
    AND C.mgrid = P.empid
     CROSS JOIN TwoNumsCTE AS TN)
SELECT * FROM SortPathCTE;

sqlcode: -345
The fullselect of the recursive common table expression SORTPATHCTE
must be the UNION of two or more fullselects and cannot include column
functions,
GROUP BY clause, HAVING clause, ORDER BY clause, or an explicit join
including an ON clause.

Anoher question - I replaced Binary with integer. Should this affect the
final output?
Thank's in advance. Leny G.
 

>> Hi Joe.
>> Thank You  very much for your help. I am trying to convert this vew in DB2,
[quoted text clipped - 3 lines]
>
>Use VARCHAR(something) FOR BIT DATA instead of VARBINARY(MAX)
Reply to this Message
Serge Rielau - 13 Apr 2008 12:26 GMT
Let's reset.
What is it that you are trying to achieve.
Most importantly which information about the recursive relationship do
you need to derive.

CREATE TABLE Tree
(emp CHAR(10) NOT NULL,
 boss CHAR(10));

insert into tree values
('a', ''),
('ab', 'a'),
('ac', 'a'),
('abd', 'ab'),
('abe', 'ab');

WITH rec(lvl, emp)
  AS (VALUES (1, CAST('a' AS CHAR(10)))
      UNION ALL
      SELECT lvl + 1, tree.emp FROM rec, tree
       WHERE rec.emp = tree.boss
         AND lvl < 1000000)
SELECT * FROM rec;

This should cover all of the cases where where you don't care for the path.

Now let's add path to the problem:
WITH rec(lvl, path, emp)
  AS (VALUES (1, CAST('' AS VARCHAR(1000)), CAST('a' AS CHAR(10)))

      UNION ALL
      SELECT lvl + 1, path || tree.emp, tree.emp FROM rec, tree
       WHERE rec.emp = tree.boss
         AND lvl < 1000000)
SELECT * FROM rec ORDER BY path;

If you want to order the elements within by something else then the
path, then you need to build an alternate path catering to that.
But I would look at your requirements first and only do that work if you
have to for teh few queries that are in need.

Lastly if you are on DB2 9.5 you can use CONNECT BY which is better
suited for _ordered_ tree walks since it encapsulates the PATH creation.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message
lenygold - 13 Apr 2008 17:57 GMT
Hi Serge! Thank you very much for your help!
Based on your example the output from query

WITH rec(lvl, path, emp)
 AS (VALUES (1, CAST('' AS VARCHAR(1000)), CAST('a' AS CHAR(10)))

     UNION ALL
     SELECT lvl + 1, path || tree.emp, tree.emp FROM rec, tree
      WHERE rec.emp = tree.boss
        AND lvl < 1000000)
SELECT * FROM rec ORDER BY path;

    LVL  EMP      
----------- ----------
         1 a        
         2 ab        
         2 ac        
         3 abd      
         3 abe      
5 record(s) selected.

is Hierarchical Adjacency List Model which is an input in my task and the
output is to convert it to
Nested Set Model:

       LFT RGT  EMP
        1    10        a
        2      5        ab
        6      9        ac
        3      4        abd
        7      8        abe
based on following tree and rules:
                         
                                a(1,10)
                               /       \
                        ab(2,5)    ac(6,9)
                          |             |
                        abd(3,4)   abe(7,8)

     
RULES:   a) The root COORDINATAES  is always:
              (left = 1, right = 2 * (Number of rows in INPUT Table) or
                                     2 * (SELECT COUNT(*) FROM INPUT Table))

               b) Leaf nodes always have (left + 1 = right)

               c) For each node (n), (rgt-lft+1)/2 = size of subtree rooted
at (n).
 
               d) Union of the rgt and lft numbers must be an unbroken
sequence,
                    no gaps.
               e)  If coordinates sequence is correct: any (left + right)
are Odd  
The  BEN-GAN VIEW with recursive CTE, which is doing this conversion is
written in T-SQL
and i have not been able to translate it to DB2.
We are working with very large hierarchical data base and it will save at
least 50% CPU time going from recursive to Nested Set Model. All recursive
queries are converted in very simle fast non recursive queries and this
conversion only left unresolved.
It is a pleasure for me to participate in this forum work.

Thank's for your time and help. Leny G.    
               

   
 
>Let's reset.
>What is it that you are trying to achieve.
[quoted text clipped - 42 lines]
>Cheers
>Serge
Reply to this Message
Serge Rielau - 13 Apr 2008 18:36 GMT
Which version/platform of DB2?
Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message
lenygold - 14 Apr 2008 19:02 GMT
DB2 V9 MAINFRAME
>Which version/platform of DB2?
Reply to this Message
lenygold - 15 Apr 2008 20:48 GMT
Please help to convert this query in DB2
create table #chain
  (    seq       integer     not null primary key,
       emp       char (20),
       chain     varchar (20),
       lft       integer,
       rgt       integer
  )
declare @begDt datetime    set @begDt = getdate();
with organization (depth, emp, boss, chain)
as
( select 1 as depth,
        emp,
        boss,
        cast(rtrim(emp) as varchar (400))
   from tree
 where boss is null
 union all
 select a.depth + 1 as depth,
        b.emp,
        b.boss,
        cast (a.chain + ', ' + rtrim(b.emp) as varchar(400))
   from organization a
  inner join tree b
     on a.emp = b.boss
)
insert into #chain (seq, emp, chain)
select row_number () over (order by chain) as seq,
      emp,
      chain
 from organization
update #chain
  set lft = 1 + 2 * ( select count(*) from #chain b
                      where b.seq < a.seq
                    )
              - ( select count(*) from #chain c
                  where c.seq < a.seq
                    and ( c.chain = rtrim(c.emp) or
                          a.chain like '%, ' + rtrim(c.emp) + '%'
                        )
                )
 from #chain a
update #chain
  set rgt = lft + 1
          + 2 * ( select count(*) from #chain b
                  where b.seq <> a.seq
                    and ( b.chain like '%, ' + rtrim(a.emp) + '%'  or
                          a.seq = 1
                )       )
 from #chain a

print ' '
select datediff (ms, @begDt, getdate()) as [Elapsed Time]
select emp, lft, rgt from #chain
-- ----------  Output:  ----------
--   emp                  lft         rgt
--   -------------------- ----------- -----------
--   Albert               1           12
--   Bert                  2           3
--   Chuck               4          11
--   Donna               5           6
--   Eddie                7           8
--   Fred                  9          10

My major problem in conversion is how to convert in DB2  expression:
( c.chain = rtrim(c.emp) or
                          a.chain like '%, ' + rtrim(c.emp) + '%'
I spend 2 days to figure it out so far no result:

This is my try:

DB2:

insert into #chain (seq, emp, chain)
WITH org(depth, emp,BOSS,Chain)
 AS (select 1, CAST('Albert' AS CHAR(10)),boss, cast(rtrim(emp) as varchar
(400))
      from tree
 where boss is null
   UNION ALL
     SELECT depth + 1, b.emp, b.boss, cast (a.chain || ', ' || rtrim(b.emp)
as varchar(400))
   from TREE B,org a
    WHERE a.emp = b.boss)
select row_number () over (order by chain) as seq,
      emp,
      chain
 from org;

--update #chain
  SELECT 1 + 2 * ROW#
  FROM #CHAIN A,
       TABLE(select count(*) AS ROW# from #chain b
                      where b.seq < a.seq) AS Temp;

 

>Which version/platform of DB2?
Reply to this Message
Serge Rielau - 16 Apr 2008 01:05 GMT
Your problem is that you have a column reference in LIKE.
Take a look at the LOCATE() function which allows a column reference in
the search string.

Cheers
Serge
Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message
lenygold - 14 Apr 2008 21:14 GMT
Part  of my query:

select lft
   FROM #CHAIN A,
              TABLE(SELECT count(*) as lft from #chain c
                  where c.seq < a.seq
                    and  (c.chain = rtrim(c.emp)
                            or a.chain like '%, ' || (rtrim(c.emp))||'%'));
sqlcode: -132
A LIKE predicate or POSSTR scalar function is not valid because the first
operand is not a string expression or the second operand is not a string.

I tested similar "LIKE"  in SPUFI:

WITH T1(col) AS                                    
     (SELECT '            12345         '            
       from  SYSIBM.SYSdummy1)                      
   select col                                        
     FROM T1                                        
    where  (col like '%,' ||  rtrim(col) || '%')    
       or col =  rtrim(col);                        
---------+---------+---------+---------+---------+----
COL
---------+---------+---------+---------+---------+----
           12345
 
                           
Any Idea?

>Hi Serge! Thank you very much for your help!
>Based on your example the output from query
[quoted text clipped - 69 lines]
>>Cheers
>>Serge
Reply to this Message
--CELKO-- - 14 Apr 2008 17:48 GMT
Great translation from T-SQL dialect!  But I still this bad feeling
about using a string to hold the path enumeration; it would be nice to
go directly to the (lft, rgt) pairs from the levels and the (boss,
emp) pairs ...

You have probably already seen this, but to convert a nested sets
model into an adjacency list model:

SELECT B.emp_id AS boss_emp_id, E.emp_id
 FROM OrgChart AS E
      LEFT OUTER JOIN
      OrgChart AS B
      ON B.lft
         = (SELECT MAX(lft)
              FROM OrgChart AS S
             WHERE E.lft > S.lft
               AND E.lft < S.rgt);
Reply to this Message
lenygold - 14 Apr 2008 21:52 GMT
Hi Joe.
I Tried this query in DB2 V9 with SERGE INPUT:

WITH t0 (EMP,LFT,RGT) AS
(VALUES ('a',1,10),
       ('ab',2,5),
       ('ac',6,9),
       ('abd',3,4),
       ('abe',7,8))  
SELECT B.emp AS boss_emp_id, E.emp
FROM t0 AS E
     LEFT OUTER JOIN
     t0 AS B
     ON B.lft
        = (SELECT MAX(lft)
             FROM t0 AS S
            WHERE E.lft > S.lft
              AND E.lft < S.rgt);

sqlcode: -338
An ON clause associated with a JOIN operator or in a MERGE statement is not
valid.
*  The ON clause cannot include any subqueries.
*  Column references in an ON clause must only reference columns of
  tables that are in the scope of the ON clause.


>Great translation from T-SQL dialect!  But I still this bad feeling
>about using a string to hold the path enumeration; it would be nice to
[quoted text clipped - 13 lines]
>              WHERE E.lft > S.lft
>                AND E.lft < S.rgt);
Reply to this Message
--CELKO-- - 15 Apr 2008 15:58 GMT
It was fine on SQL Server and it passes the Mimer syntax checker.
Weird.
Reply to this Message
Serge Rielau - 15 Apr 2008 18:07 GMT
> It was fine on SQL Server and it passes the Mimer syntax checker.
> Weird.
DB2 doesn't allow subqueries in the on clause. You need to pull the
subquery out.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message
lenygold - 15 Apr 2008 20:52 GMT
Need Help?

Please help to convert this query in DB2
create table #chain
 (    seq       integer     not null primary key,
      emp       char (20),
      chain     varchar (20),
      lft       integer,
      rgt       integer
 )
declare @begDt datetime    set @begDt = getdate();
with organization (depth, emp, boss, chain)
as
( select 1 as depth,
       emp,
       boss,
       cast(rtrim(emp) as varchar (400))
  from tree
where boss is null
union all
select a.depth + 1 as depth,
       b.emp,
       b.boss,
       cast (a.chain + ', ' + rtrim(b.emp) as varchar(400))
  from organization a
 inner join tree b
    on a.emp = b.boss
)
insert into #chain (seq, emp, chain)
select row_number () over (order by chain) as seq,
     emp,
     chain
from organization
update #chain
 set lft = 1 + 2 * ( select count(*) from #chain b
                     where b.seq < a.seq
                   )
             - ( select count(*) from #chain c
                 where c.seq < a.seq
                   and ( c.chain = rtrim(c.emp) or
                         a.chain like '%, ' + rtrim(c.emp) + '%'
                       )
               )
from #chain a
update #chain
 set rgt = lft + 1
         + 2 * ( select count(*) from #chain b
                 where b.seq <> a.seq
                   and ( b.chain like '%, ' + rtrim(a.emp) + '%'  or
                         a.seq = 1
               )       )
from #chain a

print ' '
select datediff (ms, @begDt, getdate()) as [Elapsed Time]
select emp, lft, rgt from #chain
-- ----------  Output:  ----------
--   emp                  lft         rgt
--   -------------------- ----------- -----------
--   Albert               1           12
--   Bert                  2           3
--   Chuck               4          11
--   Donna               5           6
--   Eddie                7           8
--   Fred                  9          10

My major problem in conversion is how to convert in DB2  expression:
( c.chain = rtrim(c.emp) or
                         a.chain like '%, ' + rtrim(c.emp) + '%'
I spend 2 days to figure it out so far no result:

This is my try:

DB2:

insert into #chain (seq, emp, chain)
WITH org(depth, emp,BOSS,Chain)
AS (select 1, CAST('Albert' AS CHAR(10)),boss, cast(rtrim(emp) as varchar
(400))
     from tree
where boss is null
  UNION ALL
    SELECT depth + 1, b.emp, b.boss, cast (a.chain || ', ' || rtrim(b.emp)
as varchar(400))
  from TREE B,org a
   WHERE a.emp = b.boss)
select row_number () over (order by chain) as seq,
     emp,
     chain
from org;

--update #chain
 SELECT 1 + 2 * ROW#
 FROM #CHAIN A,
      TABLE(select count(*) AS ROW# from #chain b
                     where b.seq < a.seq) AS Temp;

>> It was fine on SQL Server and it passes the Mimer syntax checker.
>> Weird.
[quoted text clipped - 3 lines]
>Cheers
>Serge
Reply to this Message
Serge Rielau - 16 Apr 2008 01:05 GMT
See my other post. Use LOCATE instead of LIKE.

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.