f



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 is *EXCLUDE, just as would be expected with 
*SQL naming, according to the SQL reference manual. 
This means that once I've DECLAREd the temporary table, 
I can't do a thing with it, including DROP.

On the benchmark machine, the ownership and authorities 
are different.  My profile owns the table, and has *ALL 
object authority; *PUBLIC again has *EXCLUDE, per *SQL 
naming.

On both machines, my profile is a member of the same 
group profile, and I am set up with OWNER(*GRPPRF). 
This makes it strange that my profile owns the 
temporary table on the benchmark machine.  My profile 
on the benchmark machine is user class *USER, but I 
have *ALLOBJ special authority; on the development 
machine, my user class is *PGMR, and I have no special 
authorities.

To get around the problem on the development machine, 
I've abandoned DECLARE GLOBAL TEMPORARY TABLE and 
instead I use CREATE TABLE QTEMP.xxxxxx.  I have full 
authority on the table created.  Interestingly, the 
CREATE TABLE statement leaves no evidence in the job 
log of having run, but DECLARE GLOBAL TEMPORARY TABLE 
leaves messages indicating that ownership was 
transferred to the group profile, and that the table 
was created but couldn't be journaled.

Also, when I create the table using CREATE TABLE, the 
object authorities for the table indicate that the 
group profile is the owner, and *GROUP has *ALL 
authority; this is true on both systems.

Does anyone have any idea why the differences, and in 
particular on the development machine, why *GROUP 
doesn't pick up any authorities when using DECLARE 
GLOBAL TEMPORARY TABLE, but *does* acquire authority 
when using CREATE TABLE?

0
jonball (677)
8/25/2004 9:22:23 PM
comp.sys.ibm.as400.misc 9219 articles. 3 followers. Post Follow

8 Replies
948 Views

Similar Articles

[PageSpeed] 26

Jonathan,

Look at the folling values on you user profile for both machines:
Owner  . . . . . . . . . . . . . . . . . . :   *GRPPRF   
Group authority  . . . . . . . . . . . . . :   *NONE     
Group authority type . . . . . . . . . . . :   *PRIVATE  


Charles



In article <jq7Xc.382$W_5.304@newsread1.news.pas.earthlink.net>, 
jonball@whitehouse.not says...
> 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 is *EXCLUDE, just as would be expected with 
> *SQL naming, according to the SQL reference manual. 
> This means that once I've DECLAREd the temporary table, 
> I can't do a thing with it, including DROP.
> 
> On the benchmark machine, the ownership and authorities 
> are different.  My profile owns the table, and has *ALL 
> object authority; *PUBLIC again has *EXCLUDE, per *SQL 
> naming.
> 
> On both machines, my profile is a member of the same 
> group profile, and I am set up with OWNER(*GRPPRF). 
> This makes it strange that my profile owns the 
> temporary table on the benchmark machine.  My profile 
> on the benchmark machine is user class *USER, but I 
> have *ALLOBJ special authority; on the development 
> machine, my user class is *PGMR, and I have no special 
> authorities.
> 
> To get around the problem on the development machine, 
> I've abandoned DECLARE GLOBAL TEMPORARY TABLE and 
> instead I use CREATE TABLE QTEMP.xxxxxx.  I have full 
> authority on the table created.  Interestingly, the 
> CREATE TABLE statement leaves no evidence in the job 
> log of having run, but DECLARE GLOBAL TEMPORARY TABLE 
> leaves messages indicating that ownership was 
> transferred to the group profile, and that the table 
> was created but couldn't be journaled.
> 
> Also, when I create the table using CREATE TABLE, the 
> object authorities for the table indicate that the 
> group profile is the owner, and *GROUP has *ALL 
> authority; this is true on both systems.
> 
> Does anyone have any idea why the differences, and in 
> particular on the development machine, why *GROUP 
> doesn't pick up any authorities when using DECLARE 
> GLOBAL TEMPORARY TABLE, but *does* acquire authority 
> when using CREATE TABLE?
> 
> 
0
cwilt1 (190)
8/26/2004 12:25:13 PM
Charles Wilt wrote:
> Jonathan,
> 
> Look at the folling values on you user profile for both machines:
> Owner  . . . . . . . . . . . . . . . . . . :   *GRPPRF   
> Group authority  . . . . . . . . . . . . . :   *NONE     
> Group authority type . . . . . . . . . . . :   *PRIVATE  
> 

I already have:  they're identical.  That's at least in 
part what makes the different behavior of the statement 
on the two systems seem odd:  on the system on which 
the created table is usable, it is owned by my profile, 
rather than the group profile as I would expect.  Note 
that GRPAUT(*NONE) on the user profile refers to 
authority granted *to* the group profile if my profile 
(rather than the group profile) is the owner; before 
looking it up, I had thought it referred to authority 
granted to group members if the group profile is the owner.

I haven't verified this yet, but a likely problem is 
that the iSeries system admins at the company don't 
know anything about SQL, and the few iSeries SQL 
experts there probably know very little about OS/400 
object authorities, at least as they tie to SQL.

> 
> 
> In article <jq7Xc.382$W_5.304@newsread1.news.pas.earthlink.net>, 
> jonball@whitehouse.not says...
> 
>>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 is *EXCLUDE, just as would be expected with 
>>*SQL naming, according to the SQL reference manual. 
>>This means that once I've DECLAREd the temporary table, 
>>I can't do a thing with it, including DROP.
>>
>>On the benchmark machine, the ownership and authorities 
>>are different.  My profile owns the table, and has *ALL 
>>object authority; *PUBLIC again has *EXCLUDE, per *SQL 
>>naming.
>>
>>On both machines, my profile is a member of the same 
>>group profile, and I am set up with OWNER(*GRPPRF). 
>>This makes it strange that my profile owns the 
>>temporary table on the benchmark machine.  My profile 
>>on the benchmark machine is user class *USER, but I 
>>have *ALLOBJ special authority; on the development 
>>machine, my user class is *PGMR, and I have no special 
>>authorities.
>>
>>To get around the problem on the development machine, 
>>I've abandoned DECLARE GLOBAL TEMPORARY TABLE and 
>>instead I use CREATE TABLE QTEMP.xxxxxx.  I have full 
>>authority on the table created.  Interestingly, the 
>>CREATE TABLE statement leaves no evidence in the job 
>>log of having run, but DECLARE GLOBAL TEMPORARY TABLE 
>>leaves messages indicating that ownership was 
>>transferred to the group profile, and that the table 
>>was created but couldn't be journaled.
>>
>>Also, when I create the table using CREATE TABLE, the 
>>object authorities for the table indicate that the 
>>group profile is the owner, and *GROUP has *ALL 
>>authority; this is true on both systems.
>>
>>Does anyone have any idea why the differences, and in 
>>particular on the development machine, why *GROUP 
>>doesn't pick up any authorities when using DECLARE 
>>GLOBAL TEMPORARY TABLE, but *does* acquire authority 
>>when using CREATE TABLE?
>>
>>

0
jonball (677)
8/26/2004 12:43:35 PM
Jonathan,

Both my systems give my profile ownership, with *ALL authority and 
*PUBLIC *NONE.

Like you, when using create table, the my group gets ownership and 
authority.

I'd say something is fishy here, maybe it is time to call IBM?


Post back if you get an answer from them.

Charles


In article <XVkXc.860$W_5.744@newsread1.news.pas.earthlink.net>, 
jonball@whitehouse.not says...
> 
> I already have:  they're identical.  That's at least in 
> part what makes the different behavior of the statement 
> on the two systems seem odd:  on the system on which 
> the created table is usable, it is owned by my profile, 
> rather than the group profile as I would expect.  Note 
> that GRPAUT(*NONE) on the user profile refers to 
> authority granted *to* the group profile if my profile 
> (rather than the group profile) is the owner; before 
> looking it up, I had thought it referred to authority 
> granted to group members if the group profile is the owner.
> 
> I haven't verified this yet, but a likely problem is 
> that the iSeries system admins at the company don't 
> know anything about SQL, and the few iSeries SQL 
> experts there probably know very little about OS/400 
> object authorities, at least as they tie to SQL.
> 
0
cwilt1 (190)
8/27/2004 12:37:16 PM
Charles Wilt wrote:
> Jonathan,
> 
> Both my systems give my profile ownership, with *ALL authority and 
> *PUBLIC *NONE.
> 
> Like you, when using create table, the my group gets ownership and 
> authority.
> 
> I'd say something is fishy here, maybe it is time to call IBM?
> 
> 
> Post back if you get an answer from them.

I haven't contacted IBM yet; that's probably something 
for someone on the permanent staff here to do.

I'm beginning to think it has something to do with the 
interaction between the QUSER profile and mine, and 
also the naming convention used (*SQL or *SYS).  Here's 
what else I've determined on the system with the problem:

- if using "green screen" interactive SQL (STRSQL command),
   the temporary table is usable for both *SYS and *SQL
   naming; the table is owned by the group profile, and
   the object authority indicates *GROUP has *ALL

- if using the iSeries navigator Run SQL Script window:

    * if using *SYS naming, the table is owned by the
      group profile, and *GROUP has *ALL authority, so I
      am able to use the table once it exists
    * if using *SQL naming, the table is owned by the
      group profile, but there is no *GROUP authority
    * in both cases, *PUBLIC has *EXCLUDE; in the case of
      *SQL naming, with neither *GROUP nor *PUBLIC 
authority,
      the table is unusable


I'd really like to be able to use DECLARE GLOBAL 
TEMPORARY TABLE, because it has the nice feature of 
WITH REPLACE, meaning I don't first have to do a DROP 
of the table, plus monitor for a failed DROP on the 
first time through.

0
jonball (677)
8/27/2004 8:20:15 PM
Looks like there may be a PTF for this:

APAR: SE16457

Error Description
An SQL stored procedure runs CREATE GLOBAL TEMPORARY TABLE, but
cannot insert data into the table due to lack of authority.  The
table is owned by the user's group profile and shows *PUBLIC
*EXCLUDE.

Problem Summary
An SQL stored procedure runs CREATE GLOBAL TEMPORARY TABLE, but
cannot insert data into the table due to lack of authority.  The
table is owned by the user's group profile and shows *PUBLIC
*EXCLUDE.

