DB2 for IBM iSeries: IF EXISTS statement syntax

Question

I am familiar with Sybase which allows queries with format: IF EXISTS () THEN ... ELSE ... END IF (or very close). This a powerful statement that allows: "if exists, then update, else insert".

I am writing queries for DB2 on IBM iSeries box. I have seen the CASE keyword, but I cannot make it work. I always receive the error: "Keyword CASE not expected."

Sample:

IF EXISTS ( SELECT * FROM MYTABLE WHERE KEY = xxx )
THEN UPDATE MYTABLE SET VALUE = zzz WHERE KEY = xxx
ELSE INSERT INTO MYTABLE (KEY, VALUE) VALUES (xxx, zzz)
END IF

Is there a way to do this against DB2 on IBM iSeries? Currently, I run two queries. First a select, then my Java code decides to update/insert. I would rather write a single query as my server is located far away (across the Pacific).

Solution 1

DB/2 on the AS/400 does not have a conditional INSERT / UPDATE statement.

You could drop the SELECT statement by executing an INSERT directly and if it fails execute the UPDATE statement. Flip the order of the statements if your data is more likely to UPDATE than INSERT.

A faster option would be to create a temporary table in QTEMP, INSERT all of the records into the temporary table and then execute a bulk UPDATE ... WHERE EXISTS and INSERT ... WHERE NOT EXISTS at the end to merge all of the records into the final table. The advantage of this method is that you can wrap all of the statements in a batch to minimize round trip communication.

Solution 2

+UPDATE+

DB2 for i, as of version 7.1, now has a MERGE statement which does what you are looking for.

>>-MERGE INTO--+-table-name-+--+--------------------+----------->
               '-view-name--'  '-correlation-clause-'   

  >--USING--table-reference--ON--search-condition----------------->

     .------------------------------------------------------------------------.   
     V                                                                        |   
  >----WHEN--+-----+--MATCHED--+----------------+--THEN--+-update-operation-+-+----->
             '-NOT-'           '-AND--condition-'        +-delete-operation-+     
                                                         +-insert-operation-+     
                                                         '-signal-statement-'     

See IBM i 7.1 InfoCenter DB2 MERGE statement reference page

Solution 3

You can perform control-flow logic (IF...THEN...ELSE) in an SQL stored procedure. Here's sample SQL source code:

-- Warning!  Untested code ahead.
CREATE PROCEDURE libname.UPSERT_MYTABLE (
    IN THEKEY DECIMAL(9,0),
    IN NEWVALUE CHAR(10) )
LANGUAGE SQL
MODIFIES SQL DATA

BEGIN

    DECLARE FOUND CHAR(1);

    -- Set FOUND to 'Y' if the key is found, 'N' if not.
    -- (Perhaps there's a more direct way to do it.)
    SET FOUND = 'N';
    SELECT 'Y' INTO FOUND
    FROM SYSIBM.SYSDUMMY1
    WHERE EXISTS
      (SELECT * FROM MYTABLE WHERE KEY = THEKEY);

    IF FOUND = 'Y' THEN

        UPDATE MYTABLE
        SET VALUE = NEWVALUE
        WHERE KEY = THEKEY;

    ELSE

        INSERT INTO MYTABLE
          (KEY, VALUE)
        VALUES
          (THEKEY, NEWVALUE);

    END IF;

END;

Once you create the stored procedure, you call it like you would any other stored procedure on this platform:

CALL UPSERT_MYTABLE( xxx, zzz );

Comments

[James Allman]

Unfortunately DB2 on the AS/400 doesn't support the MERGE statement.

[kevinarpe]

Thanks I will use this solution. It is the simplest to code and debug.

[user2338816]

To keep the info updated, DB2 for i (AS/400) has had MERGE since mid-2010. A current release OS version is required.