By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,977 Members | 1,352 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,977 IT Pros & Developers. It's quick & easy.

RETURNS TABLE in DB2

P: n/a
Hi,

I have a following issue in DB2 UDB v8.2. How can I build some data and
store it in a temp table and return as a TABLE to a function.. This
function will be called in a SELECT stmt. something like
SELECT .... FROM TABLE(GetRecursiveCDOs()) AS RecursiveCDOs()

Here is the code: The logic is as follows.When I tried to compile the
code, it errors out at SQLCODE. Is SQLCODE permissable in this context
??

CREATE FUNCTION GetRecursiveCDOs()
RETURNS TABLE(CDODefId INTEGER)
BEGIN ATOMIC
--
DECLARE SQLCODE INTEGER;
DECLARE v_SQLStmt VARCHAR(512);
DECLARE n_CDODefId INTEGER;
DECLARE CDODefs_Cur CURSOR WITH HOLD FOR CDODefsSQL;
DECLARE GLOBAL TEMPORARY TABLE gt_CDODefs(CDODefId INTEGER NOT
NULL) ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
--
SET SQLCODE = 0;
--
SET v_SQLStmt = 'WITH RecursiveCDOs (CDODefId) AS'||
'( SELECT Parent.CDODefId'||
' FROM CDODefinition Parent'||
' WHERE Parent.CDODefId IN ( 1020, 1050 )'||
' UNION ALL'||
' SELECT Child.CDODefId'||
' FROM RecursiveCDOs Parent'||
' ,CDODefinition Child'||
' WHERE Child.ParentCDOId = Parent.CDODefId'||
') SELECT CDODefId FROM RecursiveCDOs';
--
DELETE FROM SESSION.gt_CDODefs;
COMMIT;
--
PREPARE CDODefsSQL FROM v_SQLStmt;
OPEN CDODefs_Cur;
FETCH FROM CDODefs_Cur INTO n_CDODefId;
--
WHILE (SQLCODE = 0) DO
--
INSERT INTO SESSION.gt_CDODefs (CDODefId)
VALUES (n_CDODefId);
--
END WHILE;
--
CLOSE CDODefs_Cur;
--
RETURN SELECT CDODefId
FROM SESSION.gt_CDODefs;
END;

I have tried using a view with just the WITH statement. But our
application will drop the table inside of the view and recreates the
table. The view becomes inoperable because the table was dropped. At
present there is no mechanism to recompile the view. If I create a
function RETURNING TABLE, the table cannot be dropped and the
application hangs. As a work around, the SELECT statement is passed in
as literal to be opened as a cursor and then ith is stored in a GTT and
then query the GTT thus RETURNING the TABLE.

Is there a better way to approach this solution ??

Any ideas will be appreciated...

Thanks,
Purush

Dec 8 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
pu********@gmail.com wrote:
Hi,

I have a following issue in DB2 UDB v8.2. How can I build some data and
store it in a temp table and return as a TABLE to a function.. This
function will be called in a SELECT stmt. something like
SELECT .... FROM TABLE(GetRecursiveCDOs()) AS RecursiveCDOs()

Here is the code: The logic is as follows.When I tried to compile the
code, it errors out at SQLCODE. Is SQLCODE permissable in this context
??

CREATE FUNCTION GetRecursiveCDOs()
RETURNS TABLE(CDODefId INTEGER)
BEGIN ATOMIC
--
DECLARE SQLCODE INTEGER;
DECLARE v_SQLStmt VARCHAR(512);
DECLARE n_CDODefId INTEGER;
DECLARE CDODefs_Cur CURSOR WITH HOLD FOR CDODefsSQL;
DECLARE GLOBAL TEMPORARY TABLE gt_CDODefs(CDODefId INTEGER NOT
NULL) ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
--
SET SQLCODE = 0;
--
SET v_SQLStmt = 'WITH RecursiveCDOs (CDODefId) AS'||
'( SELECT Parent.CDODefId'||
' FROM CDODefinition Parent'||
' WHERE Parent.CDODefId IN ( 1020, 1050 )'||
' UNION ALL'||
' SELECT Child.CDODefId'||
' FROM RecursiveCDOs Parent'||
' ,CDODefinition Child'||
' WHERE Child.ParentCDOId = Parent.CDODefId'||
') SELECT CDODefId FROM RecursiveCDOs';
--
DELETE FROM SESSION.gt_CDODefs;
COMMIT;
--
PREPARE CDODefsSQL FROM v_SQLStmt;
OPEN CDODefs_Cur;
FETCH FROM CDODefs_Cur INTO n_CDODefId;
--
WHILE (SQLCODE = 0) DO
--
INSERT INTO SESSION.gt_CDODefs (CDODefId)
VALUES (n_CDODefId);
--
END WHILE;
--
CLOSE CDODefs_Cur;
--
RETURN SELECT CDODefId
FROM SESSION.gt_CDODefs;
END;

I have tried using a view with just the WITH statement. But our
application will drop the table inside of the view and recreates the
table. The view becomes inoperable because the table was dropped. At
present there is no mechanism to recompile the view. If I create a
function RETURNING TABLE, the table cannot be dropped and the
application hangs. As a work around, the SELECT statement is passed in
as literal to be opened as a cursor and then ith is stored in a GTT and
then query the GTT thus RETURNING the TABLE.

Like swimming in quick sand.. Everytime you move you go deeper...
What about starting at the other end:
Why does your application drop and recreate tables?

SQL Functions in DB2 UDB are essentially macros.
To do anything complex (like dynamic SQL and DDL!) with them you must
use the CALL statement.
Having said that this works great for SQL Scalar functions, but for
table functions you have to problem of linking the result set of teh
proc with the result of the table function. No joy.

So what you would have to do is use an external table function (C, JAVA,
CLR)

Anyway.. fix you orginal problem. Applications don't do DDL!
DBA's do DDL.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Dec 8 '05 #2

P: n/a
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3v*************@individual.net...
Anyway.. fix you orginal problem. Applications don't do DDL!
DBA's do DDL.
Cheers
Serge
--
Serge Rielau


Some applications create tables, such a separate audit table for each day.
Dec 8 '05 #3

P: n/a
Serge,

Thanks for the reply.

Here is the reason why we need to drop and recreate tables in DB2?
This application is a metadata repository and allows users to update
that metadata. User might create new columns or drop a metadata table.
The changes in the metadata needs to be reflected into a main database
which will contain other tables too. To do this, we need to create,
drop and populate the data. Users does not need to be connected to the
main database. Just a small footprint of the main database is necessary
on the user machine and user can do changes any time and when ready,
apply the changes to the main DB. There might more than one way to
approach this solution. But, at present, we need to resolve the current
issue and think later about changing the way the application works.

I was confident that DB2 had some thing in the store for this issue. It
tried a similar logic in Oracle and SQL Server. It works like a charm.
Now, just for DB2, I need to think of a External Table function for
DB2. I was hoping to have a FUNCTION based solution for the above issue
for all the 3 DB's. Now, I need to do some coding in Java/C/.NET to
make it work in DB2!!! more maintainance of the application!!!

Thanks,
Purush

Thanks,
Purush

Dec 8 '05 #4

P: n/a
pu********@gmail.com wrote:
Serge,

Thanks for the reply.

Here is the reason why we need to drop and recreate tables in DB2?
This application is a metadata repository and allows users to update
that metadata. User might create new columns or drop a metadata table.
The changes in the metadata needs to be reflected into a main database
which will contain other tables too. To do this, we need to create,
drop and populate the data. Users does not need to be connected to the
main database. Just a small footprint of the main database is necessary
on the user machine and user can do changes any time and when ready,
apply the changes to the main DB. There might more than one way to
approach this solution. But, at present, we need to resolve the current
issue and think later about changing the way the application works.

I was confident that DB2 had some thing in the store for this issue. It
tried a similar logic in Oracle and SQL Server. It works like a charm.
Now, just for DB2, I need to think of a External Table function for
DB2. I was hoping to have a FUNCTION based solution for the above issue
for all the 3 DB's. Now, I need to do some coding in Java/C/.NET to
make it work in DB2!!! more maintainance of the application!!!

What about recreating the view. Note that the view text will still be in
the catalog, it's just marked as invalid.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Dec 8 '05 #5

P: n/a
Why didn't you use a simple RETURN statement for the Function Body?
Like this:
CREATE FUNCTION GetRecursiveCDOs()
RETURNS TABLE(CDODefId INTEGER)
READS SQL DATA
RETURN
WITH RecursiveCDOs (CDODefId) AS (
SELECT Parent.CDODefId
FROM CDODefinition Parent
WHERE Parent.CDODefId IN ( 1020, 1050 )
UNION ALL
SELECT Child.CDODefId
FROM RecursiveCDOs Parent
, CDODefinition Child
WHERE Child.ParentCDOId = Parent.CDODefId
)
SELECT CDODefId FROM RecursiveCDOs'
;

Another point:
When you use the function, you should write a column name for
correlation name or remove "()" from correlation name.
SELECT CDODefChain
FROM TABLE(GetRecursiveCDOs()) AS RecursiveCDOs(CDODefChain);
or
SELECT CDODefId
FROM TABLE(GetRecursiveCDOs()) AS RecursiveCDOs;

Dec 10 '05 #6

P: n/a
I made mistake.
I should remove a quotation from last SELECT of the function

CREATE FUNCTION ...
....
....
SELECT CDODefId FROM RecursiveCDOs
;

Dec 10 '05 #7

P: n/a
You might want to get a copy of TREES & HIERARCHIES IN SQL. For
example, the nested sets model does not require procedural code at all
to model a tree structure.

Dec 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.