I am using DB2 on iSeries(v5r2). I have a SQL select statement that has a Where clause with two criteria. I found that in the Run SQL Script screen, running the select statement directly is much faster than calling a stored procedure that has exactly the same select clause, but accepts parameters for the criteria. I would have thought that the stored procedure should be faster because it might pre-compile the statement. Does this mean that it is better to for programs(java programs) to issue direct select statements where possible instead of using stored procedures in DB2?

The following is the stored procedure I am using -

CREATE PROCEDURE TEST (IN rundate varchar(10), IN recid BIGINT)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE C1 CURSOR WITH RETURN FOR
-- Sum all amounts for Active TLC which are current.
SELECT SUM(AMT) AS ADJUSTMENT FROM table1
WHERE STATUS='I'
AND VALUEDT <= rundate
AND TABLEID=recid;
OPEN C1;
END;


The select statement I use directly is -

SELECT SUM(AMT) AS ADJUSTMENT FROM table1
WHERE STATUS='I'
AND VALUEDT <= '2003-10-02'
AND TABLEID=100;

Thanks,

v_zac