Parameterized ResultSet return to Java

achdumeingute · #1 (**permalink**) 01-24-12, 16:51

Hello,

Im running DB2 v9.5.700.579 on AIX 6.1 SP4

I'm very new to DB2. I also can't use the search feature of this forum (i get no results), so my apologies if something like this has been asked already.

Can I do something like this:

Code:

CREATE PROCEDURE PROCEDURE1 
(IN parm INT  )
	DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure 
------------------------------------------------------------------------

P1: BEGIN
IF parm = 1 THEN
 SELECT * FROM schema.tableA;
END IF;
 
IF parm = 2 THEN
SELECT * FROM schema.tableB;
END IF;

END P1

How do I add the cursors to return the result set? I have tried giving each SELECT their own "DECLARE cursorX CURSOR WITH RETURN FOR" statement and opening them both at the end, and repeating the same cursor in each block hoping it would resove it at "run time." Both give me the unexpected token error SQLCODE=-104, SQLSTATE=42601.

If you know a good example on the web of what I am trying to do I'd really appreciate the link.

Thanks in advance.

n_i · #2 (**permalink**) 01-24-12, 17:45

You cannot "add cursors". Either compose your statement dynamically or insert data into a temporary table then return a cursor over that table.

achdumeingute · #3 (**permalink**) 01-24-12, 19:23

I was just trying to get the syntax to wrap the entire thing in a cursor or each select in a cursor. In the end, I've gotten the temp table example to work.

Code:

CREATE PROCEDURE PROCEDURE1 
(IN parm INT  )
	DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure 
------------------------------------------------------------------------
P1: BEGIN

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMPVALS (
COL1 INTEGER
) NOT LOGGED;

IF parm = 0 THEN
 	INSERT INTO SESSION.TEMPVALS (SELECT id FROM schema.tableA);
END IF;


IF parm = 1 THEN
 	INSERT INTO SESSION.TEMPVALS (SELECT id FROM schema.tableB);
END IF;
 
IF parm = 2 THEN
	INSERT INTO SESSION.TEMPVALS (SELECT id FROM schema.tableC);
END IF;

begin
 -- Declare cursor  
	 DECLARE cursor1 CURSOR WITH RETURN FOR     
	 SELECT col1 FROM SESSION.TEMPVALS;	 
	 -- Cursor left open for client application
	OPEN cursor1;
end;

END P1

I'm not a big fan of what I've done here, but it gets me what I want. Deadlines and lack of knowledge about a subject are not fun

Thank you for your response!

tonkuma · #4 (**permalink**) 01-24-12, 22:12

I thought that using UNION ALL might be better, like...

Code:

   DECLARE cursor1 CURSOR WITH RETURN FOR
   SELECT id FROM schema.tableA
   WHERE parm = 0
   UNION ALL
   SELECT id FROM schema.tableB
   WHERE parm = 1
   UNION ALL
   SELECT id FROM schema.tableC
   WHERE parm = 2
   ;

More to say,
it must be better to incorporate directly such simple query into Java code(or any host programs) than to create a procedure.

If same query was used in many programs, create a view for the query, like...

Example 1:

Code:

CREATE OR REPLACE VIEW get_id_by_parm AS
SELECT id , parm
 FROM  schema.tableA
     , (SELECT 0 FROM sysibm.sysdummy1) p(parm)
UNION ALL
SELECT id , parm
 FROM  schema.tableB
     , (SELECT 1 FROM sysibm.sysdummy1) p(parm)
UNION ALL
SELECT id , parm
 FROM  schema.tableC
　    , (SELECT 2 FROM sysibm.sysdummy1) p(parm)
;

The view in Example 1 worked efficiently on my DB2 9.7.5 on Windows/XP,
if a value for parm was hardcoded.
(Only the schema.tableB was accessed, if "parm = 1" was specified.)

Code:

------------------------------ Commands Entered ------------------------------
SELECT id
 FROM  get_id_by_parm
 WHERE parm = 1
;
------------------------------------------------------------------------------

ID         
-----------
        201

  1 record(s) selected.

Extracted from output of db2exfmt

Code:

Original Statement:
------------------
SELECT id
FROM get_id_by_parm
WHERE parm = 1


Optimized Statement:
-------------------
SELECT Q1.ID AS "ID"
FROM SCHEMA.TABLEB AS Q1

Access Plan:
-----------
        Total Cost:             0.0295303
        Query Degree:           1

        Rows
       RETURN
       (   1)
        Cost
         I/O
         |
          1
       IXSCAN
       (   2)
      0.0295303
          0
         |
          1
   INDEX: SYSIBM
 SQL120125114122000
         Q1

Test data was created by the following:

Code:

------------------------------ Commands Entered ------------------------------
CREATE TABLE schema.tableA
( id   INTEGER     NOT NULL PRIMARY KEY
, desc VARCHAR(30)
);

CREATE TABLE schema.tableB
( id   INTEGER     NOT NULL PRIMARY KEY
, desc VARCHAR(30)
);

CREATE TABLE schema.tableC
( id   INTEGER     NOT NULL PRIMARY KEY
, desc VARCHAR(30)
);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
INSERT INTO schema.tableA
( id )
VALUES  101 , 102 , 103
;

INSERT INTO schema.tableB
( id )
VALUES  201
;
   
INSERT INTO schema.tableC
( id )
VALUES  301 , 302 , 303 , 304 , 305
;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

ARWinner · #5 (**permalink**) 01-25-12, 09:57

This will work also:

Code:

CREATE PROCEDURE PROCEDURE1 
(IN parm INT  )
	DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure 
------------------------------------------------------------------------

P1: BEGIN
   DECLARE CURSOR1 CURSOR WITH RETURN FOR 
   SELECT * FROM schema.tableA;

   DECLARE CURSOR2 CURSOR WITH RETURN FOR 
   SELECT * FROM schema.tableB;

   IF parm = 1 THEN 
      OPEN CURSOR1;
   END IF;
 
   IF parm = 2 THEN 
      OPEN CURSOR2;
   END IF;

END P1

Andy

Internet Services

Web Development

Digital Marketing

Consumer Tech