Need help with a Query

odin568 · #1 (**permalink**) 08-22-11, 05:04

Hi!

I have a question to a query I wish to create.
I attached Table and query to txt file to format it a bit better, hope thats ok.

I have a Table with some cmds, that I want to bring into the right order. Dependency for one cmd is stored in column dependency.
I created a query (see text file).
I get quite a good output for my skills, but I am missing special cmds, that have dependency like ..._all (this is hardcoded). They are missing in my result and I don't know how to add them to my query.

Can anyone take a look at my problem?

Thanks in advance!

Forgot to say... It's DB2 ;-)

tonkuma · #2 (**permalink**) 08-22-11, 06:03

What do you mean by "cmd"?
I couldn't see the word or column of that name in your attached file.

What is a datatype of dependency?
Is it VARCHAR?

What output do you want for id = 22?

What columns do you want to include in your output?
All of id, depemdency, type, path, and depth?

Please don't use tab characters to format in your file or posted message.
Tab setting may be different by each environment.

tonkuma · #3 (**permalink**) 08-22-11, 06:23

I made a test data from your text file like the following.
Is it right?

Code:

------------------------------ Commands Entered ------------------------------
WITH
 run(id , dependency , type) AS (
VALUES
  ( 1 , '33'    , 'T_ALL')
, ( 4 , '33'    , 'T_ALL')
, (33 , '55'    , ''     )
, (78 , ''      , ''     )
, (23 , '78'    , ''     )
, (38 , '23'    , ''     )
, (22 , 'T_ALL' , ''     )
, (68 , ''      , ''     )
, (55 , ''      , ''     )
, (18 , ''      , ''     )
, (97 , '18'    , ''     )
)
SELECT * FROM run
;
------------------------------------------------------------------------------

ID          DEPENDENCY TYPE 
----------- ---------- -----
          1 33         T_ALL
          4 33         T_ALL
         33 55              
         78                 
         23 78              
         38 23              
         22 T_ALL           
         68                 
         55                 
         18                 
         97 18              

  11 record(s) selected.

odin568 · #4 (**permalink**) 08-22-11, 06:26

Quote:

Originally Posted by tonkuma

What do you mean by "cmd"?
I couldn't see the word or column of that name in your attached file.

What is a datatype of dependency?
Is it VARCHAR?

What output do you want for id = 22?

What columns do you want to include in your output?
All of id, depemdency, type, path, and depth?

Please don't use tab characters to format in your file or posted message.
Tab setting may be different by each environment.

Ah sorry, I mean the ID. Behind the ID stands a special Commandjob but thats irrelevant for this.

Datatype of dependency is Character (100) nullable, of ID is bigint and type varchar(100)

For id 22 I would like the following Line (should be in this case at the end, but theoreticly could also be in the middle):

ID:22
Dependency:T_ALL
Type: T_ALL
Path:55,33,1,4,22
Depth:4

But: I just played around with the path, I don't need it forcefully. It would be enough to have the IDs in the right order for the beginning.

Hope it is understandable what I mean ;-)

Thanks!

Edit: you are fast :-) yes, thats exactly right!

dr_te_z · #5 (**permalink**) 08-22-11, 06:50

Quote:

Originally Posted by odin568

Edit: you are fast :-) yes, thats exactly right!

Yeah, next time you've got a SQL question try to post it during the japanese night.... maybe we get a chance....

tonkuma · #6 (**permalink**) 08-22-11, 22:59

Although I have some questions that I wrote at the bottom of this post,
here is a query which returned same result in your attached file from your provided data.
(Tested on DB2 9.7.4 on Windows/XP)

Code:

------------------------------ Commands Entered ------------------------------
WITH
 run(id , dependency , type) AS (
SELECT BIGINT(id)
     , CHAR(dependency , 100)
     , VARCHAR(type    , 100)
 FROM
       (VALUES
          ( 1 , '33'    , 'T_ALL')
        , ( 4 , '33'    , 'T_ALL')
        , (33 , '55'    , ''     )
        , (78 , ''      , ''     )
        , (23 , '78'    , ''     )
        , (38 , '23'    , ''     )
        , (22 , 'T_ALL' , ''     )
        , (68 , ''      , ''     )
        , (55 , ''      , ''     )
        , (18 , ''      , ''     )
        , (97 , '18'    , ''     )
       ) r(id , dependency , type)
)
, r_cte(id , dependency , type , n_id , path , depth) AS (
SELECT r.id
     , r.dependency
     , COALESCE(MAX(n.type) , '') || r.type
     , MAX(n.dependency)
     , VARCHAR( COALESCE( LISTAGG(VARCHAR(n.id) || ',') , '' ) , 20 )
       || VARCHAR(r.id)
     , 1 + SIGN( COUNT(n.id) )
    /* CASE WHEN MAX(n.id) IS NULL THEN 1 ELSE 2 END */
 FROM  run r
 LEFT  OUTER JOIN
       run n
   ON  VARCHAR(n.id) = r.dependency
   OR  r.dependency  = 'T_ALL'
   AND n.type        = 'T_ALL'
 GROUP BY
       r.id
     , r.dependency
     , r.type
UNION ALL
SELECT r.id
     , r.dependency
     , r.type
     , n.dependency
     , VARCHAR(n.id) || ',' || r.path
     , r.depth + 1
 FROM  r_cte r
     , run   n
 WHERE VARCHAR(n.id) = r.n_id
   AND depth < 10
)
SELECT SMALLINT(id)             AS id
     , VARCHAR(dependency , 10) AS dependency
     , VARCHAR(type       ,  6) AS type
     , path
     , depth
 FROM  r_cte r
 WHERE NOT EXISTS
       (SELECT 0
         FROM  r_cte n
         WHERE n.id    = r.id
           AND n.depth > r.depth
       )
 ORDER BY
       depth
     , type
     , id * (depth * 2 - 3)
;
------------------------------------------------------------------------------

ID     DEPENDENCY TYPE   PATH                                     DEPTH      
------ ---------- ------ ---------------------------------------- -----------
    78                   78                                                 1
    68                   68                                                 1
    55                   55                                                 1
    18                   18                                                 1
    23 78                78,23                                              2
    33 55                55,33                                              2
    97 18                18,97                                              2
    38 23                78,23,38                                           3
     1 33         T_ALL  55,33,1                                            3
     4 33         T_ALL  55,33,4                                            3
    22 T_ALL      T_ALL  55,33,1,4,22                                       4

  11 record(s) selected.

Questions:
1) Are there more than two rows of which dependency were 'T_ALL'?
For example:

Code:

ID          DEPENDENCY TYPE 
----------- ---------- -----
         22 T_ALL           
        125 T_ALL

If so, what output do you expect?

2) Are there any row of which dependency was different from other rows with type = 'T_ALL'?
For example:

Code:

ID          DEPENDENCY TYPE 
----------- ---------- -----
          1 33         T_ALL
          4 33         T_ALL
          5 35         T_ALL

If so, what output do you expect?

3) What order of rows do you want in your result?
In your sample result,
rows id = 23 and 33 were reverse order of your sample table.

odin568 · #7 (**permalink**) 08-23-11, 02:16

Hi!

Thanks very much in advance for your effort!
Sorry, my description yesterday was not too good, was in a hurry... try to make it better now :-)

Q1: Yes, this can happen. In your example the IDs 22 and 125 can only start if all IDs which have the Type T_ALL had run.
But in this case, 22 and 125 are independent from each other. So they can start after all T_ALL have finished. So the order is not relevant.

Q2: Yes also this could happen. Then I would expect, that 1 and 4 can only run if 33 had run and 5 can only start if 35 had run. After that in this example all IDs with Dependency T_ALL can start

