473,747 Members | 2,489 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 8832
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("Unexpecte d 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
recompilatio n 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("Unexpecte d 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("Unexpecte d 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(atlea st 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(atlea st 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.serv er ;-)
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.phoneli sts 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
33654
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. Performance: 4 seconds, the system is doing a full-table scan of the second table, and the Explain Plan output
10
10820
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 The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "create global temporary table temp ( OGI_S" was
4
12049
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 LIKE SYSIBM.SYSDUMMY1 END-EXEC. EXEC SQL INSERT INTO TEM88 SELECT * FROM SYSIBM.SYSDUMMY1 END-EXEC.
0
1553
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 getting following message. SQL0526N The requested function does not apply to declared temporary tables. SQLSTATE=42995 Anyone has any ideas why I getting the above error message. Following
6
2224
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. -------------------------------------------------------------------------------- Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details
1
3364
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 temporary internet files directory. Any advice or suggestions appreciated. Server Error in '/ContactCentre/Global' Application. -------------------------------------------------------------------------------- Compilation Error
2
3370
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 when the session ends Update activity on this table is going to be pretty intense, and the transient nature of the data makes it a good candidate for a temporary and/or in-memory table.
1
5344
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. -------------------------------------------------------------------------------- Compilation Error Description: An error occurred during the compilation of a resource required
0
2685
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 (DB2). So I used the following SQL: DECLARE GLOBAL TEMPORARY TABLE SESSION.SG_ADVICE (ID DECIMAL(11,0), ZAEHLER INTEGER) ON COMMIT PRESERVE ROWS ;
0
8979
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9522
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9354
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9223
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8233
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6790
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4860
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2771
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2203
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.