473,725 Members | 2,244 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem with Global Temporary Table

I am creating a procedure A which is creating a global temporary table
DECLARE GLOBAL TEMPORARY TABLE session.temp (Service CHAR(2),
CustomerService TypeId INTEGER)
WITH REPLACE
ON COMMIT PRESERVE ROWS;

I am able to compile the proceudre.

But when I try to compile procedure B which is referencing the
temporary table in procedure A, I get the error.
"SESSION.TE MP" is an undefined name.

In procedure B all I am trying to do is
INSERT INTO session.temp VALUES ('MS', 0);
Can some one please help

Apr 5 '06 #1
2 4137
chettiar wrote:
I am creating a procedure A which is creating a global temporary table
DECLARE GLOBAL TEMPORARY TABLE session.temp (Service CHAR(2),
CustomerService TypeId INTEGER)
WITH REPLACE
ON COMMIT PRESERVE ROWS;

I am able to compile the proceudre.

But when I try to compile procedure B which is referencing the
temporary table in procedure A, I get the error.
"SESSION.TE MP" is an undefined name.


Try to add this in procedure B before the temp table is used:

IF 1 = 0 THEN
DECLARE GLOBAL TEMPORARY TABLE session.temp (Service CHAR(2),
CustomerService TypeId INTEGER)
WITH REPLACE
ON COMMIT PRESERVE ROWS;
END IF

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Apr 5 '06 #2
chettiar wrote:
I am creating a procedure A which is creating a global temporary table
DECLARE GLOBAL TEMPORARY TABLE session.temp (Service CHAR(2),
CustomerService TypeId INTEGER)
WITH REPLACE
ON COMMIT PRESERVE ROWS;

I am able to compile the proceudre.

But when I try to compile procedure B which is referencing the
temporary table in procedure A, I get the error.
"SESSION.TE MP" is an undefined name.

In procedure B all I am trying to do is
INSERT INTO session.temp VALUES ('MS', 0);

You have two choices:
Use dynamic SQL to break teh static dependency.
DECLARE the temp table before running the CREATE PROCEDURE statement.
DB2 needs to have all DDL objects defined to do proper name and function
resolution.

I recommend to customers to concentrate all temp table defintion that
are used across multiple procedures to have a dedicated DDL procedure.
Before creating any procedure you simply CALL the DDL procedures and you
know you got what you need.
Also when executing the app you CALL that same procedure at the right time.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 5 '06 #3

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

Similar topics

5
33649
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
11
4608
by: Hemant Shah | last post by:
Folks, I have a perl script that creates and uses global termporary table. This script worked fine with UDB 7.2 on AIX. Sometime ago I moved the database to UDB 8 on Linux. The select statement that used to take less than a minute now take 10 minutes. Do I have to tune something on Linux and/or DB2? I tried two different ways to create temporary tablespace but both of them give same slow performance:
2
2363
by: Chuck Crews | last post by:
I am interested in declaring a global temporary table within an application. The application processes 1 set of 600 or less rows each iteration. Multiple programs can and do call this one application at the same time. Today, we are using a permanent DB2 table and getting all kinds of locking even with row level locking on. In addition, our performance is slow as we do two inserts and two deletes on each row per program iteration. ...
1
3363
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
3369
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.
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 ;
5
8831
by: Rahul B | last post by:
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,
1
1868
by: Ragianand | last post by:
Hi All, I have created a stored procedure which populates a global emporary table...Now i want to write a function which returns a view that contains the data from the global temporary table.Can anyoune suggest a solution for this...Any sample code to return a view from a function with data from a global temporary table in another procedure. My understanding is that global temporary table will loose the data once we exit the procedure and...
0
9248
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...
1
9164
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9092
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
6000
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4506
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4775
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3212
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2622
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2151
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.