Q3: Well, the order is not too important. If two IDs are on the same "level", i.e. have the same dependency, then It doesn't matter which comes first. Perhaps it would be nice to to sort it from the id.

Things I also forgot to say:

I'm running DB2/AIX64 9.1.6 so unfortunately the functions VARCHAR doesn't work (use char() instead solves) and LISTAGG() doesn't exist

At least it says:
No authorized routine named "LISTAGG" of type "FUNCTION
No authorized routine named "VARCHAR" of type "FUNCTION

I appreceate your help, so thanks in advance for your effort!!

przytula_guy · #8 (**permalink**) 08-23-11, 03:13

LISTAGG is only in 9.7
varchar was available : some other reason for this (incorrect syntax.. incorrect value....

tonkuma · #9 (**permalink**) 08-23-11, 03:52

Quote:

I'm running DB2/AIX64 9.1.6

So, VARCHAR can be replaced by RTRIM(CHAR(...)) like in your query.
LISTAGG can be rewritten by using XMLSERIALIZE and XMLAGG.

Here is an example.

Code:

------------------------------ Commands Entered ------------------------------
WITH
 run(id , dependency , type) AS (
SELECT BIGINT(id)
     , CHAR(dependency , 100)
     , VARCHAR(type    , 100)
 FROM
       (VALUES
          ( 1 , '33'    , 'T_ALL')
        , ( 4 , '33'    , 'T_ALL')
        , (33 , '55'    , ''     )
        , (78 , ''      , ''     )
        , (23 , '78'    , ''     )
        , (38 , '23'    , ''     )
        , (22 , 'T_ALL' , ''     )
        , (68 , ''      , ''     )
        , (55 , ''      , ''     )
        , (18 , ''      , ''     )
        , (97 , '18'    , ''     )
       ) r(id , dependency , type)
)
, r_cte(id , dependency , type , n_id , path , depth) AS (
SELECT r.id
     , r.dependency
     , COALESCE(MAX(n.type) , '') || r.type
     , MAX(n.dependency)
     , REPLACE( REPLACE(
          COALESCE(
             XMLSERIALIZE(
                XMLAGG( XMLELEMENT(NAME e , n.id OPTION NULL ON NULL) )
                AS VARCHAR(30) )
           , '' )
        , '<E>' , '' ) , '</E>' , ',' )
       || RTRIM( CHAR(r.id) )
     , 1 + SIGN( COUNT(n.id) )
    /* CASE WHEN MAX(n.id) IS NULL THEN 1 ELSE 2 END */
 FROM  run r
 LEFT  OUTER JOIN
       run n
   ON  CHAR(n.id) = r.dependency
   OR  r.dependency  = 'T_ALL'
   AND n.type        = 'T_ALL'
 GROUP BY
       r.id
     , r.dependency
     , r.type
UNION ALL
SELECT r.id
     , r.dependency
     , r.type
     , n.dependency
     , RTRIM( CHAR(n.id) ) || ',' || r.path
     , r.depth + 1
 FROM  r_cte r
     , run   n
 WHERE CHAR(n.id) = r.n_id
   AND depth < 10
)
SELECT SMALLINT(id)             AS id
     , VARCHAR(dependency , 10) AS dependency
     , VARCHAR(type       ,  6) AS type
     , path
     , depth
 FROM  r_cte r
 WHERE NOT EXISTS
       (SELECT 0
         FROM  r_cte n
         WHERE n.id    = r.id
           AND n.depth > r.depth
       )
 ORDER BY
       depth
     , type
;
------------------------------------------------------------------------------

ID     DEPENDENCY TYPE   PATH                                               DEPTH      
------ ---------- ------ -------------------------------------------------- -----------
    18                   18                                                           1
    55                   55                                                           1
    68                   68                                                           1
    78                   78                                                           1
    23 78                78,23                                                        2
    33 55                55,33                                                        2
    97 18                18,97                                                        2
    38 23                78,23,38                                                     3
     1 33         T_ALL  55,33,1                                                      3
     4 33         T_ALL  55,33,4                                                      3
    22 T_ALL      T_ALL  55,33,1,4,22                                                 4

  11 record(s) selected.

tonkuma · #10 (**permalink**) 08-23-11, 04:01

Quote:

Q1: Yes, this can happen. In your example the IDs 22 and 125 can only start if all IDs which have the Type T_ALL had run.
But in this case, 22 and 125 are independent from each other. So they can start after all T_ALL have finished. So the order is not relevant.

Q2: Yes also this could happen. Then I would expect, that 1 and 4 can only run if 33 had run and 5 can only start if 35 had run. After that in this example all IDs with Dependency T_ALL can start

I couldn't understand exactly.

If the data was like the following, what result do you expect?

Code:

SELECT * FROM run;
------------------------------------------------------------------------------

ID          DEPENDENCY TYPE 
----------- ---------- -----
          1 33         T_ALL
          4 33         T_ALL
          5 35         T_ALL
         33 55              
         35                 
         78                 
         23 78              
         38 23              
         22 T_ALL           
         68                 
         55                 
         18                 
         97 18              
        125 T_ALL           

  14 record(s) selected.

I thought the result might be like the following.

But, I have some questions.
Q1) Where to put 35 for id=22 and 125?
35,55,33,1,4,5,22
55,35,33,1,4,5,22
55,33,35,1,4,5,22
55,33,1,4,35,5,22

