Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2010
    Posts
    25

    Unanswered: DB2 Stored Procedure

    Hi All,

    I am new to IBM DB2. I was working on Sybase, so I am finding it difficult to write Stored Procedure in DB2 SQL.
    Actually I want to write a procedure that generates report that I can paste into an excel sheet. Can sombody help me with the syntax and any of the basic document.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    A stored procedure in standard SQL (and DB2 follows that) can return one or more result sets. So your procedure has to run some queries, open the cursors for them and leave them open. How you get those result sets into a spreadsheet is unrelated to that. Maybe you can tell us how Sybase is supporting this?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jun 2010
    Posts
    25
    Thanks stolze. Can you explain with some example.
    Like IN sybase, we first create a temporary table then insert data for some of the fiields on the basis of requirements. Then from those fields we update remaining fields and then at the end we do Final Select from the temporary table.And paste the result into the spreadsheet/excel.

    How can we achieve this into DB2 SQL.
    Is it always necessary that we have to use cursors in DB2 SP.
    Last edited by smartcooldevil; 07-27-10 at 18:58.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can do the same in DB2: create a temp table, manipulate the data in it, and then declare and open a cursor for a SELECT statement against the temp table. There are a lot of examples in the manual.

    p.s: Cursors are the ONLY way to access data in a table. Even if you have a SELECT ... INTO ..., you can think of this as using a cursor over a single row only.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jun 2010
    Posts
    25
    Thanks stolze once again.
    You have mentioned that there are lots of examples in manual, can you provide me the link where can I find the manual. Also give me some example if it is possible for u.

    I did below thing, will thi work if I write it in the CREATE PROCEDURE BODY.

    DECLARE GLOBAL TEMPORARY TABLE SESSION.pfg_surv_higherEd
    (
    family_num bigint,
    issuer_num bigint,
    issuer_name varchar(255),
    obligor_name varchar(255),
    obligor_num bigint,
    invoice_num bigint,
    invoice_dt date,
    invoice_amt decimal(10,2),
    fee_schedule_cd varchar(10),
    fee_scedule_name varchar(255)
    )on commit preserve rows;

    insert into SESSION.pfg_surv_higherEd (.......)
    select ........
    .........

    update SESSION.pfg_surv_higherEd
    set ......
    ......
    where .....


    select * from SESSION.pfg_surv_higherEd

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can find the manuals online. For example, DB2 V9.5: IBM DB2 9.5 Information Center for Linux, UNIX, and Windows
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow Fyi

    Quote Originally Posted by smartcooldevil View Post
    Thanks stolze once again.
    You have mentioned that there are lots of examples in manual, can you provide me the link where can I find the manual. Also give me some example if it is possible for u.

    I did below thing, will thi work if I write it in the CREATE PROCEDURE BODY.

    DECLARE GLOBAL TEMPORARY TABLE SESSION.pfg_surv_higherEd
    (
    family_num bigint,
    issuer_num bigint,
    issuer_name varchar(255),
    obligor_name varchar(255),
    obligor_num bigint,
    invoice_num bigint,
    invoice_dt date,
    invoice_amt decimal(10,2),
    fee_schedule_cd varchar(10),
    fee_scedule_name varchar(255)
    )on commit preserve rows;

    insert into SESSION.pfg_surv_higherEd (.......)
    select ........
    .........

    update SESSION.pfg_surv_higherEd
    set ......
    ......
    where .....


    select * from SESSION.pfg_surv_higherEd
    You have to declare cursor and open it. Don't do FETCH from this cursor.
    All fetched rows would missed for caller program:


    Code:
    Stored procedure SP1
    
    .............................................
    .............................................
    .............................................
    
    DECLARE C1 CURSOR 
    WITH RETURN 
    FOR 
    select * from SESSION.pfg_surv_higherEd
    ;
    OPEN C1;
    However using "SELECT *...." is not a good practice.
    Also, you have to make CALL SP1 then Associate locator and Allocate cursor.

    After this, without OPEN you can FETCH your cursor with name cursor not in SP, this is local name, but from ALLOCATE statement.


    Code:
    CALL SP1;
    if sqlcode = +466 Then
       ASSOCIATE RESULT SET LOCATORS LOC1 WITH PROCEDURE SP1;
       ALLOCATE C11 CURSOR FOR RESULT SET LOC1;
       FETCH C11 into ....;
    end-if
    Lenny

  8. #8
    Join Date
    Jun 2010
    Posts
    25
    Thanks
    Now I am facing problem with "UPDATE".
    Like, I have created one temporary table with 10 columns.
    I have inserted data into some 5 coulmns.
    Now I want to update other 5 coulmns one by one on the basis of already inserted data.

    eg.
    Let say I have temporary table as temp1 with 10 fields like ID, Join Date. etc..
    Now I have populated the ID columns with 10 values.
    Now my aim is to update Join Date column from the permanant table EMP by joining the ID coumns.

    How do I perform this operation in DB2.
    I used to do in SYBASE like this.

    UPDATE temp1
    SET join_date = e.date
    FROM temp1 t1 , EMP e
    where t1.ID = e.ID

  9. #9
    Join Date
    Jun 2010
    Posts
    25
    I have one more question on stored procedures.
    I am writing the procedure in following ways...

    DROP SPECIFIC PROCEDURE sample
    ;

    CREATE PROCEDURE sample(IN FROM_DATE DATE,
    IN TO_DATE DATE)
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    NULL CALL
    LANGUAGE SQL EXTERNAL ACTION
    INHERIT SPECIAL REGISTERS
    BEGIN
    ............
    ....

    When I compile it, first time it gives error that the procedure us UNDEFINED.
    I know this is because of my first statement where I am dropping it.

    But this error will pop u only when I compile it for the first time.
    is there anyway I can check that...
    If procedure exists then drop it first and then create
    else create procedure directly.


    In sybase I used do it like following way..

    IF OBJECT_ID('dbo.sample) IS NOT NULL
    BEGIN
    DROP PROCEDURE dbo.sample
    IF OBJECT_ID('dbo.sample) IS NOT NULL
    PRINT '<<< FAILED DROPPING PROCEDURE dbo.sample>>>'
    ELSE
    PRINT '<<< DROPPED PROCEDURE dbo.sample>>>'
    END
    go
    create procedure sample
    .............
    ......
    ....


    Please help me out as I am not able find how should I do this in DB2 SQL

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    eg.
    Let say I have temporary table as temp1 with 10 fields like ID, Join Date. etc..
    Now I have populated the ID columns with 10 values.
    Now my aim is to update Join Date column from the permanant table EMP by joining the ID coumns.

    How do I perform this operation in DB2.
    I used to do in SYBASE like this.

    UPDATE temp1
    SET join_date = e.date
    FROM temp1 t1 , EMP e
    where t1.ID = e.ID
    Why did you took two steps(i.e. insert then update)?

    If you did:
    INSERT INTO temp1(... , join_date , ...)
    SELECT .... , something , ...
    FROM ...
    WHERE ...

    You can include EMP table, like:
    INSERT INTO temp1(... , join_date , ...)
    SELECT .... , e.date , ...
    FROM ... , EMP
    WHERE ...
    AND t1.ID = e.ID

    If you persist in taking two steps(i.e. insert then update), try:
    UPDATE temp1 t1
    SET (join_date, col_x, col_y, ...) =
    (SELECT e.date, xxx, yyy, ...
    FROM EMP e
    where t1.ID = e.ID)

  11. #11
    Join Date
    Sep 2010
    Posts
    1

    hi

    this is a great site thanks!

  12. #12
    Join Date
    Jun 2010
    Posts
    25
    Quote Originally Posted by smartcooldevil View Post
    I have one more question on stored procedures.
    I am writing the procedure in following ways...

    DROP SPECIFIC PROCEDURE sample
    ;

    CREATE PROCEDURE sample(IN FROM_DATE DATE,
    IN TO_DATE DATE)
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    NULL CALL
    LANGUAGE SQL EXTERNAL ACTION
    INHERIT SPECIAL REGISTERS
    BEGIN
    ............
    ....

    When I compile it, first time it gives error that the procedure us UNDEFINED.
    I know this is because of my first statement where I am dropping it.

    But this error will pop u only when I compile it for the first time.
    is there anyway I can check that...
    If procedure exists then drop it first and then create
    else create procedure directly.


    In sybase I used do it like following way..

    IF OBJECT_ID('dbo.sample) IS NOT NULL
    BEGIN
    DROP PROCEDURE dbo.sample
    IF OBJECT_ID('dbo.sample) IS NOT NULL
    PRINT '<<< FAILED DROPPING PROCEDURE dbo.sample>>>'
    ELSE
    PRINT '<<< DROPPED PROCEDURE dbo.sample>>>'
    END
    go
    create procedure sample
    .............
    ......
    ....


    Please help me out as I am not able find how should I do this in DB2 SQL

    Can somebody help me out for my above concern??

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    On DB2 9.7 for LUW, you can include OR REPLACE clause, like this:

    CREATE OR REPLACE PROCEDURE sample ...

  14. #14
    Join Date
    Jun 2010
    Posts
    25
    It did not work.
    I tried with Create OR Replace Pocedure ....
    I got Following error.

    [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "CREATE OR REPLACE" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<transfer_ownership_of>". SQLSTATE=42601


    What could be the problem??

  15. #15
    Join Date
    May 2011
    Posts
    4

    Drop procedure

    DROP SPECIFIC PROCEDURE sample
    ;
    CREATE PROCEDURE sample(IN FROM_DATE DATE,
    IN TO_DATE DATE)
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    NULL CALL
    LANGUAGE SQL EXTERNAL ACTION
    INHERIT SPECIAL REGISTERS
    BEGIN
    ............
    ....

    DB2 will generate an 'SPECIFIC NAME' for your stored procedure that is different than your procedure name. you need to use the specific name in your drop command. Use the following query to find the specific name of your procedure
    SELECT SPECIFICNAME, ROUTINENAME FROM SYSSTAT.ROUTINES WHERE ROUNTINENAME='SAMPLE';
    Then drop the procedure using the SPECIFICNAME that is returned.

    To prevent this from happening in the first place add the following line in your procedure declaration after the create procedure....
    SPECIFIC SAMPLE
    The specific name is then the routine name and the DROP SPECIFIC PROCEDURE sample will work.

Posting Permissions

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