On 26-Nov-2015 23:09 -0600, Justin Dearing wrote:
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;
If the intention is to create the temporary table vs a result-set,
then according to the documentation, the select-statement of the
as-result-table for that DECLARE [DDL] statement can not refer to
_variables_ and the declared parameter is generated implicitly as a
variable:
[
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/db2/rbafzdgtt.htm]
"... The select-statement must not refer to variables or include
parameter markers (question marks). ..."
However I believe that the above documentation should have been
updated similar to how the CREATE TABLE [DDL] statement docs were
updated, to suggest that a _global variable_ can be referenced in the
select-statement:
[
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/db2/rbafzhctabl.htm]
"... The select-statement must not reference variables, ≥ but may
reference global variables. ≤ ..."
How do pass a parameter?
Per the above doc references and the inference that the update to the
latter applies to the former [and I did not submit a comment to update
the docs to reflect that], the following is verified to function on IBM
i 7.1:
create or replace variable xbig bigint
-- issued in the job that will CALL FOO.BAR
CREATE OR REPLACE PROCEDURE FOO.BAR
( IN ROW_ID BIGINT DEFAULT 0)
...
/* pre-requisite assumed complete; i.e. a prior: */
/* create variable xbig bigint */
set xbig = row_id ;
DECLARE GLOBAL TEMPORARY TABLE BAR_LOAD AS
( SELECT A.* FROM TABLE(FOO.BARBYID( xbig )) as A )
WITH DATA WITH REPLACE ;
...
- 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?
The WITH REPLACE should be sufficient?
An isolation-clause on the select-statement or an isolation-level on
the SET OPTION-statement along with ON COMMIT and\or NOT LOGGED ON
ROLLBACK clauses, or an ATOMIC specification on the BEGIN, and\or a DROP
TABLE statement can also have an effect on the TABLE and row-data.
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?
The concern is for overwriting the data and\or temporary table, I
presume; i.e. not the stored procedure? If so...
The ATOMIC keyword or for example a SET OPTION COMMIT=*CHG would
assist for that generally; e.g. for CREATE TABLE [AS] statements. But
per the effect of DECLARE GLOBAL TEMPORARY TABLE being effectively just
a CREATE TABLE into library QTEMP [aka SESSION], and given each job has
their own\unique copy of QTEMP, that alone prevents any collisions.
- I seem to gather that outside of a stored procedure variables are
only global to the database instance.
I do not follow.
Is there some way to declare a variable that is only visible to a
job?
CREATE VARIABLE
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?
I often bring up these four pages when writing PROCEDURE routines
[choosing CREATE FUNCTION (SQL) instead for FUNCTION routines]:
[
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/db2/rbafzcrtpsf.htm]
_CREATE PROCEDURE_ (SQL)
"The CREATE PROCEDURE (SQL) statement creates an SQL procedure at the
current server.
..."
[
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/db2/rbafzsqlprocstmt.htm]
_SQL-procedure-statement_
"An SQL control statement may allow multiple SQL statements to be
specified within the SQL control statement. These statements are defined
as SQL procedure statements.
..."
[
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzsqlcontstmts.htm]
_SQL control statements_
"Control statements are SQL statements that allow SQL to be used in a
manner similar to writing a program in a structured programming
language. SQL control statements provide the capability to control the
logic flow, declare and set variables, and handle warnings and
exceptions. Some SQL control statements include other nested SQL statements.
SQL-control-statement
|--+-assignment-statement------+----------------------------|
+ ...
+-compound-statement--------+
..."
[
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/db2/rbafzcompoundstmt.htm]
_compound-statement_
"A compound statement groups other statements together in an SQL
procedure. A compound statement allows the declaration of SQL variables,
cursors, and condition handlers.
..."
Is there a good crash course in writing stored procedures
with all these gotchas for someone coming from other RDBMSes?
I find searching the web for "CREATE PROCEDURE" "EXAMPLE", or
similar, often can be fruitful; esp. when combined with keywords for
something specific with which I am having difficulties, e.g. "DECLARE
GLOBAL TEMPORARY TABLE"
Typically looking at "porting" or "migrating" docs; I seem to recall
there was something helpful document(s) for porting to DB2 that were
labeled primarily with the LUW moniker, but being part of the DB2 family
the syntax\coding issues will typically be the same despite that the
operational\environmental concerns are likely to be quite distinct
between the various DB2s.
midrange.com
