Re: Some DB2 CREATE/DECLARE questions

Hi Justin,

I vaguely remember encountering a limitation doing something like that from
an attempt a few years ago using a TEMPORARY TABLE inside a function. If I
remember correctly, there's a limitation where DECLARE GLOBAL TEMPORARY
TABLE code can't reference a parameter of the function. I'm guessing the
same applies to procedures.

Try changing it to be:

CREATE OR REPLACE QTEMP.BAR_LOAD AS (
SELECT * FROM TABLE(FOO.BARBYID(ROW_ID)) a
)

Or, if you don't have the PTFs for CREATE OR REPLACE TABLE, then try:

DROP TABLE QTEMP.BAR_LOAD
;
CREATE OR REPLACE QTEMP.BAR_LOAD AS (
SELECT * FROM TABLE(FOO.BARBYID(ROW_ID)) a
)

At the moment, I can't think of a way to scope a temporary table to a
single procedure. You could use a naming standard where the temporary
table name matches the name of the procedure. Given that you can have
multiple functions or procedures with the same name using parameter
overloading, even that won't ensure 100% uniqueness.

You could call a function that generates a unique table name, but using a
table name calculated at run time will mean use of dynamic SQL, so a
performance penalty is paid for that.

SQL Global Variables are scoped to a job.

Here's the title of a redbook with some useful information, but it doesn't
contain all the "gotchas":
* Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal
Database for iSeries

Mike

date: Fri, 27 Nov 2015 05:09:35 +0000
from: Justin Dearing <zippy1981@xxxxxxxxx>
subject: Some DB2 CREATE/DECLARE questions

I've written my first table function and my first stored procedure. I think
I have a clear idea this is a lot different from Microsoft SQL Server. I
have a few things I can't seem to figure out that I hope some of you can
shed some light on.


- My procedure signature is CREATE OR REPLACE PROCEDURE FOO.BAR (IN
ROW_ID BIGINT DEFAULT 0). I can use table.ID=ROW_ID in my WHERE clauses
in
the SELECT statements in my SQL just fine. However, I cannot pass it to
a
tabular stored procedure that looks like this. What am I doing wrong:

DECLARE GLOBAL TEMPORARY TABLE BAR_LOAD AS (
SELECT * FROM TABLE(FOO.BARBYID(ROW_ID)) a
)
WITH DATA
WITH REPLACE;
How do pass a parameter?

- I verified by experiment and the docs that global temporary tables are
global to the job (and therefore JDBC connection if I'm using SQL
workbench. Is there any way to scope a temporary tables to just inside a
stored procedure? If not, is there some keyword to make a stored
procedure
an atomic transaction so I don't have to worry about two jobs calling
the
same stored procedure at the same time and overwriting my stored
procedure?
- I seem to gather that outside of a stored procedure variables are only
global to the database instance. Is there some way to declare a variable
that is only visible to a job?

Is there a section of the 7.2 manuals besides the SQL Statement reference,
or a redbook I should be reading that would tell me these things? Is there
a good crash course in writing stored procedures with all these gotchas for
someone coming from other RDBMSes?

Justin