Results 1 to 12 of 12

Thread: Variables

  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: Variables

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    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
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    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
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    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
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    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
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  12. #12
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    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
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •