DB2 procedure return tables

Question

Hi I have this procedure:

CREATE PROCEDURE TEST() 
LANGUAGE SQL
Specific TEST
BEGIN

DECLARE V_TEST VARCHAR(100);
DECLARE V_TEST_EXT VARCHAR(100);
DECLARE SQLCODE INTEGER DEFAULT -1;
DECLARE RET_CODE INTEGER DEFAULT -2;
DECLARE LIST_CMD VARCHAR(512);
DECLARE LIST_CMD_EXT VARCHAR(512);

DECLARE CUR_TEST CURSOR WITH RETURN FOR LIST_STMT;
DECLARE CUR_TEST_EXT CURSOR WITH RETURN FOR LIST_EXT_STMT;

DECLARE CONTINUE HANDLER FOR SQLEXECPTION SET RET_CODE = SQLCODE;

DECLARE global temporary table temptbl (testres varchar(512)) with replace not logged on commit preserve rows;

SET LIST_CMD = 'SELECT TEST FROM TESTTAB';
PREPARE LIST_STMT FROM LIST_CMD;

OPEN CUR_TEST;
FETCH CUR_TEST INTO V_TEST;
WHILE (RET_CODE <> 100) DO
insert into temptbl(testres) values V_TEST;
FETCH CUR_TEST INTO V_TEST;
END WHILE;
CLOSE CUR_TEST;

SET RET_CODE=-2;
SET LIST_CMD_EXT = 'SELECT TEST FROM TESTTAB';
PREPARE LIST_STMT_EXT FROM LIST_CMD_EXT;

OPEN CUR_TEST_EXT;
FETCH CUR_TEST_EXT INTO V_TEST_EXT;
WHILE (RET_CODE <> 100) DO
FETCH CUR_TEST_EXT INTO V_TEST_EXT;
END WHILE;
CLOSE CUR_TEST_EXT;

END;

It does compile but when I call it it results in an endless loop not doing anything. How can I get this to work and how can I do it for both cursors? (each in it's own temporary table)

Any ideas? Thank you for all your help.

I am running DB2 10.5 on Windows.

Mark Barinstein · Accepted Answer · 2018-10-23T12:23:10.677

0

Try this:

--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE TEST ()
BEGIN
  DECLARE L_TABNAME VARCHAR(128);
  DECLARE SQLSTATE CHAR(5);
  DECLARE C1 CURSOR FOR SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA=USER;

  DECLARE GLOBAL TEMPORARY TABLE SESSION.TABLES (TABNAME VARCHAR(128)) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
  OPEN C1;
  L1: LOOP
    FETCH C1 INTO L_TABNAME;
    IF (SQLSTATE='02000') THEN LEAVE L1; END IF;
    INSERT INTO SESSION.TABLES (TABNAME) VALUES L_TABNAME;
  END LOOP L1;
  CLOSE C1;

END@

CALL TEST@
SELECT COUNT(1) FROM SESSION.TABLES@

edited Oct 23 '18 at 12:23

answered Oct 23 '18 at 11:56

Mark Barinstein

11,456
2
8
16

I need to insert the values of the cursor. Any idea on how to do that? – Viking Oct 23 '18 at 12:06
Look at the amended version. – Mark Barinstein Oct 23 '18 at 12:24
It still is an endless loop – Viking Oct 23 '18 at 12:46
Yes I did and it compiles but then once started never ends running – Viking Oct 23 '18 at 12:53
Strange enough. This code works fine on my db2 10.5 and must work on any db2. – Mark Barinstein Oct 23 '18 at 13:43
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/182363/discussion-between-mark-barinstein-and-viking). – Mark Barinstein Oct 23 '18 at 13:47
I got it to run.... but when selecting the result table I get that it is not defined undefinied name – Viking Oct 23 '18 at 13:55

DB2 procedure return tables

1 Answers1