Also, if you find there is something that is not available, there are a couple ways to tell IBM - this list is not anything official, although some of the folks at IBM do watch it.

One is the DCR process - design change request - there's a site where you can get the document to submit.

Then there is the COMMON requirements process - COMMON, if you don't know, is a national user group for IBM midrange computers. You can go to http://requirements.common.org and submit requirements there. There's an advisory council, COMMON Americas Advisory Council (CAAC) that meets regularly with IBM architects to present requirements that have been vetted some, and IBM does pay attention to these things.

Both avenues are important in the development of new capabilities on the IBM i.

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