Sounds like something I ran into once. Are you using the same statement name for two or more PREPAREs? I don't mean the host variable; I mean the SQL statement-name, as in prepStmt below:

Exec SQL PREPARE prepStmt from :Stmt1;
Exec SQL DECLARE c1 CURSOR FOR prepStmt;
// Open, Fetch, Close c1
Exec SQL PREPARE prepStmt from :Stmt2;
Execl SQL DECLARE c2 CURSOR FOR prepStmt;
// Open, Fetch, Close c2

What I found was the name of the PREPAREd statement, prepStmt, is never deallocated. There's no way to reuse it. It only goes away when the SQL run time ends, typicaly when module exits. Work around was each PREPARE has to specify a different statement name (s1, s2, ...).

Yes, CURSOR names and the PREPARE ID have to be unique within a program.

CURSOR have to be unique (mostly) within the Activation Group/Job.

The only thing that is the same is that I was using one variable, :sqlstmt, for the definition of the SQL SELECT. I tried using three different variable names and that didn't do anything for me either. Here's my cursors:

exec sql prepare rtnCursor from :sqlstmt1;

exec sql declare c1 cursor for rtnCursor;

exec sql close c1;

exec sql prepare canCursor from :sqlstmt2;

exec sql declare c2 cursor for canCursor;

exec sql close c2;

exec sql prepare adjCursor from :sqlstmt3;

exec sql declare c3 cursor for adjCursor;

exec sql close c3;

The host variable that's used by the PREPARE doesn't matter. Could even be a literal.

I tend to put my DECLARE, PREPARE, and OPEN in the same procedure, FETCH in its own, and CLOSE in its own. The main body of the code may occasionally do an UPDATE WHERE CURRENT OF. But all in the same module, so I've never run into problems with multiple SQL activations.

Are you calling it more than once? I also see at the top that I have Exec SQL SET OPTION CLOSQLCSR = *ENDMOD. When the module exits, cursors are implicitly closed, and SQL prepared statments are discarded; i.e., when my module exits, the prepared statement name is freed for reuse. The default is CLOSQLSCR = *ENDACTGRP, which more than likely lasts a lot longer than the current module.

The other thing could be, is this module called by, or does it call, another SQLRPGLE module? If so, does the called or caller also PREPARE a statement that has the same name (rtnCursor, canCursor, or adjCursor)? The statement name is for your entire session, basically. Try making the statement names specific to the program, PgmX_rtnCursor.

Start Debug and run the failing program again and review the joblog messages. I would put a breakpoint right after the 2nd (failing) PREPARE to see what's in the joblog and idientify the messages related to it.

Bob and Dale, so sorry to have bugged you guys. It was my bad. I was missing an "and" in my SQL statement. Thanks for all the help.

If that's the actual code, then you're getting the correct error.

Look at the WHERE clause, it is wrong.

SDSTAT = 'X' SHSLLO IN(500,507,511)

You are missing the AND/OR between the two, but the first SQL stmt has it correctly.