How do I do this in DB2

[jlacson]

Using AS400 DB2, AquaData Studio SQL editor

I am new to DB2 coming from a SQL Server background. How do I get
something like the following to work?

BEGIN
DECLARE var1 char;
set var1='x';
select * table
where column1 =var1;

END

This gives me the following error:
Error: [SQL0104] Token VAR1 was not valid. Valid tokens: SECTION.
I am not trying to create stored procedures or anything. I am just
creating a few utility scripts that I want to save and run in the
editor as needed. The scripts contain a number of select statements so
I was hoping I could just set a variable at the beginning and not have
to modify each select statement. Something similar to what I have
below.
BEGIN
DECLARE var1 char;

set var1='x';

select * table1
where column1 =var1;

select * table2
where column1 =var1;

select * table3
where column1 =var1;

END
Thanks,
Jim

[Kiran]

Hi Jim,
We need to give the length of the CHAR datatype like CHAR(10).
You can find details about MS SQL Server to DB2 Conversion in
http://www.redbooks.ibm.com/abstracts/sg246672.html

[m_v_ratnam]

Hi
the data declaration , and SQL declaration must be in two diffrent
sections.

and also set Var1 = 'x' must be with in porgram , not in sql section

[situ]

CREATE PROCEDURE XIREL2.proc_1 ( )
DYNAMIC RESULT SET 1

BEGIN
DECLARE var1 varchar(10);
DECLARE v_query VARCHAR(6000);
DECLARE query1 CURSOR WITH RETURN TO CLIENT FOR stmt;
set var1='X';

set v_query= ' select * table ' || ' where column =var1';

PREPARE stmt FROM v_query;
OPEN query1;
END

[situ]

since your scripts fetching the data i.e nothing but "result set" in
db2
so possible correction will be
CREATE PROCEDURE p1
DYNAMIC RESULT SETS 1

BEGIN
DECLARE var1 char;
DECLARE query1 VARCHAR(6000);
DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR stmt;
set var1='x';

set query1= 'select * table' ||''|| ' where column1 =var1';
PREPARE stmt FROM cursor1;
OPEN cursor1;

END

[jlacson]

Thanks to everyone for you replies. I seems the approach is radically
different in DB2 requiring the creation of procedures and the use of
cursors (generally frowned upon in SQL Server)

Jim

[Serge Rielau]

jl*****@gmail.com wrote:

Thanks to everyone for you replies. I seems the approach is radically
different in DB2 requiring the creation of procedures and the use of
cursors (generally frowned upon in SQL Server)

Ehem... if you just state:
SELECT * FROM T
In T-SQL. What do think you're getting if not a cursor with RETURN TO
CLIENT?
The SQL/PSM Standard simply spells it out.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[jlacson]

It's been a while since I have done any heavy duty T-SQL development
but I do recall the use of cursors was discouraged because they were
considered 'expensive' resource-wise. I believe these were actually
server cursors. Results from n select statement were just refered to as
result sets. I suspect this is the same as a DB2 cursor?

I look forward to exploring the differences between the two DBs

Thanks,
Jim