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]

Re: Variables in SQL???

On Oct 2, 11:24 am, Richard <rmcgor...@gmai l.comwrote:

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_TIMESTA MP;--
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]

Re: Variables in SQL???

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.sysdummy 1

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@gmail.com]

Re: Variables in SQL???

On Oct 2, 2:24 pm, Richard <rmcgor...@gmai l.comwrote:

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]

Re: Variables in SQL???

On Oct 2, 1:14 pm, Richard <rmcgor...@gmai l.comwrote:

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.sysdummy 1
>
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.SYSDUMMY 1;--

Come to think of it, you don't even need SYSIBM.SYSDUMMY 1, 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.SYSDUMMY 1);--

--Jeff

[Serge Rielau]

Re: Variables in SQL???

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.SYSDUMMY 1 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