RE: Stored procedure and close connection

["Fred Kulack" <kulack@xxxxxxxxxx>]

On 10/04/2001 at 06:20:20 AM, java400-l-admin@midrange.com wrote:
Nevertheless there is something i'd like to clarify with the
management of a stored procedure on the As400.
My background is not the as400, so there is still some things
i do not know.

When i create a connection to the AS400, as job is created.
When i call a stored procedure with this connection is a new
job created?
What happens when, because of threading problem two calls to two
stored proc a made through the same connection?
--- end of excerpt ---

--- #2 is probably what is hitting you? ---

1) Two calls to the same stored procedure using the same
connection is not necessarily a 'threading problem', except
as defined by your design, item #2 here, and what the stored
procedure actually does (item #3 here).

2) If you use the same statement to make the second call, the JDBC
specification strictly defines what will happen to the statement itself.
I.e. executing a statement again will CLOSE any previous result
sets (cursors) associated with it (probably created by the last time
the stored procedure was called) before it creates new due
to the current execution.

3) One of the powerful aspects of stored procedures
on the AS/400 is that you can write your stored procedure
to do literally anything any other application program
could do.
The stored proc could keep context between calls (in
static storage), it could keep DB positioning or some other
thing between calls. What it does could be effected by
repeated calls to it.
This is certainly an application (including the stored
procedure) design issue.

4) A new backend agent (job) is created only when you
create a new connection. A stored procedure is not a job.


5) Driver thread safety could come in here too, but
we (the native driver team... Mostly Rich 8-)) believe
we have fixed those issues.




In every single ethnic, religious or racial group, there are a
very few truly evil people. For each of those people there
are many, many, many good people.
Assuming anything (evilness or capability for evil) about the
particular group is bigotry and idiocy. Don't do it.  -- Me

Fred A. Kulack  -  AS/400e  Java and Java DB2 access, Jdbc, JTA, etc...
IBM in Rochester, MN  (Phone: 507.253.5982   T/L 553-5982)
mailto:kulack@us.ibm.com   Personal: mailto:kulack@magnaspeed.net
AOL Instant Messenger: Home:FKulack  Work:FKulackWrk