Variables

JDionne · #1 (**permalink**) 05-25-04, 11:51

I cant seem to search the forum, im sure my question has been asked but I cant tell.

I need to know if there is anything in DB2 similar to the SQL Server Declared Variable

Code:

Declare @start timestamp

The only declared variables that i have found in the help were associated with outside programing languages like C++

If there isnt anything like this in DB2 Sql code, how do I create something that can hold data like a variable?
Jim

ARWinner · #2 (**permalink**) 05-25-04, 12:31

Jim,
What construct are you trying to declare a variable? Stored Procedure,
UDF, Trigger?

Also what Version of DB2 and OS are you using?

Andy

JDionne · #3 (**permalink**) 05-25-04, 13:38

Quote:

Originally Posted by ARWinner

Jim,
What construct are you trying to declare a variable? Stored Procedure,
UDF, Trigger?

Also what Version of DB2 and OS are you using?

Andy

my environment:
DB2 Version 8.1 fixpack 5
Windows 2003 Enterprise Edition

I am just trying to write a bit of code that I would run from the CLP. Its just a script of SQL Statments and I need a variable for an audit trail.
Jim

ARWinner · #4 (**permalink**) 05-25-04, 14:01

Jim,
I am not 100% certain on this, but you could try something like:

BEGIN
DECLARE MyVar INT;

-- do your work using MyVar

END;

HTH

Andy

JDionne · #5 (**permalink**) 05-25-04, 14:17

This is the SQL Server Code Im trying to migrate

Code:

Print 'Top Customers'
Declare @start smalldatetime
Declare @finish smalldatetime
set @start = convert(smalldatetime,getdate())
Print @start

It doesent have to happen this way, but I would like to get the same functionality.
Jim

ARWinner · #6 (**permalink**) 05-25-04, 15:20

Jim,
If you have a DB2 script file (myscript.ddl) that looks like:
__________________________________________________ ____
-- Start Time
SELECT CURRENT TIMESTAMP from sysibm.sysdummy1

-- all your SQL that does the work goes here

-- END Time
SELECT CURRENT TIMESTAMP from sysibm.sysdummy1

__________________________________________________ _______

Then run: db2 -tvsf myscript.ddl > audit.out

At the completion, audit.out would contain everything you need.

As a precaution to errors occuring in yhe body of the script. You could have a batch file that does something like:

db2 connect to DB user me using password >audit.out
db2 select current timestamp from sysibm.sysdummy1 >> audit.out
db2 -tvsf myscript.ddl >> audit.out
db2 select current timestamp from sysibm.sysdummy1 >> audit.out

HTH

Andy

sathyaram_s · #7 (**permalink**) 05-25-04, 15:25

If you want to know the execution time for SQL Statements, db2batch is a good choice ...

If you want the total execution time of dozens or hundreds of statements, db2batch may be an overkill .. .Andy's method will work best

JDionne · #8 (**permalink**) 05-25-04, 15:56

Quote:

Originally Posted by sathyaram_s

If you want to know the execution time for SQL Statements, db2batch is a good choice ...

If you want the total execution time of dozens or hundreds of statements, db2batch may be an overkill .. .Andy's method will work best

What we currently do is populate a varialble and then insert that vaiable into a table to keep a history in the db.

I have run into another problem concerning vaiables that has me in a bit of a spot as well though.

I have a curser that is part of a script that provides a value to be used in a slelect statment. The curser loops through the select statment filling in a different value for the variable every time and causeing the select statment output to change.

Code:

create table #Top_EXP_Company_By_Office
(Company_Name char(25)
,office varchar(8)
,TEUs decimal(12,3)
)


declare @office char(10)


declare Office_cursor cursor for
select distinct office
from dbo.REF_NA_SALES_XREF_TBL
order by 1

OPEN office_cursor

FETCH NEXT FROM Office_cursor into @Office

WHILE @@FETCH_STATUS = 0
BEGIN


	Insert into #Top_EXP_Company_By_Office
	Select top 50 name
	     , @Office
	     , Sum(convert(money, teu))
	from dbo.stg_joc_tbl j, dbo.REF_NA_SALES_XREF_TBL s
	where j.district = s.district_code
	  and s.office = @Office
	group by name
	order by 3 desc


FETCH NEXT FROM Office_cursor into @Office

END

CLOSE Office_cursor
DEALLOCATE Office_cursor

This is important to cary through to DB2. If i cant do it this way, what would my options be?
Jim

sathyaram_s · #9 (**permalink**) 05-25-04, 16:50

If understand you are inserting the first 50 rows based on some condition for each office ...

In DB2 , cursors cannot be used in a CLP ... You may have to write SP if you want to do it using cursors ...

You can do what you have done using plain SQL in db2 ... That should be more efficient than reading the table once for each office ....

Here is a sample code ... This script picks up the top two high paid employees in each department and inserts the records into another table ... See if you can use this example

Code:

drop table depsalrank
;
create table depsalrank(empno char(6),depno char(3),esal integer)
;
insert into depsalrank
with temp(eno,dno,sal,row#) as
(
select empno,deptno,salary,
       rownumber() over (partition by deptno order by salary desc) as row#
from employee e,
     department d
where  e.workdept=d.deptno
)
select eno,dno,sal from temp where row# <=2
;
select * from depsalrank
;

Cheers
sathyaram

JDionne · #10 (**permalink**) 05-26-04, 12:49

Quote:

Originally Posted by sathyaram_s

If understand you are inserting the first 50 rows based on some condition for each office ...

In DB2 , cursors cannot be used in a CLP ... You may have to write SP if you want to do it using cursors ...

You can do what you have done using plain SQL in db2 ... That should be more efficient than reading the table once for each office ....

Here is a sample code ... This script picks up the top two high paid employees in each department and inserts the records into another table ... See if you can use this example

Code:

drop table depsalrank
;
create table depsalrank(empno char(6),depno char(3),esal integer)
;
insert into depsalrank
with temp(eno,dno,sal,row#) as
(
select empno,deptno,salary,
       rownumber() over (partition by deptno order by salary desc) as row#
from employee e,
     department d
where  e.workdept=d.deptno
)
select eno,dno,sal from temp where row# <=2
;
select * from depsalrank
;

Cheers
sathyaram

I have been going over this and a bit of it is confusing to me:

Code:

insert into depsalrank
with temp(eno,dno,sal,row#) as
(
select empno,deptno,salary,
       rownumber() over (partition by deptno order by salary desc) as row#
from employee e,
     department d
where  e.workdept=d.deptno
)
select eno,dno,sal from temp where row# <=2
;

I cant find any notes on the line Insert into depsalrank with temp...
Im lost there. Can you please clearify?
Jim

sathyaram_s · #11 (**permalink**) 05-27-04, 17:21

Have a look at the SQL Reference Manual INSERT SYNTAX

INSERT INTO TABLENAME full-select

The following is my full-select:
with temp(eno,dno,sal,row#) as
(
select empno,deptno,salary,
rownumber() over (partition by deptno order by salary desc) as row#
from employee e,
department d
where e.workdept=d.deptno
)
select eno,dno,sal from temp where row# <=2

To make it more readable, you can create a view empdeprank_view with the above CTE and then say

insert into depsalrank select * from empdeprank_view

HTH

Sathyaram

JDionne · #12 (**permalink**) 05-27-04, 17:25

Thanks
Im in the middle of reading the SQL Cookbook, its a great reference that has explaned this to me. Thank you for your time, I will try to apply your code in the comming days. Ill get back to you when I have it done.
Jim

Internet Services

Web Development

Digital Marketing

Consumer Tech