Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

retrieving stored procedure result sets with embedded SQL

476 views
Skip to first unread message

Frank Swarbrick

unread,
Jan 23, 2008, 2:02:48 PM1/23/08
to
I've been trying the following in DB2/LUW since version 8.2 (9.1 and now
9.5) and I've never been able to get this to work. (Not this exact SP,
which didn't exist prior to version 9, but just any SP that returns result
sets.)

Take the following COBOL program:

IDENTIFICATION DIVISION.
PROGRAM-ID. DBLOAD.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SPECIAL-NAMES.
CONSOLE IS CONSOLE
.

DATA DIVISION.

WORKING-STORAGE SECTION.
EXEC SQL
BEGIN DECLARE SECTION
END-EXEC
01 LOAD-COMMAND-STATEMENT.
49 LOAD-CMD-LEN PIC S9(4) COMP-5.
49 LOAD-CMD PIC X(1000).
01 RESULT1 USAGE SQL TYPE IS RESULT-SET-LOCATOR VARYING.
EXEC SQL
END DECLARE SECTION
END-EXEC
EXEC SQL
INCLUDE SQLCA
END-EXEC
77 ERRLOC PIC X(80) VALUE SPACES.
77 P COMP PIC S9(8).

PROCEDURE DIVISION.
CONTINUE.

MAIN.
CALL 'connect'
PERFORM ADMIN-CMD-LOAD
PERFORM DB-DISCONNECT
STOP RUN.

ADMIN-CMD-LOAD.
MOVE 1 TO P
STRING 'LOAD FROM ('
'DATABASE CORE'
' SELECT *'
' FROM SAFEBOX.TEST)'
' OF CURSOR'
' MESSAGES ON SERVER'
' REPLACE INTO SAFEBOX.TEST'
DELIMITED BY SIZE
INTO LOAD-CMD
WITH POINTER P
SUBTRACT 1 FROM P
GIVING LOAD-CMD-LEN
DISPLAY 'CALLING SYSPROC.ADMIN_CMD' UPON CONSOLE
DISPLAY LOAD-CMD(1:LOAD-CMD-LEN)
UPON CONSOLE
EXEC SQL
CALL SYSPROC.ADMIN_CMD(:LOAD-COMMAND-STATEMENT)
END-EXEC
DISPLAY 'RETURNED FROM CALL' UPON CONSOLE
IF SQLCODE = +466
PERFORM SET-UP-CURSOR
ELSE
CALL 'checkerr' USING SQLCA ERRLOC
END-IF
EXIT.

SET-UP-CURSOR.
EXEC SQL
ASSOCIATE RESULT SET LOCATORS(:RESULT1)
WITH PROCEDURE SYSPROC.ADMIN_CMD
END-EXEC
EXEC SQL
ALLOCATE admin_cursor CURSOR
FOR RESULT SET :RESULT1
END-EXEC
EXIT.

DB-DISCONNECT.
MOVE 'DB-DISCONNECT' TO ERRLOC
EXEC SQL
CONNECT RESET
END-EXEC
CALL 'checkerr' USING SQLCA ERRLOC
EXIT.

END PROGRAM DBLOAD.

I can't even get this to pre-compile.

prep dbload.sqb BINDFILE TARGET ibmcob CALL_RESOLUTION immediate EXPLAIN yes
EXPLSNAP yes

LINE MESSAGES FOR dbload.sqb
------
--------------------------------------------------------------------
SQL0060W The "COBOL" precompiler is in progress.

It "locks up for a time" and then the application fails: "db2bp.exe has
encountered a problem and needs to close".

I then get with the following error:
DB21018E A system error occurred. The command line processor could not
continue processing.

If I remove the ALLOCATE statement it still doesn't work, but it doesn't
have this huge failure.

When I look at the resulting COBOL file I see that RESULT1 is not properly
defined, eg:

