Variables in SQL???

[Richard]

Another of those "What's the DB2 equivalent to this Sybase syntax"

I use local variables in Sybase SQL scripts to hold values for use
later in the script.

For example:

declare @timenow datetime
select @timenow = getdate()
delete from TableA where activity_date < @timenow
delete from TableB where activity_date < @timenow
delete from TableC where activity_date < @timenow

I've used a datatime variable @timenow to hold a value for later
use, but it could have used an int, char, etc (any datatype). Are
local variables used in DB2 scripts, or do I need to drop the value
into a temp table?

Thanks.

[jefftyzzer]

On Oct 2, 11:24*am, Richard <[e-mail]:

Another of those "What's the DB2 equivalent to this Sybase syntax"

I use local variables in Sybase SQL scripts to hold values for use
later in the script.

For example:

declare @timenow datetime
select @timenow = getdate()
delete from TableA where activity_date < @timenow
delete from TableB where activity_date < @timenow
delete from TableC where activity_date < @timenow

I've used a datatime variable @timenow to hold a value for later
use, but it could have used an int, char, etc (any datatype). *Are
local variables used in DB2 scripts, or do I need to drop the value
into a temp table?

Thanks.

Not *quite* the same, but pretty close:

BEGIN ATOMIC
DECLARE V_TIMENOW TIMESTAMP;--
SET V_TIMENOW = CURRENT_TIMESTAMP;--
DELETE FROM TABLEA WHERE ACTIVITY_DATE < V_TIMENOW;--
DELETE FROM TABLEB WHERE ACTIVITY_DATE < V_TIMENOW;--
DELETE FROM TABLEC WHERE ACTIVITY_DATE < V_TIMENOW;--
END;

If ACTIVITY_DATE truly is just a date and not a timestamp, then (after
you change the column name ;-) you'll want to do this:

BEGIN ATOMIC
DECLARE V_DATE DATE;--
SET V_DATE = CURRENT_DATE;--
DELETE FROM TABLEA WHERE ACTIVITY_DATE < V_DATE;--
DELETE FROM TABLEB WHERE ACTIVITY_DATE < V_DATE;--
DELETE FROM TABLEC WHERE ACTIVITY_DATE < V_DATE;--
END;

One thing about the BEGIN ATOMIC is it's a black box output-wise. All
you'll get after it completes is a "statement completed successfully"-
type message.

--Jeff

[Richard]

That worked. Thanks. :-)

A couple of follow-up questions:

How would I display the value stored in a variable? In Sybase I could
"select" it, or "print" it. The select gives me an error and I don't
see the print option in the "SQL Reference Volume 2" manual. No other
commands in Volume 2 are popping out as obvious options. I must be
missing it.
Why do I need to type

SELECT current date FROM sysibm.sysdummy1

in my script, instead of

SELECT current date

to display the current date, yet in your example you can say

SET V_DATENOW = current date

Is it just a difference between the SET versus SELECT command?

Thanks.

[yongleig]

On Oct 2, 2:24 pm, Richard <[e-mail]:

Another of those "What's the DB2 equivalent to this Sybase syntax"

I use local variables in Sybase SQL scripts to hold values for use
later in the script.

For example:

declare @timenow datetime
select @timenow = getdate()
delete from TableA where activity_date < @timenow
delete from TableB where activity_date < @timenow
delete from TableC where activity_date < @timenow

I've used a datatime variable @timenow to hold a value for later
use, but it could have used an int, char, etc (any datatype). Are
local variables used in DB2 scripts, or do I need to drop the value
into a temp table?

Thanks.

In v9.5, db2 support variable.

create variable sch.timenow date;

set sch.timenow = current date;
delete from t1 where activate_date < sch.timenow;
delete from t2 where activate_date < sch.timenow;

[jefftyzzer]

On Oct 2, 1:14*pm, Richard <[e-mail]:

That worked. *Thanks. :-)

A couple of follow-up questions:

How would I display the value stored in a variable? *In Sybase I could
"select" it, or "print" it. *The select gives me an error and I don't
see the print option in the "SQL Reference Volume 2" manual. *No other
commands in Volume 2 are popping out as obvious options. *I must be
missing it.

Why do I need to type

* * SELECT current date FROM sysibm.sysdummy1

in my script, instead of

* * SELECT current date

to display the current date, yet in your example you can say

* *SET V_DATENOW = current date

Is it just a difference between the SET versus SELECT command?

Thanks.

Well, not to pick on Sybase, but--while handy--I don't believe a query
like 'SELECT current date' is valid, i.e. well-formed (ANSI), SQL, as
it's missing the FROM clause. Hence the need in DB2 to refer to the
dummy table.

Populating the variable is a different story, as that's in a
procedural context and the current date is coming from a special
register.

In a stored procedure you could also do a SELECT...INTO and populate a
variable using a SELECT, in addition to populating it via SET using a
scalar fullselect. In other words, in DB2, *depending on the setting*
you could do:

DECLARE V_TIMENOW TIMESTAMP;--
SET V_TIMENOW = CURRENT TIMESTAMP;--

--or--

DECLARE V_TIMENOW TIMESTAMP;--
SELECT CURRENT TIMESTAMP INTO V_TIMENOW FROM SYSIBM.SYSDUMMY1;--

Come to think of it, you don't even need SYSIBM.SYSDUMMY1, you also
could do something like:

SELECT CURRENT TIMESTAMP INTO V_TIMENOW FROM TABLE(VALUES(1)) T(C1);--

--or--

DECLARE V_TIMENOW TIMESTAMP;--
SET V_TIMENOW = (SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1);--

--Jeff

[Serge Rielau]

DB2 9.5 provides global variables:
CREATE VARIABLE X INTEGER;
and
SET x = <some expression>;
To get them out from a client you can use:
SET ? = x;
(I don't think that works from CLP)

Insteda of SELECT x FROM SYSIBM.SYSDUMMY1 you can use another ANSI
construct:
VALUES x;
Same numbers of characters to type as SELECT :-)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab