I am having problems with the following procedure, does anyone know what I am doing wrong? Below the procedure is the error message I get.
-- Stored procedure to do a non-clustered index scan
CREATE PROCEDURE IDX (IN selectivity REAL,
IN seed INTEGER,
IN vmin INTEGER,
IN vmax INTEGER,
IN unit INTEGER)
LANGUAGE SQL
BEGIN
DECLARE hi INTEGER;
DECLARE lo INTEGER;
DECLARE id INTEGER;
DECLARE val REAL;
SET id = unit;
WHILE id > 0 DO
SET lo = INTEGER(vmin + RAND(seed) * (vmax - vmin) / (1 + selectivity));
SET hi = INTEGER(lo + (vmax - vmin) * selectivity);
SELECT AVG(L_LINENUMBER)
FROM LINEITEM
WHERE L_PARTKEY > lo AND L_PARTKEY <= hi;
SET id = id - 1;
END WHILE;
END
@
C:\mbenchkit\mbench_tpcc>db2 -td@ -f idx.sql
DB21034E The command was processed as an SQL statement because it was not a valid
Command Line Processor command. During SQL processing it returned: SQL0104N An
unexpected token "SELECT AVG(L_LINENUMBER) FROM LI" was found following ") *
selectivity); ". Expected tokens may include:"<psm_return>". LINE NUMBER=19.
SQLSTATE=42601
Edited 1 time(s). Last edit at 04/13/2006 06:39PM by jay patel.