CODEWITHSUNDEEP
stored-proceduresdb2user-defined-functions
michael-mammut
michael-mammut

Reputation: 2783

How to use temp Tables in a DB2 Function

I've to write some MsSQL procedures and functions into DB2 procedures and functions. Now I've the problem, that i can not use a temp table in a function with begin atomic.

Is there an other method to use a temp table in DB2 functions?

CREATE OR REPLACE FUNCTION abc( )
RETURNS TABLE (test INTEGER) 
LANGUAGE SQL
MODIFIES SQL DATA
NO EXTERNAL ACTION
NOT DETERMINISTIC
BEGIN atomic
DECLARE GLOBAL TEMPORARY TABLE  SESSION.StringParts (indexNumber  int, stringPart   nvarchar(4000)) ON COMMIT DELETE ROWS NOT LOGGED WITH REPLACE;
END

Upvotes: 0

Views: 464

Answers (1)

mao
mao

Reputation: 12267

Db2-LUW up to (and including) version 11.5 has limitations on usage of DGTT (Declare Global Temporary Table) in RETURNS TABLE functions, according to the documentation.

You cannot use being atomic with declare global temporary table (i.e. that statement is not supported currently in Compound SQL(inlined) blocks).

If you use MODIFIES SQL DATA then it has limitations on Compound SQL(compiled) for table functions, it works for scalar functions.

It may be better to use SQL PL stored procedures instead of table functions in this case.

You can also circumvent some limitations if you use pipelined functions, and CGTTs can help in some cases.

Upvotes: 1

Related Questions