Hi,
on DB2 v8.2 FP9 on Windows XP SP3 I have written DB2 batch script (SQL commands and Windows commands). This batch script has about 50 different SQL that is running every time a batch is run. I have made explain of all of this SQLs but all have very good explain, no problems at all.

But running all of this 50 SQLs dinamicaly I get a performance problem. Is there any way a SQL package could be generated and this package could be executed from batch file with parameters? Parameters are where conditions inside SQL statement, e.g. "Select * from admin.test where col1=:variable".
Thanks,
Grofaty

You could put the statements into a stored procedure and just call this procedure. That's more portable as well - maybe an option for you?

stolze,
I have tried store procedure way, but I don't like to get displayed all sorts of meta data. The output of store procedure is:
So I don't like to get "Result set 1", "--------------" and "Return Status = 0". Is there way to omit this meta data when calling store procedure? By the way, I also dislike to get extra two spaces before result."
P.S. "my result" is output of my store procedure.
Thanks,
Grofaty

What you have shown is simply the output produced by the DB2 CLP - it is informative to show what is going on. If you don't like the output, you can always write your own small program that does the formatting you prefer. Or you apply some scripts that massage the output in the way you want it.

My impression was that you have a performance problem. Is that addressed by the stored procedure?

stolze,
I apologize for not been clear... I use DB2 CLP to execute dynamically created SQLs. This is causing a performance problems - slow response of 50 SQLs.

I have tried using Store Procedure but I don't like the output of store procedure. I don't like to write a program like Java program or something like that because code in batch file is more than 2000 lines of code - too complex to rewrite it.

I would just like to solve a performance problem, but as I see Store Procedure does not return data in DB2 CLP in the "normal" way (like normal execution of SQLs from CLP).

So I am looking for some kind of solution to solve performance problem, but still preserve nicely created output and code should remain in windows batch file.

Thanks,
Grofaty

I think I understood your issue. Here is how I see it:
- you have a lot of code in a batch file
- moving to stored procedures may be a way to address the performance issues because of reduced communication between your batch file and DB2; have you verified that SPs help you?
- relying on CLP to retrieve data is a bad idea because the CLP truncates LOBs exceeding 8K in size

Hi,
I have replaced first 10 SQLs from my batch file and put them in Store Procedure. From batch file they executed for about 5 seconds but from Store Procedure they executed in less then a second. So I see it is just a dynamic vs. static SQL problem.
Regards