SQL Question, Stored Procedure

[Thomasb]

With a background in MS SQL Server programming I'm used to temporary
tables. Have just started to work with DB2 ver 7 on z/OS and stumbled
into the concept of GLOBAL TEMPORARY TABLE.

I have created a temporary database with a tables space. Verified that
DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER);
INSERT INTO SESSION.TEMP VALUES(10);
SELECT A FROM SESSION.TEMP;

works from a query tool.

When I try to compile the following I get errors:

CREATE PROCEDURE TEST()
RESULT SETS 1
LANGUAGE SQL
COLLID TEST
WLM ENVIRONMENT WLM2
RUN OPTIONS ''
P1: BEGIN
DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER);
DECLARE SP_SQL1 CURSOR WITH RETURN FOR
SELECT A FROM SESSION.TEMP;
INSERT INTO SESSION.TEMP VALUES(10);
OPEN SP_SQL1;
END P1

And the errors are:

DSNH016I E DSNHPARS LINE 9 COL 9 "GLOBAL" REQUIRED
DSNH104I E DSNHPARS LINE 9 COL 9 ILLEGAL SYMBOL "SP_SQL1". SOME
SYMBOLS THAT MIGHT BE LEGAL ARE: GLOBAL

Anyone who can correct me on this one?

Cheers Thomas

[Philip Sherman]

Stored procedures require all definitions to be declared before
executable statements. The declaration for the global temporary table is
actually an executable statement because it creates the table.

Swap the order of the two DECLARE statements and try it again. It's
worked fine for me on V8.1.

Phil Sherman

Thomasb wrote:

With a background in MS SQL Server programming I'm used to temporary
tables. Have just started to work with DB2 ver 7 on z/OS and stumbled
into the concept of GLOBAL TEMPORARY TABLE.

I have created a temporary database with a tables space. Verified that
DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER);
INSERT INTO SESSION.TEMP VALUES(10);
SELECT A FROM SESSION.TEMP;

works from a query tool.

When I try to compile the following I get errors:

CREATE PROCEDURE TEST()
RESULT SETS 1
LANGUAGE SQL
COLLID TEST
WLM ENVIRONMENT WLM2
RUN OPTIONS ''
P1: BEGIN
DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER);
DECLARE SP_SQL1 CURSOR WITH RETURN FOR
SELECT A FROM SESSION.TEMP;
INSERT INTO SESSION.TEMP VALUES(10);
OPEN SP_SQL1;
END P1

And the errors are:

DSNH016I E DSNHPARS LINE 9 COL 9 "GLOBAL" REQUIRED
DSNH104I E DSNHPARS LINE 9 COL 9 ILLEGAL SYMBOL "SP_SQL1". SOME
SYMBOLS THAT MIGHT BE LEGAL ARE: GLOBAL

Anyone who can correct me on this one?

Cheers Thomas

[Thomasb]

Thank you Philip. It worked.

Philip Sherman <ps******@ameritech.net> wrote in message news:<XG**********************@newssvr28.news.prod igy.com>...

Stored procedures require all definitions to be declared before
executable statements. The declaration for the global temporary table is
actually an executable statement because it creates the table.

Swap the order of the two DECLARE statements and try it again. It's
worked fine for me on V8.1.

Phil Sherman

Thomasb wrote:

With a background in MS SQL Server programming I'm used to temporary
tables. Have just started to work with DB2 ver 7 on z/OS and stumbled
into the concept of GLOBAL TEMPORARY TABLE.

I have created a temporary database with a tables space. Verified that
DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER);
INSERT INTO SESSION.TEMP VALUES(10);
SELECT A FROM SESSION.TEMP;

works from a query tool.

When I try to compile the following I get errors:

CREATE PROCEDURE TEST()
RESULT SETS 1
LANGUAGE SQL
COLLID TEST
WLM ENVIRONMENT WLM2
RUN OPTIONS ''
P1: BEGIN
DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER);
DECLARE SP_SQL1 CURSOR WITH RETURN FOR
SELECT A FROM SESSION.TEMP;
INSERT INTO SESSION.TEMP VALUES(10);
OPEN SP_SQL1;
END P1

And the errors are:

DSNH016I E DSNHPARS LINE 9 COL 9 "GLOBAL" REQUIRED
DSNH104I E DSNHPARS LINE 9 COL 9 ILLEGAL SYMBOL "SP_SQL1". SOME
SYMBOLS THAT MIGHT BE LEGAL ARE: GLOBAL

