Hello
I have odd problem with simple procedure (DB2. 8.1 FP10 Win32)
CREATE PROCEDURE ADM.P_REORG_RUNSTATS(IN par_TabName VARCHAR(128), IN
par_Reorg INTEGER,IN par_Runstats INTEGER)
SPECIFIC P_REORG_RUNSTATS
NOT DETERMINISTIC
MODIFIES SQL DATA
LANGUAGE SQL
NO EXTERNAL ACTION
DYNAMIC RESULT SETS 0
BEGIN
DECLARE vSchema VARCHAR(128);
DECLARE vTabName VARCHAR(128);
DECLARE vSql VARCHAR(512);
BEGIN -- bacause this is part of bigger procedure
SET vSchema = 'TEST';
SET vTabName = UCASE(LTRIM(RTRIM(par_TabName)));
IF par_Reorg IS NULL THEN
SET par_Reorg = 0;
END IF;
IF par_Runstats IS NULL THEN
SET par_Runstats = 0;
END IF;
FOR tabLoop AS tabCur CURSOR FOR
SELECT UCASE(RTRIM(LTRIM(tabschema))) AS tabschema,
UCASE(RTRIM(LTRIM(tabname))) AS tabname
FROM SYSCAT.TABLES
WHERE UCASE(RTRIM(LTRIM(tabschema))) = UCASE(vSchema) AND
UCASE(tabname) LIKE UCASE(vTabName)
DO
IF par_Reorg = 1 THEN
SET vSql = 'REORG INDEXES ALL FOR TABLE ' || tabschema ||
'.' || tabname || ' ALLOW READ ACCESS';
INSERT INTO SESSION.T_RUNSTATS_REORG(TXT) VALUES (vSql);
CALL SYSPROC.ADMIN_CMD(vSql);
END IF;
IF par_Runstats = 1 THEN
SET vSql = 'RUNSTATS ON TABLE ' || tabschema || '.' ||
tabname || ' ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND SAMPLED
DETAILED INDEXES ALL ALLOW WRITE ACCESS';
INSERT INTO SESSION.T_RUNSTATS_REORG(TXT) VALUES (vSql);
CALL SYSPROC.ADMIN_CMD(vSql);
END IF;
END FOR;
END;
END @
When I call CALL ADM.P_REORG_RUNSTATS('ROLLE', 0, 1) everything is ok, but
when I call CALL ADM.P_REORG_RUNSTATS('ROLLE', 1, 0) procedure give me
error:
SQL0501N Cursor in FETCH or CLOSE instruction is not opened
SQLSTATE=24501
Error is in line CALL SYSPROC.ADMIN_CMD(vSql); when I try reorganize indexes
on table.
Please help. What is wrong with this procedure?
Thanks in Advance.
Yaro