472,809 Members | 3,003 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,809 software developers and data experts.

Error while declaring a global temporary table

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?

Aug 13 '07 #1
5 8720
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
Aug 13 '07 #2
On Aug 13, 7:44 pm, Serge Rielau <srie...@ca.ibm.comwrote:
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

Aug 13 '07 #3
Rahul B wrote:
On Aug 13, 7:44 pm, Serge Rielau <srie...@ca.ibm.comwrote:
>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
Aug 14 '07 #4
On Aug 14, 8:15 am, Serge Rielau <srie...@ca.ibm.comwrote:
Rahul B wrote:
On Aug 13, 7:44 pm, Serge Rielau <srie...@ca.ibm.comwrote:
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.
Aug 14 '07 #5
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
Aug 14 '07 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Jim Garrison | last post by:
Scenario: 1) Create a GLOBAL TEMPORARY table and populate it with one (1) row. 2) Join that table to another with about 1 million rows. The join condition selects a few hundred rows. ...
10
by: Ranga | last post by:
I was unable to run the statement "CREATE GLOBAL TEMPORARY TABLE" on unix version of DB2, it gave the follwing error db2 => create global temporary table temp ( OGI_SYS_NR char(8) ) DB21034E ...
4
by: prasad | last post by:
I am getting sql error during binding a program which access a temporary table. The temporary table declaration and access methods are given below. EXEC SQL DECLARE GLOBAL TEMPORARY TABLE TEM88...
0
by: vsaraog | last post by:
Hi y'all, I am using a Created Global Temporary Table in DB2 OS/390 version 7.1.2. I am inserting some data in it and then updating the table using ODBC but while I try to update it, I am...
6
by: Joachim | last post by:
I made some project changes (which seems it doesn't help if I undo) which have created compilation error: " Server Error in '/PCSWebApp1' Application....
1
by: abh1508 | last post by:
Following a release of code the following problem occurs on certain asp ..net pages. This is not a problem on other testing/demo environments. IIS seems to be creating certain files twice in the...
2
by: David Parker | last post by:
In our application we have a table that tracks network sessions. The usage is: 1) create a session record 2) read/update the record several times during the session 3) delete the session record...
1
by: David Lozzi | last post by:
Hello, Writing a web app in asp.net using vb. Just created a new page, added a few minor controls and now blamo, the below error: HELP Server Error in '/newsite' Application....
0
by: sgueder | last post by:
Hi there, hope anybody can help me: I want to create a temporary table, want to fill that table with data and finally I want to use that table as my base-table for running some selects on it...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.