Anyone who can correct me on this one?

Cheers Thomas

[Thomasb]

Thank you Philip. It worked.

Philip Sherman <ps******@ameritech.net> wrote in message news:<XG**********************@newssvr28.news.prod igy.com>...

Stored procedures require all definitions to be declared before
executable statements. The declaration for the global temporary table is
actually an executable statement because it creates the table.

Swap the order of the two DECLARE statements and try it again. It's
worked fine for me on V8.1.

Phil Sherman

Thomasb wrote:

With a background in MS SQL Server programming I'm used to temporary
tables. Have just started to work with DB2 ver 7 on z/OS and stumbled
into the concept of GLOBAL TEMPORARY TABLE.

I have created a temporary database with a tables space. Verified that
DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER);
INSERT INTO SESSION.TEMP VALUES(10);
SELECT A FROM SESSION.TEMP;

works from a query tool.

When I try to compile the following I get errors:

CREATE PROCEDURE TEST()
RESULT SETS 1
LANGUAGE SQL
COLLID TEST
WLM ENVIRONMENT WLM2
RUN OPTIONS ''
P1: BEGIN
DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER);
DECLARE SP_SQL1 CURSOR WITH RETURN FOR
SELECT A FROM SESSION.TEMP;
INSERT INTO SESSION.TEMP VALUES(10);
OPEN SP_SQL1;
END P1

And the errors are:

DSNH016I E DSNHPARS LINE 9 COL 9 "GLOBAL" REQUIRED
DSNH104I E DSNHPARS LINE 9 COL 9 ILLEGAL SYMBOL "SP_SQL1". SOME
SYMBOLS THAT MIGHT BE LEGAL ARE: GLOBAL

Anyone who can correct me on this one?

Cheers Thomas

[Philip Sherman]

Now that you're able to define and use the temporary table; you'll
discover that, as declared, you won't be able to use it as the target of
a select statement in a stored procedure. This is caused by the
requirement that the table exist at the time the stored procedure is
compiled. It can't exist before compilation because you are defining it
in the procedure.

The technique for doing this is to define a real table GLOBAL.TEMP in
the database before you define the stored procedure. The predefined
table must contain the same column names and type definitions that your
temporary table contains.

When the stored procedure is built; the predefined table is used by UDB
to determine the characteristics TEMP. When the stored procedure is
invoked, the declaration for TEMP replaces the reference to the
predefined table. This also means that if multiple connections to the
database are invoking the same stored procedure, each will get its own
"copy" of the TEMP table, independent of the others. This also means
that a predefined table containing a single integer column could be used
by multiple stored procedures for different purposes!

The declared table TEMP is not necessarily dropped when the stored
procedure terminates. Options available in the 'DECLARE GLOBAL TEMPORARY
TABLE' statement can assist controlling its persistance across stored
procedures within the same thread. One caution - if you have a
persistant temporary table and you try to DECLARE it again, the DECLARE
will fail. Again, there is an option to avoid this problem.

The writeup in the manual about global temporary tables is excellent and
should answer any additional questions that arise.

Phil Sherman
Thomasb wrote:

Thank you Philip. It worked.

Philip Sherman <ps******@ameritech.net> wrote in message news:<XG**********************@newssvr28.news.prod igy.com>...

Stored procedures require all definitions to be declared before
executable statements. The declaration for the global temporary table is
actually an executable statement because it creates the table.

Swap the order of the two DECLARE statements and try it again. It's
worked fine for me on V8.1.

Phil Sherman

Thomasb wrote:

With a background in MS SQL Server programming I'm used to temporary
tables. Have just started to work with DB2 ver 7 on z/OS and stumbled
into the concept of GLOBAL TEMPORARY TABLE.

I have created a temporary database with a tables space. Verified that
DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER);
INSERT INTO SESSION.TEMP VALUES(10);
SELECT A FROM SESSION.TEMP;

works from a query tool.

When I try to compile the following I get errors:

CREATE PROCEDURE TEST()
RESULT SETS 1
LANGUAGE SQL
COLLID TEST
WLM ENVIRONMENT WLM2
RUN OPTIONS ''
P1: BEGIN
DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER);
DECLARE SP_SQL1 CURSOR WITH RETURN FOR
SELECT A FROM SESSION.TEMP;
INSERT INTO SESSION.TEMP VALUES(10);
OPEN SP_SQL1;
END P1

And the errors are:

DSNH016I E DSNHPARS LINE 9 COL 9 "GLOBAL" REQUIRED
DSNH104I E DSNHPARS LINE 9 COL 9 ILLEGAL SYMBOL "SP_SQL1". SOME
SYMBOLS THAT MIGHT BE LEGAL ARE: GLOBAL

Anyone who can correct me on this one?

Cheers Thomas

[Knut Stolze]

Philip Sherman wrote:

Now that you're able to define and use the temporary table; you'll
discover that, as declared, you won't be able to use it as the target of
a select statement in a stored procedure. This is caused by the
requirement that the table exist at the time the stored procedure is
compiled. It can't exist before compilation because you are defining it
in the procedure.
Of course you can declare the temp table inside the procedure and
subsequently use it, i.e. insert data into it.

CREATE PROCEDURE test ()
LANGUAGE SQL
BEGIN
DECLARE GLOBAL TEMPORARY TABLE t
name VARCHAR(128)
)
ON COMMIT PRESERVE ROWS;

INSERT
INTO t
SELECT syscat.tables;
END
@

CALL test ()@

SELECT *
FROM session.t -- "session" is the default schema name for temp tables
FETCH FIRST 5 ROWS ONLY@

NAME
---------------------------------------------------------------
A
CONNECTIVITY
Q
ATTRIBUTES
BUFFERPOOLDBPARTITIONS

5 record(s) selected.
The declared table TEMP is not necessarily dropped when the stored
procedure terminates. Options available in the 'DECLARE GLOBAL TEMPORARY
TABLE' statement can assist controlling its persistance across stored
procedures within the same thread. One caution - if you have a
persistant temporary table and you try to DECLARE it again, the DECLARE
will fail. Again, there is an option to avoid this problem.

A temp table exists until you explicitly DROP it or until the
connection/session is ended.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Philip Sherman]

Yes - I didn't mention the specific schema for temporary tables. I also
should have been clearer and stated that it "couldn't be used as the
target of a cursor in the same stored procedure".

I did not go into all of the specifics of persistence of the temporary
table. Both of us are correct in our interpretation. Your use of the ON
COMMIT guarantees that the table will exist after the stored procedure
terminates. You selected from the temporary table AFTER the stored
procedure terminated, a valid use.

My statement that the temporary table is not fully accessible within the
same stored procedure is also correct. The following code, a much
different use, demonstrates this. Note that this sample only
demonstrates my point - it's not an actual piece of code. The cursor
declaration occurs before the temporary table is defined and fails
unless the table T has been predefined in the SESSION schema.

CREATE PROCEDURE testx ()
LANGUAGE SQL
BEGIN
declare temp_cursor cursor with hold with return to client for
select * from syscat.tables a
join session.t t
on t.name = a.tabname;

DECLARE GLOBAL TEMPORARY TABLE t
(name VARCHAR(128));

insert into session.t
values('TABLES');

open r1;
END
!
If the purpose of a stored procedure is to generate a temporary table
then declaration options must be used to guarantee persistence after the
stored procedure terminates. If the temporary table is intended to be
used within the stored procedure then it should have no existence
outside of the procedure.
Phil Sherman

Knut Stolze wrote:

Philip Sherman wrote:

Now that you're able to define and use the temporary table; you'll
discover that, as declared, you won't be able to use it as the target of
a select statement in a stored procedure. This is caused by the
requirement that the table exist at the time the stored procedure is
compiled. It can't exist before compilation because you are defining it
in the procedure.

Of course you can declare the temp table inside the procedure and
subsequently use it, i.e. insert data into it.

CREATE PROCEDURE test ()
LANGUAGE SQL
BEGIN
DECLARE GLOBAL TEMPORARY TABLE t
name VARCHAR(128)
)
ON COMMIT PRESERVE ROWS;

INSERT
INTO t
SELECT syscat.tables;
END
@

CALL test ()@

SELECT *
FROM session.t -- "session" is the default schema name for temp tables
FETCH FIRST 5 ROWS ONLY@

NAME
---------------------------------------------------------------
A
CONNECTIVITY
Q
ATTRIBUTES
BUFFERPOOLDBPARTITIONS

5 record(s) selected.

