34

Does anyone know how to run the following SQL Server code in DB2?

I am converting SQL Server scripts so that they will run on a DB2 system and am having some problems wrapping my head around the use of variables in DB2.

T-SQL code

This is obviously not the actual code but works well as an example.

DECLARE @INPUT_VALUE INT
SET INPUT_VALUE = 4756

SELECT *
FROM TABLE1
WHERE TABLE1.COLUMN1 = @INPUT_VALUE
jcateca
  • 65
  • 5
I_AM_JARROD
  • 685
  • 2
  • 7
  • 20

2 Answers2

32

I assume this forum posting, which I quote fully below, should answer the question.

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

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

or (in any environment):

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):

CREATE VARIABLE example VARCHAR(15) ;
SET example = 'welcome' ;
SELECT *
FROM   tablename
WHERE  column1 = example ;
Moaz El-sawaf
  • 2,306
  • 1
  • 16
  • 33
Michael Sharek
  • 5,043
  • 2
  • 30
  • 33
  • Michael, do think in the first example part of the code was left off. It appears you are setting a variable that has not been declared or created? I ran this code using a valid table and column name and got an error in return. – I_AM_JARROD Jun 03 '11 at 02:27
  • I think you are right...I couldn't get it to work either so I edited the question to remove it. – Michael Sharek Jun 03 '11 at 18:10
  • I like the last one for if I need to run a generic script with some input variables. Am I correct in saying the variable exists for the life of the transaction? – ESP May 27 '15 at 11:36
  • Michael, your first 2 examples work well, but the version I was looking for was the third, which is not working. I've got the following error on the first line (CREATE VARIABLE): WSAD of type *Collection in QSYS was not found. SQLCODE=-204, SQLSTATE=42704. Do I need something else? Thank you. – Luis Gouveia Apr 05 '17 at 10:50
  • The "CREATE VARIABLE" was new with DB2 version 11; you might not have it yet. It is of a global scope, so you'd really need to create all your variables once, in advance, and then use them in your programs. More at: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_sql_createvariable.html – Abacus Apr 05 '17 at 15:55
  • For those who don't have the needed permissions to use CREATE VARIABLE, Michael's WITH (CTE) example works great! – pstraton Jul 29 '20 at 21:53
15

I'm coming from a SQL Server background also and spent the past 2 weeks figuring out how to run scripts like this in IBM Data Studio. Hope it helps.

CREATE VARIABLE v_lookupid INTEGER DEFAULT (4815162342); --where 4815162342 is your variable data 
  SELECT * FROM DB1.PERSON WHERE PERSON_ID = v_lookupid;
  SELECT * FROM DB1.PERSON_DATA WHERE PERSON_ID = v_lookupid;
  SELECT * FROM DB1.PERSON_HIST WHERE PERSON_ID = v_lookupid;
DROP VARIABLE v_lookupid;
JeffM
  • 365
  • 1
  • 5
  • 10