how to create Store procedures without using development c..

wrote:

> Hi,
> I am new to DB2, My installation of DB does not include Development
> center...
> but i need to create stored procedures.
> Geeks please help me how to create stored procedure and please step by
> step..

As Jean-Marc points out, you can grab Data Studio and use that.
Alternatively, if you prefer a more light-weight solution (Data Studio,
being Eclipse based, is *fat*), albeit without much fancy debugging
functionality, you can simply stick the code for your procedure in a
text file and run it with the CLP.

So, step by step (assuming you're on Windows):

1. Stick the code (nicked from an example in the InfoCenter for the
stored procedure in a text file, e.g. notepad createproc.sql:

DROP SPECIFIC PROCEDURE MEDIAN_SET@

CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DOUBLE)
SPECIFIC MEDIAN_SET
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE v_numRecords INT DEFAULT 1;
DECLARE v_counter INT DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT CAST(salary AS DOUBLE)
FROM staff
ORDER BY salary;
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, job, CAST(salary AS INTEGER)
FROM staff
WHERE salary > medianSalary
ORDER BY salary;
DECLARE EXIT HANDLER FOR NOT FOUND
SET medianSalary = 6666;
SET medianSalary = 0;
SELECT COUNT(*) INTO v_numRecords
FROM STAFF;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1) DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;
OPEN c2;
END@

Note the use of an statement terminator at the end (@ in this case).
You can't use semi-colon as it's being used within the stored proc
definition and would terminate the definition early.

2. Fire up a DB2 Command Window (should be under the Start menu, IBM
DB2, Command Line Tools)

3. Enter the following to open a connection to the database in which
you wish to create the stored proc (note: the database must be
cataloged - use the Configuration Assistant for this):

db2 CONNECT TO targetdb USER username USING password

4. Run the script you created above with the following command line
(note that we need to tell DB2 about the alternate statement
terminator, (-td@) - the other switches just tell DB2 to echo commands
as they are executed (-v), and what file to run (-f)):

db2 -td@ -v -f createproc.sql


Cheers,

Dave.