DB2 local variables

karthi_syb · #1 (**permalink**) 12-17-08, 09:46

Hi All,

how to declare the local variaables in DB2?

how many types of variables are there?

in sql,

declare @example varchar(15)
set @example = "welcome"

select *
from <tablename>
where column1 = @example

will work...

what is the equavalent command in DB2 to do the above one ? i.e how to write the above code in DB2 ?

stolze · #2 (**permalink**) 12-17-08, 14:30

You care to tell us which version of DB2 you are using on which platform? The answer depends on that because we don't know if you have session variables available or have to use compound statements or resort to temp tables.

karthi_syb · #3 (**permalink**) 12-18-08, 03:08

DB2 version 9 on sun solaris machine.

Peter.Vanroose · #4 (**permalink**) 12-18-08, 04:33

Quote:

Originally Posted by karthi_syb

declare @example varchar(15)
set @example = "welcome"
select *
from <tablename>
where column1 = @example

There are several ways to do this.
First of all, you'll definitely want single quotes around "welcome".
According to the reference manual (SC23-5862), you have the DECLARE VARIABLE statement inside a BEGIN ATOMIC (p. 213), the CREATE VARIABLE statement (p. 600), the "SET variable" statement (p. 954) and the "VALUES" statement (p.991) available.
Try one of the following, depending on the environment (embedded SQL, GUI, command line interface, inside a procedure definition) where you want this:

In the "script" GUI:

Code:

  SET example = 'welcome' ;
  SELECT *
  FROM   tablename
  WHERE  column1 = example ;
END

Inside a procedure, function, or trigger definition, or in a dynamic SQL statement (embedded in a host program):

Code:

BEGIN ATOMIC
  DECLARE example VARCHAR(15) ;
  SET example = 'welcome' ;
  SELECT *
  FROM   tablename
  WHERE  column1 = example ;
END

or (in any environment):

Code:

WITH t(example) AS (VALUES('welcome'))
  SELECT *
  FROM   tablename, t
  WHERE  column1 = example

or (although this is probably not what you want, since the variable needs to be created just once, but can be used thereafter by everybody although its content will be private on a per-user basis):

Code:

CREATE VARIABLE example VARCHAR(15) ;
SET example = 'welcome' ;
SELECT *
FROM   tablename
WHERE  column1 = example ;

Internet Services

Web Development

Digital Marketing

Consumer Tech