Re: Some DB2 CREATE/DECLARE questions

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Having enough fun yet, man?  :)

Have you gone to the DB2 for IBM i web site? 
http://www.ibm.com/systems/i/software/db2 has a Resources tab - also the 
DB2 Family Common SQL Reference Document on the main page.

On the Resources tab is a link for porting information that may have 
stuff you want. Not sure if it's enough, but it may have some help. And 
there are some tools mentioned there.

I know there is an ATOMIC keyword - haven't used it, so can't speak to 
its exact usage, although I understand what it generally means.

There IS a redbook, that main page on the Resources tab has a link to 
DB2 for IBM i redbooks, and the stored procedures one is there. It is a 
bit long in the tooth, but it is still useful.

HTH
Vern

On 11/26/2015 11:09 PM, 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;
> 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