Parallel SQL (forking) in SQL stored procedures

[anthonyroach]

How would I execute two independent SQL statements in the same stored
procedure? I have a business process that requires a number of long-
running procedures to be executed. The procedures themselves are not
related and can be run simultaneously. The pseudo-code would look
like this

call proc_1;
if variable_1 is 'GO' then
call long_proc_2 &
call long_proc_3 &
fi
if variable_2 is 'GO' then
call proc_4
fi

The & means start the procedure and move on to the next statement
without waiting for the return.

Is this possible with SQL stored procedures? What about C or Java?

Thanks!

[Ian]

an**********@hotmail.com wrote:

How would I execute two independent SQL statements in the same stored
procedure? I have a business process that requires a number of long-
running procedures to be executed. The procedures themselves are not
related and can be run simultaneously. The pseudo-code would look
like this

call proc_1;
if variable_1 is 'GO' then
call long_proc_2 &
call long_proc_3 &
fi
if variable_2 is 'GO' then
call proc_4
fi

The & means start the procedure and move on to the next statement
without waiting for the return.

Is this possible with SQL stored procedures? What about C or Java?

No, not possible with SQL stored procedures.

C/Java could certainly do it, though.

[Serge Rielau]

Ian wrote:

an**********@hotmail.com wrote:

>How would I execute two independent SQL statements in the same stored
procedure? I have a business process that requires a number of long-
running procedures to be executed. The procedures themselves are not
related and can be run simultaneously. The pseudo-code would look
like this

call proc_1;
if variable_1 is 'GO' then
call long_proc_2 &
call long_proc_3 &
fi
if variable_2 is 'GO' then
call proc_4
fi

The & means start the procedure and move on to the next statement
without waiting for the return.

Is this possible with SQL stored procedures? What about C or Java?

No, not possible with SQL stored procedures.

C/Java could certainly do it, though.

You need multiple connections.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Ian]

Serge Rielau wrote:

Ian wrote:

>an**********@hotmail.com wrote:

>>How would I execute two independent SQL statements in the same stored
procedure? I have a business process that requires a number of long-
running procedures to be executed. The procedures themselves are not
related and can be run simultaneously. The pseudo-code would look
like this

call proc_1;
if variable_1 is 'GO' then
call long_proc_2 &
call long_proc_3 &
fi
if variable_2 is 'GO' then
call proc_4
fi

The & means start the procedure and move on to the next statement
without waiting for the return.

Is this possible with SQL stored procedures? What about C or Java?

No, not possible with SQL stored procedures.

C/Java could certainly do it, though.

You need multiple connections.

Indeed. I didn't say it would be a good idea :-)

[--CELKO--]

>Is this possible with SQL stored procedures? What about C or Java? <<

T-SQL is a simple one-pass compiler, so you are not going to do
anything this modern and fancy with it. It was never meant to be
development language, but just an extra tool to get around the
limitations of the early Sybase SQL engine. The old rule of thumb was
not to do a stored procedure longer than one page, including
comments.

Other programming languages can use multiple connections and call
SQL. Maybe Erlang has an SQL API now. You could also use WX2 or
Teradata, which are SQL engines that are naturally parallel.

That is about all I can think of right now.