Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: select query with performance tune

    Hi

    DB2 ver 9.1 z/os

    Please find the below query
    Code:
    SELECT t1.p_loc
         , t1.p1_num
         , t1.p2_num 
         , t1.p_det
         , t2.tran_no
         , t2.tran_code
         , t3_1.tran_str
         , t3.tran_des   AS tran_des1
         , t3_2.tran_des AS tran_des2
     FROM
           table1 t1
    inner join
    
          table3 t3_1
    ON  
    t3_1  .tran_item =  t1.p_loc
    left outer JOIN
           table2 t2
    
    ON                                                
          t2  .tran_no   =  t1.p_loc
     AND  T2.P0_NUM =                
          T1.P1_NUM || '-'   
       || T1.P2_NUM   
          
    
    inner join
      table3 t3
    
    
    ON  (t3.tran_item =  t2.tran_no
         or
         t3.tran_item =  t1.p_loc)
     
     LEFT  OUTER JOIN
           table3 t3_2
       ON  t3_2.tran_item =  t2.tran_code
     ORDER BY
           t1.p_loc
          ,t1.p1_num
          ,t1.p2_num
         
    ;

    Table1
    Code:
    p_loc   p1_Num  p2_num      p_det
    A11     12       34          aaa
    A11     3        334         aaa
    B11     131      1           WWW
    B11     44       41          zzz
    Table2
    Code:
    tran_no  p_nor   tran_code
    A11      12-34    A11
    A11      3-334    
    B11      131-1    S11
    Table3
    Code:
    tran_ITEM     tran_str    tran_des
    A11             xxx        sout
    B11             yyy        west
    S11             zzz        EAST
    Result set
    Code:
    p_LOC         p1_num  p2_num        p-det   tran_no   tran-code tran_str  tran_des1   tran-des2 
    A11           12       34          aaa       A11      A11         xxx       SOUT       SOUT
    A11           3       334          aaa       A11                  xxx       SOUT       
    B11           131       1          WWW       B11      S11         yyy       west       EAST
    B11           44       41          zzz       B11      S11         yyy       west       EAST
    The above query will return more than 4000 rows in my real system.My problem is ' the query takes too much of time while running and returns the "connection closed error" '
    If i remove the below part of query in the original query means it gives the good result with in minimal time.
    Code:
    ...
    left outer JOIN
           table2 t2
    
    ON                                                
          t2  .tran_no   =  t1.p_loc
     AND  T2.P0_NUM =                
          T1.P1_NUM || '-'   
       || T1.P2_NUM   
          
    
    inner join
      table3 t3
    
    
    ON  (t3.tran_item =  t2.tran_no
         or
         t3.tran_item =  t1.p_loc)
     
     LEFT  OUTER JOIN
           table3 t3_2
       ON  t3_2.tran_item =  t2.tran_code
    Please help me on that how to fine tune the query and the way of correct query

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that there were some contradictions between your query, sample data and result set.

    For example
    (1) " AND T2.P0_NUM ="
    There is no P0_NUM column in Table2.
    (2) If assumed it was p_nor,
    then no corresponding row of Table2 for 4th row of Table1.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (3) t3 can be replaced by t3_1. (t3 is not contradiction, but it is redundant and useless.)

    Code:
    ON  (t3.tran_item =  /*t2.tran_no*/t1.p_loc
         or
         t3.tran_item =  t1.p_loc)
    t2.tran_no could be replaced by t1.p_loc, if t2.tran_no is not null.
    Because
    Code:
    ON                                                
          t2  .tran_no   =  t1.p_loc
     AND  T2.P0_NUM ...
    Then
    Code:
    ON  (t3.tran_item =  /*t2.tran_no*/t1.p_loc
    /*
         or
         t3.tran_item =  t1.p_loc
    */
        )
    This is same condition as for t3_1
    Code:
    ON  
    t3_1  .tran_item =  t1.p_loc

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Billa007,

    Could you solved your issue?

    Although you wrote "with performance tune", your query might not produce your "Result set".
    Actualy, I tested your query with a modification " AND T2./*P0_NUM*/p_nor =" on my DB2 for Windows,
    and the result was what I wrote in (2) in my previous post.

    So, I thought that your query was not complete and you also want to modify the query to produce the exact result which you wanted.

    I guessed further more ...
    If no corresponding row of table2 with the ON condition,
    join with any row(s) which satisfy "t2.tran_no = t1.p_loc".
    Code:
    ON
          t2  .tran_no   =  t1.p_loc
     AND  T2./*P0_NUM*/p_nor =
          T1.P1_NUM || '-'
       || T1.P2_NUM

    According to my guess, I made some examples.
    Note: I tested examples on DB2 9.7.5 on Windows/XP, because I hace no z/OS environment.

    Example 1:
    Code:
    SELECT t1.p_loc
         , t1.p1_num
         , t1.p2_num 
         , t1.p_det
         , COALESCE(t2_1.tran_no   , t2_2.tran_no  ) AS tran_no
         , COALESCE(t2_1.tran_code , t2_2.tran_code) AS tran_code
         , t3_1.tran_str
         , t3_1.tran_des AS tran_des1
         , t3_2.tran_des AS tran_des2
     FROM
           table1 t1
     INNER JOIN
           table3 t3_1
       ON  t3_1.tran_item = t1.p_loc
     LEFT  OUTER JOIN
           table2 t2_1
       ON  t2_1.tran_no = t1.p_loc
       AND t2_1.p_nor   = t1.p1_num || '-' || t1.p2_num
     LEFT  OUTER JOIN
           table2 t2_2
       ON  t2_1.tran_no IS NULL
       AND t2_2.tran_no = t1.p_loc
     LEFT  OUTER JOIN
           table3 t3_2
       ON  t3_2.tran_item = COALESCE(t2_1.tran_code , t2_2.tran_code)
     ORDER BY
           t1.p_loc
          ,t1.p1_num
          ,t1.p2_num
    ;

    Example 2: Because you are using DB2 9.1 for z/OS, ORDER BY clause and FETCH FIRST clause are supported in a nested subquery.
    Code:
    SELECT t1.p_loc
         , t1.p1_num
         , t1.p2_num 
         , t1.p_det
         , t2.tran_no
         , t2.tran_code
         , t3_1.tran_str
         , t3_1.tran_des AS tran_des1
         , t3_2.tran_des AS tran_des2
     FROM
           table1 t1
     INNER JOIN
           table3 t3_1
       ON  t3_1.tran_item = t1.p_loc
     INNER JOIN 
           TABLE (
           SELECT tran_no
                , tran_code
            FROM  table2 t2
            WHERE t2.tran_no = t1.p_loc
            ORDER BY
                  NULLIF(
                     t2.p_nor
                   , t1.p1_num || '-' || t1.p2_num
                  ) DESC
            FETCH FIRST ROW ONLY
           ) t2
       ON  0=0
     LEFT  OUTER JOIN
           table3 t3_2
       ON  t3_2.tran_item = t2.tran_code
     ORDER BY
           t1.p_loc
          ,t1.p1_num
          ,t1.p2_num
    ;

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 3 may show better performance than Example 2.
    Because, it references table3 once, compared twice in Example 2.

    Example 3:
    Note: Used different subquery for t2 from Example 2.
    Code:
    SELECT t1.p_loc
         , t1.p1_num
         , t1.p2_num 
         , t1.p_det
         , t1.p_loc     AS tran_no
         , t2.tran_code
         , t3.tran_str
         , t3.tran_des1
         , t3.tran_des2
     FROM
           table1 t1
     INNER JOIN 
           TABLE (
           SELECT COALESCE(
                     MAX( CASE t2.p_nor
                          WHEN t1.p1_num || '-' || t1.p2_num THEN
                               tran_code
                          END
                        )
                   , MAX( tran_code )
                  )  AS tran_code
            FROM  table2 t2
            WHERE t2.tran_no = t1.p_loc
           ) t2
       ON  0=0
     INNER JOIN
           TABLE (
           SELECT MAX( CASE t3.tran_item
                       WHEN t1.p_loc     THEN
                            tran_str
                       END
                     )  AS  tran_str
                , MAX( CASE t3.tran_item
                       WHEN t1.p_loc     THEN
                            tran_des
                       END
                     )  AS  tran_des1
                , MAX( CASE t3.tran_item
                       WHEN t2.tran_code THEN
                            tran_des
                       END
                     )  AS  tran_des2
            FROM  table3 t3
            WHERE t3.tran_item IN (t1.p_loc , t2.tran_code)
           ) t3
       ON  0=0
     ORDER BY
           t1.p_loc
          ,t1.p1_num
          ,t1.p2_num
    ;

  6. #6
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the reply..I am using the Example3 code and got SQL error like
    SQL error "-133" SQLSTATE=42906

    Can we know why the MAX function included in the code ?

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Really?

    SQL error code -133 is related to HAVING clause by referencing manual "DB2 Version 9.1 for z/OS Codes".
    Though, no HAVING clause in Example 3.

    -133
    AN AGGREGATE FUNCTION IN A
    SUBQUERY OF A HAVING CLAUSE IS
    INVALID BECAUSE ALL COLUMN
    REFERENCES IN ITS ARGUMENT
    ARE NOT CORRELATED TO THE
    GROUP BY RESULT THAT THE
    HAVING CLAUSE IS APPLIED TO

    Explanation:
    If an aggregate function has a correlated column
    reference, it must be correlated from within a HAVING
    clause to the GROUP BY result that the HAVING
    clause is applied to. All column references in the
    argument must satisfy this condition.
    Please pubilish whole query you executed and whole error message you got.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Can we know why the MAX function included in the code ?
    Please execute the query with removing MAX functions, then see the result.

  9. #9
    Join Date
    Sep 2011
    Posts
    220
    Hi;

    I used the same query with no changes what is in in Example 3..

    I was executed the same query with out MAX fuction,but same error got

    Error

    Code:
    SQL0969N  There is no message text corresponding to SQL error "-133" in the 
    message file on this workstation.  The error was returned from module 
    "DSNXOOS1" with original tokens "".  SQLSTATE=42906
    
    SQL10007N Message "133" could not be retrieved.  Reason code: "4".
    But Example 2 working fine but taking more time to execute for 4000 rows..

    p.s : Table1 and Table3 will have have duplicate rows also

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SQL0969N There is no message text ...
    How(what client tool? on which platform? so on...) did you executed the query?

    ... Example 2 working fine but taking more time to execute for 4000 rows..
    What query did you compared with?


    Table1 and Table3 will have have duplicate rows also
    Your sample data was not included such data.

    Please supply CREATE TABLE statements and INSERT statements to populate the tables,
    if you want me to further investigate your issue.

  11. #11
    Join Date
    Sep 2011
    Posts
    220
    Hi;

    Please find the Example 3 query with CTE
    Code:
    WITH
     TABLE1(p_loc , p1_num , p2_num,p_det) AS (
    VALUES
      ( 'A11' , '12','34' , 'aaa' )
    , ( 'A11' , '3','334' , 'aaa' )
    , ( 'B11' , '131','1' , 'WWW' )
    , ( 'B11' , '44','41' , 'zzz' )
    , ( 'B11' , '44','41' , 'zzz' )
    )
    , TABLE2(tran_no ,p_nor , tran_code) AS (
    VALUES
      ( 'A11' ,12-34, 'A11' )
    , ( 'A11' ,3-334, '   ' )
    , ( 'B11' ,131-1, 'S11' )
    )
    , TABLE3(tran_item ,tran_str, tran_des) AS (
    VALUES
      ( 'A11' ,'xxx', 'sout' )
    , ( 'A11' ,'xxx', 'sout' )
    , ( 'B11' ,'yyy', 'west' )
    , ( 'S11' ,'zzz', 'EAST' )
    , ( 'S11' ,'zzz', 'EAST' )
    )
    as
    Code:
    SELECT t1.p_loc
         , t1.p1_num
         , t1.p2_num 
         , t1.p_det
         , t1.p_loc     AS tran_no
         , t2.tran_code
         , t3.tran_str
         , t3.tran_des1
         , t3.tran_des2
     FROM
           table1 t1
     INNER JOIN 
           TABLE (
           SELECT COALESCE(
                     MAX( CASE t2.p_nor
                          WHEN t1.p1_num || '-' || t1.p2_num THEN
                               tran_code
                          END
                        )
                   , MAX( tran_code )
                  )  AS tran_code
            FROM  table2 t2
            WHERE t2.tran_no = t1.p_loc
           ) t2
       ON  0=0
     INNER JOIN
           TABLE (
           SELECT MAX( CASE t3.tran_item
                       WHEN t1.p_loc     THEN
                            tran_str
                       END
                     )  AS  tran_str
                , MAX( CASE t3.tran_item
                       WHEN t1.p_loc     THEN
                            tran_des
                       END
                     )  AS  tran_des1
                , MAX( CASE t3.tran_item
                       WHEN t2.tran_code THEN
                            tran_des
                       END
                     )  AS  tran_des2
            FROM  table3 t3
            WHERE t3.tran_item IN (t1.p_loc , t2.tran_code)
           ) t3
       ON  0=0
     ORDER BY
           t1.p_loc
          ,t1.p1_num
          ,t1.p2_num
    ;
    The above query executed in the real system and gave error like
    Code:
    :
    END OF COMPILATION 1,  PROGRAM TESTREP,  HIGHEST SEVERITY 4.                   
    RETURN CODE 4                                                                   
    READY                                                                           
     DSN SYSTEM(TEST) RETRY(10)                                                     
    DSN                                                                             
         BIND PACKAGE (MAIN) MEMBER(TESTREP) ACTION(REPLACE) VALIDATE(BIND) ISOLATION(CS) RELEASE(COMMIT)
        EXPLAIN(YES) CURRENTDATA(YES) QUALIFIER(ERE@DEV) OWNER(ERE@DEV) DEGREE(1)
    DSNX200I  -DSN7 BIND SQL ERROR                                                  
               USING ERE@DEV AUTHORITY                                              
               PLAN=(NOT APPLICABLE)                                                
               DBRM=TESTREP                                                        
               SQLCODE=-133                                                         
               SQLSTATE=42906                                                       
               TOKENS=                                                              
               CSECT NAME=DSNXOOS1                                                  
               RDS CODE=-100 
    DSNT233I  -TEST UNSUCCESSFUL BIND FOR             
               PACKAGE =                              
    TEST.MAIN.TESTREP.(MFG2012-01-30-06.20.42.231452)
    Executed Example2 query in IBM Command editor tool and taken more time to excute,but gets connection closed error in real system..

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I executed your query by removing "as Code:" on my DB2 9.7.5 on Windows/XP,
    then I got error
    SQL0420N Invalid character found in a character string argument of the
    function "DECFLOAT". SQLSTATE=22018

    Next, I changed to
    Code:
    , TABLE2(tran_no ,p_nor , tran_code) AS (
    VALUES
      ( 'A11' ,'12-34', 'A11' )
    , ( 'A11' ,'3-334', '   ' )
    , ( 'B11' ,'131-1', 'S11' )
    )
    then I got the result
    Code:
    P_LOC P1_NUM P2_NUM P_DET TRAN_NO TRAN_CODE TRAN_STR TRAN_DES1 TRAN_DES2
    ----- ------ ------ ----- ------- --------- -------- --------- ---------
    A11   12     34     aaa   A11     A11       xxx      sout      sout     
    A11   3      334    aaa   A11               xxx      sout      -        
    B11   131    1      WWW   B11     S11       yyy      west      EAST     
    B11   44     41     zzz   B11     S11       yyy      west      EAST     
    B11   44     41     zzz   B11     S11       yyy      west      EAST     
    
      5 record(s) selected.
    By the way, your code with the CTEs wouldn't work on DB2 for z/OS.
    Because, DB2 for z/OS doesn't support VALUES row constructor.


    Anyway, I don't know so much about DB2 for z/OS than DB2 for LUW.
    If my suggested code doesn't work on DB2 for z/OS,
    please forgive my example and look for another way.

Posting Permissions

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