-1

I am getting a following warning when running a query

      Performance of this complex query might be sub-optimal Reason Code 2
      DB2 SQL error: SQLCODE: 437, SQLSTATE: 01602, SQLERRMC: 2

The query is as follows :

Query 1

       SELECT 
       DISTINCT
       a.Part_NO,
       a.PART_DESC,
       a.C_Purchase,
       a.Supplier,
       d.Supplier_Order_date as Date1,
       l.Supplier_Order_date as Date2,
       e.Supplier_Order_date as Date3,
       f.Supplier_Order_date as Date4,
       h.Supplier_Order_date as Date5,
       i.Supplier_Order_date as Date6,
       j.Supplier_Order_date as Date7,
       j.D_SUPLR_CPLN_PROM as Date8,
       k.Supplier_Order_date as Date9,
       k.D_SUPLR_CPLN_PROM as Date10,

       FROM
           table100 a 
       JOIN
          table101 b 
          ON a.Part_NO = b.Part_NO 
          AND a.C_Purchase = b.C_ENTRY 
      JOIN
          table102 d        
          ON a.Part_NO = d.Part_NO 
          AND a.Supplier = d.Supplier 
          AND a.C_Purchase = d.C_Purchase
          AND d.C_QLTY = 'ABC' 
      JOIN
          table102 l      
          ON a.Part_NO = l.Part_NO 
          AND a.Supplier = l.Supplier 
          AND a.C_Purchase = l.C_Purchase 
          AND l.C_QLTY = 'AB S' 
       JOIN 
           table102 e      
           ON a.Part_NO = e.Part_NO 
           AND a.Supplier = e.Supplier 
           AND a.C_Purchase = e.C_Purchase
           AND e.C_QLTY = 'CDE' 
       JOIN
           table102 f 
           ON a.Part_NO = f.Part_NO 
           AND a.Supplier = f.Supplier 
           AND a.C_Purchase = f.C_Purchase
           AND f.C_QLTY = 'AEC' 
       JOIN
            table102 h 
            ON a.Part_NO = h.Part_NO 
            AND a.Supplier = h.Supplier 
            AND a.C_Purchase = h.C_Purchase
            AND h.C_QLTY = 'EFG' 
       JOIN
          table102 i 
          ON a.Part_NO = i.Part_NO 
          AND a.Supplier = i.Supplier 
          AND a.C_Purchase = i.C_Purchase
          AND i.C_QLTY = 'AGAG' 
       JOIN
          table102 j 
          ON a.Part_NO = j.Part_NO 
          AND a.Supplier = j.Supplier 
          AND a.C_Purchase = j.C_Purchase
          AND j.C_QLTY = 'CAP' 
       JOIN
           table102 k 
           ON a.Part_NO = k.Part_NO 
           AND a.Supplier = k.Supplier 
           AND a.C_Purchase = k.C_Purchase
           AND k.C_QLTY = 'OTP' 

In the above query, table102 is used multiple times with alias as b,d,l,e,f,h,i,j,k. This query seems to cause a performance issue .

These are the log details, error message from Production Server.When the same query runs in a Java Code.

Message: The statement is too long or too complex..
SQLCODE=-101, SQLSTATE=54001, DRIVER=3.57.110
Vendor: -101
SQLState: 56098

But the Fetch time is 50 seconds. On doing a web search, reason for the above error comes out to be as follows .

     The join enumeration method was altered due to query complexity
0
3

Assuming that (Part_NO,Supplier,C_Purchase,C_QLTY) is unique a common technique is to pre-aggregate the data like this:

WITH cte AS
 ( SELECT Part_NO, Supplier, C_Purchase,
        MAX(CASE WHEN C_QLTY = 'ABC' THEN Supplier_Order_date END) AS Date1,
        MAX(CASE WHEN C_QLTY = 'AB S' THEN Supplier_Order_date END) AS Date2,
        ...
     FROM table102 d
     -- not needed to get the result, but might reduce resource usage
     WHERE C_QLTY IN ('ABC', 'AB S', ...)
     GROUP BY Part_NO, Supplier, C_Purchase
   ) 
SELECT 
   DISTINCT
   a.Part_NO,
   a.PART_DESC,
   a.C_Purchase,
   a.Supplier,
   cte.Date1,
   cte.Date2,
   ...

   FROM
       table100 a 
   JOIN
      table101 b 
      ON a.Part_NO = b.Part_NO 
      AND a.C_Purchase = b.C_ENTRY 
  JOIN cte
      ON a.Part_NO = cte.Part_NO 
      AND a.Supplier = cte.Supplier 
      AND a.C_Purchase = cte.C_Purchase

Caution, if any of the C_QLTY values doesn't exists it's the same result as Left Joining all those table102`s. Then you might need to add a

-- outer Select 
WHERE 'ABC' IS NOT NULL 
  AND 'AB S' IS NOT NULL
  ...

Or you add (thanks to @AndriyM):

-- cte:
HAVING COUNT(*) = 8 -- number of rows/conditions

If (Part_NO,Supplier,C_Purchase,C_QLTY) is not unique (but then it's a different result)

HAVING COUNT(DISTINCT C_QLTY) = 8
0
1

I have solved the above , the solution comes out to be setting the optimization level.

    Set CURRENT QUERY OPTIMIZATION=1 

This solved the issue.And the query is not giving the error. There was no issues with multiple joins. Link

Most SQL statements are adequately optimized with the default query optimization class(5 is default mostly). Source-code precompilation time and resource consumption, at any optimization class level, is primarily determined by the complexity of the query being precompiled, especially if the query contains joins and subqueries

Use the following "rules of thumb" to select the best query optimization class:

    1. Start by using the default query optimization class (5).

    2. If you feel you need an optimization class other than the default,  
       try class 3 or 7.

    3. Use a lower optimization class (0 or 1) for queries that have
       a short runtime (less than 1 second). 

    4.Use a higher optimization class (7 or 9) for queries that have a 
      long runtime (greater than 10 seconds).

    5.Use optimization class 1 if your queries access many tables with 
      join predicates on the same column. *****

To get the Optimization class level currently used , execute the below command

  VALUES CURRENT QUERY OPTIMIZATION 

The default value assigned to a new connection to a database is determined by the database configuration parameter (DFT_QUERYOPT).

Note: The optimization class is specific to the connection session. If the optimization class is changed from default value , it will be specific to the session it is changed. A new connection/session will have the default optimization class

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.