Q2) Is it neccesary to show type = T_ALL for id=22 and 125?

Code:

ID          DEPENDENCY TYPE  PATH                 DEPTH
----------- ---------- ----- -------------------- -----
         35                  35                   1
         78                  78                   1
         68                  68                   1
         55                  55                   1
         18                  18                   1
         33 55               55,33                2
         23 78               78,23                2
         97 18               18,97                2
          5 35         T_ALL 35,5                 2
         38 23               78,23,38             3
          1 33         T_ALL 55,33,1              3
          4 33         T_ALL 55,33,4              3
         22 T_ALL      T_ALL 55,33,1,4,5,22       4   -- where to put 35?
        125 T_ALL      T_ALL 55,33,1,4,5,125      4   -- where to put 35?

tonkuma · #11 (**permalink**) 08-25-11, 20:23

I thought another design of the table.

If you put the list of preceding ids into dependency column,
you would be able to keep more complex command job sequencies into the run table.

And, it would be better to make the datatype of the dependency column as VARCHAR,
because of ease of queries and save of disk space.

For example:
This data in the run table represents the following job networks.

Code:

ID     DEPENDENCY          
------ --------------------
     1 33                  
     4 33                  
     5 34                  
    33 55                  
    34                     
    78                     
    23 78                  
    38 23                  
    22 1,4,5               
    68                     
    55                     
    41                     
    43 41                  
    45 43,47               
    47 41                  
    49 47                  
   101                     
   141 47,101              
   125 1,4,5               
   201 125                 
   252 125,45              
   343 45,125,141          
   490 49,343

Code:

     55                    68                    78        
     |                                           |
     |                                           |
     V                                           V
     33          34                              23
     ||          |                               |
     |+----+     |                               |
     |     |     |                               V
     V     V     V                               38
     01    04    05              41
     ||    ||    ||              ||
     ||    ||    |+----+         |+----+
     ||    |+----|----+|         |     |
     |+----|-----|---+||         V     V
     |     |     |   |||         43    47
     |+----+     |   |||         |     |||
     ||+---------+   |||         |+----+|+---------+
     |||             |||         ||     |          |
     VVV             VVV         VV     |     101  |
     22              125         45     |     |    |
                     |||         ||     |+----+    |
               +-----+|+-----+   ||     ||         |
               |      |      |   ||     VV         V
               |      |+-----|---+|     141        49
               |      ||     |+---+     |          |
               |      ||     ||+--------+          |
               |      ||     |||                   |
               V      VV     VVV                   |
               201    252    343                   |
                             |                     |
                             |+--------------------+
                             ||
                             VV
                             490

tonkuma · #12 (**permalink**) 08-25-11, 20:52

Here is a trial query to get the path and depth.
I'm sorry!
There was a way which was easier to understand and shorter.
I replaced all of the query example and it's result.

Note 1: I used shorter datatypes of id and dependency, because of ease of checking the intermediate and final results in testing of queries.

Note 2: The data was same as the previous post.

Note 3: I tested the query on DB2 9.7.4 on Windows.
But, you might be able to run the query on DB2 9.1 by the application of same modifications I showed in previous examples like...

Quote:

I'm running DB2/AIX64 9.1.6

So, VARCHAR can be replaced by RTRIM(CHAR(...)) like in your query.
LISTAGG can be rewritten by using XMLSERIALISE and XMLAGG.

Here is an example.
...

Sample query:

Code:

------------------------------ Commands Entered ------------------------------
WITH
 run(id , dependency) AS (
SELECT SMALLINT(id)
     , VARCHAR(dependency , 20)
 FROM
       (VALUES
...
       ) r(id , dependency)
)
, r_cte(id , dependency , path_id , depth) AS (
SELECT id
     , ' ,' || dependency
     , VARCHAR(id)
     , 1
 FROM  run
UNION ALL
SELECT r.id
     , ',' || n.dependency
     , VARCHAR(n.id)
     , depth + 1
 FROM  r_cte r
     , run   n
 WHERE LOCATE( ',' || VARCHAR(n.id) || ','
             , r.dependency || ','
             ) > 0
)
SELECT id
     , SUBSTR( MIN(dependency) , 3 ) AS dependency
     , VARCHAR(
          LISTAGG( path_id , ',' )
             WITHIN GROUP( ORDER BY depth DESC , INT(path_id) )
        , 60
       ) AS path
     , MAX(depth) AS depth
 FROM  (SELECT r.*
             , ROW_NUMBER()
                  OVER( PARTITION BY id , path_id
                            ORDER BY depth DESC ) AS rn
         FROM  r_cte r
       ) r
 WHERE rn = 1
 GROUP BY
       id
 ORDER BY
       depth
;

Note 4:
A predicate using a LOCATE function in the WHERE clause can be replaced by a LIKE predicate on DB2 9.7.4 for LUW, like...

Code:

...
/*
 WHERE LOCATE( ',' || VARCHAR(n.id) || ','
             , r.dependency || ','
             ) > 0
*/
 WHERE r.dependency || ',' LIKE '%,' || VARCHAR(n.id) || ',%'
   AND r.depth < 10
)

Result of the sample query:

Code:

------------------------------------------------------------------------------

ID     DEPENDENCY             PATH                                                         DEPTH      
------ ---------------------- ------------------------------------------------------------ -----------
    34                        34                                                                     1
    41                        41                                                                     1
    55                        55                                                                     1
    68                        68                                                                     1
    78                        78                                                                     1
   101                        101                                                                    1
     5 34                     34,5                                                                   2
    23 78                     78,23                                                                  2
    33 55                     55,33                                                                  2
    43 41                     41,43                                                                  2
    47 41                     41,47                                                                  2
     1 33                     55,33,1                                                                3
     4 33                     55,33,4                                                                3
    38 23                     78,23,38                                                               3
    45 43,47                  41,43,47,45                                                            3
    49 47                     41,47,49                                                               3
   141 47,101                 41,47,101,141                                                          3
    22 1,4,5                  55,33,34,1,4,5,22                                                      4
   125 1,4,5                  55,33,34,1,4,5,125                                                     4
   201 125                    55,33,34,1,4,5,125,201                                                 5
   252 125,45                 55,33,34,41,1,4,5,43,47,45,125,252                                     5
   343 45,125,141             55,33,34,41,1,4,5,43,47,101,45,125,141,343                             5
   490 49,343                 55,33,34,41,1,4,5,43,47,101,45,125,141,49,343,490                      6

  23 record(s) selected.

tonkuma · #13 (**permalink**) 08-28-11, 00:53

Another example slightly diffrent in calculations of dependency.

Code:

------------------------------ Commands Entered ------------------------------
WITH
 run(id , dependency) AS (
SELECT SMALLINT(id)
     , VARCHAR(dependency , 20)
 FROM
       (VALUES
...
       ) r(id , dependency)
)
, r_cte(id , dependency , path_id , depth) AS (
SELECT id
     , dependency
     , VARCHAR(id)
     , 1
 FROM  run
UNION ALL
SELECT r.id
     , n.dependency
     , VARCHAR(n.id)
     , depth + 1
 FROM  r_cte r
     , run   n
 WHERE LOCATE( ',' || VARCHAR(n.id) || ','
             , ',' || r.dependency  || ','
             ) > 0
   AND r.depth < 10
)
SELECT id
     , MAX( CASE depth WHEN 1 THEN dependency END ) AS dependency
     , VARCHAR(
          LISTAGG( path_id , ',' )
             WITHIN GROUP( ORDER BY depth DESC , INT(path_id) )
        , 60
       ) AS path
     , MAX(depth) AS depth
 FROM  (SELECT r.*
             , ROW_NUMBER()
                  OVER( PARTITION BY id , path_id
                            ORDER BY depth DESC ) AS rn
         FROM  r_cte r
       ) r
 WHERE rn = 1
 GROUP BY
       id
 ORDER BY
       depth
;

Another expression for the dependency column in final select on DB2 9.7 for LUW.

Code:

--   , MAX( CASE depth WHEN 1 THEN dependency END ) AS dependency
     , SUBSTR( MIN( depth || dependency ) , 2 ) AS dependency

Result: Length of dependecy column was 20(different from previous result 22.)

Code:

------------------------------------------------------------------------------

ID     DEPENDENCY           PATH                                                         DEPTH      
------ -------------------- ------------------------------------------------------------ -----------
    34                      34                                                                     1
    41                      41                                                                     1
    55                      55                                                                     1
    68                      68                                                                     1
    78                      78                                                                     1
   101                      101                                                                    1
     5 34                   34,5                                                                   2
    23 78                   78,23                                                                  2
    33 55                   55,33                                                                  2
    43 41                   41,43                                                                  2
    47 41                   41,47                                                                  2
     1 33                   55,33,1                                                                3
     4 33                   55,33,4                                                                3
    38 23                   78,23,38                                                               3
    45 43,47                41,43,47,45                                                            3
    49 47                   41,47,49                                                               3
   141 47,101               41,47,101,141                                                          3
    22 1,4,5                55,33,34,1,4,5,22                                                      4
   125 1,4,5                55,33,34,1,4,5,125                                                     4
   201 125                  55,33,34,1,4,5,125,201                                                 5
   252 125,45               55,33,34,41,1,4,5,43,47,45,125,252                                     5
   343 45,125,141           55,33,34,41,1,4,5,43,47,101,45,125,141,343                             5
   490 49,343               55,33,34,41,1,4,5,43,47,101,45,125,141,49,343,490                      6

  23 record(s) selected.

odin568 · #14 (**permalink**) 08-29-11, 02:11

Hi!

I am very sorry, I didn't reply the last days, I was on travel and had no Internet, sorry. Wow, your support in my problem is really super, thanks a lot!

So, I try to answer chronological to your posts:

Quote:

Originally Posted by tonkuma

So, VARCHAR can be replaced by RTRIM(CHAR(...)) like in your query.
LISTAGG can be rewritten by using XMLSERIALIZE and XMLAGG.