*EXEC SQL BEGIN DECLARE SECTION
* END-EXEC
01 LOAD-COMMAND-STATEMENT.
49 LOAD-CMD-LEN PIC S9(4) COMP-5.
49 LOAD-CMD PIC X(1000).
01 RESULT1
*USAGE SQL TYPE IS RESULT-SET-LOCATOR VARYING.
*
*EXEC SQL
* END DECLARE SECTION
* END-EXEC

Is it possible that no one has ever attempted this in DB2/LUW? Or do I just
not know what I am doing...

By the way, it works fine when I just call the SP but don't try to retrieve
the results. Of course that means I can't retrieve the results, so...

Anyway, by current db2 version information is:
F:\DB2\COBOL>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09050"
with
level identifier "03010107".
Informational tokens are "DB2 v9.5.0.808", "s071001", "NT3295", and Fix
Pack
"0".
Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name
"DB2COPY1".

Thanks,
Frank

Serge Rielau

unread,
Jan 23, 2008, 3:06:50 PM1/23/08
to
Frank Swarbrick wrote:
> I've been trying the following in DB2/LUW since version 8.2 (9.1 and now
> 9.5) and I've never been able to get this to work. (Not this exact SP,
> which didn't exist prior to version 9, but just any SP that returns result
> sets.)
Have you ever considered opening a PMR?
What development doesn't know, development can't fix....
(I do not count as development btw)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Frank Swarbrick

unread,
Jan 23, 2008, 3:53:35 PM1/23/08
to
>>> On 1/23/2008 at 1:06 PM, in message
<5vpl2vF...@mid.individual.net>,

Serge Rielau<sri...@ca.ibm.com> wrote:
> Frank Swarbrick wrote:
>> I've been trying the following in DB2/LUW since version 8.2 (9.1 and now
>> 9.5) and I've never been able to get this to work. (Not this exact SP,
>> which didn't exist prior to version 9, but just any SP that returns
> result
>> sets.)
> Have you ever considered opening a PMR?
> What development doesn't know, development can't fix....
> (I do not count as development btw)

Yes, but I was first wondering if anyone had every actually made something
like this work.

(If I could open a PMR myself I'd do it. When I have to get someone else
involved I prefer to do other things first.)

Frank

Frank Swarbrick

unread,
Jan 23, 2008, 8:24:11 PM1/23/08
to
>>> On 1/23/2008 at 1:53 PM, in message
<479746DF.6...@efirstbank.com>,

OK, well, I feel a bit stupid. It appears that ASSOCIATE LOCATORS and
ALLOCATE CURSOR cannot be used in an application program. Both statements
state the following: "This statement can only be embedded in an SQL
procedure. It is not an executable statement and cannot be dynamically
prepared."

Of course the fact that they sort of compiled without giving a specific
error saying that this was not allowed didn't help, but...

Anyway, I've now written an SP which I intend to call to call the SP that I
actually want to call. Seems to me there must be a better way, but...

Here's what I have so far. Seems to work. Any comments welcome. This is
my first SP of any consequence.

DROP SPECIFIC PROCEDURE dbload1@
CREATE PROCEDURE dbload(inout load_statement VARCHAR(1000)
, out my_result INTEGER
, out sql_state CHAR(5)
, out sql_code INTEGER
)
SPECIFIC dbload1
MODIFIES SQL DATA
NOT DETERMINISTIC
LANGUAGE SQL
dbload1: BEGIN
DECLARE SQLSTATE char(5) default '00000';
DECLARE SQLCODE integer default 0;
DECLARE step INTEGER;
body: BEGIN
DECLARE loc RESULT_SET_LOCATOR VARYING;
DECLARE ROWS_READ BIGINT;
DECLARE ROWS_SKIPPED BIGINT;
DECLARE ROWS_LOADED BIGINT;
DECLARE ROWS_REJECTED BIGINT;
DECLARE ROWS_DELETED BIGINT;
DECLARE ROWS_COMMITTED BIGINT;
DECLARE ROWS_PARTITIONED BIGINT;
DECLARE NUM_AGENTINFO_ENTRIES BIGINT;
DECLARE MSG_RETRIEVAL VARCHAR(512);
DECLARE MSG_REMOVAL VARCHAR(512);

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
SET sql_state = sqlstate;
SET sql_code = sqlcode;
END;

