Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2008
    Posts
    2

    Unanswered: DB2 IN clause limitation with number of parameters

    ALL,

    My update query wouldnt work because the number of parameters in IN clause exceeds 1000 parameters.

    Could someone suggest an alternative to IN clause.

    P.S. : I do not have permissions to create a temp table.

    my code snippet is as follows(Shell : KSH):

    $EMPLIST=""
    cat $FILEDIR/exec.csv | tr -d '\r' | while read emplid
    do
    EMPLIST=$EMPLIST,"'"$emplid"'"
    done

    db2 -x "update employee set modifiedtstmp = current timestamp,xec_fl='N' where emplid not IN ($emplid)"

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You could insert the values into a temporary table, then use a subselect against that table: "... WHERE emplid NOT IN ( SELECT ... FROM temp-table )"
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Nov 2008
    Posts
    2
    Thank you for the reply.
    I do not have the permissions to create TMP tables in database.
    My database do not have any existing TMP tables.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Then you may have to break this down as:
    Code:
    ...
    WHERE emplid NOT IN ( ... ) AND emplid NOT IN ( ... )
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I don't know much about Shell script.
    I guessed $emplid was made like 'emplid1','emplid2', ... ,'emplidn'.
    If you can modify it like 'emplid1,emplid2, ... ,emplidn'.
    You can try this:
    where LOCATE(emplid,$emplid) = 0
    I assumed column emplid doesn't include comma(',').
    If length of emplid is 6, you can make about 4500 emplid list.

  6. #6
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    The Temp table concept is more feasible for such cases. If you have privilege to CONNECT to the database, you should also have Privilege to DECLARE A TEMP TABLE.

    Its very simple: just DECLARE TEMP TABLE (COL1) inside your shell script.
    Insert into the TEMP TABLE
    and then use the same script.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DB2 Version 9 for Linux, UNIX, and Windows SQL Reference Volume 2
    DECLARE GLOBAL TEMPORARY TABLE

    The DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for the current session.
    The declared temporary table description does not appear in the system catalog.
    It is not persistent and cannot be shared with other sessions.
    Each session that defines a declared global temporary table of the same name has its own unique description of the temporary table.
    When the session terminates, the rows of the table are deleted, and the description of the temporary table is dropped.

    Invocation:

    This statement can be embedded in an application program or issued through the use of dynamic SQL statements.
    It is an executable statement that can be dynamically prepared.

    Authorization:

    The privileges held by the authorization ID of the statement must include at least one of the following:
    v USE privilege on the USER TEMPORARY table space
    v SYSADM or DBADM authority

    ...
    ...
    Does it mean that Privilege to DECLARE A TEMP TABLE is not automatically given at the time of CONNECT?

Posting Permissions

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