By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,590 Members | 2,191 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,590 IT Pros & Developers. It's quick & easy.

How do I do this in DB2

P: n/a
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

Apr 6 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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

Apr 6 '06 #2

P: n/a
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

Apr 6 '06 #3

P: n/a
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

Apr 6 '06 #4

P: n/a
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

Apr 6 '06 #5

P: n/a
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

Apr 10 '06 #6

P: n/a
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
Apr 10 '06 #7

P: n/a
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

Apr 10 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.