Modifying Parallel Degree in Stored Procedures

[ML]

DB2 V8.2 64-bit on AIX:

I have INTRA_PARALLEL turned on (with Default Degree = 8) on my
reporting database. I have a couple of SQL stored procedures that I
want to run WITHOUT parallel.

We tried 'SET CURRENT DEGREE = 1' inside the body of the procedure but
that did not work.

I also need to put the returned data into output parameters for the
proc.

[Ian]

ML wrote:
> DB2 V8.2 64-bit on AIX:
>
> I have INTRA_PARALLEL turned on (with Default Degree = 8) on my
> reporting database. I have a couple of SQL stored procedures that I
> want to run WITHOUT parallel.
>
> We tried 'SET CURRENT DEGREE = 1' inside the body of the procedure but
> that did not work.

You have 2 options here:

1) Use SYSPROC.SET_ROUTINE_OPTS stored procedure to set the bind
options (e.g., 'DEGREE 1') you want when you compile an SQL
stored proc. Settings you make with this stored proc are used
only for the connection that called the procedure.

2) Set the DB2_SQLROUTINE_PREPOPTS registry variable.

If you want most of your procedures to be bound using DFT_DEGREE,
then option #1 is a much better solution.

> I also need to put the returned data into output parameters for the
> proc.

Not really sure what you mean here. Are you looking for something
more than this:

create procedure times (IN iVal1, IN iVal2 INT, OUT oVal INT)
language sql
begin
set oVal = iVal1 * iVal2;
end
@

call procedure times (2, 4, ?)
@

?

[ML]

SYSPROC.SET_ROUTINE_OPTS worked like a champ! Thanks for the help.