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