Here is an example.

Code:

------------------------------ Commands Entered ------------------------------
WITH
 run(id , dependency , type) AS (
SELECT BIGINT(id)
     , CHAR(dependency , 100)
     , VARCHAR(type    , 100)
 FROM
       (VALUES
          ( 1 , '33'    , 'T_ALL')
        , ( 4 , '33'    , 'T_ALL')
        , (33 , '55'    , ''     )
        , (78 , ''      , ''     )
        , (23 , '78'    , ''     )
        , (38 , '23'    , ''     )
        , (22 , 'T_ALL' , ''     )
        , (68 , ''      , ''     )
        , (55 , ''      , ''     )
        , (18 , ''      , ''     )
        , (97 , '18'    , ''     )
       ) r(id , dependency , type)
)
, r_cte(id , dependency , type , n_id , path , depth) AS (
SELECT r.id
     , r.dependency
     , COALESCE(MAX(n.type) , '') || r.type
     , MAX(n.dependency)
     , REPLACE( REPLACE(
          COALESCE(
             XMLSERIALIZE(
                XMLAGG( XMLELEMENT(NAME e , n.id OPTION NULL ON NULL) )
                AS VARCHAR(30) )
           , '' )
        , '<E>' , '' ) , '</E>' , ',' )
       || RTRIM( CHAR(r.id) )
     , 1 + SIGN( COUNT(n.id) )
    /* CASE WHEN MAX(n.id) IS NULL THEN 1 ELSE 2 END */
 FROM  run r
 LEFT  OUTER JOIN
       run n
   ON  CHAR(n.id) = r.dependency
   OR  r.dependency  = 'T_ALL'
   AND n.type        = 'T_ALL'
 GROUP BY
       r.id
     , r.dependency
     , r.type
UNION ALL
SELECT r.id
     , r.dependency
     , r.type
     , n.dependency
     , RTRIM( CHAR(n.id) ) || ',' || r.path
     , r.depth + 1
 FROM  r_cte r
     , run   n
 WHERE CHAR(n.id) = r.n_id
   AND depth < 10
)
SELECT SMALLINT(id)             AS id
     , VARCHAR(dependency , 10) AS dependency
     , VARCHAR(type       ,  6) AS type
     , path
     , depth
 FROM  r_cte r
 WHERE NOT EXISTS
       (SELECT 0
         FROM  r_cte n
         WHERE n.id    = r.id
           AND n.depth > r.depth
       )
 ORDER BY
       depth
     , type
;
------------------------------------------------------------------------------

ID     DEPENDENCY TYPE   PATH                                               DEPTH      
------ ---------- ------ -------------------------------------------------- -----------
    18                   18                                                           1
    55                   55                                                           1
    68                   68                                                           1
    78                   78                                                           1
    23 78                78,23                                                        2
    33 55                55,33                                                        2
    97 18                18,97                                                        2
    38 23                78,23,38                                                     3
     1 33         T_ALL  55,33,1                                                      3
     4 33         T_ALL  55,33,4                                                      3
    22 T_ALL      T_ALL  55,33,1,4,22                                                 4

  11 record(s) selected.

This example works on my machines with DB2 9.1 on AIX64. Didn't knew the commands XMLSERIALIZE, thanks for that hint!

Quote:

Originally Posted by tonkuma

I couldn't understand exactly.

If the data was like the following, what result do you expect?

Code:

SELECT * FROM run;
------------------------------------------------------------------------------

ID          DEPENDENCY TYPE 
----------- ---------- -----
          1 33         T_ALL
          4 33         T_ALL
          5 35         T_ALL
         33 55              
         35                 
         78                 
         23 78              
         38 23              
         22 T_ALL           
         68                 
         55                 
         18                 
         97 18              
        125 T_ALL           

  14 record(s) selected.

I thought the result might be like the following.

