Re: Stored Procedure Calls

It depends on two things, number of connections and what driver is being
used.

Each connection is served by a separate database job. If you use toolbox
driver, than job name is something like QZS... and it runs in subsystem
QSERVER. If native driver is used, than job has slightly different name,
something like QSR... and it runs in QSYSWRK. So, performance impact
depends on jobd and class that define specific subsystem. Important
thing to note (based on my experience) is that main load and performance
impact is not on the database serving job(s), but on BCI job that
actually executes Java program (assuming that it runs on iSeries). In my
experience, with a computation intensive program that processes around
10 million records,  CPU usage of database job never goes over 0.3%
while BCI job runs at 7% - 10%, no matter batch or interactive subsystem
(and we are talking 12 way system). So if you call your Java program
from an interactive session it will hit you hard, because BCI job is
executed in the subsystem where interactive job lives. (Same goes for
standalone Tomcat, but that is another story). Interestingly enough, BCI
job doesn't seem to care about run priority. It took it all the way down
to 99, didn't let go a bit :))).

BTW, job is running under some other user profile, but it does profile
swap, so all objects are accessed under your profile authorities.
Because there are always a few jobs with same name, the best way (I
found) to locate the one that serves you is to use something like
WRKOBJLCK <your user profile> *USRPRF, and it will beautifully list all
the jobs involved.

Hope this helps somewhat.

Vanja Jovic,
Canada

chris_price@nsb.co.uk wrote:

> When you open a JDBC Connection through the toolbox, a job on the AS/400 is
> created, under the QUSER profile (I can't remember the job name of the top
> of my head, along the lines of QSQxxxxx or something like that.) Every JDBC
> call which uses that connection, whether dynamic SQL, prepared statement or
> stored procedure will be executed by the same QUSER job.
>
> So if a non-LR RPG program is called twice, through the same connection,
> then it will the same as if the program was called twice in a "normal" job,
> with files open etc.
>
> As far as I'm aware, the OS/400 job is not multithreaded. If two java
> threads used the same connection to try and run a stored procedure, one of
> the calls would wait until the first had completed.
>
> Chris
>
>  -----Original Message-----
> From:   Tanveer, Mohammad [mailto:MTanveer@friedmancorp.com]
> Sent:   11 June 2002 20:48
> To: 'java400-l@midrange.com'
> Subject:    RE: Stored Procedure Calls
>
> When I call my stored procedure using jdbc and don't set LR on in RPG
> program.  It seems like my next call using the existing instance of the RPG
> program and if multiple calls are executed to the same procedure it seems
> like OS/400 is creating multiple threads of the same job.
>
> Is it a true analysis?
>
> -----Original Message-----
> From: Price, Chris [mailto:chris_price@nsb.co.uk]
> Sent: Tuesday, June 11, 2002 2:49 PM
> To: 'java400-l@midrange.com'
> Subject: RE: Stored Procedure Calls
>
>
>
> All client Stored Procedures calls, along with all JDBC operations are batch
> through the Toolbox.
>
> I think that only if the Java App is running on the iSeries/AS400 in an
> interactive 5250 session, then the JDBC calls will probably also be
> interactive (perhaps one of the Toolbox guys could confirm this?)
>
> Chris
>
>  -----Original Message-----
> From:   Tanveer, Mohammad [mailto:MTanveer@friedmancorp.com]
> Sent:   11 June 2002 20:37
> To: 'java400-l@midrange.com'
> Subject:    Stored Procedure Calls
>
> Stored Procedure Calls using JDBC drivers are interactive jobs on AS/400 or
> Batch Jobs?  If interactive is there any study on performance issues using
> stored procedures?
>
> _______________________________________________
> This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
> mailing list
> To post a message email: JAVA400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
> or email: JAVA400-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.
> _______________________________________________
> This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
> mailing list
> To post a message email: JAVA400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
> or email: JAVA400-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.
> _______________________________________________
> This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
> mailing list
> To post a message email: JAVA400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
> or email: JAVA400-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.
> _______________________________________________
> This is the Java Programming on and around the iSeries / AS400 (JAVA400-L) 
>mailing list
> To post a message email: JAVA400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
> or email: JAVA400-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.
>
>