Error while declaring a global temporary table

[Rahul B]

Hi,

I have very little knowledge about creating Procedures/functions in
DB2.

When i tried to create the test function like

CREATE FUNCTION GET_TEST
(P_TEST_ID INTEGER,
P_SEL_OR_SORT INTEGER,
P_TEST VARCHAR(2)
)
RETURNS VARCHAR(1000)
SPECIFIC GET_RULE
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
DECLARE v_TEST VARCHAR(4000);
DECLARE v_TEST_Select VARCHAR(4000);
DECLARE v_TEST_Sort VARCHAR(1000);
DECLARE GLOBAL TEMPORARY TABLE TEMP_TEST
(id INTEGER
)
ON COMMIT PRESERVE ROWS;

SET v_TEST_Select = NULL;
SET v_TEST_Sort = NULL;
RETURN(v_TEST);
END;
It give an error SQL 0104N "An unexpected token "Table" was found
following "..are Global Temporary".

What is the problem in that?

[Serge Rielau]

Rahul B wrote:

Hi,

I have very little knowledge about creating Procedures/functions in
DB2.

When i tried to create the test function like

CREATE FUNCTION GET_TEST
(P_TEST_ID INTEGER,
P_SEL_OR_SORT INTEGER,
P_TEST VARCHAR(2)
)
RETURNS VARCHAR(1000)
SPECIFIC GET_RULE
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
DECLARE v_TEST VARCHAR(4000);
DECLARE v_TEST_Select VARCHAR(4000);
DECLARE v_TEST_Sort VARCHAR(1000);
DECLARE GLOBAL TEMPORARY TABLE TEMP_TEST
(id INTEGER
)
ON COMMIT PRESERVE ROWS;

SET v_TEST_Select = NULL;
SET v_TEST_Sort = NULL;
RETURN(v_TEST);
END;
It give an error SQL 0104N "An unexpected token "Table" was found
following "..are Global Temporary".

What is the problem in that?

Rahul,

In DB2 for LUW SQL Functions are macros. As such they cannot do DDL,
open cursors, etc.
If you have complex SQL PL inside of a function it is advisable to write
a stored procedure and then CALL that procedure from the UDF.
Either way DDL in a function? That's a really bad idea. It would be slow
and eat a lot of CPU.
typically temp tables are declared much higher up. E.g. right after the
connection. Constant DECLARE and DROP of a temp table causes
recompilation of the statements using them which will eat at your CPU.

For a list of the legal statements in an SQL Function is here:
http://publib.boulder.ibm.com/infoce...c/r0004240.htm

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

[Rahul B]

On Aug 13, 7:44 pm, Serge Rielau <[e-mail]:

Rahul B wrote:

Hi,

I have very little knowledge about creating Procedures/functions in
DB2.

When i tried to create the test function like

CREATE FUNCTION GET_TEST
(P_TEST_ID INTEGER,
P_SEL_OR_SORT INTEGER,
P_TEST VARCHAR(2)
)
RETURNS VARCHAR(1000)
SPECIFIC GET_RULE
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
DECLARE v_TEST VARCHAR(4000);
DECLARE v_TEST_Select VARCHAR(4000);
DECLARE v_TEST_Sort VARCHAR(1000);
DECLARE GLOBAL TEMPORARY TABLE TEMP_TEST
(id INTEGER
)
ON COMMIT PRESERVE ROWS;

SET v_TEST_Select = NULL;
SET v_TEST_Sort = NULL;
RETURN(v_TEST);
END;

It give an error SQL 0104N "An unexpected token "Table" was found
following "..are Global Temporary".

What is the problem in that?

Rahul,

In DB2 for LUW SQL Functions are macros. As such they cannot do DDL,
open cursors, etc.
If you have complex SQL PL inside of a function it is advisable to write
a stored procedure and then CALL that procedure from the UDF.
Either way DDL in a function? That's a really bad idea. It would be slow
and eat a lot of CPU.
typically temp tables are declared much higher up. E.g. right after the
connection. Constant DECLARE and DROP of a temp table causes
recompilation of the statements using them which will eat at your CPU.

For a list of the legal statements in an SQL Function is here:http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -

Serge,

I am trying to create the temporary table inside the procedure because
i have to use an array of varchars (the code written is only a portion
of the function which i was trying to convert from Oracle to DB2, so i
started with the smallest and basic chunk first), and you have said in
a thread in this group that we have to use a temporary table since DB2
doesn't support arrays.

I will try to write a proc and call it from the function, but still
why should the error come("Unexpected toke Table.....")...
Leaving performance issue, is there a synactical error?

Thanks a lot,

Rahul

[Serge Rielau]

Rahul B wrote:

On Aug 13, 7:44 pm, Serge Rielau <[e-mail]:

>Rahul B wrote:

>>Hi,
I have very little knowledge about creating Procedures/functions in
DB2.
When i tried to create the test function like
CREATE FUNCTION GET_TEST
(P_TEST_ID INTEGER,
P_SEL_OR_SORT INTEGER,
P_TEST VARCHAR(2)
)
RETURNS VARCHAR(1000)
SPECIFIC GET_RULE
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
DECLARE v_TEST VARCHAR(4000);
DECLARE v_TEST_Select VARCHAR(4000);
DECLARE v_TEST_Sort VARCHAR(1000);
DECLARE GLOBAL TEMPORARY TABLE TEMP_TEST
(id INTEGER
)
ON COMMIT PRESERVE ROWS;
SET v_TEST_Select = NULL;
SET v_TEST_Sort = NULL;
RETURN(v_TEST);
END;
It give an error SQL 0104N "An unexpected token "Table" was found
following "..are Global Temporary".
What is the problem in that?

Rahul,

In DB2 for LUW SQL Functions are macros. As such they cannot do DDL,
open cursors, etc.
If you have complex SQL PL inside of a function it is advisable to write
a stored procedure and then CALL that procedure from the UDF.
Either way DDL in a function? That's a really bad idea. It would be slow
and eat a lot of CPU.
typically temp tables are declared much higher up. E.g. right after the
connection. Constant DECLARE and DROP of a temp table causes
recompilation of the statements using them which will eat at your CPU.

For a list of the legal statements in an SQL Function is here:http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -

Serge,

I am trying to create the temporary table inside the procedure because
i have to use an array of varchars (the code written is only a portion
of the function which i was trying to convert from Oracle to DB2, so i
started with the smallest and basic chunk first), and you have said in
a thread in this group that we have to use a temporary table since DB2
doesn't support arrays.

I will try to write a proc and call it from the function, but still
why should the error come("Unexpected toke Table.....")...
Leaving performance issue, is there a synactical error?

Yes it is a syntax error. If you check out teh link I posted to:
Compound Statement (dynamic) you will find that DECLARE GLOBAL TEMPORARY
TABLE is not in the syntax diagram, hence a -104.
Using a temp table to pass around ARRAYS between PROCEDURES using tenmp
tables has no relevance on DECLARING temps in FUNCTIONs.
DB2 (unlike Oracle) clearly distinguishes between functions and procedures.
Functions extend SQL (either expressions or relational operations)
Procedures encapsulate procedural logic.
In general do not DECLARE TEMP tables in the procedures that use them.
Declare them at the beginning of your connections.

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

[Rahul B]

On Aug 14, 8:15 am, Serge Rielau <[e-mail]:

Rahul B wrote:

On Aug 13, 7:44 pm, Serge Rielau <[e-mail]:

Rahul B wrote:
Hi,
I have very little knowledge about creating Procedures/functions in
DB2.
When i tried to create the test function like
CREATE FUNCTION GET_TEST
(P_TEST_ID INTEGER,
P_SEL_OR_SORT INTEGER,
P_TEST VARCHAR(2)
)
RETURNS VARCHAR(1000)
SPECIFIC GET_RULE
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
DECLARE v_TEST VARCHAR(4000);
DECLARE v_TEST_Select VARCHAR(4000);
DECLARE v_TEST_Sort VARCHAR(1000);
DECLARE GLOBAL TEMPORARY TABLE TEMP_TEST
(id INTEGER
)
ON COMMIT PRESERVE ROWS;
SET v_TEST_Select = NULL;
SET v_TEST_Sort = NULL;
RETURN(v_TEST);
END;
It give an error SQL 0104N "An unexpected token "Table" was found
following "..are Global Temporary".
What is the problem in that?
Rahul,

In DB2 for LUW SQL Functions are macros. As such they cannot do DDL,
open cursors, etc.
If you have complex SQL PL inside of a function it is advisable to write
a stored procedure and then CALL that procedure from the UDF.
Either way DDL in a function? That's a really bad idea. It would be slow
and eat a lot of CPU.
typically temp tables are declared much higher up. E.g. right after the
connection. Constant DECLARE and DROP of a temp table causes
recompilation of the statements using them which will eat at your CPU.

For a list of the legal statements in an SQL Function is here:http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -

Serge,

I am trying to create the temporary table inside the procedure because
i have to use an array of varchars (the code written is only a portion
of the function which i was trying to convert from Oracle to DB2, so i
started with the smallest and basic chunk first), and you have said in
a thread in this group that we have to use a temporary table since DB2
doesn't support arrays.

I will try to write a proc and call it from the function, but still
why should the error come("Unexpected toke Table.....")...
Leaving performance issue, is there a synactical error?

Yes it is a syntax error. If you check out teh link I posted to:
Compound Statement (dynamic) you will find that DECLARE GLOBAL TEMPORARY
TABLE is not in the syntax diagram, hence a -104.
Using a temp table to pass around ARRAYS between PROCEDURES using tenmp
tables has no relevance on DECLARING temps in FUNCTIONs.
DB2 (unlike Oracle) clearly distinguishes between functions and procedures.
Functions extend SQL (either expressions or relational operations)
Procedures encapsulate procedural logic.
In general do not DECLARE TEMP tables in the procedures that use them.
Declare them at the beginning of your connections.

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

Thanks a lot Serge,

Can you please explain what you mean by
"Declare them(temp tables) at the beginning of your connections."
and
" Functions extend SQL (either expressions or relational operations)"

We have a scenario where application code calls a procedure or a
function, and we support both Oracle and DB2.
Further, we don't want the application code to call different
procedures/functions if the databases are different(atleast the name
should be same).
Now Oracle uses arrays in its function, i have to use Temp tables in
DB2 function, which cannot be done.

In the previous post(2nd last), you said, "If you have complex SQL PL
inside of a function it is advisable to write
a stored procedure and then CALL that procedure from the UDF. ".
So i thought of writing a procedure (and create temp table there) and
call that proc from my function.
However, you also said,
"In general do not DECLARE TEMP tables in the procedures that use
them."

In this context, i could not get what you mean by "beginning of
connection"?

Please don't mind any questions that may seem "silly" to you, since i
am an amateur to the Databases.

Thanks again.

[Serge Rielau]

Rahul B wrote:

Can you please explain what you mean by
"Declare them(temp tables) at the beginning of your connections."
and
" Functions extend SQL (either expressions or relational operations)"

We have a scenario where application code calls a procedure or a
function, and we support both Oracle and DB2.
Further, we don't want the application code to call different
procedures/functions if the databases are different(atleast the name
should be same).
Now Oracle uses arrays in its function, i have to use Temp tables in
DB2 function, which cannot be done.

You can use temp tables in a procedure that is called by a fucntion, you
just cannot DECLARE them

In the previous post(2nd last), you said, "If you have complex SQL PL
inside of a function it is advisable to write
a stored procedure and then CALL that procedure from the UDF. ".
So i thought of writing a procedure (and create temp table there) and
call that proc from my function.
However, you also said,
"In general do not DECLARE TEMP tables in the procedures that use
them."

Think about DECLARE GLOBAL TEMPORARY TABLE as being used the same way as
CREATE GLOBAL TEMPORARY TABLE in Oracle.
You would never dare CREATEing a table inside of a stored procedure.
(Some guy got skinned for that last week in c.d.oracle.server ;-)
What you do if you want to use a temp table is that you CREATEit in your
DB schema and then just use it.
Now DB2's DGTT are not defined in the schema. They are privately defined
per session.

What I recommend to customer is to use an init-procedure. E.g.
CREATE PROCEDURE inittemps()
BEGIN
DECLARE GLOBAL TEMPORARY TABLE phonelists(id INT NOT NULL PRIMARY KEY,
number BIGINT);
DECLARE GLOBAL TEMPORARY TABLE ....;
END

Now sometime after you connect and whenever you create a procedure using
the temp tables you CALL inittemps().

db2 -td%
CALL inittemps()
%
CREATE PROCEDURE myproc()
BEGIN
DECLARE firstnum BIGINT;
SET firstnum = (SELECT number FROM session.phonelists WHERE id = 1);
END
%
terminate
%

Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab