Store procedure and multi-threading
Last Post 18 Sep 2005 02:55 AM by Anonymous. 11 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
Anonymous
Editorial Staff Member
Editorial Staff Member
Posts:81236

--
15 Sep 2005 11:53 PM
Is store procedure multi-thread? My goal is to use the WDSC wizard to create a web service from an RPG program. Here are several problems with this approach: 1. Using THREAD(*SERIAL) on the H-specs of the RPG will cause bottleneck if there are many web service clients connecting at the same time. 2. Without THREAD(*SERIAL), calling the RPG multiple times within the same job by the web service, each in its own thread, will produce garbage result. I think of a way to overcome this limitation: First, turn the RPG program into an external store procedure. Second, using WDSC, generate a web service from the external store procedure. This will work provided that store procedures are multithread capable. Are they? It seems to me that they should be. Aren't they being served by those prestarted JDBC server jobs? or database server jobs? Any insight would be greately appreciated.
Anonymous
Editorial Staff Member
Editorial Staff Member
Posts:81236

--
16 Sep 2005 12:33 AM Accepted Answer
Open JDBC/ODBC jobs - QZDASOINIT - do not run multithread enabled by default. Same goes for native CLI/JDBC driver jobs - QSQSRVR. Although not directly applicable to your situation, here are IBM sites listing restrictions for Java stored procedures and UDFs. So, my first reflex would be to say you can't count on jobs being multithread enabled by default when using JDBC. But you can force these jobs to run multithreaded and in fact I have seen one of our customers with a separate, user created subsystem, that runs these jobs as multithreaded. Not sure if changing that job option would negatively impact anything else. I know a lot of native commands and APIs are not threadsafe, so if you're using them in other parts of your program you would be exposed to some risk. If you were to call these stored procedures from within your programs with embeddeded SQL (RPG, COBOL, C, JAVA...), it would again depend on the multithread option on the job these programs run in.
Anonymous
Editorial Staff Member
Editorial Staff Member
Posts:81236

--
16 Sep 2005 08:52 AM Accepted Answer
Thank you very much for your input. Although Qzdasoinit jobs are not multithread enabled by default, this may not be applicable to my situation. Say that Client1 and Client2 both request service from MyWebService at the same time. MyWebService will spawn two threads, one for each client. Thread1 will make a JDBC CALL to MyStoreProcedure, which in turn will call MyRpgProgram. Since this is a JDBC CALL, it will be served by a pre-started Qzdasoinit #1. Now, Thread2's JDBC call to MyStoreProcedure will be served by a DIFFERENT Qzdasoinit job. In this context, it does not seem to matter if Qzdasoinit is multithread enabled or not. Please comment. Best regards
Anonymous
Editorial Staff Member
Editorial Staff Member
Posts:81236

--
16 Sep 2005 08:53 AM Accepted Answer
Thank you very much for your input. Although Qzdasoinit jobs are not multithread enabled by default, this may not be applicable to my situation. Say that Client1 and Client2 both request service from MyWebService at the same time. MyWebService will spawn two threads, one for each client. Thread1 will make a JDBC CALL to MyStoreProcedure, which in turn will call MyRpgProgram. Since this is a JDBC CALL, it will be served by a pre-started Qzdasoinit #1. Now, Thread2's JDBC call to MyStoreProcedure will be served by a DIFFERENT Qzdasoinit job. In this context, it does not seem to matter if Qzdasoinit is multithread enabled or not. Please comment. Best regards
Anonymous
Editorial Staff Member
Editorial Staff Member
Posts:81236

--
16 Sep 2005 09:01 AM Accepted Answer
It wasn't totally clear to me what the separation of workload is. Now that you've outlined it, I agree, you shouldn't run into any issues pertaining to multithreading.
Anonymous
Editorial Staff Member
Editorial Staff Member
Posts:81236

--
16 Sep 2005 09:05 AM Accepted Answer
the main thing is that RPG is not threadsafe. a stored procedure is an RPG program. so calling an RPG from multiple threads of the same job requires THREAD(*SERIAL)! you can either code in C or start multiple never ending batch jobs with which you communicate via DTAQ's HTH
Anonymous
Editorial Staff Member
Editorial Staff Member
Posts:81236

--
16 Sep 2005 12:20 PM Accepted Answer
Yes, RPG is not threadsafe. Yes, the stored procedure in this case is an RPG program. But store procedures are being servived by pre-started job qzdasoinit. When executed via the SQL CALL statement, it is my understanding that they must be routed to these pre-started jobs. Thread1 is routed to qzdasoinit #1, while Thread2 is routed to qzdasoinit #2. Therefore, there is no need to concern about threadsafe in the RPG here. Here I made two assumptions: 1. All JDBC calls must be routed to qzdasoinit. No exceptions. 2. Since qzdasoinit #1 is busy serving Thread1, the system will use qzdasoinit #2 to serve Thread2. Assumption 1 seems to be a safe assumption. Assumption 2 seems to be weaker. Nevertheless, it is hard to imagine that IBM would allow qzdasoinit #1 to spawn another thread to service Thread2. If so, how does one writes multi-threading Java applications making calls to ANY JDBC API??? Yes, Java is threadsafe, but in the end, the system will route JDBC request to one of these qzdasoinit job which is after all, not multithreading by default. Please comment. Best Regards,
Anonymous
Editorial Staff Member
Editorial Staff Member
Posts:81236

--
17 Sep 2005 09:26 AM Accepted Answer
1. Using THREAD(*SERIAL) on the H-specs of the RPG will cause bottleneck if there are many web service clients connecting at the same time. Is this your opinion, because it's not fact. Using THREAD(*SERIAL) makes your RPG program faster in this context. See the manual. And when you say "Thread1" and "Thread2", that's quite confusing. Every QZDASOINIT is job a is separate distinct job on the system while threads run in the same job (same JVM). In any event, if an RPG program is downstream of a JVM, then you need to put Thread(*Serialize) in the H-Spec, and every program that RPG program calls, etc. Here's a question for you: How do you set the classpath for a web service? Chris
Anonymous
Editorial Staff Member
Editorial Staff Member
Posts:81236

--
17 Sep 2005 12:30 PM Accepted Answer
Using THREAD(*SERIALIZE) on the H specs will force other threads to wait until the current thread is finished. This is a fact, not an opinion. The following red book on pages 11-12 lists a whole set of possible problems that may caused by using THREAD(*SERIALIZE)in addition to bottleneck http://www.redbooks.ibm.com/redpape...dp0192.pdf
Anonymous
Editorial Staff Member
Editorial Staff Member
Posts:81236

--
17 Sep 2005 12:31 PM Accepted Answer
Using THREAD(*SERIALIZE) on the H specs will force other threads to wait until the current thread is finished. This is a fact, not an opinion. The following red book on pages 11-12 lists a whole set of possible problems that may caused by using THREAD(*SERIALIZE)in addition to bottleneck http://www.redbooks.ibm.com/redpape...dp0192.pdf
Anonymous
Editorial Staff Member
Editorial Staff Member
Posts:81236

--
17 Sep 2005 12:59 PM Accepted Answer
1. Using THREAD(*SERIALIZE) on the H specs will force other threads to wait until the current thread is finished. This is a fact, not an opinion. The following red book on pages 11-12 lists a whole set of SERIOUS problems that may be caused by using THREAD(*SERIALIZE) in addition to the bottleneck problem mentioned previously. This red paper suggests to use a socket program to overcome these problems. To me, this is overly complicated. That's why I would like to use store procedure to achieve the same result. http://www.redbooks.ibm.com/redpape...dp0192.pdf 2. Here is an example to clarify my point: JobA spawns two threads: Thread1 and Thread2. Thread1 issues a JDBC call to fetch a record from a table. This JDBC request will be serviced by pre-started job qzdasoinit#1. Thread 2 needs to fetch the same record from the file. This JDBC request will be serviced by pre-started job qzdasoinit#2. Since qzdasoinit 1 and 2 are independent jobs, the two I/O are thread safe (Believe or not file I/O's are not thread safe, see the Thread section on SQL Reference). 3. I am using servets and JSP but I have also a need to use iSeries based web services. I do not want to rewrite the business logic provided by my RPG programs. That's why I have to wrap them in a Java bean first. However, since the RPG is not thread safe, I want to make them thread safe first. I do not want to use THREAD(*SERIALIZE) because it will introduce a whole new set of problems. So I will transform my RPG into a store procedure first. Store procedures, being RPG, are not thread safe. However, because store procedures are serviced
Anonymous
Editorial Staff Member
Editorial Staff Member
Posts:81236

--
18 Sep 2005 02:55 AM Accepted Answer
> 1. Using THREAD(*SERIALIZE) on the H specs will
> force other threads to wait until the current
> thread is finished. This is a fact, yes, but when and only when the threads are in the same job. you're confusing threads and jobs! > This red paper suggests to use a socket program
> to overcome these problems. see my post earlier > To me, this is overly complicated. you really should make sure that you understand how system/subsystem/job/thread are set up and work together HTH
You are not authorized to post a reply.

Acceptable Use Policy