DB2 Stored Procedures Hekp Needed

Database Developers,

I have just started working on DB2 which the syntax is a lot different from traditional SQL engines. I am trying to create a SQL stored procedure for generating reports. I want to use temporary database which in this example it is truly not needed but due to past experience it is key to developing good reports for client/server application.

Here is my example which of course does not work and documentation on the Internet is limited about DB2. Please if anyone can clarify these scripts please let me know.

CREATE PROCEDURE LDBS_SP_SEL_MYTABLE
( IN BeginDate DATE, IN EndDate DATE )
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
BEGIN
DECLARE GLOBAL TEMPORARY TABLE myResults AS
(SELECT
MYTABLE.LAST_NAME,
MYTABLE.FIRST_NAME,
MYTABLE.BIRTH_DATE
FROM ADMINISTRATOR.MYTABLE AS MYTABLE
WHERE MYTABLE.BIRTH_DATE >= BeginDate
AND MYTABLE.BIRTH_DATE < EndDate)
DEFINITION ONLY NOT LOGGED;
BEGIN
--Declare client cursor
DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
SELECT
LAST_NAME,
FIRST_NAME,
BIRTH_DATE
FROM SESSION.myResults;
--Cursor left open for client application
OPEN cursor1;
END;
END

Also I tried to use the INSERT INTO which is common usage on other SQL engines.

BEGIN
DECLARE GLOBAL TEMPORARY TABLE myResults
LIKE ADMINISTRATOR.MYTABLE
ON COMMIT DELETE ROWS
NOT LOGGED
IN USERTEMPSPACE1;
INSERT INTO SESSION.myResults
SELECT
MYTABLE.LAST_NAME,
MYTABLE.FIRST_NAME,
MYTABLE.BIRTH_DATE
FROM ADMINISTRATOR.MYTABLE AS MYTABLE
WHERE MYTABLE.BIRTH_DATE >= BeginDate
AND MYTABLE.BIRTH_DATE < EndDate;
END

Also on the second code example I tried to show the result and I am not sure what is right. Personally and with other SQL engines I do not have to use cursors which I can just select my result set.

-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT
MYRESULTS.LAST_NAME,
MYRESULTS.FIRST_NAME,
MYRESULTS.BIRTH_DATE
FROM SESSION.myResults AS MYRESULTS;
-- Cursor left open for client application
OPEN cursor1;

Or just

SELECT * FROM SESSION.myResults;

Both of these code blocks come up with errors. I have to develop a standard for returning data which is readable in SQL format. What I want to accomplish is populating a temporary table and then sending the results to the client. I would appreciate anyone’s input and wisdom.

Best Regards,

Bob Zagars

The documentation is out there, but it is hard to find

Try this:
http://www-306.ibm.com/software/dat.../v8/stpUDF.html