Conditional Insert in SQL:

Sometimes we would like to insert records into database on a conditional basis.

Example:

if not exists (select 1 from table1 where key1 = ?) then
insert into table1
(key1, key2, key3, key4)
values (?, ?, ?, 1);
end if

As DB2 does not support dynamic scripting, this can be achieved by the following query.

insert into table1
(key1, key2, key3, key4)
Select ?,?,?,1
From SysDummy1
Where Not Exists
(Select 1
From table1
Where key1=?)

The question marks represent parameter markers (roughly equivalent to host variables in pre-compiled SQL.) Values need to be assigned to each of these markers before the statement can execute successfully.
Take note that SysDummy1 is a special IBM one row table that can be used as a trick for these one row operation situations! This is because inserting parameter values from a one row table is equivalent to the INSERT/VALUES statement. Placing the NOT EXISTS predicate in the WHERE clause instead of using an IF statement still allows us to condition if the row should be inserted by testing whether the row already exists.
As a side note, SysDummy1 resides in the SysIBM schema so it should be part of the library list when using the *SYS naming convention or fully qualified (SYSIBM.SysDummy1) when using the *SQL naming convention. Alternatively, if you have a one row table in your own schema it can be substituted for SYSDUMMY1 as well.