MySQL Forums
Forum List  »  IBM DB2

problem during create stored procedure
Posted by: salimah sinon
Date: November 25, 2005 12:52AM

hi.. im new in ibm db2..
i have a problem during create my simple stored procedure

actually i have create table profile and staff like shown below :-

--TABLE PROFILE

Create Table Profile
create table profile (
ic_number varchar (12) not null primary key,
name varchar(255) not null,
address varchar(255),
email varchar(150),
mobile varchar(12),
dob date
)

--TABLE STAFF

create table staff (
staff_id varchar (6) not null,
designation varchar (50) not null,
salary decimal,
join_date timestamp,
status integer,
ic_number varchar (12) not null,
constraint ic_number1 foreign key (ic_number) references profile (ic_number),
constraint status1 check (status in (0,1)),
primary key (staff_id, ic_number)
)


after that i try to create a simple stored procedure like this...

CREATE PROCEDURE DB2ADMIN2.PROCEDURE1 (IN staff_id VARCHAR(6))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
select s.staff_id, s.designation, s.salary, s.join_date, s.status,
p.ic_number, p.name, p.address, p.email, p.mobile, p.dob
from staff S
inner join profile P on s.ic_number = p.ic_number
where s.staff_id = staff_id;
OPEN cursor1;
END P1


finally i found this error..
---------------------------------------------------------------------------------
DB2ADMIN2.PROCEDURE1 - Build started.
Create stored procedure returns -7032.

-- LOG FILE P4311254.log FOR PROCEDURE DB2ADMIN2.PROCEDURE1

-- DB2_SQLROUTINE_PREPOPTS=

-- PREP/BIND MESSAGES FOR C:\PROGRA~1\IBM\SQLLIB\function\routine\sqlproc\EMPLOYEE\DB2ADMIN2\tmp\P4311254.sqc


LINE MESSAGES FOR P4311254.sqc

------ --------------------------------------------------------------------

SQL0060W The "C" precompiler is in progress.

SQL0091W Precompilation or binding was ended with "0"

errors and "0" warnings.


-- CONTENTS OF C:\PROGRA~1\IBM\SQLLIB\function\routine\sr_cpath.bat




-- CONTENTS OF C:\PROGRA~1\IBM\SQLLIB\function\routine\sqlproc\EMPLOYEE\DB2ADMIN2\tmp\P4311254.BAT

@echo on
set SQLROUTINE_FILENAME=P4311254
set db2path=C:\PROGRA~1\IBM\SQLLIB

-- COMPILATION COMMAND:

nmake /f C:\PROGRA~1\IBM\SQLLIB\function\routine\sqlproc.mak


-- CONTENTS OF C:\PROGRA~1\IBM\SQLLIB\function\routine\sqlproc\EMPLOYEE\DB2ADMIN2\tmp\P4311254.def

LIBRARY P4311254
EXPORTS _pgsjmp@8
pgsjmp=_pgsjmp@8


-- COMPILATION MESSAGES FOR C:\PROGRA~1\IBM\SQLLIB\function\routine\sqlproc\EMPLOYEE\DB2ADMIN2\tmp\P4311254.c
-- C COMPILATION ERROR CODES: 0 0 1


C:\PROGRA~1\IBM\SQLLIB\function\routine\sqlproc\EMPLOYEE\DB2ADMIN2\tmp>set SQLROUTINE_FILENAME=P4311254



C:\PROGRA~1\IBM\SQLLIB\function\routine\sqlproc\EMPLOYEE\DB2ADMIN2\tmp>set db2path=C:\PROGRA~1\IBM\SQLLIB



C:\PROGRA~1\IBM\SQLLIB\function\routine\sqlproc\EMPLOYEE\DB2ADMIN2\tmp>nmake /f C:\PROGRA~1\IBM\SQLLIB\function\routine\sqlproc.mak

'nmake' is not recognized as an internal or external command,

operable program or batch file.


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

DB2ADMIN2.PROCEDURE1 - Build failed.
DB2ADMIN2.PROCEDURE1 - Roll back completed successfully.
-----------------------------------------------------------------------------------

i really2 need ur help...
i dont know how to solve this problem..

Options: ReplyQuote


Subject
Views
Written By
Posted
problem during create stored procedure
12515
November 25, 2005 12:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.