SET statement

By using the SET statement variables can be assigned. Just the variables which are specified in the DECLARE statement or parameters in the CREATE statement or global variables can be used.

Variable names are not case sensitive.

By using a select statement to assign a variable the statement should return just one row. Otherwise a runtime error will occur. In this case the ‚FETCH FIRST 1 ROWS‘ option will help.

Another possibility is to assign variables is to use the SELECT INTO statement.
Example:

 create table mytab (col1 char(20), col2 char(20))!

 insert into mytab values ('V1_C1','V1_C2')!
 insert into mytab values ('V2_C1','V2_C2')!


 create procedure myproc (IN v_var01 integer, OUT v_var02 integer)
 begin
    declare v_string char(10);
    declare v_date   date;
    declare v_col1   char(20);
    declare v_col2   char(20);
    declare v_int    integer default 10;

    set v_string = NULL;
    set v_string = 'ABCDEF';
    set v_date = current date;

    set v_var01 = v_var01 + 1;
    set v_var02 = v_var01 + 10;

    set (v_col1, v_col2) = (select col1, col2 from mytab fetch first 1 rows only);

    set v_var02 = (select count(*) from mytab);

    set v_int default;  

end!

Link to the DB2 Information Center: DB2 9.7 DB2 10.1 DB2 10.5