How to execute a store procedure whose parameters come from a select statement

[gimme_this...@yahoo.com]

Hello fellow DB2-ers!

Here are the first few lines of a PROCEDURE: my_procedure:

create procedure my_procedure(
in a_p_id bigint,
in a_region_id integer,
in a_a_date timestamp,
in a_a_flag character
)

Calling it normally works fine:

call my_procedure (0245674,3,NULL,'Y' )

But I'd like to call my_procedure where the parmeters come from a
SELECT statement.

Something like this (which doesn't work):

call my_procedure (select p_id,region_id,a_date,a_flag from people
where b_id= 700050188 and t_flag='Y' );

I understand that I can create another procedure that declares a
P_cursor and calls my_procedure in a LOOP.

But does syntax exist to execute my_procedure where the parameters
come from a SELECT statement?

Thanks.

[CRPence]

On 16-Jan-2012 15:30 , gimme_this...@yahoo.com wrote:
>
> Here are the first few lines of a PROCEDURE: my_procedure:
>
> create procedure my_procedure(
> in a_p_id bigint,
> in a_region_id integer,
> in a_a_date timestamp,
> in a_a_flag character
> )
>
> Calling it normally works fine:
> call my_procedure (0245674,3,NULL,'Y')
>

> But I'd like to call my_procedure where the parameters come from a

> SELECT statement.
>
> Something like this (which doesn't work):
>
> call my_procedure (select p_id,region_id,a_date,a_flag from people
> where b_id= 700050188 and t_flag='Y' );
>
> I understand that I can create another procedure that declares a
> P_cursor and calls my_procedure in a LOOP.
>
> But does syntax exist to execute my_procedure where the parameters
> come from a SELECT statement?
>

I sometimes will create a sibling function which performs the CALL
and returns a success\failure indication, then use that UDF invocation
in the desired SELECT; e.g.:

create function my_function
( a_p_id bigint,
a_region_id integer,
a_a_date timestamp,
a_a_flag character
) returns int
language sql /* ... */
begin
declare exit handler for SQLEXCEPTION return 0;
call my_procedure (a_p_id, a_region_id, a_a_date, a_a_flag);
return 1;
end

select my_function(p_id,region_id,a_date,a_flag)
from people
where b_id=700050188
and t_flag='Y'

Similarly using a UDF, but a more generic dynamically prepared CALL,
presumably could suffice in some cases.? Obviously the following would
have to be adjusted to enable any NULL values, making the expression
even less readable:

select my_dyn_sql_call('call my_procedure (' concat p_id concat ','
concat region_id concat ',''' concat a_date concat ''',''' concat a_flag
concat ''')')

from people
where b_id= 700050188 and t_flag='Y'

Regards, Chuck

[gimme_this...@yahoo.com]

Hi Chuck.

I'm getting this error:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0440N No authorized routine named "MY_PROCEDURE" of type
"PROCEDURE"
having compatible arguments was found. LINE NUMBER=10.
SQLSTATE=42884


But the arguments look like they should be okay.

Thanks

[CRPence]

No idea about the DB2 warning, but the SQL error does seem to imply
that the arguments were not correct for whatever invocation was made [or
perhaps that a matching procedure exists, but is not authorized]. Note
that the procedure name is upper-cased in the message, so ensure
delimiters are used if\when required to preserve case. Also verify the
path being used.

There is no indication of what the "command" was that was entered at
the command line. Perhaps a CREATE FUNCTION, or a SELECT statement
referencing a UDF from a prior CREATE FUNCTION as either a sibling or
dynamic statement processor to effect the SQL CALL to the routine from a
prior CREATE PROCEDURE? What was and was not qualified versus
implicitly qualified by the PATH?

Why not try scripting a very simplified variation of a TABLE,
PROCEDURE, FUNCTION, and the SELECT to see if that gives the same
issue(s). If so, then include the script and where are the error(s).
For example, the following script [a table to establish a row value to
SELECT and another to be acted upon in the procedure] runs fine for me
[with autocommit]:

<code>

-- set current schema and current path if not authorization id
create table my_t1 (b bigint)
;
create table my_tt as
(select cast(200 as bigint) as i from sysibm.sysdummy1)
with data
;
create procedure "my_p1" (in a_p_id bigint) language sql
insert into my_t1 values(a_p_id)
;
create function my_f1 (a_p_id bigint) returns int
language sql not deterministic modifies sql data
begin
declare exit handler for SQLEXCEPTION return NULL;
call "my_p1" (a_p_id);
return 1;
end
;
select my_f1(i) from my_tt where i > 0
;
select b from my_t1
;

</code>

Regards, Chuck

[gimme_this...@yahoo.com]

Thanks Chuck.

Your approach worked...

I can define the function - but now get this error:


51034(-740)[IBM][CLI Driver][DB2/SUN64] SQL0740N Routine
"my_f1" (specific name "SQL120118130715600") is defined with the
MODIFIES SQL DATA option, which is not valid in the context where the
routine is invoked. SQLSTATE=51034
(0.02 secs)

Both the procedure and function have a "modifies sql data" option.

[CRPence]

Hmmm, I usually _resolve_ problems by adding the MODIFIES SQL DATA
rather than causing them; i.e. that specification allows more possible
statements, both in that routine and any of the called routines.

I am not sure what is the deal; no clue as to what the "context" is,
for the "Invalid Application State" being diagnosed. Perhaps the SELECT
is implied to be read-only, and thus restricting the functions to no
more than READS SQL DATA? I defaulted to MODIFIES SQL DATA in my sample
procedure and in even purposely coded an INSERT. My DB2 [for i] did not
care, no negative sqlcode nor non-zero sqlstate, even though my SELECT
was [implicitly] run FOR READ ONLY WITH NC. I am not sure if running
FOR UPDATE could help.?

Searching for a possible limitation in DB2 LUW... Maybe the scalar
function itself can not use MODIFIES SQL DATA, but then the obvious
question would be why would the the function even be created with that
[explicit] specification?

DB2 Version 9 for Linux, UNIX, and Windows
_i SQL access levels in routines i_
http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.apdv.sql.doc/doc/c0011291.htm
"
...
The default and maximal SQL access levels for different types of
routines are shown below:
Table 1. Default and maximum SQL access levels for routines
Routine type Default SQL access lvl Maximum allowed SQL access level
SQL procedures MODIFIES SQL DATA MODIFIES SQL DATA
SQL functions (scalar) READS SQL DATA READS SQL DATA
SQL functions (table) READS SQL DATA MODIFIES SQL DATA
...
"

Regards, Chuck