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.

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?

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.

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.

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 can find the manuals online. For example, DB2 V9.5: IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

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

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.

Lenny

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

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

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)

this is a great site thanks!

Can somebody help me out for my above concern??

On DB2 9.7 for LUW, you can include OR REPLACE clause, like this:

CREATE OR REPLACE PROCEDURE sample ...

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??

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.