SET step = 1;
IF load_statement = '' THEN
SET load_statement = 'LOAD FROM (DATABASE core SELECT * FROM
safebox.test) OF CURSOR MESSAGES ON SERVER REPLACE INTO safebox.test';
END IF;
CALL SYSPROC.ADMIN_CMD(load_statement);
SET step = 2;
ASSOCIATE RESULT SET LOCATOR (loc)
WITH PROCEDURE SYSPROC.ADMIN_CMD;
SET step = 3;
ALLOCATE my_curs CURSOR
FOR RESULT SET loc;
SET step = 4;
OPEN my_curs;
SET step = 5;
FETCH FROM my_curs INTO
ROWS_READ
, ROWS_SKIPPED
, ROWS_LOADED
, ROWS_REJECTED
, ROWS_DELETED
, ROWS_COMMITTED
, ROWS_PARTITIONED
, NUM_AGENTINFO_ENTRIES
, MSG_RETRIEVAL
, MSG_REMOVAL
;
SET step = 6;
SELECT RESULT_KEY
INTO my_result
FROM FINAL TABLE (
INSERT INTO LOAD_RESULTS (
ROWS_READ
, ROWS_SKIPPED
, ROWS_LOADED
, ROWS_REJECTED
, ROWS_DELETED
, ROWS_COMMITTED
, ROWS_PARTITIONED
, NUM_AGENTINFO_ENTRIES
, MSG_RETRIEVAL
, MSG_REMOVAL
)
VALUES (
ROWS_READ
, ROWS_SKIPPED
, ROWS_LOADED
, ROWS_REJECTED
, ROWS_DELETED
, ROWS_COMMITTED
, ROWS_PARTITIONED
, NUM_AGENTINFO_ENTRIES
, MSG_RETRIEVAL
, MSG_REMOVAL
)
);
SET step = 7;
CLOSE my_curs WITH RELEASE;
SET step = 99;
END body;
RETURN step;
END dbload1@

This requires the following table to hold the results:
CREATE TABLE LOAD_RESULTS (
RESULT_KEY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
, ROWS_READ BIGINT
, ROWS_SKIPPED BIGINT
, ROWS_LOADED BIGINT
, ROWS_REJECTED BIGINT
, ROWS_DELETED BIGINT
, ROWS_COMMITTED BIGINT
, ROWS_PARTITIONED BIGINT
, NUM_AGENTINFO_ENTRIES BIGINT
, MSG_RETRIEVAL VARCHAR(512)
, MSG_REMOVAL VARCHAR(512)
);

I'll probably end up changing the LOAD_RESULTS table in to a global
temporary table. Maybe tomorrow. :-)

It seems to me it would be very beneficial to allow an application program
to retrieve result sets from a call to a procedure. Has this never been
requested?

Anyway....

Frank

Serge Rielau

unread,
Jan 23, 2008, 10:06:50 PM1/23/08
to
Allocate/associate is an abomination. Had I my way when it was added
things would have looked different. But there is still hope.

Frank Swarbrick

unread,
Jan 24, 2008, 10:59:57 AM1/24/08
to
>>> On 1/23/2008 at 8:06 PM, in message
<5vqdmgF...@mid.individual.net>,

Serge Rielau<sri...@ca.ibm.com> wrote:
> Allocate/associate is an abomination. Had I my way when it was added
> things would have looked different. But there is still hope.

What kind of hope? Now you have me very curious!

Frank

Serge Rielau

unread,
Jan 24, 2008, 11:29:59 AM1/24/08
to
...always important to leave folks slightly hungry.... ;-)
0 new messages