But, I have some questions.
Q1) Where to put 35 for id=22 and 125?
35,55,33,1,4,5,22
55,35,33,1,4,5,22
55,33,35,1,4,5,22
55,33,1,4,35,5,22

Q2) Is it neccesary to show type = T_ALL for id=22 and 125?

Code:

ID          DEPENDENCY TYPE  PATH                 DEPTH
----------- ---------- ----- -------------------- -----
         35                  35                   1
         78                  78                   1
         68                  68                   1
         55                  55                   1
         18                  18                   1
         33 55               55,33                2
         23 78               78,23                2
         97 18               18,97                2
          5 35         T_ALL 35,5                 2
         38 23               78,23,38             3
          1 33         T_ALL 55,33,1              3
          4 33         T_ALL 55,33,4              3
         22 T_ALL      T_ALL 55,33,1,4,5,22       4   -- where to put 35?
        125 T_ALL      T_ALL 55,33,1,4,5,125      4   -- where to put 35?

Q1-A1: Because 35 has no dependency at all it should be before any other id, which has any dependency. So I would expect it at the beginning or after the 55 but before 33. Best would be, that 35 is the first in line, because id is smaller than 55, but that's only for the eyes...

Q2-A2: No, it's not necessary, because they have the dependency T_ALL, they are not from the Type T_ALL.

Quote:

Originally Posted by tonkuma

I thought another design of the table.

If you put the list of preceding ids into dependency column,
you would be able to keep more complex command job sequencies into the run table.

And, it would be better to make the datatype of the dependency column as VARCHAR,
because of ease of queries and save of disk space.

For example:
This data in the run table represents the following job networks.

Code:

ID     DEPENDENCY          
------ --------------------
     1 33                  
     4 33                  
     5 34                  
    33 55                  
    34                     
    78                     
    23 78                  
    38 23                  
    22 1,4,5               
    68                     
    55                     
    41                     
    43 41                  
    45 43,47               
    47 41                  
    49 47                  
   101                     
   141 47,101              
   125 1,4,5               
   201 125                 
   252 125,45              
   343 45,125,141          
   490 49,343

Code:

     55                    68                    78        
     |                                           |
     |                                           |
     V                                           V
     33          34                              23
     ||          |                               |
     |+----+     |                               |
     |     |     |                               V
     V     V     V                               38
     01    04    05              41
     ||    ||    ||              ||
     ||    ||    |+----+         |+----+
     ||    |+----|----+|         |     |
     |+----|-----|---+||         V     V
     |     |     |   |||         43    47
     |+----+     |   |||         |     |||
     ||+---------+   |||         |+----+|+---------+
     |||             |||         ||     |          |
     VVV             VVV         VV     |     101  |
     22              125         45     |     |    |
                     |||         ||     |+----+    |
               +-----+|+-----+   ||     ||         |
               |      |      |   ||     VV         V
               |      |+-----|---+|     141        49
               |      ||     |+---+     |          |
               |      ||     ||+--------+          |
               |      ||     |||                   |
               V      VV     VVV                   |
               201    252    343                   |
                             |                     |
                             |+--------------------+
                             ||
                             VV
                             490

Yes, that was/is exactly my thought I also had and wanted to write, that one ID can have several Dependencys, which schould bei commaseperated like in your example!
Yes, I see, varchar should be better, at first, I want to leave it as char. When it works as I want, I will refactor it, that's a good hint, thanks.

I am trying to understand your example in the next posts you made. There you left out checking for T_ALL, or is my mind playing games with me this monday morning? :-) This is something I would need, in combination with the commaseperated Dependency list.

I hope I cleared some Questions you had and didn't forget anything. I will read over it again today, is quite much at once monday morning ;-)

I couldn't say it often enough: THANK YOU DB2-GODS!

odin568 · #15 (**permalink**) 08-29-11, 02:24

ah sorry, you shortened it for ease

just too early this morning....

Internet Services

Web Development

Digital Marketing

Consumer Tech