indexable global temp table

Amy C. Whitehead

indexable global temp table
Does anyone have information and/or steps to create an indexable global
temp table in os/390 DB2? I can get the embedded create global temp table
to work. I have also created a global table that is permanent and used the
declare global temp code, but I could not create an index. I have not
found much information on creating the indexable global temp tables.

Any tips or pointers??

Thanks,
Amy Whitehead



[login to unmask email]

Re: indexable global temp table
(in response to Amy C. Whitehead)
What you are looking for is part of DB2 V6.1, a feature called "Declared
Temporary Tables". From the V7.1 "What's new ..." document, refering to
V6.1 features . . . <snip> Declared temporary tables complement the
existing created temporary tables available in Version 5 of DB2 for OS/390.
Declared temporary tables do not have descriptions in the catalog tables.
The tables support indexes, UPDATE statements, and DELETE statements. You
can implicitly define the columns and use the result
table from a SELECT. <end snip>





Amy C Whitehead <[login to unmask email]>@RYCI.COM> on 2000/12/29 12:07:40 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: indexable global temp table


Does anyone have information and/or steps to create an indexable global
temp table in os/390 DB2? I can get the embedded create global temp table
to work. I have also created a global table that is permanent and used the
declare global temp code, but I could not create an index. I have not
found much information on creating the indexable global temp tables.

Any tips or pointers??

Thanks,
Amy Whitehead








David Seibert

Re: indexable global temp table
(in response to Rohn.Solecki@MTS.MB.CA)
Hi Amy,
As Rohn points out, Declared global temporary tables are a feature new
in v6. It was introduced via APAR, so you might not find it in your doc if
you have hardcopy or old softcopy.

You can create indices on them and there are other differences from the v5
global temp tables.
See the doc. There are pre-reqs. to being able to create them such as a TEMP
database and tablespace.
I recall an earlier thread about these pre-reqs.

Here is DDL I have used to create an index on a dgtt.

DECLARE GLOBAL TEMPORARY TABLE
TEMSTAFF LIKE BFHDJS1.STAFFU;

CREATE UNIQUE INDEX UNNEEDED ON SESSION.TEMSTAFF
(ID ASC );

INSERT INTO SESSION.TEMSTAFF
SELECT * FROM STAFFU;

SELECT * FROM SESSION.TEMSTAFF

Note that the dgtt creator name is SESSION.

Note, this DDL works fine for me. However, a colleague crashes our DB2
subsystem when he executes the same DDL. We have an open problem with IBM on
this.


David Seibert
Compuware Corporation File-AID Product Architect
[login to unmask email]



Amy C. Whitehead

Re: indexable global temp table
(in response to David Seibert)
Yes, I am trying this on a version 6 system. We can get the temp table to
work, but not the index. How do you make a global temp table indexable?
We are creating the temp area with the DECLARE GLOBAL TEMP TABLE
SESSION.TABA LIKE X.TABA clause embedded in the code. However, the end
result seems to be the same as creating the global temp table with the
CREATE clause.



Rohn.Solecki@
MTS.MB.CA To: [login to unmask email]
Sent by: DB2 cc:
Data Base Subject: Re: indexable global temp table
Discussion
List
<[login to unmask email]
OM>


12/29/00
01:28 PM
Please
respond to
DB2 Data Base
Discussion
List






What you are looking for is part of DB2 V6.1, a feature called "Declared
Temporary Tables". From the V7.1 "What's new ..." document, refering to
V6.1 features . . . <snip> Declared temporary tables complement the
existing created temporary tables available in Version 5 of DB2 for OS/390.
Declared temporary tables do not have descriptions in the catalog tables.
The tables support indexes, UPDATE statements, and DELETE statements. You
can implicitly define the columns and use the result
table from a SELECT. <end snip>





Amy C Whitehead <[login to unmask email]>@RYCI.COM> on 2000/12/29 12:07:40 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: indexable global temp table


Does anyone have information and/or steps to create an indexable global
temp table in os/390 DB2? I can get the embedded create global temp table
to work. I have also created a global table that is permanent and used the
declare global temp code, but I could not create an index. I have not
found much information on creating the indexable global temp tables.

Any tips or pointers??

Thanks,
Amy Whitehead













Amy C. Whitehead

Re: indexable global temp table
(in response to Amy C. Whitehead)
Thanks, for the information. I did not want to embed the index create in
the COBOL Code, so we have not tried that approach. We will give it a go.

Also, thanks for crash warning!!



"Seibert, Dave"
<[login to unmask email] To: [login to unmask email]
UWARE.COM> cc:
Sent by: DB2 Data Subject: Re: indexable global temp table
Base Discussion
List
<[login to unmask email]>


12/29/00 01:44 PM
Please respond to
DB2 Data Base
Discussion List






Hi Amy,
As Rohn points out, Declared global temporary tables are a feature new
in v6. It was introduced via APAR, so you might not find it in your doc if
you have hardcopy or old softcopy.

You can create indices on them and there are other differences from the v5
global temp tables.
See the doc. There are pre-reqs. to being able to create them such as a
TEMP
database and tablespace.
I recall an earlier thread about these pre-reqs.

Here is DDL I have used to create an index on a dgtt.

DECLARE GLOBAL TEMPORARY TABLE
TEMSTAFF LIKE BFHDJS1.STAFFU;

CREATE UNIQUE INDEX UNNEEDED ON SESSION.TEMSTAFF
(ID ASC );

INSERT INTO SESSION.TEMSTAFF
SELECT * FROM STAFFU;

SELECT * FROM SESSION.TEMSTAFF

Note that the dgtt creator name is SESSION.

Note, this DDL works fine for me. However, a colleague crashes our DB2
subsystem when he executes the same DDL. We have an open problem with IBM
on
this.


David Seibert
Compuware Corporation File-AID Product Architect
[login to unmask email]








David Seibert

Re: indexable global temp table
(in response to Amy C. Whitehead)
Hi Amy,
What result do you see when you try creating an index on your declared
temp table?
Do you get a negative SQLcode?

>I did not want to embed the index create in
>the COBOL Code, so we have not tried that approach. We will give it a go.

Ahhhh. I bet that provides the answer to your problem.

Your post above sounds like you might be declaring the table in one place
(your COBOL pgm) and the Create Index in another.
If that's the case, that IS the problem.

Declared global temp tables exist only within the task in which they are
defined -- even the definition does not exist beyond the task. Unike v5 GTT
where the definition of Global Temporary Tables is stored in the DB2
catalog, Declared Global Temp Tables are not cataloged. Therefore,
presumably, nothing is known about a DGTT outside the task defining it. So
the Create Index as well as ANY other SQL referencing the DGTT must be
within the same task.

David Seibert
Compuware Corporation File-AID Product Architect
[login to unmask email]