What's incorrect with this function?

[thborges]

I tried create this function with DB2 Express and received the error
message:

-104
Thiago.FUNCTION1: 9: [IBM][CLI Driver][DB2/NT] SQL0104N An inexpected
token "TABLE SESSION.TESTE(C1 INTEGER) ON CO" was found following
"ARE GLOBAL TEMPORARY". Expected tokens may include: "<space>". LINE
NUMBER=9. SQLSTATE=42601
CREATE FUNCTION THIAGO.FUNCTION1( )
RETURNS TABLE (C1 INTEGER)
------------------------------------------------------------------------
-- SQL UDF (Table)
------------------------------------------------------------------------
MODIFIES SQL DATA
BEGIN ATOMIC
DECLARE GLOBAL TEMPORARY TABLE SESSION.TESTE(C1 INTEGER)
ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;

insert into SESSION.TESTE select count(*) from SYSCAT.FUNCTIONS
RETURN SELECT C1 from SESSION.TESTE
END

Is some incorrect?

Thanks

[Eugene F]

Get rid of SESSION qualifier in the temp table declaration, it's only
required when referencing a temp table.

-Eugene

[Serge Rielau]

th******@gmail.com wrote:

I tried create this function with DB2 Express and received the error
message:

-104
Thiago.FUNCTION1: 9: [IBM][CLI Driver][DB2/NT] SQL0104N An inexpected
token "TABLE SESSION.TESTE(C1 INTEGER) ON CO" was found following
"ARE GLOBAL TEMPORARY". Expected tokens may include: "<space>". LINE
NUMBER=9. SQLSTATE=42601
CREATE FUNCTION THIAGO.FUNCTION1( )
RETURNS TABLE (C1 INTEGER)
------------------------------------------------------------------------
-- SQL UDF (Table)
------------------------------------------------------------------------
MODIFIES SQL DATA
BEGIN ATOMIC
DECLARE GLOBAL TEMPORARY TABLE SESSION.TESTE(C1 INTEGER)
ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;

insert into SESSION.TESTE select count(*) from SYSCAT.FUNCTIONS
RETURN SELECT C1 from SESSION.TESTE
END

Is some incorrect?

SQL Functions use "inline SQL PL" which is the subset of SQL PL
supported by "compound statement(dynamic)". DDL of any shape is NOT
supported. "inline" means that the function is expanded into the query
like a view. Imagine trying to do DDL in a view :-)

In general to extend the capabilities of SQL functions use the CALL
statement in the function and place all the heavy lifting into the
called procedure.

Note though that you can't catch a resultset from the call.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[thborges]

I'm migrating a Application from the Firebird Database to DB2 and I
need of some resource for migrating complex Stored Procedures that
transform values from tables in database and create a RecordSet return.

With SQL Functions in Java/C this is possible?

[Serge Rielau]

th******@gmail.com wrote:

I'm migrating a Application from the Firebird Database to DB2 and I
need of some resource for migrating complex Stored Procedures that
transform values from tables in database and create a RecordSet return.

With SQL Functions in Java/C this is possible?

DB2 supports SQL procedures as well as C-Procedures.
If you had procedures before that may be the easiest.
Table functions (C/JAVA and to some extend SQL) are also available.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Knut Stolze]

th******@gmail.com wrote:

CREATE FUNCTION THIAGO.FUNCTION1( )
RETURNS TABLE (C1 INTEGER)
------------------------------------------------------------------------
-- SQL UDF (Table)
------------------------------------------------------------------------
MODIFIES SQL DATA
BEGIN ATOMIC
DECLARE GLOBAL TEMPORARY TABLE SESSION.TESTE(C1 INTEGER)
ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;

insert into SESSION.TESTE select count(*) from SYSCAT.FUNCTIONS
RETURN SELECT C1 from SESSION.TESTE
END

Serge told you the "why" this is not working. Here is a simple way to
convert the above into something working:

CREATE FUNCTION THIAGO.FUNCTION1( )
RETURNS TABLE (C1 INTEGER)
------------------------------------------------------------------------
-- SQL UDF (Table)
------------------------------------------------------------------------
RETURN select count(*) from SYSCAT.FUNCTIONS
Depending on your actual logic, I'm pretty confident that you will be able
to solve most of these things with non-procedural SQL.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[thborges]

Stolze,

The real procedure I need build is more complex. Envolve many
computations and verifications. I sent an example of my necessity.

I see this sample in the ibm public help:

CREATE FUNCTION RANK(N INTEGER)
RETURNS TABLE(
POSITION INTEGER,
EMPNO CHAR(6),
FIRSTNME CHAR(20),
LASTNAME CHAR(20),
SALARY DECIMAL(13,2)
)
LANGUAGE SQL
DISALLOW PARALLEL
MODIFIES SQL DATA
NOT FENCED
BEGIN
DECLARE LAST_SALARY DEC(13,2) DEFAULT 0;
DECLARE I INTEGER DEFAULT 1;
DECLARE STMT VARCHAR(255);
DECLARE TABLE_ALREADY_EXISTS CONDITION FOR '42710'; 1
DECLARE CONTINUE HANDLER FOR TABLE_ALREADY_EXISTS 2
DELETE FROM SESSION.RETURN_TBL;
DECLARE GLOBAL TEMPORARY TABLE SESSION.RETURN_TBL ( 3
POSITION INTEGER NOT NULL,
EMPNO CHAR(6) NOT NULL,
FIRSTNME CHAR(20) NOT NULL,
LASTNAME CHAR(20) NOT NULL,
SALARY DECIMAL(13,2) NOT NULL);

FOR_LOOP: FOR EACH_ROW AS C1 CURSOR FOR
SELECT EMPNO, FIRSTNME, LASTNAME, SALARY
FROM SAMPLEDB01.EMPLOYEE ORDER BY SALARY DESC DO

IF (I > N) AND (EACH_ROW.SALARY < LAST_SALARY) THEN
LEAVE FOR_LOOP;
ELSE
SET LAST_SALARY = EACH_ROW.SALARY;
END IF;

INSERT INTO SESSION.RETURN_TBL
VALUES ( I, EACH_ROW.EMPNO, EACH_ROW.FIRSTNME,
EACH_ROW.LASTNAME, EACH_ROW.SALARY);
SET I = I + 1;
END FOR;

RETURN
SELECT POSITION, EMPNO, FIRSTNME, LASTNAME, SALARY
FROM SESSION.RETURN_TBL;
END;

But I can't create it in DB2 Express edition.
In other versions of DB2 this work proper?
I need some like it.

Thanks

[Serge Rielau]

th******@gmail.com wrote:

Stolze,

The real procedure I need build is more complex. Envolve many
computations and verifications. I sent an example of my necessity.

I see this sample in the ibm public help:

CREATE FUNCTION RANK(N INTEGER)
RETURNS TABLE(
POSITION INTEGER,
EMPNO CHAR(6),
FIRSTNME CHAR(20),
LASTNAME CHAR(20),
SALARY DECIMAL(13,2)
)
LANGUAGE SQL
DISALLOW PARALLEL
MODIFIES SQL DATA
NOT FENCED
BEGIN
DECLARE LAST_SALARY DEC(13,2) DEFAULT 0;
DECLARE I INTEGER DEFAULT 1;
DECLARE STMT VARCHAR(255);
DECLARE TABLE_ALREADY_EXISTS CONDITION FOR '42710'; 1
DECLARE CONTINUE HANDLER FOR TABLE_ALREADY_EXISTS 2
DELETE FROM SESSION.RETURN_TBL;
DECLARE GLOBAL TEMPORARY TABLE SESSION.RETURN_TBL ( 3
POSITION INTEGER NOT NULL,
EMPNO CHAR(6) NOT NULL,
FIRSTNME CHAR(20) NOT NULL,
LASTNAME CHAR(20) NOT NULL,
SALARY DECIMAL(13,2) NOT NULL);

FOR_LOOP: FOR EACH_ROW AS C1 CURSOR FOR
SELECT EMPNO, FIRSTNME, LASTNAME, SALARY
FROM SAMPLEDB01.EMPLOYEE ORDER BY SALARY DESC DO

IF (I > N) AND (EACH_ROW.SALARY < LAST_SALARY) THEN
LEAVE FOR_LOOP;
ELSE
SET LAST_SALARY = EACH_ROW.SALARY;
END IF;

INSERT INTO SESSION.RETURN_TBL
VALUES ( I, EACH_ROW.EMPNO, EACH_ROW.FIRSTNME,
EACH_ROW.LASTNAME, EACH_ROW.SALARY);
SET I = I + 1;
END FOR;

RETURN
SELECT POSITION, EMPNO, FIRSTNME, LASTNAME, SALARY
FROM SESSION.RETURN_TBL;
END;

But I can't create it in DB2 Express edition.
In other versions of DB2 this work proper?
I need some like it.

This for sure won't work on any DB2 for LUW.
And AFAIK DB2 for LUW is the only platform that allows MODIFIES SQL DATA
in table functions to begin with.
Would you mind posting a link to the page where you find tis example?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[thborges]

I found it in this redbook:
http://www.redbooks.ibm.com/abstracts/sg246503.html
PDF Page: 442

Thanks

[Serge Rielau]

th******@gmail.com wrote:

I found it in this redbook:
http://www.redbooks.ibm.com/abstracts/sg246503.html
PDF Page: 442

Thanks

Interesting. Appears DB2 for iSeries is ahead here.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Gert van der Kooij]

In article <11**********************@i39g2000cwa.googlegroups .com>,
th******@gmail.com says...

I found it in this redbook:
http://www.redbooks.ibm.com/abstracts/sg246503.html
PDF Page: 442

Thanks

That's the problem.
This redbook is titled:
Stored Procedures, Triggers and User Defined Functions on DB2 Universal
Database for >>>> iSeries <<<<

Examples from this book might work on DB2 for Linux, Windows or Unix but
they are not written for it.

[Knut Stolze]

th******@gmail.com wrote:

Stolze,

The real procedure I need build is more complex. Envolve many
computations and verifications. I sent an example of my necessity.

I see this sample in the ibm public help:

CREATE FUNCTION RANK(N INTEGER)
RETURNS TABLE(
POSITION INTEGER,
EMPNO CHAR(6),
FIRSTNME CHAR(20),
LASTNAME CHAR(20),
SALARY DECIMAL(13,2)
)
LANGUAGE SQL
DISALLOW PARALLEL
MODIFIES SQL DATA
NOT FENCED
BEGIN
DECLARE LAST_SALARY DEC(13,2) DEFAULT 0;
DECLARE I INTEGER DEFAULT 1;
DECLARE STMT VARCHAR(255);
DECLARE TABLE_ALREADY_EXISTS CONDITION FOR '42710'; 1
DECLARE CONTINUE HANDLER FOR TABLE_ALREADY_EXISTS 2
DELETE FROM SESSION.RETURN_TBL;
DECLARE GLOBAL TEMPORARY TABLE SESSION.RETURN_TBL ( 3
POSITION INTEGER NOT NULL,
EMPNO CHAR(6) NOT NULL,
FIRSTNME CHAR(20) NOT NULL,
LASTNAME CHAR(20) NOT NULL,
SALARY DECIMAL(13,2) NOT NULL);

FOR_LOOP: FOR EACH_ROW AS C1 CURSOR FOR
SELECT EMPNO, FIRSTNME, LASTNAME, SALARY
FROM SAMPLEDB01.EMPLOYEE ORDER BY SALARY DESC DO

IF (I > N) AND (EACH_ROW.SALARY < LAST_SALARY) THEN
LEAVE FOR_LOOP;
ELSE
SET LAST_SALARY = EACH_ROW.SALARY;
END IF;

INSERT INTO SESSION.RETURN_TBL
VALUES ( I, EACH_ROW.EMPNO, EACH_ROW.FIRSTNME,
EACH_ROW.LASTNAME, EACH_ROW.SALARY);
SET I = I + 1;
END FOR;

RETURN
SELECT POSITION, EMPNO, FIRSTNME, LASTNAME, SALARY
FROM SESSION.RETURN_TBL;
END;

If I get this right, then you select some rows from the EMPLOYEE table,
insert into into a temp table and then return the stuff in the temp table.
At the same time, the parameter N limits the number of rows processed. Is
that right? If so, the following should do something along those lines:

CREATE FUNCTION rank(n INTEGER)
RETURNS TABLE (
position INTEGER,
empno CHAR(6),
firstnme CHAR(20),
lastname CHAR(20),
salary DECIMAL(13, 2) )
LANGUAGE SQL
RETURN
SELECT *
FROM ( SELECT row_number() OVER ( ORDER BY salary DESC
AS rn,
empno, firstnmo, lastname, salary
FROM sampledb01.employee ) AS t
WHERE rn < n

Ok, this is a bit different than your statement because it does not return
all the rows beyond the first N where salary is equal to the salary of the
N-th row. But this could be added if needed.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany