Hello

I am pretty new to DB2 Scripting. I am using DB2 UDB 8.1 fixpack 3 on Solaris .
Written a simple stored procedure like.

CREATE PROCEDURE loop_until_space(OUT counter INT)
LANGUAGE SQL
P1: BEGIN
DECLARE v_firstnme VARCHAR(12);
DECLARE v_midinit CHAR(1);
DECLARE v_lastname VARCHAR(15);
DECLARE v_counter SMALLINT DEFAULT 0;

DECLARE c1 CURSOR FOR
SELECT ACCOUNT_OFFICER.ACCT_OFFICER_NM,
ACCOUNT_OFFICER.ACCT_OFFICER_NM,
ACCOUNT_OFFICER.ACCT_OFFICER_REGION
FROM GCS.ACCOUNT_OFFICER
AS ACCOUNT_OFFICER;
--SELECT firstnme, midinit, lastname
--FROM employee
--ORDER BY midinit DESC;
-- DECLARE CONTINUE HANDLER FOR NOT FOUND
-- SET counter = -1;

-- initialize OUT parameter
-- SET counter = 0;
OPEN c1;
fetch_loop:
LOOP
FETCH c1 INTO
v_firstnme, v_midinit, v_lastname;
-- Use a local variable for the iterator variable
-- because SQL procedures only allow you to assign
-- values to an OUT parameter
-- SET v_counter = v_counter + 1;
IF v_midinit = ' ' THEN
LEAVE fetch_loop;
END IF;
END LOOP fetch_loop;
CLOSE c1;

-- Now assign the value of the local
-- variable to the OUT parameter
-- SET counter = v_counter;
END p1
************************************************** ********
When I am trying to build this procedure , it throws an error

GCS.loop_until_space - Build started.
Create stored procedure returns -7032.

-- LOG FILE P9483692.log FOR PROCEDURE GCS .LOOP_UNTIL_SPACE

-- DB2_SQLROUTINE_PREPOPTS=

-- PREP/BIND MESSAGES FOR /export/home/db2srv/sqllib/function/routine/sqlproc/GCS_STAG/GCS/tmp/P9483692.sqc

LINE MESSAGES FOR P9483692.sqc
------ --------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
SQL0091W Precompilation or binding was ended with "0"
errors and "0" warnings.

-- DB2_SQLROUTINE_COMPILER_PATH=/export/home/db2srv/sqllib/function/routine/sr_cpath

-- DB2_SQLROUTINE_COMPILE_COMMAND=cc -I/export/home/db2srv/sqllib/include SQLROUTINE_FILENAME.c -shared -o SQLROUTINE_FILENAME -L/export/home/db2srv/sqllib/lib -ldb2

-- CONTENTS OF /export/home/db2srv/sqllib/function/routine/sr_cpath

#!/bin/sh
PATH=$PATH:/usr/local/bin:/opt/SUNWspro/bin
export PATH

-- CONTENTS OF /export/home/db2srv/sqllib/function/routine/sqlproc/GCS_STAG/GCS/tmp/P9483692.sh

SQLROUTINE_FILENAME=P9483692
export SQLROUTINE_FILENAME

-- COMPILATION COMMAND:

cc -I/export/home/db2srv/sqllib/include P9483692.c -shared -o P9483692 -L/export/home/db2srv/sqllib/lib -ldb2


-- CONTENTS OF /export/home/db2srv/sqllib/function/routine/sqlproc/GCS_STAG/GCS/tmp/P9483692.exp

pgsjmp


-- COMPILATION MESSAGES FOR /export/home/db2srv/sqllib/function/routine/sqlproc/GCS_STAG/GCS/tmp/P9483692.c
P9483692.sqc: In function `pgsjmp':
P9483692.sqc:465: storage size of `sql_setdlist' isn't known
P9483692.sqc:492: storage size of `sql_setdlist' isn't known
P9483692.sqc:492: storage size of `sql_setdlist' isn't known

-- END OF LOG FILE (SQLCODE: -7032)

GCS.loop_until_space - Build failed.
GCS.loop_until_space - Roll back completed successfully.
************************************************** ********
Point to note is : If I comment out all 'set ' commands like Set counter = V_count +1 , etc and assignment operators Then it compiles .

Any hing on this? What is this function 'pgsjmp' ? What are the settings required to succcessfully compile a proc.

Any help on this will be greatly appreciated.

Regards
Swati

SQL stored procedures are a bit of a mess... they'll be fixed in Stinger, a C compiler will no longer be required (yay!)

A good reference in the mean time is:
"Application Development Guide: Building and Running Applications", part of the db2 docs.

This particular error I haven't seen, but it looks like your compiler can't resolve some type that sql_setdlist depends on? I'm not a C compiler guru by any stretch.

Hi Jonathan

Thanks for the reply. But still I am not able to resolve the problem. I am now doing the conversion on a oracle database and then migrate it to DB2.

Can you suggest a way where I can migrate the data from Oracle to DB2...using ODBC just like taking a dump. I have table created at both instances.


By the way where can I get the 'Application Development Guide: Building and Running Applications' book /material?

Regards
Swati

What compiler are you using ? If it is one of IBM supported (Application Building Guide has the list) , then get in touch with IBM support ...

Visit the website, www.ibm.com/db2/migration for migration resources .... There is a redbook on 'Oracle to DB2 Migration' at www.redbooks.ibm.com which you may find very helpful ...

See the thread 'Useful DB2 Stuff' for link to the manuals ...

HTH

Sathyaram

I am using GCC compiler.

Books:
http://www-306.ibm.com/software/data...manualsv8.html

See pg 31-32, seems to deal with the set-up for compiling.

You could do it with ODBC in theory, using federated databases, although the set-up might be a bit much to deal with.

Use sathyaram_s's links.

(BTW - I used to use DB2 on Solaris, and it was wonderful! Rock solid... even the folks in the lab praise it as one of the best OS's out there for DB2)