Problem Conclusion
The environment is a  member profile of a group profile is
attempting to create a temporary table through the DECLARE
GLOBAL TEMPORARY TABLE SQL statement under SQL naming rules. The
group profile is defined to own the temporary table. The problem
is authority is revoked from both the member an group authority.
This has been fixed.

PTFs Available
R520 SI14781   1000
R530 SI14782   1000

http://www-
912.ibm.com/n_dir/nas4apar.nsf/c79815e083182fec862564c00079d117/a65f1649
61c5d2cc86256ec6003c9114?OpenDocument

http://tinyurl.com/65qn8


HTH,
Charles


In article <3IMXc.380$w%6.308@newsread1.news.pas.earthlink.net>, 
jonball@whitehouse.not says...
> 
> I haven't contacted IBM yet; that's probably something 
> for someone on the permanent staff here to do.
> 
> I'm beginning to think it has something to do with the 
> interaction between the QUSER profile and mine, and 
> also the naming convention used (*SQL or *SYS).  Here's 
> what else I've determined on the system with the problem:
> 
> - if using "green screen" interactive SQL (STRSQL command),
>    the temporary table is usable for both *SYS and *SQL
>    naming; the table is owned by the group profile, and
>    the object authority indicates *GROUP has *ALL
> 
> - if using the iSeries navigator Run SQL Script window:
> 
>     * if using *SYS naming, the table is owned by the
>       group profile, and *GROUP has *ALL authority, so I
>       am able to use the table once it exists
>     * if using *SQL naming, the table is owned by the
>       group profile, but there is no *GROUP authority
>     * in both cases, *PUBLIC has *EXCLUDE; in the case of
>       *SQL naming, with neither *GROUP nor *PUBLIC 
> authority,
>       the table is unusable
> 
> 
> I'd really like to be able to use DECLARE GLOBAL 
> TEMPORARY TABLE, because it has the nice feature of 
> WITH REPLACE, meaning I don't first have to do a DROP 
> of the table, plus monitor for a failed DROP on the 
> first time through.
> 
> 
0
cwilt1 (190)
8/30/2004 12:30:07 PM
Charles Wilt wrote:
> Looks like there may be a PTF for this:
> 
> APAR: SE16457
> 
> Error Description
> An SQL stored procedure runs CREATE GLOBAL TEMPORARY TABLE, but
> cannot insert data into the table due to lack of authority.  The
> table is owned by the user's group profile and shows *PUBLIC
> *EXCLUDE.
> 
> Problem Summary
> An SQL stored procedure runs CREATE GLOBAL TEMPORARY TABLE, but
> cannot insert data into the table due to lack of authority.  The
> table is owned by the user's group profile and shows *PUBLIC
> *EXCLUDE.
> 
> Problem Conclusion
> The environment is a  member profile of a group profile is
> attempting to create a temporary table through the DECLARE
> GLOBAL TEMPORARY TABLE SQL statement under SQL naming rules. The
> group profile is defined to own the temporary table. The problem
> is authority is revoked from both the member an group authority.
> This has been fixed.
> 
> PTFs Available
> R520 SI14781   1000
> R530 SI14782   1000
> 
> http://www-
> 912.ibm.com/n_dir/nas4apar.nsf/c79815e083182fec862564c00079d117/a65f1649
> 61c5d2cc86256ec6003c9114?OpenDocument
> 
> http://tinyurl.com/65qn8
> 
> 
> HTH,

"HTH"?!  Of course it helps!  How the heck did you find it?

Thanks very much!

Of course, I'd still like to know (out of idle 
curiosity) why the DECLARE GLOBAL... behaves 
differently on the two systems at my client, but as 
long as there's a way to get it to work correctly on 
the system where it currently fails, I can put my 
curiosity aside.

Thanks again.

> Charles
> 
> 
> In article <3IMXc.380$w%6.308@newsread1.news.pas.earthlink.net>, 
> jonball@whitehouse.not says...
> 
>>I haven't contacted IBM yet; that's probably something 
>>for someone on the permanent staff here to do.
>>
>>I'm beginning to think it has something to do with the 
>>interaction between the QUSER profile and mine, and 
>>also the naming convention used (*SQL or *SYS).  Here's 
>>what else I've determined on the system with the problem:
>>
>>- if using "green screen" interactive SQL (STRSQL command),
>>   the temporary table is usable for both *SYS and *SQL
>>   naming; the table is owned by the group profile, and
>>   the object authority indicates *GROUP has *ALL
>>
>>- if using the iSeries navigator Run SQL Script window:
>>
>>    * if using *SYS naming, the table is owned by the
>>      group profile, and *GROUP has *ALL authority, so I
>>      am able to use the table once it exists
>>    * if using *SQL naming, the table is owned by the
>>      group profile, but there is no *GROUP authority
>>    * in both cases, *PUBLIC has *EXCLUDE; in the case of
>>      *SQL naming, with neither *GROUP nor *PUBLIC 
>>authority,
>>      the table is unusable
>>
>>
>>I'd really like to be able to use DECLARE GLOBAL 
>>TEMPORARY TABLE, because it has the nice feature of 
>>WITH REPLACE, meaning I don't first have to do a DROP 
>>of the table, plus monitor for a failed DROP on the 
>>first time through.
>>
>>

