indexable global temp table

Amy C. Whitehead

indexable global temp table

December 29, 2000 12:07 PM

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]

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>

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.

David Seibert

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.

Amy C. Whitehead

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.

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>

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.

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

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.