(too old to reply)
REORG on few tables in storage procedure loop
J.Kubica
2009-07-06 14:00:11 UTC
Hello

We have stored procedure which iterate on required tables and makes REORG.
It was working on DB2 8.2
Now we are moving to DB2 9.5 fp3 and procedure gives me error after first
iteration.

SQL0501N The cursor specified in a FETCH or CLOSE statement is not open.
SQLSTATE=24501
SQL0501N The cursor specified in a FETCH or CLOSE statement is not open.
Explanation:
The program attempted to either: (1) FETCH using a cursor, or (2) CLOSE
a cursor at a time when the specified cursor was not open.

----------------------------------------------------

-- par_Reorg : NULL or 0 - no REORG, 1 - REORG table

DROP PROCEDURE ADM.P_REORG
@

CREATE PROCEDURE ADM.P_REORG(
IN par_Schema VARCHAR(128) ,
IN par_TabName VARCHAR(128) ,
IN par_Reorg INTEGER
)
SPECIFIC P_REORG
NOT DETERMINISTIC
MODIFIES SQL DATA
LANGUAGE SQL
DYNAMIC RESULT SETS 1

BEGIN
DECLARE vSchema VARCHAR(128);
DECLARE vTabName VARCHAR(128);
DECLARE vSql VARCHAR(512);
DECLARE vIsSpecial INTEGER;
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE GLOBAL TEMPORARY TABLE SESSION.T_RUNSTATS_REORG(
TXT VARCHAR(512),
TXT_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ON COMMIT PRESERVE ROWS WITH REPLACE;

BEGIN
DECLARE ResCur CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT TXT FROM SESSION.T_RUNSTATS_REORG ORDER BY TXT_TIME ASC;

SET vSchema = UCASE(LTRIM(RTRIM(par_Schema)));
SET vTabName = UCASE(LTRIM(RTRIM(par_TabName)));
IF par_Reorg IS NULL THEN
SET par_Reorg = 0;
END IF;

FOR tabLoop AS tabCur CURSOR WITH HOLD FOR
SELECT UCASE(RTRIM(LTRIM(tabschema))) AS tabschema,
UCASE(RTRIM(LTRIM(tabname))) AS tabname
FROM SYSCAT.TABLES
WHERE UCASE(RTRIM(LTRIM(tabschema))) LIKE UCASE(vSchema) AND
UCASE(tabname) LIKE UCASE(vTabName)
AND TYPE = 'T'
DO
IF par_Reorg = 1 THEN
SET vSql = 'REORG TABLE ' || tabschema || '.' || tabname ;
INSERT INTO SESSION.T_RUNSTATS_REORG(TXT) VALUES (vSql);
IF SQLCODE = -501 THEN
OPEN tabCur;
END IF;
CALL ADMIN_CMD(vSql);
END IF;
END FOR;
OPEN ResCur;
END;

END
@

-- CALL ADM.P_Reorg('myschema','part%', 1)


Could anyone help me with this?

Thank you
Y.
jefftyzzer
2009-07-07 00:45:32 UTC
Post by J.Kubica
Hello
We have stored procedure which iterate on required tables and makes REORG.
It was working on DB2 8.2
Now we are moving to DB2 9.5 fp3 and procedure gives me error after first
iteration.
SQL0501N  The cursor specified in a FETCH or CLOSE statement is not open.
SQLSTATE=24501
SQL0501N  The cursor specified in a FETCH or CLOSE statement is not open.
The program attempted to either: (1) FETCH using a cursor, or (2) CLOSE
a cursor at a time when the specified cursor was not open.
----------------------------------------------------
-- par_Reorg : NULL or 0 - no REORG, 1 - REORG table
DROP PROCEDURE ADM.P_REORG
@
CREATE PROCEDURE ADM.P_REORG(
     IN par_Schema   VARCHAR(128) ,
     IN par_TabName  VARCHAR(128) ,
     IN par_Reorg    INTEGER
)
SPECIFIC P_REORG
NOT DETERMINISTIC
MODIFIES SQL DATA
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
   DECLARE vSchema    VARCHAR(128);
   DECLARE vTabName   VARCHAR(128);
   DECLARE vSql       VARCHAR(512);
   DECLARE vIsSpecial INTEGER;
   DECLARE SQLCODE    INTEGER DEFAULT 0;
   DECLARE GLOBAL TEMPORARY TABLE SESSION.T_RUNSTATS_REORG(
     TXT         VARCHAR(512),
     TXT_TIME    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   ) ON COMMIT PRESERVE ROWS WITH REPLACE;
   BEGIN
       DECLARE ResCur CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
           SELECT TXT FROM SESSION.T_RUNSTATS_REORG ORDER BY TXT_TIME ASC;
       SET vSchema  = UCASE(LTRIM(RTRIM(par_Schema)));
       SET vTabName = UCASE(LTRIM(RTRIM(par_TabName)));
       IF par_Reorg IS NULL THEN
          SET par_Reorg = 0;
       END IF;
       FOR tabLoop AS tabCur CURSOR WITH HOLD FOR
          SELECT UCASE(RTRIM(LTRIM(tabschema))) AS tabschema,
UCASE(RTRIM(LTRIM(tabname))) AS tabname
            FROM SYSCAT.TABLES
           WHERE UCASE(RTRIM(LTRIM(tabschema))) LIKE UCASE(vSchema) AND
UCASE(tabname) LIKE UCASE(vTabName)
             AND TYPE = 'T'
       DO
          IF par_Reorg = 1 THEN
             SET vSql     = 'REORG TABLE ' || tabschema || '.' || tabname ;
             INSERT INTO SESSION.T_RUNSTATS_REORG(TXT) VALUES (vSql);
             IF SQLCODE = -501  THEN
                 OPEN tabCur;
             END IF;
             CALL ADMIN_CMD(vSql);
          END IF;
       END FOR;
       OPEN ResCur;
   END;
END
@
-- CALL ADM.P_Reorg('myschema','part%', 1)
Could anyone help me with this?
Thank you
        Y.
Y,

Having declared the cursor FOR loop tabCur, you've already opened it,
yet you have an explicit "OPEN tabCur" statement after you check the
SQLCODE. Should that OPEN refer instead to some other cursor?

--Jeff
J.Kubica
2009-07-07 07:21:14 UTC
Post by jefftyzzer
Post by J.Kubica
Hello
We have stored procedure which iterate on required tables and makes REORG.
It was working on DB2 8.2
Now we are moving to DB2 9.5 fp3 and procedure gives me error after first
iteration.
SQL0501N The cursor specified in a FETCH or CLOSE statement is not open.
SQLSTATE=24501
SQL0501N The cursor specified in a FETCH or CLOSE statement is not open.
The program attempted to either: (1) FETCH using a cursor, or (2) CLOSE
a cursor at a time when the specified cursor was not open.
----------------------------------------------------
-- par_Reorg : NULL or 0 - no REORG, 1 - REORG table
DROP PROCEDURE ADM.P_REORG
@
CREATE PROCEDURE ADM.P_REORG(
IN par_Schema VARCHAR(128) ,
IN par_TabName VARCHAR(128) ,
IN par_Reorg INTEGER
)
SPECIFIC P_REORG
NOT DETERMINISTIC
MODIFIES SQL DATA
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE vSchema VARCHAR(128);
DECLARE vTabName VARCHAR(128);
DECLARE vSql VARCHAR(512);
DECLARE vIsSpecial INTEGER;
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE GLOBAL TEMPORARY TABLE SESSION.T_RUNSTATS_REORG(
TXT VARCHAR(512),
TXT_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ON COMMIT PRESERVE ROWS WITH REPLACE;
BEGIN
DECLARE ResCur CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT TXT FROM SESSION.T_RUNSTATS_REORG ORDER BY TXT_TIME ASC;
SET vSchema = UCASE(LTRIM(RTRIM(par_Schema)));
SET vTabName = UCASE(LTRIM(RTRIM(par_TabName)));
IF par_Reorg IS NULL THEN
SET par_Reorg = 0;
END IF;
FOR tabLoop AS tabCur CURSOR WITH HOLD FOR
SELECT UCASE(RTRIM(LTRIM(tabschema))) AS tabschema,
UCASE(RTRIM(LTRIM(tabname))) AS tabname
FROM SYSCAT.TABLES
WHERE UCASE(RTRIM(LTRIM(tabschema))) LIKE UCASE(vSchema) AND
UCASE(tabname) LIKE UCASE(vTabName)
AND TYPE = 'T'
DO
IF par_Reorg = 1 THEN
SET vSql = 'REORG TABLE ' || tabschema || '.' || tabname ;
INSERT INTO SESSION.T_RUNSTATS_REORG(TXT) VALUES (vSql);
IF SQLCODE = -501 THEN
OPEN tabCur;
END IF;
CALL ADMIN_CMD(vSql);
END IF;
END FOR;
OPEN ResCur;
END;
END
@
-- CALL ADM.P_Reorg('myschema','part%', 1)
Could anyone help me with this?
Thank you
Y.
Y,
Having declared the cursor FOR loop tabCur, you've already opened it,
yet you have an explicit "OPEN tabCur" statement after you check the
SQLCODE. Should that OPEN refer instead to some other cursor?
--Jeff
I would be too easy :-)
I enter this tricky IF after advise someone from this newsgroup. It was
working on v 8.2.
On 9.5 I declared this procedure without additional "OPEN tabCur" - still the
same. I analyzed call of this procedure and it seems for me that REORG close
cursor and FETCH called by FOR loop gives error message.

Have you any ideas what is wrong or how to rewrite it?

Thanks
Y.
J.Kubica
2009-07-07 07:21:50 UTC
Post by jefftyzzer
Post by J.Kubica
Hello
We have stored procedure which iterate on required tables and makes REORG.
It was working on DB2 8.2
Now we are moving to DB2 9.5 fp3 and procedure gives me error after first
iteration.
SQL0501N The cursor specified in a FETCH or CLOSE statement is not open.
SQLSTATE=24501
SQL0501N The cursor specified in a FETCH or CLOSE statement is not open.
The program attempted to either: (1) FETCH using a cursor, or (2) CLOSE
a cursor at a time when the specified cursor was not open.
----------------------------------------------------
-- par_Reorg : NULL or 0 - no REORG, 1 - REORG table
DROP PROCEDURE ADM.P_REORG
@
CREATE PROCEDURE ADM.P_REORG(
IN par_Schema VARCHAR(128) ,
IN par_TabName VARCHAR(128) ,
IN par_Reorg INTEGER
)
SPECIFIC P_REORG
NOT DETERMINISTIC
MODIFIES SQL DATA
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE vSchema VARCHAR(128);
DECLARE vTabName VARCHAR(128);
DECLARE vSql VARCHAR(512);
DECLARE vIsSpecial INTEGER;
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE GLOBAL TEMPORARY TABLE SESSION.T_RUNSTATS_REORG(
TXT VARCHAR(512),
TXT_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ON COMMIT PRESERVE ROWS WITH REPLACE;
BEGIN
DECLARE ResCur CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT TXT FROM SESSION.T_RUNSTATS_REORG ORDER BY TXT_TIME ASC;
SET vSchema = UCASE(LTRIM(RTRIM(par_Schema)));
SET vTabName = UCASE(LTRIM(RTRIM(par_TabName)));
IF par_Reorg IS NULL THEN
SET par_Reorg = 0;
END IF;
FOR tabLoop AS tabCur CURSOR WITH HOLD FOR
SELECT UCASE(RTRIM(LTRIM(tabschema))) AS tabschema,
UCASE(RTRIM(LTRIM(tabname))) AS tabname
FROM SYSCAT.TABLES
WHERE UCASE(RTRIM(LTRIM(tabschema))) LIKE UCASE(vSchema) AND
UCASE(tabname) LIKE UCASE(vTabName)
AND TYPE = 'T'
DO
IF par_Reorg = 1 THEN
SET vSql = 'REORG TABLE ' || tabschema || '.' || tabname ;
INSERT INTO SESSION.T_RUNSTATS_REORG(TXT) VALUES (vSql);
IF SQLCODE = -501 THEN
OPEN tabCur;
END IF;
CALL ADMIN_CMD(vSql);
END IF;
END FOR;
OPEN ResCur;
END;
END
@
-- CALL ADM.P_Reorg('myschema','part%', 1)
Could anyone help me with this?
Thank you
Y.
Y,
Having declared the cursor FOR loop tabCur, you've already opened it,
yet you have an explicit "OPEN tabCur" statement after you check the
SQLCODE. Should that OPEN refer instead to some other cursor?
--Jeff
I would be too easy :-)
I enter this tricky IF after advise someone from this newsgroup. It was
working on v 8.2.
On 9.5 I declared this procedure without additional "OPEN tabCur" - still the
same. I analyzed call of this procedure and it seems for me that REORG close
cursor and FETCH called by FOR loop gives error message.

Have you any ideas what is wrong or how to rewrite it?

Thanks
Y.
J.Kubica
2009-07-07 07:22:11 UTC
Post by jefftyzzer
Post by J.Kubica
Hello
We have stored procedure which iterate on required tables and makes REORG.
It was working on DB2 8.2
Now we are moving to DB2 9.5 fp3 and procedure gives me error after first
iteration.
SQL0501N The cursor specified in a FETCH or CLOSE statement is not open.
SQLSTATE=24501
SQL0501N The cursor specified in a FETCH or CLOSE statement is not open.
The program attempted to either: (1) FETCH using a cursor, or (2) CLOSE
a cursor at a time when the specified cursor was not open.
----------------------------------------------------
-- par_Reorg : NULL or 0 - no REORG, 1 - REORG table
DROP PROCEDURE ADM.P_REORG
@
CREATE PROCEDURE ADM.P_REORG(
IN par_Schema VARCHAR(128) ,
IN par_TabName VARCHAR(128) ,
IN par_Reorg INTEGER
)
SPECIFIC P_REORG
NOT DETERMINISTIC
MODIFIES SQL DATA
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE vSchema VARCHAR(128);
DECLARE vTabName VARCHAR(128);
DECLARE vSql VARCHAR(512);
DECLARE vIsSpecial INTEGER;
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE GLOBAL TEMPORARY TABLE SESSION.T_RUNSTATS_REORG(
TXT VARCHAR(512),
TXT_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ON COMMIT PRESERVE ROWS WITH REPLACE;
BEGIN
DECLARE ResCur CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT TXT FROM SESSION.T_RUNSTATS_REORG ORDER BY TXT_TIME ASC;
SET vSchema = UCASE(LTRIM(RTRIM(par_Schema)));
SET vTabName = UCASE(LTRIM(RTRIM(par_TabName)));
IF par_Reorg IS NULL THEN
SET par_Reorg = 0;
END IF;
FOR tabLoop AS tabCur CURSOR WITH HOLD FOR
SELECT UCASE(RTRIM(LTRIM(tabschema))) AS tabschema,
UCASE(RTRIM(LTRIM(tabname))) AS tabname
FROM SYSCAT.TABLES
WHERE UCASE(RTRIM(LTRIM(tabschema))) LIKE UCASE(vSchema) AND
UCASE(tabname) LIKE UCASE(vTabName)
AND TYPE = 'T'
DO
IF par_Reorg = 1 THEN
SET vSql = 'REORG TABLE ' || tabschema || '.' || tabname ;
INSERT INTO SESSION.T_RUNSTATS_REORG(TXT) VALUES (vSql);
IF SQLCODE = -501 THEN
OPEN tabCur;
END IF;
CALL ADMIN_CMD(vSql);
END IF;
END FOR;
OPEN ResCur;
END;
END
@
-- CALL ADM.P_Reorg('myschema','part%', 1)
Could anyone help me with this?
Thank you
Y.
Y,
Having declared the cursor FOR loop tabCur, you've already opened it,
yet you have an explicit "OPEN tabCur" statement after you check the
SQLCODE. Should that OPEN refer instead to some other cursor?
--Jeff
I would be too easy :-)
I enter this tricky IF after advise someone from this newsgroup. It was
working on v 8.2.
On 9.5 I declared this procedure without additional "OPEN tabCur" - still the
same. I analyzed call of this procedure and it seems for me that REORG close
cursor and FETCH called by FOR loop gives error message.

Have you any ideas what is wrong or how to rewrite it?

Thanks
Y.
Serge Rielau
2009-07-07 15:16:51 UTC
Post by J.Kubica
Hello
We have stored procedure which iterate on required tables and makes REORG.
It was working on DB2 8.2
Now we are moving to DB2 9.5 fp3 and procedure gives me error after
first iteration.
SQL0501N The cursor specified in a FETCH or CLOSE statement is not open.
SQLSTATE=24501
SQL0501N The cursor specified in a FETCH or CLOSE statement is not open.
The program attempted to either: (1) FETCH using a cursor, or (2) CLOSE
a cursor at a time when the specified cursor was not open.
----------------------------------------------------
-- par_Reorg : NULL or 0 - no REORG, 1 - REORG table
DROP PROCEDURE ADM.P_REORG
@
CREATE PROCEDURE ADM.P_REORG(
IN par_Schema VARCHAR(128) ,
IN par_TabName VARCHAR(128) ,
IN par_Reorg INTEGER
)
SPECIFIC P_REORG
NOT DETERMINISTIC
MODIFIES SQL DATA
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE vSchema VARCHAR(128);
DECLARE vTabName VARCHAR(128);
DECLARE vSql VARCHAR(512);
DECLARE vIsSpecial INTEGER;
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE GLOBAL TEMPORARY TABLE SESSION.T_RUNSTATS_REORG(
TXT VARCHAR(512),
TXT_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ON COMMIT PRESERVE ROWS WITH REPLACE;
BEGIN
DECLARE ResCur CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT TXT FROM SESSION.T_RUNSTATS_REORG ORDER BY TXT_TIME ASC;
SET vSchema = UCASE(LTRIM(RTRIM(par_Schema)));
SET vTabName = UCASE(LTRIM(RTRIM(par_TabName)));
IF par_Reorg IS NULL THEN
SET par_Reorg = 0;
END IF;
FOR tabLoop AS tabCur CURSOR WITH HOLD FOR
SELECT UCASE(RTRIM(LTRIM(tabschema))) AS tabschema,
UCASE(RTRIM(LTRIM(tabname))) AS tabname
FROM SYSCAT.TABLES
WHERE UCASE(RTRIM(LTRIM(tabschema))) LIKE UCASE(vSchema) AND
UCASE(tabname) LIKE UCASE(vTabName)
AND TYPE = 'T'
DO
IF par_Reorg = 1 THEN
SET vSql = 'REORG TABLE ' || tabschema || '.' || tabname ;
INSERT INTO SESSION.T_RUNSTATS_REORG(TXT) VALUES (vSql);
IF SQLCODE = -501 THEN
OPEN tabCur;
END IF;
CALL ADMIN_CMD(vSql);
END IF;
END FOR;
OPEN ResCur;
END;
END
@
-- CALL ADM.P_Reorg('myschema','part%', 1)
I think REORG does a COMMIT and that will close all cursors not defined
as WITH HOLD. Now I can never remember whether WITH HOLD is allowed in a
FOR LOOP. Worst case you need to code the FOR using a a straight cursor
logic.

I'm somewhat puzzled that this worked in DB2 8.2. May have been a bug (?)
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab
jefftyzzer
2009-07-07 18:31:40 UTC
Post by Serge Rielau
Post by J.Kubica
Hello
We have stored procedure which iterate on required tables and makes REORG.
It was working on DB2 8.2
Now we are moving to DB2 9.5 fp3 and procedure gives me error after
first iteration.
SQL0501N  The cursor specified in a FETCH or CLOSE statement is not open.
SQLSTATE=24501
SQL0501N  The cursor specified in a FETCH or CLOSE statement is not open.
The program attempted to either: (1) FETCH using a cursor, or (2) CLOSE
a cursor at a time when the specified cursor was not open.
----------------------------------------------------
-- par_Reorg : NULL or 0 - no REORG, 1 - REORG table
DROP PROCEDURE ADM.P_REORG
@
CREATE PROCEDURE ADM.P_REORG(
    IN par_Schema   VARCHAR(128) ,
    IN par_TabName  VARCHAR(128) ,
    IN par_Reorg    INTEGER
)
SPECIFIC P_REORG
NOT DETERMINISTIC
MODIFIES SQL DATA
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
  DECLARE vSchema    VARCHAR(128);
  DECLARE vTabName   VARCHAR(128);
  DECLARE vSql       VARCHAR(512);
  DECLARE vIsSpecial INTEGER;
  DECLARE SQLCODE    INTEGER DEFAULT 0;
  DECLARE GLOBAL TEMPORARY TABLE SESSION.T_RUNSTATS_REORG(
    TXT         VARCHAR(512),
    TXT_TIME    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  ) ON COMMIT PRESERVE ROWS WITH REPLACE;
  BEGIN
      DECLARE ResCur CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
          SELECT TXT FROM SESSION.T_RUNSTATS_REORG ORDER BY TXT_TIME ASC;
      SET vSchema  = UCASE(LTRIM(RTRIM(par_Schema)));
      SET vTabName = UCASE(LTRIM(RTRIM(par_TabName)));
      IF par_Reorg IS NULL THEN
         SET par_Reorg = 0;
      END IF;
      FOR tabLoop AS tabCur CURSOR WITH HOLD FOR
         SELECT UCASE(RTRIM(LTRIM(tabschema))) AS tabschema,
UCASE(RTRIM(LTRIM(tabname))) AS tabname
           FROM SYSCAT.TABLES
          WHERE UCASE(RTRIM(LTRIM(tabschema))) LIKE UCASE(vSchema) AND
UCASE(tabname) LIKE UCASE(vTabName)
            AND TYPE = 'T'
      DO
         IF par_Reorg = 1 THEN
            SET vSql     = 'REORG TABLE ' || tabschema || '.' || tabname ;
            INSERT INTO SESSION.T_RUNSTATS_REORG(TXT) VALUES (vSql);
            IF SQLCODE = -501  THEN
                OPEN tabCur;
            END IF;
            CALL ADMIN_CMD(vSql);
         END IF;
      END FOR;
      OPEN ResCur;
  END;
END
@
-- CALL ADM.P_Reorg('myschema','part%', 1)
I think REORG does a COMMIT and that will close all cursors not defined
as WITH HOLD. Now I can never remember whether WITH HOLD is allowed in a
FOR LOOP. Worst case you need to code the FOR using a a straight cursor
logic.
I'm somewhat puzzled that this worked in DB2 8.2. May have been a bug (?)
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab
According to p.520 of the SQL Ref (v2), WITH HOLD is allowed in the
declaration of a cursor FOR loop. But I see that you have that with
each of the two declarations in your SP. According to the ADMIN_CMD
documentation, REORG "issues a COMMIT statement at the beginning of
the operation which, in the case of Type 2 connections, causes the
procedure to return SQL30090N with reason code 2." But again, you'd
think that the WITH HOLD would handle that COMMIT.

I don't suppose you're encountering any of the cursor-related issues
associated with SQL30090N? There are several, and reason code 14 may
be worth investigating (the long-shot theory being that throwing
SQL30090N causes the cursor to close)....

Sorry I've got nothing definitive.

--Jeff
J.Kubica
2009-07-08 08:25:10 UTC
Post by jefftyzzer
Post by Serge Rielau
Post by J.Kubica
Hello
We have stored procedure which iterate on required tables and makes REORG.
It was working on DB2 8.2
Now we are moving to DB2 9.5 fp3 and procedure gives me error after
first iteration.
SQL0501N The cursor specified in a FETCH or CLOSE statement is not open.
SQLSTATE=24501
SQL0501N The cursor specified in a FETCH or CLOSE statement is not open.
The program attempted to either: (1) FETCH using a cursor, or (2) CLOSE
a cursor at a time when the specified cursor was not open.
----------------------------------------------------
-- par_Reorg : NULL or 0 - no REORG, 1 - REORG table
DROP PROCEDURE ADM.P_REORG
@
CREATE PROCEDURE ADM.P_REORG(
IN par_Schema VARCHAR(128) ,
IN par_TabName VARCHAR(128) ,
IN par_Reorg INTEGER
)
SPECIFIC P_REORG
NOT DETERMINISTIC
MODIFIES SQL DATA
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE vSchema VARCHAR(128);
DECLARE vTabName VARCHAR(128);
DECLARE vSql VARCHAR(512);
DECLARE vIsSpecial INTEGER;
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE GLOBAL TEMPORARY TABLE SESSION.T_RUNSTATS_REORG(
TXT VARCHAR(512),
TXT_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ON COMMIT PRESERVE ROWS WITH REPLACE;
BEGIN
DECLARE ResCur CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT TXT FROM SESSION.T_RUNSTATS_REORG ORDER BY TXT_TIME ASC;
SET vSchema = UCASE(LTRIM(RTRIM(par_Schema)));
SET vTabName = UCASE(LTRIM(RTRIM(par_TabName)));
IF par_Reorg IS NULL THEN
SET par_Reorg = 0;
END IF;
FOR tabLoop AS tabCur CURSOR WITH HOLD FOR
SELECT UCASE(RTRIM(LTRIM(tabschema))) AS tabschema,
UCASE(RTRIM(LTRIM(tabname))) AS tabname
FROM SYSCAT.TABLES
WHERE UCASE(RTRIM(LTRIM(tabschema))) LIKE UCASE(vSchema) AND
UCASE(tabname) LIKE UCASE(vTabName)
AND TYPE = 'T'
DO
IF par_Reorg = 1 THEN
SET vSql = 'REORG TABLE ' || tabschema || '.' || tabname ;
INSERT INTO SESSION.T_RUNSTATS_REORG(TXT) VALUES (vSql);
IF SQLCODE = -501 THEN
OPEN tabCur;
END IF;
CALL ADMIN_CMD(vSql);
END IF;
END FOR;
OPEN ResCur;
END;
END
@
-- CALL ADM.P_Reorg('myschema','part%', 1)
I think REORG does a COMMIT and that will close all cursors not defined
as WITH HOLD. Now I can never remember whether WITH HOLD is allowed in a
FOR LOOP. Worst case you need to code the FOR using a a straight cursor
logic.
I'm somewhat puzzled that this worked in DB2 8.2. May have been a bug (?)
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab
According to p.520 of the SQL Ref (v2), WITH HOLD is allowed in the
declaration of a cursor FOR loop. But I see that you have that with
each of the two declarations in your SP. According to the ADMIN_CMD
documentation, REORG "issues a COMMIT statement at the beginning of
the operation which, in the case of Type 2 connections, causes the
procedure to return SQL30090N with reason code 2." But again, you'd
think that the WITH HOLD would handle that COMMIT.
I don't suppose you're encountering any of the cursor-related issues
associated with SQL30090N? There are several, and reason code 14 may
be worth investigating (the long-shot theory being that throwing
SQL30090N causes the cursor to close)....
Sorry I've got nothing definitive.
--Jeff
Thanks for your answers.
We found solution or rather workaround.

In loop we collect tasks to do (REORG strings) in temporary table,
then in loop we read 1st task, delete item in temporary table and call
ADMIN_CMD. Works.

Y.