Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2011
    Posts
    9

    Unanswered: Convert recursive query Oracle to DB2/UDB

    Hello guys I need help for convert a SQL Query Oracle for DB2/UDB database.
    My query is recursive and I have some difficulties to make that.

    This is the Oracle query :
    Code:
    WITH matable AS ( 
    	SELECT 'GTETS' AS table1, 'SVCDV' AS table2, 'gtets.numgtets = svcdv.etssvcdv' AS sql 										FROM dual UNION ALL 
    	SELECT 'SGART' AS table1, 'SVLCV' AS table2, 'sgart.numsgart = svlcv.artsvlcv' AS sql 										FROM dual UNION ALL 
    	SELECT 'SVCDV' AS table1, 'SKDRE' AS table2, 'svcdv.ecvsvcdv <= 158' AS sql 												FROM dual UNION ALL 
    	SELECT 'SVFAV' AS table1, 'SVCDV' AS table2, 'svfav.numsvfav = svcdv.favsvcdv and svfav.etssvfav = svcdv.etssvcdv' AS sql 	FROM dual UNION ALL 
    	SELECT 'SVCDV' AS table1, 'SVCLI' AS table2, 'svcdv.clisvcdv = svcli.numsvcli and svcdv.etssvcdv = svcli.etssvcli' AS sql 	FROM dual UNION ALL 
    	SELECT 'SVCVE' AS table1, 'SVCDV' AS table2, 'svcve.nuisvcve = svcdv.nuisvcdv' AS sql 										FROM dual UNION ALL 
    	SELECT 'SVLCV' AS table1, 'SVCDV' AS table2, 'svlcv.nuisvlcv = svcdv.nuisvcdv' AS sql 										FROM dual UNION ALL 
    	SELECT 'SGART' AS table1, 'SGAAE' AS table2, 'sgart.numsgart = sgaae.artsgaae' AS sql 										FROM dual 
    ), 
    sr1 AS ( 
    SELECT table1, table2, sql FROM matable UNION SELECT table2, table1, sql FROM matable 
    ), 
    sr2 AS ( 
    SELECT level, table1, table2, sys_connect_by_path(sql, ' AND ') AS chemin FROM sr1 WHERE table2 = 'SVFAV'  
    START WITH table1 = 'SGART' CONNECT BY NOCYCLE table1 = PRIOR table2 ORDER BY level ASC 
    ) 
    SELECT chemin FROM sr2 WHERE rownum = 1


    and this is my DB2 query for the moment :
    Code:
    WITH matable AS ( 
    	SELECT 'GTETS' AS table1, 'SVCDV' AS table2, 'gtets.numgtets = svcdv.etssvcdv' AS sql 								FROM dual UNION ALL 
    	SELECT 'SGART' AS table1, 'SVLCV' AS table2, 'sgart.numsgart = svlcv.artsvlcv' AS sql 								FROM dual UNION ALL 
    	SELECT 'SVCDV' AS table1, 'SKDRE' AS table2, 'svcdv.ecvsvcdv <= 158' AS sql 										FROM dual UNION ALL 
    	SELECT 'SVFAV' AS table1, 'SVCDV' AS table2, 'svfav.numsvfav = svcdv.favsvcdv and svfav.etssvfav = svcdv.etssvcdv' AS sql 	FROM dual UNION ALL 
    	SELECT 'SVCDV' AS table1, 'SVCLI' AS table2, 'svcdv.clisvcdv = svcli.numsvcli and svcdv.etssvcdv = svcli.etssvcli' AS sql 	FROM dual UNION ALL 
    	SELECT 'SVCVE' AS table1, 'SVCDV' AS table2, 'svcve.nuisvcve = svcdv.nuisvcdv' AS sql 								FROM dual UNION ALL 
    	SELECT 'SVLCV' AS table1, 'SVCDV' AS table2, 'svlcv.nuisvlcv = svcdv.nuisvcdv' AS sql 								FROM dual UNION ALL 
    	SELECT 'SGART' AS table1, 'SGAAE' AS table2, 'sgart.numsgart = sgaae.artsgaae' AS sql 								FROM dual 
    ), 
    sr1 AS ( SELECT table1, table2, sql FROM matable UNION SELECT table2, table1, sql FROM matable ),
    sr2(TA, TB, STEP, SQL, MOI) AS 
    (
    SELECT sr1.table1, sr1.table2, 0, sr1.sql, '' FROM sr1 WHERE sr1.table1 = 'SGART'
    UNION ALL
    SELECT sr1.table1, sr1.table2, sr2.STEP + 1, sr1.sql, sr2.sql || 'AND ' || sr1.sql FROM sr1, sr2 WHERE sr1.table1 = sr2.TB
    )
    SELECT * FROM sr2 WHERE sr2.TB = 'SVFAV'
    If someone have an idea, that will be great

    Thanks
    Last edited by ghosty177; 08-09-11 at 09:55.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Do you get any error message ?

    or are you not getting the same data as Oracle ?

    Can you post table definitions also ?

    If you don't provide enough information, no one can help
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2011
    Posts
    9
    No I don't have any error message

    The query works but doesnt return the good results, I thinks it's boucling to infinity.

    Code:
    WITH matable AS ( 
    	SELECT 'GTETS' AS table1, 'SVCDV' AS table2, 'gtets.numgtets = svcdv.etssvcdv' AS sql 								FROM dual UNION ALL 
    	SELECT 'SGART' AS table1, 'SVLCV' AS table2, 'sgart.numsgart = svlcv.artsvlcv' AS sql 								FROM dual UNION ALL 
    	SELECT 'SVCDV' AS table1, 'SKDRE' AS table2, 'svcdv.ecvsvcdv <= 158' AS sql 										FROM dual UNION ALL 
    	SELECT 'SVFAV' AS table1, 'SVCDV' AS table2, 'svfav.numsvfav = svcdv.favsvcdv and svfav.etssvfav = svcdv.etssvcdv' AS sql 	FROM dual UNION ALL 
    	SELECT 'SVCDV' AS table1, 'SVCLI' AS table2, 'svcdv.clisvcdv = svcli.numsvcli and svcdv.etssvcdv = svcli.etssvcli' AS sql 	FROM dual UNION ALL 
    	SELECT 'SVCVE' AS table1, 'SVCDV' AS table2, 'svcve.nuisvcve = svcdv.nuisvcdv' AS sql 								FROM dual UNION ALL 
    	SELECT 'SVLCV' AS table1, 'SVCDV' AS table2, 'svlcv.nuisvlcv = svcdv.nuisvcdv' AS sql 								FROM dual UNION ALL 
    	SELECT 'SGART' AS table1, 'SGAAE' AS table2, 'sgart.numsgart = sgaae.artsgaae' AS sql 								FROM dual 
    ), 
    sr1 AS ( SELECT table1, table2, sql FROM matable UNION SELECT table2, table1, sql FROM matable ),
    sr2(TA, TB, STEP, SQL, MOI) AS 
    (
    SELECT sr1.table1, sr1.table2, 0, sr1.sql, '' FROM sr1 WHERE sr1.table1 = 'SGART'
    UNION ALL
    SELECT sr1.table1, sr1.table2, sr2.STEP + 1, sr1.sql, sr2.sql || 'AND ' || sr1.sql FROM sr1, sr2 WHERE sr1.table1 = sr2.TB
    )
    SELECT * FROM sr2 WHERE sr2.TB = 'SVFAV'
    In fact I have to define the way beetween two tables. The upper part in the query ("WITH matable") generate a temporary table with my usefull data.
    If you analyse the relations beetween the data, you 'll see their is a unique shortest path beetween two tables. And I try to found this with the recursive query. This query works fine on oracle DB like this and I can't obtein the same result on DB2.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    What is the result you want for the data you are using here ?

    ---
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Aug 2011
    Posts
    9
    The result is :

    Code:
    sgart.numsgart = svlcv.artsvlcv AND svlcv.nuisvlcv = svcdv.nuisvcdv AND svfav.numsvfav = svcdv.favsvcdv and svfav.etssvfav = svcdv.etssvcdv
    it's the concatenation of all sql fields of the path. For this example it's the path between 'SGART' and 'SVFAV'


    For the path beetween 'SGART' and 'CDLCV'. the result will be :
    Code:
    sgart.numsgart = svlcv.artsvlcv

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that you need to simulate "NOCYCLE" of Oracle.


    Here is an example.

    I added a column "tables" and the following condition to simulate "NOCYCLE".
    Code:
       AND LOCATE(sr1.table1 , sr2.tables) = 0
    The following condition was added to prevent a warning message
    SQL0347W The recursive common table expression "DB2ADMIN.SR2" may contain an infinite loop. SQLSTATE=01605
    Code:
       AND level < 10
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     matable(table1 , table2 , sql) AS ( 
    SELECT 'GTETS' , 'SVCDV' , 'gtets.numgtets = svcdv.etssvcdv'
     FROM  sysibm.sysdummy1 UNION ALL 
    SELECT 'SGART' , 'SVLCV' , 'sgart.numsgart = svlcv.artsvlcv'
     FROM  sysibm.sysdummy1 UNION ALL 
    SELECT 'SVCDV' , 'SKDRE' , 'svcdv.ecvsvcdv <= 158'
     FROM  sysibm.sysdummy1 UNION ALL 
    SELECT 'SVFAV' , 'SVCDV' , 'svfav.numsvfav = svcdv.favsvcdv and svfav.etssvfav = svcdv.etssvcdv'
     FROM  sysibm.sysdummy1 UNION ALL 
    SELECT 'SVCDV' , 'SVCLI' , 'svcdv.clisvcdv = svcli.numsvcli and svcdv.etssvcdv = svcli.etssvcli'
     FROM  sysibm.sysdummy1 UNION ALL 
    SELECT 'SVCVE' , 'SVCDV' , 'svcve.nuisvcve = svcdv.nuisvcdv'
     FROM  sysibm.sysdummy1 UNION ALL 
    SELECT 'SVLCV' , 'SVCDV' , 'svlcv.nuisvlcv = svcdv.nuisvcdv'
     FROM  sysibm.sysdummy1 UNION ALL 
    SELECT 'SGART' , 'SGAAE' , 'sgart.numsgart = sgaae.artsgaae'
     FROM  sysibm.sysdummy1
    )
    , sr1 AS (
    SELECT table1 , table2 , sql FROM matable
    UNION ALL
    SELECT table2 , table1 , sql FROM matable
    )
    , sr2(level , tables, table2 , chemin) AS (
    SELECT 0
         , CAST(table1 AS VARCHAR(40) )
         , table2
         , CAST(sql AS VARCHAR(400) )
     FROM  sr1
     WHERE table1 = 'SGART'
    UNION ALL
    SELECT level + 1
         , sr2.tables || ' , ' || sr1.table1
         , sr1.table2
         , sr2.chemin || ' AND ' || sr1.sql
     FROM  sr1
         , sr2
     WHERE sr1.table1 = sr2.table2
       AND LOCATE(sr1.table1 , sr2.tables) = 0
       AND level < 10
    )
    SELECT chemin
     FROM  sr2
     WHERE table2 = 'SVFAV'
     ORDER BY
           level
    ;
    ------------------------------------------------------------------------------
    
    CHEMIN                                                                                                                                                                                                                                                                                                                                                                                                          
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    sgart.numsgart = svlcv.artsvlcv AND svlcv.nuisvlcv = svcdv.nuisvcdv AND svfav.numsvfav = svcdv.favsvcdv and svfav.etssvfav = svcdv.etssvcdv                                                                                                                                                                                                                                                                     
    
      1 record(s) selected.

  7. #7
    Join Date
    Aug 2011
    Posts
    9
    Wow great it's looks good. I'll test that, but a big thank you to you.

    About the limit of 10, if I understand well, it's just for avoid the database loop to the limit of allowed loop, thats right?
    And do you know how much loop the database allowed in standard configuration?

    And I have an other question. For result, the request give me :
    Code:
    sgart.numsgart = svlcv.artsvlcv                                     AND svlcv.nuisvlcv = svcdv.nuisvcdv                                     AND svfav.numsvfav = svcdv.favsvcdv and svfav.etssvfav = svcdv.etssvcdv
    As you can see, it put me a lot of spaces beetween the results, did you know why? Because in you extract their is no such spaces?
    Last edited by ghosty177; 08-09-11 at 11:49.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ..., it put me a lot of spaces beetween the results, did you know why?
    I don't know why.
    But, columns chemin and/or sql must be interpreted as fixed length string at the time of concatenation.

    How about to use RTRIM() functions, like...
    CAST(RTRIM(sql) AS VARCHAR(400) )
    and
    RTRIM(sr2.chemin) || ' AND ' || RTRIM(sr1.sql)

  9. #9
    Join Date
    Aug 2011
    Posts
    9
    Hy,
    I add the RTRIM to your query and it works fine, no more spaces.

    But I have an other problem when I adapt this in my program, the query return an error to me :
    Code:
    Error: DB2 SQL Error: SQLCODE=-433, SQLSTATE=22001, SQLERRMC=GTETS            , SVCDV            , SVCLI           , DRIVER=3.59.81
    So I put the VARCHAR limit to 150 and 2048, is it right?

    And, is it possible to format the sql to only keep the first path between to tables (first row of result)?
    Because after run some test, I see only the first result is interesting for me, so the database don't need to search after.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I put the VARCHAR limit to 150 and 2048, is it right?
    Yes.
    The datatype of table1 and table2 must be CHAR(16).
    So, length of concatenated string should be at least
    18 * (number of tables between <from table> and <to table>).

    is it possible to format the sql to only keep the first path between to tables (first row of result)?
    Add "FETCH FIRST 1 ROW ONLY" clause at the last of the query.

  11. #11
    Join Date
    Aug 2011
    Posts
    9
    Ok for the lenght of the string.

    About the "FETCH FIRST 1 ROW ONLY", it's works but it isn't solve my problem.

    I test to change the number put in "level < 10", and that impact the time in a big way :

    for an exemple with the level result is 1, if I put level at
    level = 5 ==> 0,016s
    level = 7 ==> 0,234s
    level = 9 ==> 4,359s
    level = 10 ==> 17,609s

    Is it possible to break the query when the the DB found the first result?
    Like this I can put the level limit high, and the DB will use what they need.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How many pathes are there between from_table and to_table?

    If you stop searching next tables by a condition "LOCATE(sr1.table1 , sr2.tables) = 0",
    then recursion may not take so much times(except the case having millions of pathes between from_table and to_table),
    even if levels get high(query time may get longer proportionally to levels).

    I guessed that you might join next tables by removing join conditions(or loosened the conditions) on your test.
    So, it joined many tables to existing pathes on each level
    and number of intermidiate result rows grow by multiplication.

  13. #13
    Join Date
    Aug 2011
    Posts
    9
    Their is only one (shortest) way beetween two table.
    And the maximun level beetween two table is around 10 and 20(big max) interval table.

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Their is only one (shortest) way beetween two table.
    I asked number of all the ways(including shortest and longest) beetween two table.

    Anyhow,
    how about the result and the execution time of this query?
    Code:
    WITH
     sr1(table1 , table2 , sql) AS (
    SELECT RTRIM(table1) , RTRIM(table2) , sql FROM matable
    UNION ALL
    SELECT RTRIM(table2) , RTRIM(table1) , sql FROM matable
    )
    , sr2(level , table_next , tables) AS (
    SELECT 1
         , table2
         , VARCHAR(table1 , 200)
     FROM  sr1
     WHERE table1 = 'SGART'
    UNION ALL
    SELECT level + 1
         , table2
         , tables || ',' || table1
     FROM  sr1
         , sr2
     WHERE table1 = table_next
       AND LOCATE(table1 , tables) = 0
       AND level < 10 /*1000000000*/
    )
    SELECT
           level
         , COUNT(*) AS count_rows
     FROM  sr2
     GROUP BY
           level
     ORDER BY
           level
    ;
    Last edited by tonkuma; 08-10-11 at 14:46. Reason: Moved RTRIM to sr1 from sr2

  15. #15
    Join Date
    Aug 2011
    Posts
    9
    Hy,

    Their is only one path between two table, their is no circle in the pattern.

    However, times of query are shortest.
    But one thinks is wired I can't change the result I want to show : the "chemin" of the first query
    And I don't see where you put the second table for the end of path.
    Last edited by ghosty177; 08-11-11 at 10:23.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •