Error while defining UDF

rpkulkarni · #1 (**permalink**) 11-28-09, 07:21

I am really new to DB2 and UDFs. I am facing compilation error on DB2 9.5 for windows at While statement in defining UDF.

Code:

	SET LIMIT = ( SELECT COUNT(*) FROM COLUMN_LIST);
	set index = 1;

	WHILE ( INDEX < LIMIT + 1) DO
		SET ARRAY_REP[INDEX] = 0;
		SET INDEX = INDEX + 1;
	END WHILE;

I read in other thread that suppoerted SQL statements in UDF are

HTML Code:

The following list of SQL-control-statements can be used within the dynamic compound statement:

* FOR Statement
* GET DIAGNOSTICS Statement
* IF Statement
* ITERATE Statement
* LEAVE Statement
* SIGNAL Statement
* WHILE Statement 

The SQL statements that can be issued are:

* fullselect (A common-table-expression can precede the fullselect.)
* Searched UPDATE
* Searched DELETE
* INSERT
* SET variable statement

Then What error I am doing in my code?
Please advise.

tonkuma · #2 (**permalink**) 11-28-09, 08:15

What error message(code and text) did you get?
You would find some more specific information in the message text.

tonkuma · #3 (**permalink**) 11-28-09, 09:00

You can't use ARRAY in an UDF on DB2 9.5 for LUW,
according to the manual "DB2 Version 9.5 for Linux, UNIX, and Windows SQL Reference, Volume 2 Updated March, 2008 SC23-5862-01".

ARRAY in an UDF is supported on DB2 9.7 for LUW, with some restrictions.

Quote:

CREATE FUNCTION (SQL scalar, table, or row)
.....
.....
.....
Notes:
.....
4
The compound SQL (compiled) statement is supported only for an
SQL-function-body in an SQL scalar function definition in a non-partitioned
database. It is not supported for SQL table function definitions or in
partitioned database environments.

Internet Services

Web Development

Digital Marketing

Consumer Tech