Calling an SP from RPG Using SQL CLI

[Dan Flynn]

We have a stored procedure that returns a result set. We call this
stored procedure from an RPG program using SQL CLI. The first call is
successful and we can fetch rows, etc. The RPG program ends with
*INLR set on. The second time the program is called and the stored
procedure is executed, the stored procedure bombs before it can return
the cursor to the result set.

In the job log we see:

Space offset X'00000000' or teraspace offset X'0000000000000000' is
outside current limit for object SQLDA TEMP SPACE00000017.

The stored procedure creates a temporary table named "TEMP" in QTEMP.
It returns a cursor to this table with:

DECLARE C2 CURSOR WITH RETURN FOR
SELECT *
FROM QTEMP.TEMP;

In the job log we see this for the first successful call:

1 rows inserted in TEMP in QTEMP.
ODP reused.
ODP not deleted.
SET statement complete.
Row not found for C1.
ODP deleted.
Cursor C1 closed.
Unable to retrieve query options file.
**** Starting optimizer debug message for query .
Unable to retrieve query options file.
**** Starting optimizer debug message for query .
The OS/400 Query access plan has been rebuilt.
Unable to retrieve query options file.
Arrival sequence access was used for file TEMP.
**** Ending debug message for query .
ODP created.
Blocking used for query.
Cursor C2 opened.
1 result sets are available from procedure PAS001 in PRDELIB.
1 rows fetched from cursor *N.
1 rows fetched from cursor *N.
Row not found for *N.
Row not found for *N.
ODP not deleted.
Cursor *N closed.
SQL cursors closed.

and this for the 2nd attempt (the stored procedure dies before it can
return the result set, even though table TEMP has been filled):

1 rows inserted in TEMP in QTEMP.
ODP reused.
ODP not deleted.
SET statement complete.
Row not found for C1.
Space offset X'00000000' or teraspace offset X'0000000000000000' is
outside current limit for object SQLDA TEMP SPACE00000017.

Any help with this issue would be appreciated.

[Karl Hanson]

Dan Flynn wrote:

We have a stored procedure that returns a result set. We call this
stored procedure from an RPG program using SQL CLI. The first call is
successful and we can fetch rows, etc. The RPG program ends with
*INLR set on. The second time the program is called and the stored
procedure is executed, the stored procedure bombs before it can return
the cursor to the result set.

In the job log we see:

Space offset X'00000000' or teraspace offset X'0000000000000000' is
outside current limit for object SQLDA TEMP SPACE00000017.
<snip>
Any help with this issue would be appreciated.

Best option is probably working with your IBM service provider.
Information that would be helpful:
-> What is the "To program" for the "Space offset.." exception
(MCH0601?) message?
-> Is the application running in "server mode"?
http://www-1.ibm.com/servers/eserver...q.htm#header_3
http://publib.boulder.ibm.com/iserie...zadpmst119.htm
-> If possible, a CLI trace.

--
Karl Hanson

[Karl Hanson]

Dan Flynn wrote:

We have a stored procedure that returns a result set. We call this
stored procedure from an RPG program using SQL CLI. The first call is
successful and we can fetch rows, etc. The RPG program ends with
*INLR set on. The second time the program is called and the stored
procedure is executed, the stored procedure bombs before it can return
the cursor to the result set.

In the job log we see:

Space offset X'00000000' or teraspace offset X'0000000000000000' is
outside current limit for object SQLDA TEMP SPACE00000017.
<snip>
Any help with this issue would be appreciated.

Best option is probably working with your IBM service provider.
Information that would be helpful:
-> What is the "To program" for the "Space offset.." exception
(MCH0601?) message?
-> Is the application running in "server mode"?
http://www-1.ibm.com/servers/eserver...q.htm#header_3
http://publib.boulder.ibm.com/iserie...zadpmst119.htm
-> If possible, a CLI trace.

--
Karl Hanson