The declared table TEMP is not necessarily dropped when the stored
procedure terminates. Options available in the 'DECLARE GLOBAL TEMPORARY
TABLE' statement can assist controlling its persistence across stored
procedures within the same thread. One caution - if you have a
persistent temporary table and you try to DECLARE it again, the DECLARE
will fail. Again, there is an option to avoid this problem.

A temp table exists until you explicitly DROP it or until the
connection/session is ended.

[Knut Stolze]

Philip Sherman wrote:

Yes - I didn't mention the specific schema for temporary tables. I also
should have been clearer and stated that it "couldn't be used as the
target of a cursor in the same stored procedure".
Not true - see below.
I did not go into all of the specifics of persistence of the temporary
table. Both of us are correct in our interpretation. Your use of the ON
COMMIT guarantees that the table will exist after the stored procedure
terminates.
The ON COMMIT clause guarantees that the _rows_ in the DGTT (declared global
temp table) remain after a COMMIT occurs either explicitly or implicitly
(if auto-commit was turned on). The ON COMMIT has nothing whatsoever to do
with the existence of the temp table itself.

In fact, you can easily do this (where auto-commit is turned off):

db2 -td@ -c-

db2 => CREATE PROCEDURE test () LANGUAGE SQL
BEGIN
DECLARE GLOBAL TEMPORARY TABLE t (
name VARCHAR(128)
);

INSERT INTO session.t
SELECT tabname FROM syscat.tables;
END@

db2 => CALL test()@

db2 => SELECT * FROM session.t FETCH FIRST 5 ROWS ONLY@

NAME
--------------------------------------------------------------------
ATTRIBUTES
BUFFERPOOLDBPARTITIONS
BUFFERPOOLNODES
BUFFERPOOLS
CASTFUNCTIONS

5 record(s) selected.
As you can see, I did not use the ON COMMIT PRESERVE ROWS clause and the
temp table still exists in the database.

db2 => commit@
DB20000I The SQL command completed successfully.
db2 => SELECT * FROM session.t FETCH FIRST 5 ROWS ONLY@

NAME
--------------------------------------------------------------------

0 record(s) selected.
Here you see the effect of the clause: the COMMIT caused the temp table to
be truncated. The temp table still exists just its _rows_ vanished because
they are not preserved beyond the scope of the current
transaction/unit-of-work.
You selected from the temporary table AFTER the stored
procedure terminated, a valid use.
You can also do this inside the procedure, no problem. You just have to
make sure that the cursor declaration taken place after the temp table was
declared. I know that DB2 requires the reverse order for the declaration
of cursors and temp tables, but you can use nested compound statements
instead to achieve your goal:

db2 => CREATE PROCEDURE test()
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE GLOBAL TEMPORARY TABLE t (
name VARCHAR(128)
);

INSERT INTO session.t
SELECT tabname FROM syscat.tables
FETCH FIRST 5 ROWS ONLY;

BEGIN
DECLARE c1 CURSOR WITH RETURN TO CLIENT FOR
SELECT * FROM session.t;
OPEN c1;
END;
END@

db2 => CALL test()@

Result set 1
--------------

NAME
----------------------------------------------------------------
ATTRIBUTES
BUFFERPOOLDBPARTITIONS
BUFFERPOOLNODES
BUFFERPOOLS
CASTFUNCTIONS

5 record(s) selected.

Return Status = 0
My statement that the temporary table is not fully accessible within the
same stored procedure is also correct.
No, it's not - as shown above.
Granted, it is not the most convenient way.
If the purpose of a stored procedure is to generate a temporary table
then declaration options must be used to guarantee persistence after the
stored procedure terminates. If the temporary table is intended to be
used within the stored procedure then it should have no existence
outside of the procedure.

That's not the case. A DGTT exists until you run a DROP TABLE or end your
session, i.e. disconnect from the database. If you want the existence of
the DGTT to be scoped to the procedure, then you simply clean it up before
you leave the procedure, i.e. run the DROP TABLE there.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Kent Lewandowski]

I can corroborate Philip's post from 2004-07-29 07:50:03 PST ("The
technique for doing this is to define a real table GLOBAL.TEMP in the
database before you define the stored procedure").

FYI for the beginners like myself: I noticed the table has to be
created in the same schema you are using for GLOBAL TEMPORARY tables
(default is SESSION, as Knut points out.

drop table session.TEMP;
create table session.TEMP (
YOURCOLUMNS DATATYPE
);

Then in the SP my code looks like this:

declare global temporary table TEMP (
YOURCOLUMNS DATATYPE
) NOT LOGGED;

I have to execute both statements in sequence if I am recompiling my
SP (putting the session.TEMP DDL in a separate SQL file). Otherwise I
get the following error:

DB21034E The command was processed as an SQL statement because it was
not a valid Command Line Processor command. During SQL processing it
returned: SQL0601N The name of the object to be created is identical
to the existing name "SESSION.TEMP" of type "DECLARED TEMPORARY
TABLE". LINE NUMBER=78. SQLSTATE=42710

Philip thanks for your very informative posting! Better than IBM doc.

Kent
Knut Stolze <st****@de.ibm.com> wrote in message news:<ce**********@fsuj29.rz.uni-jena.de>...

Philip Sherman wrote:

Yes - I didn't mention the specific schema for temporary tables. I also
should have been clearer and stated that it "couldn't be used as the
target of a cursor in the same stored procedure".

Not true - see below.

I did not go into all of the specifics of persistence of the temporary
table. Both of us are correct in our interpretation. Your use of the ON
COMMIT guarantees that the table will exist after the stored procedure
terminates.

The ON COMMIT clause guarantees that the _rows_ in the DGTT (declared global
temp table) remain after a COMMIT occurs either explicitly or implicitly
(if auto-commit was turned on). The ON COMMIT has nothing whatsoever to do
with the existence of the temp table itself.

In fact, you can easily do this (where auto-commit is turned off):

db2 -td@ -c-

db2 => CREATE PROCEDURE test () LANGUAGE SQL
BEGIN
DECLARE GLOBAL TEMPORARY TABLE t (
name VARCHAR(128)
);

INSERT INTO session.t
SELECT tabname FROM syscat.tables;
END@

db2 => CALL test()@

db2 => SELECT * FROM session.t FETCH FIRST 5 ROWS ONLY@

NAME
--------------------------------------------------------------------
ATTRIBUTES
BUFFERPOOLDBPARTITIONS
BUFFERPOOLNODES
BUFFERPOOLS
CASTFUNCTIONS

5 record(s) selected.
As you can see, I did not use the ON COMMIT PRESERVE ROWS clause and the
temp table still exists in the database.

db2 => commit@
DB20000I The SQL command completed successfully.
db2 => SELECT * FROM session.t FETCH FIRST 5 ROWS ONLY@

NAME
--------------------------------------------------------------------

0 record(s) selected.
Here you see the effect of the clause: the COMMIT caused the temp table to
be truncated. The temp table still exists just its _rows_ vanished because
they are not preserved beyond the scope of the current
transaction/unit-of-work.

You selected from the temporary table AFTER the stored
procedure terminated, a valid use.

You can also do this inside the procedure, no problem. You just have to
make sure that the cursor declaration taken place after the temp table was
declared. I know that DB2 requires the reverse order for the declaration
of cursors and temp tables, but you can use nested compound statements
instead to achieve your goal:

db2 => CREATE PROCEDURE test()
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE GLOBAL TEMPORARY TABLE t (
name VARCHAR(128)
);

INSERT INTO session.t
SELECT tabname FROM syscat.tables
FETCH FIRST 5 ROWS ONLY;

BEGIN
DECLARE c1 CURSOR WITH RETURN TO CLIENT FOR
SELECT * FROM session.t;
OPEN c1;
END;
END@

db2 => CALL test()@

Result set 1
--------------

NAME
----------------------------------------------------------------
ATTRIBUTES
BUFFERPOOLDBPARTITIONS
BUFFERPOOLNODES
BUFFERPOOLS
CASTFUNCTIONS

5 record(s) selected.

Return Status = 0

My statement that the temporary table is not fully accessible within the
same stored procedure is also correct.

No, it's not - as shown above.
Granted, it is not the most convenient way.

If the purpose of a stored procedure is to generate a temporary table
then declaration options must be used to guarantee persistence after the
stored procedure terminates. If the temporary table is intended to be
used within the stored procedure then it should have no existence
outside of the procedure.

That's not the case. A DGTT exists until you run a DROP TABLE or end your
session, i.e. disconnect from the database. If you want the existence of
the DGTT to be scoped to the procedure, then you simply clean it up before
you leave the procedure, i.e. run the DROP TABLE there.