DB2 SQL Statement Optimization

[JHam]

I am trying to pull some data from a JDEdwards system, I am having a
lot of trouble with the sql statement that follows. It takes much
longer than I would expect it to (several hours for 1500 records. Is
there a way to optimize this statement?

Thanks very much,
JH

SELECT
TRIM("IBSTKT") AS
"STOCK_CODE",
TRIM("IBMCU") || '{$U_SEPARATOR_CHAR}' ||
TRIM(CAST("IBITM" AS CHAR(8))) AS "ID"
FROM {$U_JDDATALIB}."F4102", {$U_JDDATALIB}."F4111"
WHERE
IBMCU = ILMCU
AND IBITM = ILITM
AND
(TRIM("ILMCU") = '2784' OR TRIM("ILMCU") = '2744' AND ILAN8 = 2784)
AND
(ILTRQT <> 0 AND ILPAID <> 0)
AND
(F4111."ILCRDJ" >= 103200 AND F4111."ILCRDJ" < 103300)

[Mark A]

"JHam" <je*************@ufa.com> wrote in message
news:ce**************************@posting.google.c om...

I am trying to pull some data from a JDEdwards system, I am having a
lot of trouble with the sql statement that follows. It takes much
longer than I would expect it to (several hours for 1500 records. Is
there a way to optimize this statement?

Thanks very much,
JH

SELECT
TRIM("IBSTKT") AS
"STOCK_CODE",
TRIM("IBMCU") || '{$U_SEPARATOR_CHAR}' ||
TRIM(CAST("IBITM" AS CHAR(8))) AS "ID"
FROM {$U_JDDATALIB}."F4102", {$U_JDDATALIB}."F4111"
WHERE
IBMCU = ILMCU
AND IBITM = ILITM
AND
(TRIM("ILMCU") = '2784' OR TRIM("ILMCU") = '2744' AND ILAN8 = 2784)
AND
(ILTRQT <> 0 AND ILPAID <> 0)
AND
(F4111."ILCRDJ" >= 103200 AND F4111."ILCRDJ" < 103300)

You didn't say which DB2 version and OS. I would try it without the TRIM
function on the WHERE clause. Not sure why you are using it.

(ILMCU = '2784' OR ILMCU = '2744' AND ILAN8 = 2784)

Get rid of single quotes if ILMCU is numeric.

You also "should" to use another parenthesis in the above so it is
absolutely clear.

You will also need some indexes, probably at least on ILMCU and ILAN8. Other
indexes may help, but it cannot be determined without more information on
cardinality of the columns.