Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2008
    Location
    India
    Posts
    96

    Unanswered: DB2 local variables

    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 ?

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    DB2 version 9 on sun solaris machine.

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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 ;
    Last edited by Peter.Vanroose; 12-18-08 at 05:46.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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