Discussion:
REORG on few tables in storage procedure loop
(too old to reply)
J.Kubica
2009-07-06 13:49:06 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:02 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:10:06 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:10:43 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:11:03 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:14:01 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.



Database (slave) 0.005229 s
44.2969 kB
Benchmark Min Max Average Total
SELECT `lang` FROM `newsgroup` WHERE `newsgroup_url` = 'comp.databases.ibm-db2' (4)
0.000188 s
0.000438 s
0.000344 s
0.001377 s
2.2344 kB
2.3672 kB
2.2676 kB
9.0703 kB
SELECT `code`, `name` FROM `lang` WHERE `code` = 'en' (3)
0.000136 s
0.000150 s
0.000142 s
0.000426 s
2.4141 kB
2.4141 kB
2.4141 kB
7.2422 kB
SELECT * FROM `lang` WHERE `code` = 'en' (1)
0.000183 s
0.000183 s
0.000183 s
0.000183 s
12.5313 kB
12.5313 kB
12.5313 kB
12.5313 kB
SELECT * FROM `thread` WHERE `hash` = 'spskCM0D' (1)
0.001848 s
0.001848 s
0.001848 s
0.001848 s
4.2500 kB
4.2500 kB
4.2500 kB
4.2500 kB
SELECT * FROM `newsgroup` WHERE `newsgroup_id` = '7601' (1)
0.000489 s
0.000489 s
0.000489 s
0.000489 s
5.8203 kB
5.8203 kB
5.8203 kB
5.8203 kB
SELECT * FROM `temp_simthread` WHERE `hash` = 'spskCM0D' (1)
0.000556 s
0.000556 s
0.000556 s
0.000556 s
2.6953 kB
2.6953 kB
2.6953 kB
2.6953 kB
SELECT text_res, img_res, manual FROM porndetect WHERE hash = 'spskCM0D' AND page = 1 (1)
0.000350 s
0.000350 s
0.000350 s
0.000350 s
2.6875 kB
2.6875 kB
2.6875 kB
2.6875 kB
Kohana 0.010828 s
55.4766 kB
Benchmark Min Max Average Total
find_file (85)
0.000006 s
0.000351 s
0.000127 s
0.010828 s
0.5234 kB
2.1563 kB
0.6527 kB
55.4766 kB
Requests 0.108565 s
542.5000 kB
Benchmark Min Max Average Total
"spskCM0D/reorg-on-few-tables-in-storage-procedure-loop" (1)
0.110570 s
0.110570 s
0.110570 s
0.110570 s
570.5234 kB
570.5234 kB
570.5234 kB
570.5234 kB
Thread controller 0.001123 s
27.7109 kB
Benchmark Min Max Average Total
Init (1)
0.001123 s
0.001123 s
0.001123 s
0.001123 s
27.7109 kB
27.7109 kB
27.7109 kB
27.7109 kB
Thread parsing 0.088397 s
206.6641 kB
Benchmark Min Max Average Total
Other (2)
0.003012 s
0.037462 s
0.020237 s
0.040474 s
38.7578 kB
66.6094 kB
52.6836 kB
105.3672 kB
PHP Post Parsing (1)
0.005820 s
0.005820 s
0.005820 s
0.005820 s
61.9297 kB
61.9297 kB
61.9297 kB
61.9297 kB
C++ Post Parsing (1)
0.041726 s
0.041726 s
0.041726 s
0.041726 s
29.9063 kB
29.9063 kB
29.9063 kB
29.9063 kB
Three (1)
0.000377 s
0.000377 s
0.000377 s
0.000377 s
9.4609 kB
9.4609 kB
9.4609 kB
9.4609 kB
Load & uncompress 0.002873 s
36.1484 kB
Benchmark Min Max Average Total
load_thread (1)
0.002873 s
0.002873 s
0.002873 s
0.002873 s
36.1484 kB
36.1484 kB
36.1484 kB
36.1484 kB
Application Execution (507) 0.020666 s 33.418700 s 0.198001 s 0.126788 s
749.6172 kB 4,534.3750 kB 1,358.5596 kB 1,190.5156 kB