472,958 Members | 2,647 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 software developers and data experts.

SQL0501N error in simple procedure

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
Nov 12 '05 #1
5 6777
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

Nov 12 '05 #2
> 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

Nov 12 '05 #3
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.
Nov 12 '05 #4
> 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
Nov 12 '05 #5
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

Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Chetan | last post by:
Hi We have Oracle 8.1.7.4 and using .net framework 1.1 with Microsoft client. While executting the stored procedure we are getting an error message. Here is our code. here is the code that...
0
by: DB2 News Group | last post by:
Receiving the following error when trying to run the following from a User ID who has CONTROL on the table. This works fine when I run from an account which has SYSADM privileges. It also runs...
11
by: ColdCanuck | last post by:
Greetings! I am VERY new to DB2 but not Orable, Sybase and SQL Server. I am trying to call a stored procedure via VB 6 and ADO/OLEDB. But when I try to execute
18
by: Rhino | last post by:
I am having a problem with a nested SQL Procedure on DB2 V8.2.1 on Windows. This simple-looking proc is giving me this error message when I try to build it in the Development Center: ...
2
by: Steve Jorgensen | last post by:
When writing VB or VBA code that works with databases or other external libraries that cannot be trusted to automatically do the right thing when references to their objects are arbitrarily...
4
by: Jack | last post by:
Hi, I am trying to run an example code from a book. However I am getting the following error message: Number: -2147217900 Description: Syntax error or access violation Source: Microsoft OLE...
13
by: Neil | last post by:
Can I get the name of a procedure from within the procedure? In my error handler, I write the error to an error table. I'd like to write the name of the procedure that's writing the error. But,...
0
by: SOI_0152 | last post by:
Hi all! Happy New Year 2008. Il hope it will bring you love and happyness I'm new on this forum. I wrote a stored procedure on mainframe using DB2 7.1.1 and IBM language c. Everything works...
2
by: Eraser | last post by:
Hello, I'm just starting to learn PL/SQL. To get my feet wet, I'm trying to write a simple stored procedure that takes some values as parameters, and inserts those values into a table. For...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
2
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.