DB2 Stored Procedures and Dynamic SQL returning a Result Set

[Todd Peterson]

I'm a newbie to DB2 and am trying to figure out how to write a stored
procedure, using dynamic SQL statements to return a result set. I
believe the majority of the hurdles I have been facing might be due to
the fact that the samples and postings I have read have been related
to the UDB... I believe our company is on some version of MVS or
OS/390, but I am not sure which one, at the time of this posting.

I have an example, below, of a HelloWorld procedure that is currently
working in our environment, and would like to know how to convert it
to use a dynamic SQL statement. I have made a first attempt at
getting this to work and am also providing an example of that below.

Rather than dealing with the environmental issues I may be facing,
what I'm really hoping for is validation of the approach and syntax I
am using or guidance in getting a "Simple" HelloWorld with dynamic SQL
returning a result to work.

Be gentle on me... ;-) I am a Windows developer with Oracle and SQL
Server database experience. This MVS, OS/390 and DB2 environment is a
whole new world for me!

Thanks in advance for any assistance!
Todd

========== FUNCTIONAL HELLOWORLD PROCEDURE =========
CREATE PROCEDURE OTDSTPR.SP0200 ( IN in_client_id char(12),

OUT SQLSTATE_OUT char(5),
OUT SQLCODE_OUT int )
RESULT SETS 1
LANGUAGE SQL
MODIFIES SQL DATA
COLLID OTDSTPR
WLM ENVIRONMENT DBSOTD00
ASUTIME LIMIT 50000
RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&172.16.7.109:*)'

P1: BEGIN
-- Declare variables
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;

-- Declare Cursor
DECLARE myCursor CURSOR WITH RETURN FOR
SELECT DISTINCT
'HELLO WORLD', C.CLIENT_LONG_NM AS CLIENT
FROM CLIENT C
WHERE
(
C.CLIENT_ID = in_client_id
OR
in_client_id = 'ALL'
)
WITH UR;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
FROM SYSIBM.SYSDUMMY1;

open myCursor;

SET SQLSTATE_OUT = SQLSTATE;
SET SQLCODE_OUT = SQLCODE;

END P1
======= END FUNCTIONAL HELLOWORLD PROCEDURE =======
========== MY DYNAMIC ATTEMPT... =========
CREATE PROCEDURE OTDSTPR.SP0207 ( IN in_client_id char(12),
OUT SQLSTATE_OUT char(5),
OUT SQLCODE_OUT int )
RESULT SETS 1
LANGUAGE SQL
MODIFIES SQL DATA
COLLID OTDSTPR
WLM ENVIRONMENT DBSOTD00
ASUTIME LIMIT 50000
RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&172.16.7.109:*)'

P1: BEGIN
-- Declare variables
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;

DECLARE stmt VARCHAR(1000);
DECLARE c_clients CURSOR WITH RETURN TO CLIENT FOR s1;

SET stmt = 'SELECT CLIENT_ID, CLIENT_LONG_NM FROM OTIOTD.AF7 WHERE
CLIENT_ID = ?';
PREPARE s1 FROM stmt;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
FROM SYSIBM.SYSDUMMY1;

OPEN c_clients USING in_client_id;

SET SQLSTATE_OUT = SQLSTATE;
SET SQLCODE_OUT = SQLCODE;
END P1
========== END MY DYNAMIC ATTEMPT... =========

[Rhino]

"Todd Peterson" <to***********@dstoutput.com> wrote in message
news:c1**************************@posting.google.c om...

I'm a newbie to DB2 and am trying to figure out how to write a stored
procedure, using dynamic SQL statements to return a result set. I
believe the majority of the hurdles I have been facing might be due to
the fact that the samples and postings I have read have been related
to the UDB... I believe our company is on some version of MVS or
OS/390, but I am not sure which one, at the time of this posting.

