467,923 Members | 1,766 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,923 developers. It's quick & easy.

How do I do this in DB2

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
  • viewed: 1958
Share:
7 Replies
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
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
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
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
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
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
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.

Similar topics

4 posts views Thread by James | last post: by
5 posts views Thread by Scott D | last post: by
2 posts views Thread by Nick | last post: by
2 posts views Thread by Alexander Ross | last post: by
reply views Thread by Dan Foley | last post: by
5 posts views Thread by Lee Redeem | last post: by
5 posts views Thread by christopher vogt | last post: by
6 posts views Thread by Phil Powell | last post: by
1 post views Thread by Michel | last post: by
11 posts views Thread by Maciej Nadolski | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.