f



Declared Global Temporary Table in multiple Stored Procedures

Hi,

As I understand it, Declared Global Temporary Tables (DGTTs) have a
scope that is session/connection based.
Using the same connection, I have discovered that if I declare a DGTT
in one stored procedure, then I can't create a second stored procedure
that uses the DGTT, as the DGTT is not "visible". The only way around
this is to use dynamic SQL in the second stored procedure. In this way
(using dynamic sql) i can create and use the DGTT across multiple
stored procedures.

Is there perhaps a way to make DGTT visible across multiple stored
procedures using static sql, or is this an inherint limitation of
DGTTs.

Cheers
Otto

0
Otto
2/16/2007 11:07:01 AM
comp.databases.ibm-db2 12448 articles. 3 followers. arunrocks (9) is leader. Post Follow

3 Replies
481 Views

Similar Articles

[PageSpeed] 48

Otto Carl Marte wrote:
> Hi,
> 
> As I understand it, Declared Global Temporary Tables (DGTTs) have a
> scope that is session/connection based.
> Using the same connection, I have discovered that if I declare a DGTT
> in one stored procedure, then I can't create a second stored procedure
> that uses the DGTT, as the DGTT is not "visible". The only way around
> this is to use dynamic SQL in the second stored procedure. In this way
> (using dynamic sql) i can create and use the DGTT across multiple
> stored procedures.
> 
> Is there perhaps a way to make DGTT visible across multiple stored
> procedures using static sql, or is this an inherint limitation of
> DGTTs.
The limitation is this:
To create a procedure that uses a DGTT the table must be declare before 
CREATE PROCEDURE is run OR it must be declared within the CREATE 
PROCEDURE statement itself. The reason is that DB2 needs to be able to 
distinguish between variables and columns which share the same namespace 
in the SQL/PSM standard.

My preferred code-convention for x-procedure DGTT usage is to declare 
all temps in a dedicated "init" procedure.
Then before you run any DDL using the temps you CALL INIT()
That way all your temps are centralized and no mix up can happne.
Of course when you connect you also call init().

Cheers
Serge
-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
0
Serge
2/16/2007 12:27:26 PM
Thanks Serge. That is definitely an option.


0
Otto
2/16/2007 1:04:15 PM
Thanks Serge, this works well, and definitely solves our problem.


0
Otto
2/28/2007 11:01:07 AM
Reply:

Similar Artilces:

Stored procedures using declared temporary tables: please help!
Hi, we are currently implementing an application running on DB2 V7 on Z/OS using largely COBOL stored procedures, managed using WLM. Some of these stored procedures declared global temporary tables, which are declared with ON COMMIT DELETE ROWS to perform work on and then return these temporary tables to the client (which is a message driven EJB connecting via DB2 connect). The client reads the data in the result sets, creates some XML to be sent back to the front end and then commits the transaction. At this point, I would expect the temporary tables to be destroyed and the t...

declare global temporary table with com.ibm.as400.access.AS400JDBCDriver
Hello. V5R2 I try to create global temporary table in my Windows java application: ((java.sql.Connection) conn).createStatement().execute("declare global temportary table test (c1 integer)"); With COM.ibm.db2.jdbc.app.DB2Driver it works, but with com.ibm.as400.access.AS400JDBCDriver I get such SQLException: --start-- java.sql.SQLException: [PWS9801] Function rejected by user exit program IMBEPSQL in QUSRSYS. Cause . . . . . : User exit program IMBEPSQL in library QUSRSYS called for the function and returned an indication that the function should not be done. Recovery . . . : Determine why the user exit program rejected the function, correct the problem, and run the function again. --end--- ((SQLException) ex).getErrorCode() = -101 ((SQLException) ex).getSQLState() = "HY001" Is it possible to declare tables with this driver? Sincerely, Mark B ...

Arrays vs Declared Global Temp Tables for Stored Procs in DB2 9.5
I am using DB2 9.5.5 and cannot migrate to 9.7 anytime soon, so I cannot use Created Global Temporary Tables (CGTT). I am wondering how Arrays are implemented for stored procedures and what the performance is compared to declared global temporary tables (DGTT). In order to improve performance, I want to use arrays instead of a DGTT, but I want to make sure that Arrays are not creating a DGTT under the covers. Assume that Arrays provides me with the functionality I need. On 7/14/2010 12:45 AM, Martin wrote: > I am using DB2 9.5.5 and cannot migrate to 9.7 anytime soon, so I cannot use > Created Global Temporary Tables (CGTT). > > I am wondering how Arrays are implemented for stored procedures and what the > performance is compared to declared global temporary tables (DGTT). In order > to improve performance, I want to use arrays instead of a DGTT, but I want > to make sure that Arrays are not creating a DGTT under the covers. > > Assume that Arrays provides me with the functionality I need. I'm actually preparing a talk on the topic. For now: ARRAYs are a continuous piece of memory allocated on the application heap. At lot less overhead than all the table infrastructure. On the flip side if you have some open-ended and huge arrays paired with many concurrent users... well you do the math. Of course the API for ARRAY (SET, ARRAY_AGG, UNNEST) is fundamentally different from DGTT (UPDATE, DELETE, INSERT, DEC...

Stored procedure in database X, executes stored procedure in database Y, wrapped in transaction?
Is it possible to execute a stored procedure in one database, which then itself executes a stored procedure from another database? We have decide to split our data into a tree structure (DB1) and data blobs (DB2) (we are using MSDE and we have a 2gb limit with each DB so we've done it this way for that reason). I would like to, say, execute a stored procedure in DB1, passing in the data blob and other details, DB1 will create a tree node in DB1 and then add the blob record to DB2. DB1 will wrap in a transaction of course, as will DB2 when it adds the blob. Is this possible? [poste...

DB2 COBOL stored procedure to UDB SQL stored procedure
I have been given the task of taking a 3,200 line COBOL stored procedure and duplicating the same functionality in UDB 7.2 on the Windows platform with a procedural SQL stored procedure. I have fiddled with procedural SQL stored procs on UDB, but mostly short,trivial ones. Looking for good advice, links, etc. on the best approach to this. There doesn't seem to be the concept of subroutines within a stored proc, so I am guessing one main stored proc that may be calling multiple smaller stored procs? Just found out about this need late this afternoon and wondering what the best approach is to doing such a task. Any and all ideas most welcome. thanks, fv <PJackson@txfb-ins.com> wrote in message news:1175743257.812351.234300@y80g2000hsf.googlegroups.com... >I have been given the task of taking a 3,200 line COBOL stored > procedure and duplicating the same functionality in UDB 7.2 on the > Windows platform with a procedural SQL stored procedure. I have > fiddled with procedural SQL stored procs on UDB, but mostly > short,trivial ones. Looking for good advice, links, etc. on the best > approach to this. There doesn't seem to be the concept of subroutines > within a stored proc, so I am guessing one main stored proc that may > be calling multiple smaller stored procs? Just found out about this > need late this afternoon and wondering what the best approach is to > doing such a task. > > Any...

DECLARE GLOBAL TEMPORARY TABLE problem
A bit longwinded; apologies in advance. My client has two large iSeries machines, both at V5R2 (they may be logical partitions of the same physical machine; I'm not sure.) The DECLARE GLOBAL TEMPORARY TABLE statement in SQL behaves a little differently on the two machines, causing me problems. I am issuing the statement in the Run SQL Scripts window of the navigator; *SQL naming is in effect (this matters.) On the development machine, the table created is owned by the group profile to which my user profile belongs; there is no *GROUP authority generated, and *PUBLIC authority ...

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? 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 VARC...

SQL Stored Procedures X Java Stored Procedures in DB2 8.2
Hi All, We are starting a large data warehousing project using DB2 8.2 on AIX. There is a direction to move any new internal development to Java and a question was raised: Would it be a good idea to have all stored procedures that we might need (ETL, additional transformers, etc) for this project and any other project written in Java as opposed to SQL/P? Does anyone have any number or experience in terms of performance differences? Regards, Rafael Faria Rafael Faria wrote: > Hi All, > > We are starting a large data warehousing project using DB2 8.2 on AIX. >...

Calling Stored Procedure remotely from other stored procedure (DB2 9.7 LUW)
Hello db2-experts, I'm planning an asynchronous application driven data replication. Therefor I want to call a stored procedure in database "x" on machine "b" from a stored procedure in database "y" on machine "a". Any ideas out there how to do it ? I found nothing appropriate in the manuals or examples. TIA Frank On Tuesday, September 4, 2012 8:37:33 PM UTC+2, Frank Mickler wrote: > Hello db2-experts, > > > > I'm planning an asynchronous application driven data replication. > > Therefor I want to call a stored procedure in database "x" on machine > > "b" from a stored procedure in database "y" on machine "a". > > > > Any ideas out there how to do it ? > > > > I found nothing appropriate in the manuals or examples. > > > > TIA > > Frank Did you consider SQL Replication or Q Replication? They're very appropriate in this scenario(async)... On Sep 5, 3:56=A0am, Luiz da Silva <luiz...@gmail.com> wrote: > On Tuesday, September 4, 2012 8:37:33 PM UTC+2, Frank Mickler wrote: > > Hello db2-experts, > > > I'm planning an asynchronous application driven data replication. > > > Therefor I want to call a stored procedure in database "x" on machine > > > "b" from a stored procedure in database &...

Stored procedure to create a new database with tables
I want my application to create a new database/tables when run for the first time. I have created a stored procedure to create the new database named "budget". When I run the stored procedure, it creates the budget database but the tables are created in the "master" database. Please help. Before the CREATE TABLE statements you should put this: USE Budget go "Shiller" <shillerc@gmail.com> ha scritto nel messaggio news:1172675843.097303.293060@8g2000cwh.googlegroups.com... > I want my application to create a new database/tables when run for the >...

Reg: Owner for Declared Global Temporary tables
I am using below Query to find the declared global temporary tables in a Database: Below query is for DB2 version 9.1 , you can get same information by taking snapshot of the tables. SELECT * from SYSIBMADM.SNAPTAB WHERE TABSCHEMA = 'SESSION' ORDER BY TABNAME, DBPARTITIONNUM; Is it possible to find the Owner of the declared global Temporary table ? ( who actually created it) Thanks Ravi Ravi wrote: > I am using below Query to find the declared global temporary tables in > a Database: > Below query is for DB2 version 9.1 , you can get same information by > taking snapshot of the tables. > > SELECT * from SYSIBMADM.SNAPTAB WHERE TABSCHEMA = 'SESSION' ORDER BY > TABNAME, DBPARTITIONNUM; > > Is it possible to find the Owner of the declared global Temporary > table ? ( who actually created it) Hmm.. not sure what you are ultimately after, but if you want to enforce a quota you could control it through the user temp table space. I.e. give each group of users a different temp space. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab On Aug 30, 8:12=A0am, Serge Rielau <srie...@ca.ibm.com> wrote: > Ravi wrote: > > I am using below Query to find the declaredglobaltemporarytablesin > > a Database: > > Below query is for DB2 version 9.1 , you can get same information by > > taking snapshot of thetables. > > > SELECT =A0* from...

Stored Procedure Issue
I would like to create a stored procedure which creates a temp table to store some XML. The # of fields of XML is dependent upon the contents of another table in the application, so the first part of my procedure identifies the # of fields necessary. That is working correctly. The second part of the procedure actually attempts to create the table. This is where my issue is. If I attempt to create the table as a non-temporary table, the procedure executes correctly. As soon as I add the hash marks in front of the table name to indicate that it is a temporary table, it is failing. Is this ...

Using a temporary tables within the stored procedure
Hi, If one uses a temporary table/ table variable within a stored procedure, will it use the compiled plan each time the stored procedure is executed or will it recompile for each execution? Thanks in advance, Thyagu ...

nested queries, stored procedures, temporary table
Hi, I'm adapting access queries to sql server and I have difficulties with the following pattern : query1 : SELECT * FROM Query2 WHERE A=@param1 query 2: SELECT * FROM Table2 WHERE B=@param2 The queries are nested, and they both use parameters. In MS Acccess the management of nested queries with parameters is so easy (implicit declaration of parameters, transmission of parameters from main query to nested query) that I don't know what the syntax should be for stored procedures. The corresponding stored procedure would be something like CREATE TABLE #TempTable (...table definition......

Using temporary tables within the stored procedure
Hi, If one uses a temporary table/ table variable within a stored procedure, will it use the compiled plan each time the stored procedure is executed or will it recompile for each execution? Thanks in advance, Thyagu It will use compiled plan. Thyagu wrote: > Hi, > > If one uses a temporary table/ table variable within a stored > procedure, will it use the compiled plan each time the stored procedure > is executed or will it recompile for each execution? > > Thanks in advance, > Thyagu ...

Putting result set of stored procedure into temporary table
I can execute a stored procedure and get some rows as results. For example sp_spaceused returns one row with six columns, and then a row with one column containing the stored procedure exit status. I would like to get the rows returned by the stored procedure (though not the exit status) into a temporary table. Something like exec sp_spaceused 'whatever' into #foo The table #foo would then have a single row containing the results from sp_spaceused. Is this possible? (ASE 11.9.2) -- Ed Avis <ed@membled.com> On Tue, 16 Dec 2003 17:15:16 +0000, Ed Avis wrote: > I can execute a stored procedure and get some rows as results. For > example sp_spaceused returns one row with six columns, and then a row with > one column containing the stored procedure exit status. > > I would like to get the rows returned by the stored procedure (though not > the exit status) into a temporary table. Something like > > exec sp_spaceused 'whatever' into #foo > > The table #foo would then have a single row containing the results from > sp_spaceused. Is this possible? (ASE 11.9.2) Not directly. However, by using CIS you can map an RPC call to a proxy table, and then use a normal SELECT over the proxy table to execute the RPC. I'm not sure how that would work with multiple result sets. Look for the "create existing table" command in the docs. Michael -- Michael Peppler ...

Returning Results from a SQL Stored Procedures that uses temporary tables
Hello All! Any help is appreciated. I have a LabView 7.0 application in which I am using the Database Utilities to connect to a MSDE 2000 server. Instead of doing all of the table selects and grouping in LabView, I have stored procedures in the database. So, I use the EXEC tool to execute my stored procedures and receive result sets. This works great in general. However, I have a more complicated stored procedure which uses temporary tables to hold and massage data before I do my final select statement. When I do this, LabView returns no results, nor yields an error. The stored procedure works...

Creating a dynamic Global Temp Table within a stored procedure
hi, I wish to create a temporary table who's name is dynamic based on the argument. ALTER PROCEDURE [dbo].[generateTicketTable] @PID1 VARCHAR(50), @PID2 VARCHAR(50), @TICKET VARCHAR(20) AS BEGIN SET NOCOUNT ON; DECLARE @DATA XML SET @DATA = (SELECT dbo.getHistoryLocationXMLF (@PID1, @PID2) as data) CREATE TABLE ##@TICKET (DATA XML) INSERT INTO ##@TICKET VALUES(@DATA) END is what i have so far - although it just creates a table with a name of ##@TICKET - which isn't what i want. I want it to evaluate the name. any ideas? Am 6 Sep 2006 22:14:18 -0700 schrieb pukivruk...

DB2 Express-C Global Temporary Table Performance on vista
After I have istalled express version of DB 9.5 on Vista. I create a stored procedure(used global temporary table), When i try to execute this procedure, it's very very slow, even if there is only one row can be fetched. But I try this on XP instead, it's ok. Does it Needs addtional setting for global temporary table on Vista? Thanks in advance. How much RAM is on the machine? Do you think it's possible that it's Vista that is usurping the memory? Larry E. DB2 wrote: > After I have istalled express version of DB 9.5 on Vista. > I create a stored procedure(used global temporary table), > When i try to execute this procedure, it's very very slow, > even if there is only one row can be fetched. > But I try this on XP instead, it's ok. > Does it Needs addtional setting for global temporary table on Vista? > > Thanks in advance. ...

Stored procedure from stored procedure
Is it possible to create a stored procedure from a stored procedure? When I attempt this inanity, it doesn't blow up until syntax error at the first "end procedure" statement of the SP I'm trying to create within the outer SP. Can this be done? Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask). On 24 Sep 2003 11:47:09 -0700, red_valsen@yahoo.com (Red Valsen) wrote: >Is it possible to create a stored procedure from a stored procedure? >When I attempt this inanity, it doesn't blow up until syntax error at >the first "end procedure" statement of the SP I'm trying to create >within the outer SP. Can this be done? > >Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask). Write it out to flatfile and execute the create from command line using dbaccess??? John Carlson wrote: > On 24 Sep 2003 11:47:09 -0700, red_valsen@yahoo.com (Red Valsen) > wrote: > > >>Is it possible to create a stored procedure from a stored procedure? >>When I attempt this inanity, it doesn't blow up until syntax error at >>the first "end procedure" statement of the SP I'm trying to create >>within the outer SP. Can this be done? >> >>Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask). > > > Write it out to flatfile and execute the create from command line > using dbaccess??? As John said, somewhat obliquely, there is...

Comparing and Synchronizing Databases, Tables, Stored Procedures accross servers
25 more days until the "get it free" promotion runs out for xSQL Object (you can get it from http://www.x-sql.com) Here are just some of the great features packed in the product: - Compare SQL Server objects (databases, tables, views, stored procedures, user defined data functions etc.) accross servers. - view and print dependencies; - generate color coded scripts for any object in the database or many of them at once (many configurable options); - generate scripts for synchronizing databases or certain objects within the databases. We would love to here back from you as to how...

How to find query plan for a stored procedure using temporary tables
This post is related to SQL server 2000 and SQL Server 2005 all editions. Many of my stored procedures create temporary tables in the code. I want to find a way to find the query plan for these procs Repro --*********************************** use pubs go CREATE PROCEDURE Test @percentage int AS SET Nocount on --Create and load a temporary table select * into #Temp1 from titleauthor --Create second temporary table create table #Temp2 ( au_id varchar(20), title_id varchar (20), au_ord int, rolaylityper int) --load the second temporary table from the first one insert into #Temp2 select * from...

Search for a table/string within job steps, dts, database, stored procedures etc
Hi All : A couple of tables have been identified to be deleted. My job is to find if it is at all used. On searching the web, i found a proc to search for a string within all databases in a server. using system sproc : sp_msforeachdb it searches for a string in views, sprocs, functions, check constraints, defaults, foreign key, scalar function, inlined tablefunction, primary key, 'Replication filter stored procedure, System table, Table function, Trigger, 'User table, 'UNIQUE constraint''Extended stored procedure' So it is pretty extensive. But i dont think it is cov...

load SAS table into DB2 temporary table
Hello, I have a problem with the load DB2. I'm working on MVS system under Z/OS and with DB2. I have to create a DB2 temporary table from a SAS table. I've tried 2 solution, the first work good but it's slow to store the records The second should be very faster but it doesn't work. The 2 solutions : 1. put all the record read from the SAS table into macro-variable then create the DB2 temporary table then do a loop to insert all macro-variable in the DB2 table then do a SQL request to try if the table is loaded ==> this work very good 2. create a SAS t...

Web resources about - Declared Global Temporary Table in multiple Stored Procedures - comp.databases.ibm-db2

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Eye tattoos: Tattooist Luna Cobra defends practice following calls to ban procedure
IT SOUNDS like the really gross scene in a horror movie.

Survivors of female genital mutilation outraged by 'compromise' which could legalise procedure
Survivors of female genital mutilation are fighting back against a so-called compromise, which could let a modified form of the procedure continue. ...

Doctors Perform Medical Procedure On Wrong Newborn
The procedure was meant for another child.

Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?

Butt procedures, male breast reduction growing slices of plastic surgery
Every 30 min of 2015, a rump was remodeled. Men had 40% of all breast reductions.

"Gynecologists Kavita Shah Arora and Allan Jacobs said procedures that slightly changed the look of a ...
"... were comparable to male circumcision or cosmetic procedures in Western countries like labiaplasty. Laws against mild modifications were ...

We need to reform the culture of law enforcement, not just the procedures
We need to reform the culture of law enforcement, not just the procedures by digby I have a new piece up at Salon this morning about police ...

Rescued tiger cub "Himmel" dies during medical procedure - CBS News 8 - San Diego, CA News Station ...
Alpine animal rescue sanctuary Lions, Tigers & Bears is sadly reporting Himmel, the tiger cub found roaming the streets of Hemet and rescued ...

Catholic hospital allowed to deny sterilization procedure, judge says
Hospital in California argued that the procedure known as ‚Äčtubal ligation would violate its religious freedom

Resources last updated: 3/7/2016 2:46:26 AM