Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to compile SQL and include it in batch file

    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

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    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:
    Code:
      Result set 1
      --------------
      my result
      Return Status = 0
    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

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    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

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •