Create Global Temp Tables

Arthur Calvetti

Create Global Temp Tables
I have a Cobol program where I'm using the following create statement

EXEC SQL
CREATE GLOBAL TEMPORARY TABLE DBSPACE
(DBNAME CHAR(8) NOT NULL,
DB_ENVR CHAR(1) NOT NULL,
VERSION_NO DECIMAL(1) ,
SPACE_TOTAL DECIMAL(18))
END-EXEC

I am inserting values into this table using the following SQL


EXEC SQL
INSERT INTO DBSPACE
VALUES (:DATASET-NAME,:ENVIROMENT,:VERSION-NO,
:SPACE-COMP)
END-EXEC.

The precompiler works but my bind gives me a -204 on the insert table name.
Can someone help me with this?



Bernd Oppolzer

Re: Create Global Temp Tables
(in response to SHIV PRASAD)
If you're on DB2 V6 or V7, I would suggest you use DECLARE GLOBAL TEMPORARY
TABLE. For the INSERT you then could code INSERT INTO SESSION.DBSPACE ...;
I think it's the qualifier SESSION which prevents the BIND errors.

This should work IMO. I don't know for CREATE GLOBAL TEMPORARY TABLE; this
was introduced with DB2 V5 and is now obsolete, AFAIK.

Regards

Bernd


Am Die, 05 Nov 2002 schrieben Sie:
> I have a Cobol program where I'm using the following create statement
>
> EXEC SQL
> CREATE GLOBAL TEMPORARY TABLE DBSPACE
> (DBNAME CHAR(8) NOT NULL,
> DB_ENVR CHAR(1) NOT NULL,
> VERSION_NO DECIMAL(1) ,
> SPACE_TOTAL DECIMAL(18))
> END-EXEC
>
> I am inserting values into this table using the following SQL
>
>
> EXEC SQL
> INSERT INTO DBSPACE
> VALUES (:DATASET-NAME,:ENVIROMENT,:VERSION-NO,
> :SPACE-COMP)
> END-EXEC.
>
> The precompiler works but my bind gives me a -204 on the insert table name.
> Can someone help me with this?
>



SHIV PRASAD

Re: Create Global Temp Tables
(in response to Arthur Calvetti)
I am a beginner to DB2, but aren't you supposed to prefix the temp table by
keyword .SESSION. So your insert statement would read like...

EXEC SQL
INSERT INTO SESSION.DBSPACE
VALUES (:DATASET-NAME,:ENVIROMENT,:VERSION-NO,
:SPACE-COMP)
END-EXEC.

-----Original Message-----
From: Calvetti, Arthur B [mailto:[login to unmask email]
Sent: Tuesday, November 05, 2002 3:44 PM
To: [login to unmask email]
Subject: Create Global Temp Tables


I have a Cobol program where I'm using the following create statement

EXEC SQL
CREATE GLOBAL TEMPORARY TABLE DBSPACE
(DBNAME CHAR(8) NOT NULL,
DB_ENVR CHAR(1) NOT NULL,
VERSION_NO DECIMAL(1) ,
SPACE_TOTAL DECIMAL(18))
END-EXEC

I am inserting values into this table using the following SQL


EXEC SQL
INSERT INTO DBSPACE
VALUES (:DATASET-NAME,:ENVIROMENT,:VERSION-NO,
:SPACE-COMP)
END-EXEC.

The precompiler works but my bind gives me a -204 on the insert table name.
Can someone help me with this?








Arthur Calvetti

Re: Create Global Temp Tables
(in response to Bernd Oppolzer)
The problem with a Declare Global Temporary Table is you need to define a
database prior to using it. This is something I'm trying to avoid.

-----Original Message-----
From: Bernd Oppolzer [mailto:[login to unmask email]
Sent: Tuesday, November 05, 2002 1:17 PM
To: [login to unmask email]
Subject: Re: Create Global Temp Tables


If you're on DB2 V6 or V7, I would suggest you use DECLARE GLOBAL TEMPORARY
TABLE. For the INSERT you then could code INSERT INTO SESSION.DBSPACE ...;
I think it's the qualifier SESSION which prevents the BIND errors.

This should work IMO. I don't know for CREATE GLOBAL TEMPORARY TABLE; this
was introduced with DB2 V5 and is now obsolete, AFAIK.

Regards

Bernd


Am Die, 05 Nov 2002 schrieben Sie:
> I have a Cobol program where I'm using the following create statement
>
> EXEC SQL
> CREATE GLOBAL TEMPORARY TABLE DBSPACE
> (DBNAME CHAR(8) NOT NULL,
> DB_ENVR CHAR(1) NOT NULL,
> VERSION_NO DECIMAL(1) ,
> SPACE_TOTAL DECIMAL(18))
> END-EXEC
>
> I am inserting values into this table using the following SQL
>
>
> EXEC SQL
> INSERT INTO DBSPACE
> VALUES (:DATASET-NAME,:ENVIROMENT,:VERSION-NO,
> :SPACE-COMP)
> END-EXEC.
>
> The precompiler works but my bind gives me a -204 on the insert table
name.
> Can someone help me with this?
>








teldb2kals

Re: Create Global Temp Tables
(in response to Arthur Calvetti)
Hi Arthur,

You would need some database (to which the user has authorisation) for
Create GTT as well.

Have you tried using the bind VALIDATE(RUN) option to avoid the error for
non-existing objects ?

Regards,
Kals


On Tue, 5 Nov 2002 13:35:01 -0800, Calvetti, Arthur B
<[login to unmask email]> wrote:

>The problem with a Declare Global Temporary Table is you need to define a
>database prior to using it. This is something I'm trying to avoid.
>
>-----Original Message-----
>From: Bernd Oppolzer [mailto:[login to unmask email]
>Sent: Tuesday, November 05, 2002 1:17 PM
>To: [login to unmask email]
>Subject: Re: Create Global Temp Tables
>
>
>If you're on DB2 V6 or V7, I would suggest you use DECLARE GLOBAL TEMPORARY
>TABLE. For the INSERT you then could code INSERT INTO SESSION.DBSPACE ...;
>I think it's the qualifier SESSION which prevents the BIND errors.
>
>This should work IMO. I don't know for CREATE GLOBAL TEMPORARY TABLE; this
>was introduced with DB2 V5 and is now obsolete, AFAIK.
>
>Regards
>
>Bernd
>
>
>Am Die, 05 Nov 2002 schrieben Sie:
>> I have a Cobol program where I'm using the following create statement
>>
>> EXEC SQL
>> CREATE GLOBAL TEMPORARY TABLE DBSPACE
>> (DBNAME CHAR(8) NOT NULL,
>> DB_ENVR CHAR(1) NOT NULL,
>> VERSION_NO DECIMAL(1) ,
>> SPACE_TOTAL DECIMAL(18))
>> END-EXEC
>>
>> I am inserting values into this table using the following SQL
>>
>>
>> EXEC SQL
>> INSERT INTO DBSPACE
>> VALUES (:DATASET-NAME,:ENVIROMENT,:VERSION-NO,
>> :SPACE-COMP)
>> END-EXEC.
>>
>> The precompiler works but my bind gives me a -204 on the insert table
>name.
>> Can someone help me with this?
>>
>
>
>
the
>
>
>
>
>
the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



Stephen Mallett

Re: Create Global Temp Tables
(in response to teldb2kals)
You may get the same error with VALIDATE(RUN) as the embedded create
wouldn't have been executed by RUN time ?

You should consider externalising the CREATE GTT - for one thing you'd be
able to run multiple occurrences of the package similtaneously (whereas you
can't with the CREATE embedded as only the first instance would be able to
create the GTT).

I create my GTT's just the once through SPUFI,etc and usually don't bother
DROPing them.

regards,
Steve



Bernd Oppolzer

Re: Create Global Temp Tables
(in response to Stephen Mallett)
well, but with declared global temptables, you can do a lot of more interesting
things, from the application point of view. you can define indexes on them,
they don't need an open cursor to survive the commit, and you can easily define
them as a result of a select statement. looks good to me, and i persuaded our
DBAs to create the TEMP database, with success.

Regards

Bernd



Am Die, 05 Nov 2002 schrieben Sie:
> The problem with a Declare Global Temporary Table is you need to define a
> database prior to using it. This is something I'm trying to avoid.
>



Ava Collins

Re: Create Global Temp Tables
(in response to Bernd Oppolzer)



Ava Collins

Re: Create Global Temp Tables
(in response to Ava Collins)
Bernd writes:

> but with declared global temptables, <snip> you can define indexes on them,
> they don't need an open cursor to survive the commit, and you can easily
> define
> them as a result of a select statement. looks good to me, and i persuaded
> our
> DBAs to create the TEMP database, with success.
>

Not only that, but declared global temp tables don't compete for DB2's
general workspace like created global temp tables do.

Jacquie

Phil Grainger

Re: Create Global Temp Tables
(in response to Ava Collins)
The problem is the extremely subtle (and not so subtle) differences between
GLOBAL TEMPORARY TABLES
and
DECLARED GLOBAL TEMPORARY TABLES
(don't you just wish IBM had thought of a different name?)

Anyway,

GLOBAL TEMPORARY TABLES are CREATED and their definitions exist in
SYSTABLES, so you cannot use them UNTIL they have been created. In your
case, the BIND fails because you haven't executed the CREATE yet (if this is
REALLY what you want to do, then bind with VALIDATE(RUN) instead, but see
below...)

DECLARED GLOBAL TEMPORARY TABLES are DECLARED and their definitions do NOT
exist in SYSTABLES. The only exist AFTER the DECLARE but binds will
automatically use VALIDATE(RUN) for all references to them

Hope this helps

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: Calvetti, Arthur B [mailto:[login to unmask email]
Sent: 05 November 2002 20:44
To: [login to unmask email]
Subject: [DB2-L] Create Global Temp Tables


I have a Cobol program where I'm using the following create statement

EXEC SQL
CREATE GLOBAL TEMPORARY TABLE DBSPACE
(DBNAME CHAR(8) NOT NULL,
DB_ENVR CHAR(1) NOT NULL,
VERSION_NO DECIMAL(1) ,
SPACE_TOTAL DECIMAL(18))
END-EXEC

Peter Backlund

Re: Create Global Temp Tables
(in response to Phil Grainger)
But still do remember;
if you have a declared global temporary table,
you have to reference it with the qualifier SESSION

Best regards,

Peter

Grainger, Phil wrote:

> The problem is the extremely subtle (and not so subtle) differences
> between
> GLOBAL TEMPORARY TABLES
> and
> DECLARED GLOBAL TEMPORARY TABLES
> (don't you just wish IBM had thought of a different name?)
>
> Anyway,
>
> GLOBAL TEMPORARY TABLES are CREATED and their definitions exist in
> SYSTABLES, so you cannot use them UNTIL they have been created. In
> your case, the BIND fails because you haven't executed the CREATE yet
> (if this is REALLY what you want to do, then bind with VALIDATE(RUN)
> instead, but see below...)
>
> DECLARED GLOBAL TEMPORARY TABLES are DECLARED and their definitions do
> NOT exist in SYSTABLES. The only exist AFTER the DECLARE but binds
> will automatically use VALIDATE(RUN) for all references to them
>
> Hope this helps
>
> Phil Grainger
> Computer Associates
> Product Manager, DB2
> Tel: +44 (0)161 928 9334
> Fax: +44 (0)161 941 3775
> Mobile: +44 (0)7970 125 752
> [login to unmask email]
>
>
> -----Original Message-----
> From: Calvetti, Arthur B [mailto:[login to unmask email]
> Sent: 05 November 2002 20:44
> To: [login to unmask email]
> Subject: [DB2-L] Create Global Temp Tables
>
>
> I have a Cobol program where I'm using the following create statement
>
> EXEC SQL
> CREATE GLOBAL TEMPORARY TABLE DBSPACE
> (DBNAME CHAR(8) NOT NULL,
> DB_ENVR CHAR(1) NOT NULL,
> VERSION_NO DECIMAL(1) ,
> SPACE_TOTAL DECIMAL(18))
> END-EXEC
>

--

=====> See you in October, 2003 at IDUG in Nice, France <======

+-------------------------------+---------------------------------+
| Peter G Backlund | Telephone: 08 - 38 69 08 |
| Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 |
| Smastuguvagen 2 | Country Code (Sweden): 46 |
| S-165 72 HASSELBY | |
| Sweden | E-mail: [login to unmask email] |
+-------------------------------+---------------------------------+
| IBM Database GOLD Consultant |
+-------------------------------+---------------------------------+
| Working with DB2 since 1981 ... and forever! |
+-----------------------------------------------------------------+



Walter Jani&#223;en

Re: Create Global Temp Tables
(in response to Peter Backlund)
Hi

One Thing I want to add: Each SQL agint a DTT is treated as dynamic SQL
with one difference: they can't use the dynamic statement cache. That's my
main problem, I have with DTT's



Phil Grainger

Re: Create Global Temp Tables
(in response to Walter Janißen)
Walter,

I've not seen this restriction about DGTTs and the dynamic statement cache -
where did you read it (or hear about it)?

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: Walter Janißen [mailto:[login to unmask email]
Sent: 06 November 2002 11:51
To: [login to unmask email]
Subject: Re: [DB2-L] Create Global Temp Tables


Hi

One Thing I want to add: Each SQL agint a DTT is treated as dynamic SQL
with one difference: they can't use the dynamic statement cache. That's my
main problem, I have with DTT's


visit the


Walter Jani&#223;en

Re: Create Global Temp Tables
(in response to Phil Grainger)
Phil

Recently I spoke to Jim Yothers just about that problem and he agreed with
me.

What do you think about that (DB2 for z/OS Application programming Topics):

> ... These static SQL statements which reference a table-name qualified by
> SESSION are incrementally bound at run time, if they are executed by the
> application process. ...

and a few pages later:

> Dynamic statement caching is not supprorted for any statement containing
> a DTT.



Horacio Villa

Re: Create Global Temp Tables
(in response to Walter Janißen)
I think the answer is: if you put the CGTT in the same program where you
code the insert, at bind time the table is not created yet and you get -204.
Horacio Villa
TTI S.A.
Buenos Aires - Argentina

>Am Die, 05 Nov 2002 schrieben Sie:
>> I have a Cobol program where I'm using the following create statement
>>
>> EXEC SQL
>> CREATE GLOBAL TEMPORARY TABLE DBSPACE
>> (DBNAME CHAR(8) NOT NULL,
>> DB_ENVR CHAR(1) NOT NULL,
>> VERSION_NO DECIMAL(1) ,
>> SPACE_TOTAL DECIMAL(18))
>> END-EXEC
>>
>> I am inserting values into this table using the following SQL
>>
>>
>> EXEC SQL
>> INSERT INTO DBSPACE
>> VALUES (:DATASET-NAME,:ENVIROMENT,:VERSION-NO,
>> :SPACE-COMP)
>> END-EXEC.
>>
>> The precompiler works but my bind gives me a -204 on the insert table
>name.
>> Can someone help me with this?
>>
>
>
>
the
>
>
>
>
>
the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can








Phil Grainger

Re: Create Global Temp Tables
(in response to Horacio Villa)
Thanks Walter

It sounds to me like something that may get "fixed" in a later release of
DB2 - I can think of no reason not to cache the statements UNLESS IBM have
taken the view that most statements referencing DGTTs will only be executed
once (no FETCHing from big tables then) so there's no point in caching them.

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: Walter Janißen [mailto:[login to unmask email]
Sent: 06 November 2002 12:45
To: [login to unmask email]
Subject: Re: [DB2-L] Create Global Temp Tables


Phil

Recently I spoke to Jim Yothers just about that problem and he agreed with
me.

What do you think about that (DB2 for z/OS Application programming Topics):

> ... These static SQL statements which reference a table-name qualified by
> SESSION are incrementally bound at run time, if they are executed by the
> application process. ...

and a few pages later:

> Dynamic statement caching is not supprorted for any statement containing
> a DTT.


visit the


Scott Fauque

Re: Create Global Temp Tables
(in response to Arthur Calvetti)
On Tue, 5 Nov 2002 17:14:32 -0600, Stephen Mallett
<[login to unmask email]> wrote:

<<snip>>
>You should consider externalising the CREATE GTT - for one thing you'd be
>able to run multiple occurrences of the package similtaneously (whereas
you
>can't with the CREATE embedded as only the first instance would be able to
>create the GTT).
>
>I create my GTT's just the once through SPUFI,etc and usually don't bother
>DROPing them.
<<snip>>

From a couple articles I've read that is another advantage of DECLARED temp
tables ... that is each application gets their own instance.

http://www.idug.org/member/journal/summer00/articl11.cfm
http://www.idug.org/member/journal/mar00/techtalk.cfm

Scott Fauque



Arthur Calvetti

Re: Create Global Temp Tables
(in response to Phil Grainger)
Thanks to all who took the time to answer my question. Using Val(Run)
worked.









Isaac Yassin

Re: Create Global Temp Tables
(in response to Scott Fauque)
Hi,

DGTT - Declared Global Temporary tables are more useful but you pay the
price of building them while running.
I would not use them for OLTP when response time is an issue.

For best performance, give them a separate BP, not with DSNDB07.

Isaac Yassin
DBMS & IT Consultant
IBM Certified Solution Expert
DB2 V7.1 Database Administration for OS/390



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Calvetti, Arthur B
Sent: Tuesday, November 05, 2002 11:35 PM
To: [login to unmask email]
Subject: Re: Create Global Temp Tables


The problem with a Declare Global Temporary Table is you need to define
a database prior to using it. This is something I'm trying to avoid.



SHIV PRASAD

Re: Create Global Temp Tables
(in response to Isaac Yassin)
Can't one Declare it once and then keep reusing (put a check for SQLCode
42710 inside the code), unless OLTP system is designed such that it resets
sessions repeatedly?

Thanks

-----Original Message-----
From: Isaac Yassin [mailto:[login to unmask email]
Sent: Wednesday, November 06, 2002 2:10 PM
To: [login to unmask email]
Subject: Re: Create Global Temp Tables


Hi,

DGTT - Declared Global Temporary tables are more useful but you pay the
price of building them while running.
I would not use them for OLTP when response time is an issue.

For best performance, give them a separate BP, not with DSNDB07.

Isaac Yassin
DBMS & IT Consultant
IBM Certified Solution Expert
DB2 V7.1 Database Administration for OS/390



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Calvetti, Arthur B
Sent: Tuesday, November 05, 2002 11:35 PM
To: [login to unmask email]
Subject: Re: Create Global Temp Tables


The problem with a Declare Global Temporary Table is you need to define
a database prior to using it. This is something I'm trying to avoid.








Isaac Yassin

Re: Create Global Temp Tables
(in response to SHIV PRASAD)
Hi,

42710 is SQLSTATE not SQLCODE and it is connected with too many SQLCODES
... :-)

Anyway, in OLTP, when working pseudo-conversational there's nothing left
when you return.
For conversational, you can as long as you don't close the transaction.
As conversational may cause
many other problems it is not used usually.

Isaac Yassin
DBMS & IT Consultant
IBM Certified Solution Expert
DB2 V7.1 Database Administration for OS/390



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of PRASAD, SHIV
Sent: Wednesday, November 06, 2002 9:28 PM
To: [login to unmask email]
Subject: Re: Create Global Temp Tables


Can't one Declare it once and then keep reusing (put a check for SQLCode
42710 inside the code), unless OLTP system is designed such that it
resets sessions repeatedly?

Thanks

-----Original Message-----
From: Isaac Yassin [mailto:[login to unmask email]
Sent: Wednesday, November 06, 2002 2:10 PM
To: [login to unmask email]
Subject: Re: Create Global Temp Tables


Hi,

DGTT - Declared Global Temporary tables are more useful but you pay the
price of building them while running. I would not use them for OLTP when
response time is an issue.

For best performance, give them a separate BP, not with DSNDB07.

Isaac Yassin
DBMS & IT Consultant
IBM Certified Solution Expert
DB2 V7.1 Database Administration for OS/390



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Calvetti, Arthur B
Sent: Tuesday, November 05, 2002 11:35 PM
To: [login to unmask email]
Subject: Re: Create Global Temp Tables


The problem with a Declare Global Temporary Table is you need to define
a database prior to using it. This is something I'm trying to avoid.



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can