Accessing an already existing temporary table

[Mike Hubbard]

I have read many many messages about temporary tables and stored
procedures. Still, I am struggling with a simple concept. I have a
java program which creates a temporary table. I now want to access
(read) this table in a stored procedure (written in SQL). This stored
procedure is called from the same java program. My question is - how
do I "compile" the stored procedure and avoid the error message:
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0204N "SESSION.ALERTS MASSUPDATES" is an undefined name. LINE
NUMBER=15.
SQLSTATE=42704

I am running DB2 V8 on Linux.

Thanks so much
Mike Hubbard

[Sean McKeough]

You'd have to make anything referencing the table dynamic sql (build the
select in a string var, and the execute immediate on it).

Mike Hubbard wrote:

I have read many many messages about temporary tables and stored
procedures. Still, I am struggling with a simple concept. I have a
java program which creates a temporary table. I now want to access
(read) this table in a stored procedure (written in SQL). This stored
procedure is called from the same java program. My question is - how
do I "compile" the stored procedure and avoid the error message:
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0204N "SESSION.ALERTS MASSUPDATES" is an undefined name. LINE
NUMBER=15.
SQLSTATE=42704

I am running DB2 V8 on Linux.

Thanks so much
Mike Hubbard

[Serge Rielau]

Mike,

The declared global temp (aka DGTT) has to exist when you create the
stored procedure.

In my experience customers throw the declarations into a procedure of
it's own, e.g.

CREATE PROCEDURE setup_temps()
BEGIN
DECLARE GLOBAL TEMPORARY TABLE SESSION.T1(c1 INT);
END
%

CALL setup_temps();

CREATE PROCEDURE myproc()...

So whenever you run a CREATE PROCEDURE you would precede it with a
CALL setup_temps();

And in your App you throw in the same CALL:
....
CALL setup_temps();
.....
CALL myproc();
....

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Knut Stolze]

Mike Hubbard wrote:

I have read many many messages about temporary tables and stored
procedures. Still, I am struggling with a simple concept. I have a
java program which creates a temporary table. I now want to access
(read) this table in a stored procedure (written in SQL). This stored
procedure is called from the same java program. My question is - how
do I "compile" the stored procedure and avoid the error message:
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0204N "SESSION.ALERTS MASSUPDATES" is an undefined name. LINE
NUMBER=15.
SQLSTATE=42704

Besides Serge's and Sean's remarks, there is yet another way:

Have one procedure (or normal statement) create the temp table, and in the
body of the stored procedure that you want to create, you can do this:

CREATE PROCEDURE ...
BEGIN
IF 1 = 0 THEN
DECLARE GLOBAL TEMPORARY TABLE mytemp ...
END IF

-- now you can use "mytemp"
END

That way, you avoid the SQL0204 but the temp table is not created inside the
procedure. At execution time, the already existing temp table is used
instead.

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

[Mike Hubbard]

I go from very frustrated to having 3 great options. Thanks so much to everyone.

Mike
Knut Stolze <st****@de.ibm. com> wrote in message news:<c5******* ***@fsuj29.rz.u ni-jena.de>...

Mike Hubbard wrote:

I have read many many messages about temporary tables and stored
procedures. Still, I am struggling with a simple concept. I have a
java program which creates a temporary table. I now want to access
(read) this table in a stored procedure (written in SQL). This stored
procedure is called from the same java program. My question is - how
do I "compile" the stored procedure and avoid the error message:
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0204N "SESSION.ALERTS MASSUPDATES" is an undefined name. LINE
NUMBER=15.
SQLSTATE=42704

Besides Serge's and Sean's remarks, there is yet another way:

Have one procedure (or normal statement) create the temp table, and in the
body of the stored procedure that you want to create, you can do this:

CREATE PROCEDURE ...
BEGIN
IF 1 = 0 THEN
DECLARE GLOBAL TEMPORARY TABLE mytemp ...
END IF

-- now you can use "mytemp"
END

That way, you avoid the SQL0204 but the temp table is not created inside the
procedure. At execution time, the already existing temp table is used
instead.

[Serge Rielau]

Correct. This "pseudo-pragma" approach does have an achilles-heel however.
It lies in the fact that the definitoion for the DGTT is now not
centralized.

CREATE PROCEDURE p1(OUT c3 INT)
BEGIN
DECLARE c1 INT;
DECLARE c2 INT DEFAULT 5;
DECLARE c3 INT;
IF 1 = 0 THEN
DECLARE GLOBAL TEMPORARY TABLE SESSION.T1
(c1 INT);
END ID;

SELECT c2 + c1 INTO c3 FROM SESSION.T1;
END
@
DECLARE GLOBAL TEMPORARY TABLE SESSION.T1
(c1 INT, c2 INT)
@
INSERT INTO SESSION.T1 VALUES (1, 2)
@
CALL p1(?)
=> c3: 6

Good luck debugging that one.
Other scenarios are implicit bind errors.
The SQL/PSM standard requires variables to be resolved form the inside out.
I.e. first DB2 will try to bind c2 to the table. Only if that fails it
binds to the variable c2 (moving from the inner most compount towards
the parameters (and in some future version to global variables).
The decision what is part of the SQL Query and what is a variable of
sorts is made at CREATE PROCEDURE time. It is irrevocable.
So if you pass in another signature, add a column to a table, doesn't
matter. DB2 has made up its' midn on who is who.

It's one of those things where the SQL Standard isn't exactly
encouraging usability. Read: It pays to have a naming convention.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Paul Reddin]

Serge,

Couldn't

CALL setup_temps();

simply be put into myproc() ??

Thus relieving the App. of the CALL myproc(); ?

also, would this then allow myproc() to be implemented
as static sql?
- a problem we had with temp tables were that they precluded
the procedures from being static.

I'll try anyway.

Thanks.

Paul.

Serge Rielau <sr*****@ca.e ye-be-em.com> wrote in message news:<c5******* ***@hanover.tor olab.ibm.com>.. .

So whenever you run a CREATE PROCEDURE you would precede it with a
CALL setup_temps();

And in your App you throw in the same CALL:
...
CALL setup_temps();
....
CALL myproc();
...

Cheers
Serge

[Serge Rielau]

Paul,

If the temp table does not exist when you create the procedure then
the you need to use dynamic SQL.
However. If you do the CALL in your DDL script, then the procedure will
create just fine with static SQL.
This does not preclude you from embedding the CALL into the stored
procedure for execution.

Note that all statemenst that involve temp tables will be compiled on
the fly once the procedure executes for the first time and recompiled
everytime the temp gets recreated.
This technique is liek dynamic SQL in a way, but it observes
conservative semantics (timestamp, function reolution, schema, path, ...).
You find it explained in VALIDATE RUN under BIND or PREPARE.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab