MVSFORUMS.com

mukthi · Beginner Joined: 05 Dec 2002 Posts: 4 Topics: 2

Hi,

I am working on DB2 Stored Procedures now. These procedures are called from the web. One of the stored procedure takes atleast 15 minutes to run. The web user does not like to wait for that much of time.

Is it possible to run a stored procedure independently without waiting of the calling program to complete that procedure?? What I want is the calling program should kick off the stored procedure, Calling program should be finished and the procedure should run in the back ground. Is it possible??

Thanks in Advance,
Mukthi

kolusu · Posted: Thu Dec 19, 2002 11:33 am Post subject:

Mukthi,

I am confused with your request.First of all I am very interested to know what kind of operations you are doing in the stored procedure that it takes 15 mins to execuete.If the web is calling the stored procedure, does the web expect any results from the stored procedure??

If the web is not expecting any results from the stored procedure then why not make the stored procedure as a batch job and trigger it from the web?? or am I missing something here?

Kolusu

mukthi · Beginner Joined: 05 Dec 2002 Posts: 4 Topics: 2

Hi Kolusu,

The web user is expecting results from the stored procedure. The SP(Stored Procedure) reads three tables which will be having 150 million rows. The user may not pass all the key information. In that case the SP has to do a table scan which may take long time. I am thinking about the batch job option also. According to my client there may be 5000 web requests per day.

I tried the following plan :
Web calls a SP named S1. S1 will insert a row in a table with request id and status. The S1 should trigger the main SP named S2(which takes long time) and S1 should end. After this a display a message to the web user saying that 'your request id is XXXXX. Please check back the status after 15 minutes..'.

But S1 is waiting till S2 is finished. Is there any way where the S2 should run independently.

Thanks in Advance.
Mukthi

kolusu · Posted: Thu Dec 19, 2002 1:40 pm Post subject:

Mukthi,

Well here is what i would do in your case

The web calls the stored procedure S1
S1 inserts a row in the table T1
on table T1 define a TRIGGER ON INSERT which will invoke stored procedure S2
As soon as the Insert is completed on T1 by S1 you will return to the web with the approriate message.

Since the stored procedure S2 is initiated by the TRIGGER , you will not hold back S1.

The function of S1 is just to insert the row into the table and GOBACK.

Hope this helps...

cheers

kolusu

mukthi · Beginner Joined: 05 Dec 2002 Posts: 4 Topics: 2

Hi Kolusu,

Just now I tried that option also. But the Insert statement is waiting till the trigger procedure is completed.

Thanks in Advance.
Mukthi

gotluru · Beginner Joined: 17 Dec 2002 Posts: 29 Topics: 6

Hi Mukthi,

What we do at my shop for this kind of reports is run in a batch mode by submitting jcl through internal reader.. and send a message to web, your job is submitted.

Prasad