Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2010
    Posts
    3

    Unanswered: DB2 Bind variable

    How to bind comma separated Ids to ID (which is BIGINT)

    LIST_OF_IDS VARCHAR(200);
    SET LIST_OF_IDS = '1,2,3';

    SQL = 'select * from employee where id in (?)';

    Prepare cursor1 from SQL;

    open cursor1 USING LIST_OF_IDS;

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    LIST_OF_IDS is a single value. So the query would be the same as:
    Code:
    SELECT * FROM employee WHERE id = '1,2,3'
    I think this is not really what you want. You want to test for "id = 1 OR id = 2 OR id = 3". For that, you will have to construct the whole statement in the string and execute it dynamically. If you think about it, it makes a lot of sense. If you would have a long IN-list, DB2 may be better of using a different access plan than for a point query (with just a single element in the IN-list).

    Another alternative is to store the desired IDs in a separate table and then do:
    Code:
    SELECT * FROM employee
    WHERE id IN ( SELECT id FROM other_table )
    Yet another approach would be arrays. (Depends on your DB2 version and platform.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jul 2010
    Posts
    3

    Thats fine

    Thanks. These values are filtered after few steps, i'm using dynamic SQL. But to improve the performance i need to make it as static.

    I tried to use Global temp table to store these values and join/used IN clause. both were not giving better performance.

    If i could convert as static query, it's not required to prepare every time.So i can save some time.

    any idea other thought on this would be great.

  4. #4
    Join Date
    Apr 2009
    Posts
    42
    saving the prepare time dont think it would contribute a lot . however, i believe it'd better to locate the sql which is running slow. mind the sql posting here?

Posting Permissions

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