0
jonball (677)
8/30/2004 1:05:48 PM
No problem.

Just search the APARs on "Global Temporary" w/o the quotes.

It was the forth one listed.

I'd imagine that perhaps one systems already has it applied?

Charles


In article <MCFYc.2519$w%6.922@newsread1.news.pas.earthlink.net>, 
jonball@whitehouse.not says...
> 
> "HTH"?!  Of course it helps!  How the heck did you find it?
> 
> Thanks very much!
> 
> Of course, I'd still like to know (out of idle 
> curiosity) why the DECLARE GLOBAL... behaves 
> differently on the two systems at my client, but as 
> long as there's a way to get it to work correctly on 
> the system where it currently fails, I can put my 
> curiosity aside.
> 
> Thanks again.
> 

0
cwilt1 (190)
8/31/2004 12:22:55 PM
Charles Wilt wrote:
> No problem.
> 
> Just search the APARs on "Global Temporary" w/o the quotes.
> 
> It was the forth one listed.
> 
> I'd imagine that perhaps one systems already has it applied?

Dunno.  I'll have to wait until they apply it to the 
other, because on the system where it currently 
"works", it doesn't work quite as I'd expect.  That is, 
the temporary table is created as owned by my profile, 
even though my profile specifies that created objects 
should be owned by the group profile; possibly that's 
the fix.  I don't have authority to the DSPPTF command 
on either system to check if it's applied already on 
the benchmark system, where the table is usable.  Once 
the PTF is applied on the development machine, I'll run 
the statement and check to see what profile owns the table.

Thanks again for your help.

> 
> Charles
> 
> 
> In article <MCFYc.2519$w%6.922@newsread1.news.pas.earthlink.net>, 
> jonball@whitehouse.not says...
> 
>>"HTH"?!  Of course it helps!  How the heck did you find it?
>>
>>Thanks very much!
>>
>>Of course, I'd still like to know (out of idle 
>>curiosity) why the DECLARE GLOBAL... behaves 
>>differently on the two systems at my client, but as 
>>long as there's a way to get it to work correctly on 
>>the system where it currently fails, I can put my 
>>curiosity aside.
>>
>>Thanks again.
>>
> 
> 
0
jonball (677)
8/31/2004 1:30:18 PM
Reply:

Similar Artilces:

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

Global Temporary Table Scalability Problem
Hi, We have a complex stored procedure that runs slowly under large volumes of data. After profiling and testing, I found that the use of a single global temporary table (i.e. one w/ only 5 records) caused the problem and that if I replaced the temp table with a permanent table, the results improved dramatically. (20-50x). Some more info: the procedure in question receives as one of its params a comma-delimited-list of "principal Ids". This list is usually very short (1-6 entries). The procedure parses the list and populates a 1field/1key temp table. The procedure then uses that table in a complicated query to return the records "which the user has access to". When I came across the fact that the global temp tables performed so poorly, I tried using a permanent table to store the "principal IDs" (i.e. same key structure). The query ran 50x quicker . (This was a hack. I'd actually need a way to store these values temporary 'per session') Two questions: 1) why these counterintuitive results? Why are global temporary tables so slow? under these conditions? Why do they perform differently from permanent tables? 2) is there any way to improve the performance of global temporary tables for these circumstances? thanks, bill milbratz william milbratz wrote: > Hi, > > We have a complex stored procedure that runs slowly under large > volumes of data. > <snip> > Some more info: > the procedure in question...

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

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

GLOBAL TEMPORARY table
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 indicates that Oracle thinks the first table has 4000 rows. Now replace the GLOBAL TEMPORARY table with a real table and repeat exactly the same query. This runs in 94 milliseconds and the Explain Plan shows the correct number of rows for the driving table and an index scan on the second table, as I would have expected. Can anyone suggest a solution that will make the GLOBAL TEMPORARY implementation as fast as the real table version? BTW, why are there two sets of parallel groups under both comp.database.oracle and comp.databases.oracle? Jim Garrison jhg@athensgroup.com Jim Garrison wrote: > > > BTW, why are there two sets of parallel groups under both > comp.database.oracle and comp.databases.oracle? comp.database.oracle was created a couple of years ago by accident. Some ISPs allowed users to create newsgroups on demand. SOme people still inist on using that. comp.databases.oracle itself has been voted out, in favor of comp.databases.oracle.* heirarchy, so those of us answering could get a bit of discrimination on the questions. Jim Garrison wrote: > Scenario: > > 1) Create a GLOBAL TEMPORARY table and populate it with > one (1) r...

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

Global Temporary Tables
Each, Am wanting to create a temporary table within a PLSQL package. Have used EXECUTE IMMEDIATE 'create global temporary table temp_status........' Problem now is that package wont compile due to insert statements for this table. Compiler has no idea about this temp table or its structure. I take it temporary tables arent designed for this use are am i missing something here? Cheers Roy Munson wrote: > Each, > > Am wanting to create a temporary table within a PLSQL package. Have > used > > EXECUTE IMMEDIATE 'create global temporary table temp_status........' > > Problem now is that package wont compile due to insert statements for > this table. Compiler has no idea about this temp table or its > structure. > > I take it temporary tables arent designed for this use are am i > missing something here? > > Cheers Please read the concepts manual about global temporary tables, and forget those M$ programming practices. There can be no valid reason to create (global temporary or other) tables, using pl/sql (a.k.a. on-the-fly). And now that you're asking - yes you completely missed the purpose of global temporary tables - as well as the Oracle transaction model, I assume. -- Regards, Frank van Bortel "Roy Munson" <mtproc@yahoo.co.uk> wrote in message news:853055e7.0504220914.5e7640b8@posting.google.com... > Each, > > Am wanting to create a temporary table within a PLSQL package...

Global Temporary Tables
Hi, I am inserting records to a global temporary table(say A) in which a trigger is fired and im getting results from other global tempory tables(say B, C, D,..)... This is my scenario... I am able to fetch the results from the global temporary tables B,C,D,...when inserting directly in the table A from the front end. But I am not able to fetch the results from the global temporary tables B,C,D,...when inserting thru a web service (in dotnet)in the table A from the front end. Can somebody explain the reson for the above and give a solution to fetch the records from the global tempory tables thru webservice..Thanks in advance. ...

Global Temporary Table
Hello Oraclegurus, I would like to know if i am doing the right thing or not?? I've created a global temporary table "tempcomp". "create global temporary table tempcomp (col1 number,col2 char,col3varchar2)" . I looked into these groups and it was mentioned somewhere that default is "on commit delete rows"... I am assuming that the rows are being deleted after commit and not stored . We have a web application which would be calling a stored procedure which inserts into a global temporary some values we need the values only for temporary time i.e., till user wants to study those values . The web client users could be many and they all connect the application with their respective id but the database connectivity is using only one database userid "scott" and accessing the schema and shcema procedures. The procedure call does inserts into the temporary global table. The data that would be inserted could be like this 'bike' 'big' 'bum' etc question: If one web user queries the table .... "where col2 like 'b%' will he get the data which was inserted on execution of procedure by the first user who caused the inserts 'bike' 'bum'etc ...???? or each web user is a seperate session and the select query will be unique for each user ...????? Please let me know if i am using t...

Global temporary table
Hi all! I dont know how to declare and use a global temporary table in my java stored procedure.... I used these statements: sql="declare global temporary table session.temp(recordinteger,eventtype character(20)) not logged"; Statement st=con.createStatement(); st.execute(sql); For inserting I'm trying to do.. sql="insert into session.temp values(2003,'fatal error'); st.executeUpdate(sql); But its flagging an error.With just the declare statement there is no error.But i read somewhere that a temporary table space needs to be created.I dont kn...

Problem with Global declaration
Hi All, I am running across this, and wonder if it is a problem or feature... Here is my problem how to reproduce it: 1) Clear the workspace 2) Run this simple function as say test.m function test global bob bob = 10; save out.mat end 3) Load out.mat, look at the workspace and bob = 10, as it should be. 4) Clear the workspace, delete out.mat. 5) Run this program (same as before but delete bob = 10) function test global bob save out.mat end 6) Load out.mat, look at the work space and bob = 10?? What the heck? Where did that come from, if I delete...

Global Temporary Table #2
Hello, We are running on Oracle 8.1.7.4 on Unix (AIX 4.3.3.0) I'm facing a problem with a global temporary table (on commit preserve rows) : it is going through the loop : - delete all - insert - update At the beginning of the procedure everything is going fine. I can see how the process is going on in v$session. After half an hour, the speed begins to decrease a lot and there are lot of "db file scattered read" events pointing to my temporary table. It is true that there are lots of FTS on this temporary table, but its content is very small (about 20 rows at each step of the loop) Our temporary tablespace is PERMANENT. Now the process is nearly stucked. What can we do ? What can be the cause of this problem ? Regards, Laly. On 7 Sep 2004 05:33:19 -0700, lalydba@free.fr (Laly) wrote: >Hello, > > >We are running on Oracle 8.1.7.4 on Unix (AIX 4.3.3.0) > >I'm facing a problem with a global temporary table (on commit preserve >rows) : it is going through the loop : >- delete all >- insert >- update > >At the beginning of the procedure everything is going fine. I can see >how the process is going on in v$session. > >After half an hour, the speed begins to decrease a lot and there are >lot of "db file scattered read" events pointing to my temporary table. >It is true that there are lots of FTS on this temporary table, but its >content is very small (about 20 rows at each step of the lo...

Temporary table problem (maybe)
Hi, I have a stored procedure, that works perfectly when run from the query analyser, however if I run it through access vba, (using exec) I get a runtime error 208: invalid object name '#tmpContact'. Any ideas why this happens? The temporary table #tmpContact is used in the procedure, but as I say, it all works fine from the analyser. Thanks, Chris "Not Me" <Noone.is.home@here.com> wrote in message news:ciu4nm$r3e$1@ucsnew1.ncl.ac.uk... > Hi, > > I have a stored procedure, that works perfectly when run from the query > analyser, however if I run it through access vba, (using exec) I get a > runtime error 208: invalid object name '#tmpContact'. > > Any ideas why this happens? The temporary table #tmpContact is used in the > procedure, but as I say, it all works fine from the analyser. > > Thanks, > Chris It's not really possible to say without seeing the procedure code. Is the table created in the procedure or outside it? If outside, then has the Access connection created it before executing the procedure? Normal temp tables are only visible to the connection that created them. Simon Simon Hayes wrote: > "Not Me" <Noone.is.home@here.com> wrote in message > news:ciu4nm$r3e$1@ucsnew1.ncl.ac.uk... >>I have a stored procedure, that works perfectly when run from the query >>analyser, however if I run it through access vba, (using exec) I get a >>runti...

Global temporary tables performance
Hello, I'm trying to use a global temporary tables with a bulk insert, and the performance for the temporary table is about 5 times LESS than those of a real table - shouldent it be the opposite way? Here is my example: -- Create real table and a temporary one create table realTable( id number(15) ); create global temporary table tmpTable(id number(15)) ON COMMIT DELETE ROWS; -- Insert 50000 records with bulk operation -- into the rea table and the temporary one, and getting the time it took declare tableSize constant number := 50000; type ty_numTable is varray (50000) of number(15); numTable ty_numTable := ty_numTable(); st pls_integer; en pls_integer; begin -- Fill up a data array numTable.extend( tableSize ); for i in 1 .. tableSize loop numTable(i) := i; end loop; -- -- Inserting data to the real table st := dbms_utility.get_time(); forall i in numTable.first .. numTable.last insert into realTable values (numTable(i)); en := dbms_utility.get_Time(); dbms_output.put_line( en-st ); -- Time taken for the insert -- -- Inserting data to the temporary table st := dbms_utility.get_time(); forall i in numTable.first .. numTable.last insert into tmpTable values (numTable(i)); en := dbms_utility.get_Time(); dbms_output.put_line( en-st ); -- Time taken for the insert end; The output is: 11 57 Do you have any idea? TIA. Ronen S. Known bug (at least I think is't known, I've mentioned here a couple of times). if you do a sn...

Global temporary table and SP
Hello all, I'm using SS2K on W2K. Brieffing: Many months ago, I created a stored procedure only used by those with admin rights in SS. Now, someone else (without admin rights) has to run it. I gave him rigth to execute the SP but, at the second and more execution, he got a error message concerning a temp table already existing (see further). The SP: ------------------------------------------------------ CREATE PROCEDURE MySP @Type INT DECLARE @strSQL AS VARCHAR(4000) IF EXISTS (SELECT table_name FROM tempdb.information_Schema.tables WHERE table_name = '##MyTmpTable') DROP TABLE ##MyTmpTable SELECT @strSQL = 'SELECT MyField1, MyField2, MyField3 INTO ##MyTmpTable FROM MyTable' EXECUTE(@strSQL) IF @Type = 1 SELECT MyField1 FROM ##MyTmpTable ELSE IF @Type = 2 SELECT MyField2 FROM ##MyTmpTable ELSE SELECT MyField3 FROM ##MyTmpTable GO ------------------------------------------------------ The error I got on the second time the user run the sp is: "Table ##MyTmpTable already exists." The front-end where this SP is run is A97. That's where I got this message. This SP looks like a simple SELECT query from A97 users perspective. Please, do no argue about the way of doing the work done! It is simplified at most in order to make it short and easy to read. I have to use the command "EXECUTE(String)" and, because of this, I connot use a local temporary table instead of a ...

EXPLAIN and Global Temporary Tables
I have my developers explaining the stored procedures that they write using visual studio. These stored procedures are for DB2 os390. In one case, one of the developers has defined a global temporary table that he uses to put the input parms into and then uses it to join against to create a dataset. When he runs explain (both visual explain, and CA's explain) it chokes on the SESSION.TABLE saying that it doesn't exist. Is there anyway I can have them explain these when they have global temporary tables defined? ...

link table IBM AS400
Hi. Sorry for my english but I have MS Access in italian. My problem is by link a table from IBM As400 (with > 100.000 rows). The output of the table is many #ELIMINATO (in english "delete" / "erase".. I dont't know the exact message). I have tried with "requery" or recreating the link without results. If I *import* the table from AS400 it works perfectly without problems. Here an abstract txt BOLAZI;BOLCLI;BOLDBO;BOLNBO;BOLCAU;BOLNPR;BOLTRE;BOLMAT;BOLDES [........] [........] ;;;;;;;#Eliminato;#Eliminato ;;;;;;;#Eliminato;#Eliminato ;;;;;;;#Eliminato;#Eliminato 0;474;990204;354;10;1;1; 936;PC Intel Pentium II 400 Miditower 0;474;990204;354;10;4;1; 1890;CD-ROM drive 36X 0;474;990204;354;10;5;1; 1692;HD-SCSI 4.5 Gb IBM Ultrastar WIDE 7200 0;474;990204;354;10;6;1; 1497;HD-SCSI 9.1 Gb IBM DDRS-39130 UWIDE [........] Thanks in advance M. "Mire" <mary@hotmail.com> wrote in message news:buo9eo$k39dq$1@ID-57790.news.uni-berlin.de... > Hi. > Sorry for my english but I have MS Access in italian. > My problem is by link a table from IBM As400 (with > 100.000 rows). > > The output of the table is many #ELIMINATO (in english "delete" / "erase".. > I dont't know the exact message). > I have tried with "requery" or recreating the link without results. > > If I *import* the table from AS400 it works perfectly without problems. > &...

Problem with Global Variable Declarations (QT)
I have a problem I've been working on for days now with global variable use. I'm currently using QT for development but this same problem should apply to anytime a C++ object is trying to be used globally. Just assume the QT stuff is fine and that they are predefined library objects. Anyway, I want to have a global QTextStream object that I use to print debug information to. This program compiles fine, but if I ever try to use my global QTextStream object it gives me the error: "binary '<<' : no operator found which takes a left-hand operand of type 'QTextStre...

Problem with temporary table in Access project
Hi, I'm developing an application with an Access Project 2000 (ADP) front- end and a SQL Server 2000 back-end. I've created a temporary table thrugh ADO code then i want to use the result table (e.g. #Emp ) as a RecordSource of Report. but Unfortunately, the report could not recognize existence of this table. I've also tried prefixing #Emp with 'tempdb', also not work Any help or ideas would be gratefully appreciated. Thank you, Thank you, Ridwan -- Posted via http://dbforums.com "ridwan" <member36104@dbforums.com> wrote in message news:3255529.1061209304@dbforums.com... > > Hi, > > > > I'm developing an application with an Access Project 2000 (ADP) front- > end and a SQL Server 2000 back-end. I've created a temporary table > thrugh ADO code > > then i want to use the result table (e.g. #Emp ) as a RecordSource > of Report. > > but Unfortunately, the report could not recognize existence of > this table. > > I've also tried prefixing #Emp with 'tempdb', also not work > > > > Any help or ideas would be gratefully appreciated. Thank you, > > > Local temp tables are only visible to the connection that created them, so you need to create and populate the table, and retrieve the resultset in a single procedure. Make sure you put SET NOCOUNT ON at the start of the procedure. If you still have problems - post the SQL In n ADP, each time...

Re: Global temporary tables performance
Hi, Does anyone know the Oracle bug number? thanks Dominic Aviv wrote: > *Hello, > > I'm trying to use a global temporary tables with a bulk insert, and > the performance for the temporary table is about 5 times LESS than > those of a real table - shouldent it be the opposite way? > > Here is my example: > > -- Create real table and a temporary one > create table realTable( id number(15) ); > create global temporary table tmpTable(id number(15)) ON COMMI > DELETE > ROWS; > > -- Insert 50000 records with bulk operation > -- into the rea table and the temporary one, and getting the time it > took > declare > tableSize constant number := 50000; > type ty_numTable is varray (50000) of number(15); > numTable ty_numTable := ty_numTable(); > st pls_integer; > en pls_integer; > begin > -- Fill up a data array > numTable.extend( tableSize ); > for i in 1 .. tableSize loop > numTable(i) := i; > end loop; > -- > -- Inserting data to the real table > st := dbms_utility.get_time(); > forall i in numTable.first .. numTable.last > insert into realTable values (numTable(i)); > en := dbms_utility.get_Time(); > dbms_output.put_line( en-st ); -- Time taken for the insert > -- > -- Inserting data to the temporary table > st := dbms_utility.get_time(); > forall i in numTable.first .. numTable.last > insert into tmpTable values (numTable(i)); > en := dbm...

global temporary table and connection pooling
The environment I'm running is apache/tomcat and oracle 816. I'm using global temporary tablespaces in certain cases( they were created using on commit preserve rows option as it is a multipart query), and then the table is truncated after the last query has executed. I've noticed that occasionally if I run two independent queries using 2 different sessions, and the second query accesses the temp table before the first session has truncated it, I'm seeing results from the first session. I thought that the global temporary tables were supposed to be restricted to the session. Does this have anything to do with connection pooling? Any help/ideas would be greatly appreciated. http://asktom.oracle.com/pls/ask/f?p=4950:8:2524853494752269270::NO::F4950_P 8_DISPLAYID,F4950_P8_CRITERIA:4541826681375, Just because it is a multipart query doesn't mean you have to "on commit preserve rows". Do your multipart using the same session/connection and rows from the first query will still be there. Change the table to on commit delete, and make sure your session gets committed or rolled back after use. "Barnoit" <jbarney_ca@yahoo.ca> wrote in message news:cd2c60c8.0403050904.3988e24c@posting.google.com... > The environment I'm running is apache/tomcat and oracle 816. I'm using > global temporary tablespaces in certain cases( they were created using > on commit preserve rows option as it is a multipart query), and then > th...

Global Temporary Table performance with inserts.
I am using an Oracle Temporary table to generate a tree of data dependencies. I noticed that Inserts are significantly longer into the Temporary table than if I replaced the Global Temporary Table with a permanent table (just as a test case), almost by a factor of 50. The number of rows being inserted are approx. 100. Has any one experienced this issue and what are the workarounds? I am using Oracle 8.1.7 and have created the Global Temporary Table with "on commit preserve rows" clause. Thank you for your help in advance. Hector wrote: > I am using an Oracle Temporary table to generate a tree of data > dependencies. I noticed that Inserts are significantly longer into the > Temporary table than if I replaced the Global Temporary Table with a > permanent table (just as a test case), almost by a factor of 50. The > number of rows being inserted are approx. 100. Has any one experienced > this issue and what are the workarounds? I am using Oracle 8.1.7 and > have created the Global Temporary Table with "on commit preserve rows" > clause. Thank you for your help in advance. I've never seen this behavior. And with 100 rows I'm quite frankly amazed you can detect the time required at all. But do you really need the "on commit preserve rows"? -- Daniel Morgan http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp damorgan@x.washington.e...

Declaring a global table in PL/SQL
Why can't I have? CREATE OR REPLACE PACKAGE BODY test AS TYPE test_table IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(2); DEMO_TABLE test_table; DEMO_TABLE('NO') := 'No'; END test; / On 2005-08-04, absinth <absinth@gmail.com> wrote: > Why can't I have? > > CREATE OR REPLACE PACKAGE BODY test AS > TYPE test_table IS TABLE OF VARCHAR2(30) > INDEX BY VARCHAR2(2); > > DEMO_TABLE test_table; > DEMO_TABLE('NO') := 'No'; > > END test; > / Do you have a package specification? The following works, at least on 10g: create or replace package test as type test_table is table of varchar2(30) index by varchar2(2); demo_table test_table; end test; / create or replace package body test as begin demo_table('NO') := 'NO'; end test; / hth Rene -- Rene Nyffenegger http://www.adp-gmbh.ch/ absinth wrote: > Why can't I have? > > CREATE OR REPLACE PACKAGE BODY test AS > TYPE test_table IS TABLE OF VARCHAR2(30) > INDEX BY VARCHAR2(2); > > DEMO_TABLE test_table; > DEMO_TABLE('NO') := 'No'; > > END test; > / Er, you can. Associative arrays were introduced back in 9i. You just need a supported version of Oracle and a BEGIN keyword ;-) absinth wrote: > Why can't I have? > > CREATE...

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 a known bug? Or is my code just uncharacteristically bad? ;) I'm getting an error that says "Invalid object name '#temp'." The section of code that has an issue is (the value of @max is 25 in my test): SET @xq = 'CREATE TABLE #temp ( respid int, ' SET @i = 0 WHILE( @i <= @max ) BEGIN SET @xq = @xq + 'response' + CAST( @i AS VARCHAR( 4 ) ) + ' xml' IF ( @i < @max ) BEGIN SET @xq = @xq + ', ' END SET @i = @i + 1 END SET @xq = @xq + ' )' SELECT @nxq = CAST( @xq AS NVARCHAR( 40000 ) ) EXECUTE sp_executesql @nxq ...... DROP TABLE #temp Hi Pinney > EXECUTE sp_executesql @nxq What this does is creates a table in the scope of the EXECUTE and then table then goes away as soon as the EXECUTE is finished, But Execute does know about #temp tables created by t...

Web resources about - DECLARE GLOBAL TEMPORARY TABLE problem - comp.sys.ibm.as400.misc

Temporary - Wikipedia, the free encyclopedia
Text is available under the Creative Commons Attribution-ShareAlike License ;additional terms may apply. By using this site, you agree to the ...

Report: Facebook To Open Temporary Vancouver Office To Recruit, Train Engineering Talent
Facebook will set up a temporary office in Vancouver , British Columbia, where it will conduct “boot camps” to train recent software engineering ...

Facebook brings post insights back to Pages Manager app after temporary removal
A recent update to Pages Manager for iOS has returned detailed post insights to the product after a version earlier this month was released without ...

TFRs: Temporary Flight Restrictions Aviation Pilot NOTAMs for iPhone, iPod touch, and iPad on the iTunes ...
Get TFRs: Temporary Flight Restrictions Aviation Pilot NOTAMs on the App Store. See screenshots and ratings, and read customer reviews.

QR Code Temporary Tattoo SCANS!!! - Flickr - Photo Sharing!
Embed metadata on your skin with these temporary QR Code tattoos. You can personalize the 2D barcode with plain text, website URL, email address, ...

Paul McCartney - Temporary Secretary - YouTube
please comment~~~~~ lyrics :::: Mister Marks Can You Find For Me Someone Strong And Sweet Fitting On My Knee She Can Keep Her Job If She Gets ...

Temporary speed reduction on Abu Dhabi-Dubai highway - The National
Radars along a stretch of the road will be set at 121kph, down from 141kph, as roadwork is carried out.

Labor dilemma on temporary protection visas for refugees
... internal debate over whether to shadow the Abbott government closely on refugee policy or oppose the government's tough plan to restore temporary ...

Morrison says deficit levy 'only temporary'
Treasurer Scott Morrison says a 'deficit levy' on high-income earners was always a temporary measure.

Apple Watch Diary: A (temporary) world without Apple Pay
I said last summer in my Apple Watch Diary series that Apple Pay arriving in the UK was the tipping point for me in transforming the Watch from ...

Resources last updated: 3/24/2016 3:01:43 PM