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.


This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].