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


This thread ...

Replies:

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].