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.
I tryed but in this case this solution didn't help. I tryed also change FOR
to WHILE loop - still the same.
If I call REORG in ADMIN_CMD() - I recive error, if I call RUNSTATS -
everythig is fine.
I am in despair
if the cursor is still open. IF sqlCode = -501 THEN OPEN tabCur; END IF;
I tryed but in this case this solution didn't help. I tryed also change FOR to WHILE loop - still the same. If I call REORG in ADMIN_CMD() - I recive error, if I call RUNSTATS - everythig is fine. I am in despair
Yaro
Just a guess, maybe REORG is doing a commit in the background. If that's
the problem you can define your cursor with the 'with hold' option.
if the cursor is still open. IF sqlCode = -501 THEN OPEN tabCur; END IF;
I tryed but in this case this solution didn't help. I tryed also change FOR to WHILE loop - still the same. If I call REORG in ADMIN_CMD() - I recive error, if I call RUNSTATS - everythig is fine. I am in despair Yaro
Just a guess, maybe REORG is doing a commit in the background. If that's the problem you can define your cursor with the 'with hold' option.
Sounds like the runstats SP is ending your unit of work, and closing the
cursor...try declaring your cursor with hold.
Yaro wrote:
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.
