SQL0501N error in simple procedure

[Yaro]

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

[fayez]

I had this problem in more than 1 stored procedures, all I did, check
if the cursor is still open.

IF sqlCode = -501 THEN
OPEN tabCur;
END IF;
Fayez

[Yaro]

> 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

[Gert van der Kooij]

In article <df**********@83.238.170.160>, ya***************@op.pl
says...

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.

[Yaro]

> says...

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.

It work.
Thanks a lot

Yaro

[Sean McKeough]

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.

Yaro