I have an example, below, of a HelloWorld procedure that is currently
working in our environment, and would like to know how to convert it
to use a dynamic SQL statement. I have made a first attempt at
getting this to work and am also providing an example of that below.

Rather than dealing with the environmental issues I may be facing,
what I'm really hoping for is validation of the approach and syntax I
am using or guidance in getting a "Simple" HelloWorld with dynamic SQL
returning a result to work.

Be gentle on me... ;-) I am a Windows developer with Oracle and SQL
Server database experience. This MVS, OS/390 and DB2 environment is a
whole new world for me!

Thanks in advance for any assistance!
Todd

========== FUNCTIONAL HELLOWORLD PROCEDURE =========
CREATE PROCEDURE OTDSTPR.SP0200 ( IN in_client_id char(12),

OUT SQLSTATE_OUT char(5),
OUT SQLCODE_OUT int )
RESULT SETS 1
LANGUAGE SQL
MODIFIES SQL DATA
COLLID OTDSTPR
WLM ENVIRONMENT DBSOTD00
ASUTIME LIMIT 50000
RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&172.16.7.109:*)'

P1: BEGIN
-- Declare variables
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;

-- Declare Cursor
DECLARE myCursor CURSOR WITH RETURN FOR
SELECT DISTINCT
'HELLO WORLD', C.CLIENT_LONG_NM AS CLIENT
FROM CLIENT C
WHERE
(
C.CLIENT_ID = in_client_id
OR
in_client_id = 'ALL'
)
WITH UR;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
FROM SYSIBM.SYSDUMMY1;

open myCursor;

SET SQLSTATE_OUT = SQLSTATE;
SET SQLCODE_OUT = SQLCODE;

END P1
======= END FUNCTIONAL HELLOWORLD PROCEDURE =======
========== MY DYNAMIC ATTEMPT... =========
CREATE PROCEDURE OTDSTPR.SP0207 ( IN in_client_id char(12),
OUT SQLSTATE_OUT char(5),
OUT SQLCODE_OUT int )
RESULT SETS 1
LANGUAGE SQL
MODIFIES SQL DATA
COLLID OTDSTPR
WLM ENVIRONMENT DBSOTD00
ASUTIME LIMIT 50000
RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&172.16.7.109:*)'

P1: BEGIN
-- Declare variables
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;

DECLARE stmt VARCHAR(1000);
DECLARE c_clients CURSOR WITH RETURN TO CLIENT FOR s1;

SET stmt = 'SELECT CLIENT_ID, CLIENT_LONG_NM FROM OTIOTD.AF7 WHERE
CLIENT_ID = ?';
PREPARE s1 FROM stmt;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
FROM SYSIBM.SYSDUMMY1;

OPEN c_clients USING in_client_id;

SET SQLSTATE_OUT = SQLSTATE;
SET SQLCODE_OUT = SQLCODE;
END P1
========== END MY DYNAMIC ATTEMPT... =========

It is very difficult to teach programming techniques to someone via a
newgroup.

I think your best bet is to determine exactly what version of DB2 you are
using and which OS you are on, then read the Application Development Guide
for that version and platform. The Application Development Guide, in
conjunction with the SQL Reference, should tell you enough to get you going
in the right direction. Also, consult the examples that came with your copy
of DB2. You should find examples of what you are trying to do in the Samples
directory, most of which are explained in the same two manuals, Application
Development Guide and SQL Reference.

The manuals for DB2 on z/OS (the new name for MVS and OS/390) and DB2 on
Linux, Unix and Windows, can be found at:
http://www-306.ibm.com/software/data...nfocenters.jsp.
You should be able to find the manuals there. The manuals should indicate
exactly where the sample programs are too.

The other alternative I can suggest is that you take DB2 courses from either
IBM or other vendors. You should be able to find courses in your area that
cover development of stored procedures for your platform. It will cost you
(or your management) some money but you'll probably learn the concept and
techniques better than if you try to teach yourself from the manuals.

Rhino