select query with performance tune

Billa007 · #1 (**permalink**) 01-24-12, 08:35

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

tonkuma · #2 (**permalink**) 01-24-12, 09:17

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.

tonkuma · #3 (**permalink**) 01-24-12, 14:17

(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

tonkuma · #4 (**permalink**) 01-28-12, 07:26

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
;

tonkuma · #5 (**permalink**) 01-29-12, 09:35

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
;

Billa007 · #6 (**permalink**) 01-30-12, 02:52

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 ?

tonkuma · #7 (**permalink**) 01-30-12, 03:14

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.

Quote:

-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.

tonkuma · #8 (**permalink**) 01-30-12, 03:15

Quote:

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

Please execute the query with removing MAX functions, then see the result.

Billa007 · #9 (**permalink**) 01-30-12, 03:33

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

tonkuma · #10 (**permalink**) 01-30-12, 03:49

Quote:

SQL0969N There is no message text ...

How(what client tool? on which platform? so on...) did you executed the query?

Quote:

... Example 2 working fine but taking more time to execute for 4000 rows..

What query did you compared with?

Quote:

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.

Billa007 · #11 (**permalink**) 01-30-12, 06:39

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..

tonkuma · #12 (**permalink**) 01-30-12, 08:16

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech