Errors in first stored procedure

[DB_2]

Hello,

I created the following stored procedure. (Please ignore the logic in
it; at this point it is not complete and I am trying to get the basic
structure working first.)

CREATE PROCEDURE upgrade_sal ()
LANGUAGE SQL
BEGIN
DECLARE eldest INT;
DECLARE nextemp INT;
DECLARE newsalary INT;
DECLARE newbonus INT;
DECLARE c1 CURSOR WITH HOLD FOR
SELECT empno FROM employees FOR UPDATE OF salary, bonus;

SELECT MAX(age) INTO eldest FROM employees WHERE status = 1 ;
IF (eldest = 0) THEN GOTO NO_MORE; END IF;

OPEN c1;

FETCH c1 INTO nextemp;

LOOP
SET newsalary = 1000;
SET newbonus = newsalary * 10 / 100;
UPDATE employees SET salary = newsalary, bonus = newbonus
WHERE CURRENT OF c1;
FETCH c1 INTO nextemp;
END ;

NO_MORE:
CLOSE c1;
END


Now, when I try to compile this, DB2 complains with the following
error.

SQL7032N SQL procedure "UPGRADE_SA L" not created. Diagnostic file is
"P5350392.l og". SQLSTATE=42904

I tried to locate the diagnostic file mentioned in the error message
but can't find it. Any help will be greatly appreciated.

[Kumar]

Re: Errors in first stored procedure

Hello,

You can find these files under :

UNIX
$HOME/sqllib/function/routine/sqlproc/<db_name>/<schema_name>/tmp

Windows
sqllib\function \routine\sqlpro c\<db_name>\<sc hema_name>\tmp

AND here is the explanation for SQL7032N error :
=============== =============== =============== ==

SQL7032N SQL procedure procedure-name not created. Diagnostic file is
file-name.

Explanation: SQL Procedure procedure-name was not created. One of the
following has occurred:

Support for SQL stored procedures is not installed and configured on
this server. You need to have the DB2 Application Development Client
and a C compiler installed on the server to create SQL procedures. You
may need to set the DB2 registry variable DB2_SQLROUTINE_ COMPILER_PATH
to point to a script or batch file that contains environment settings
for the C compiler on your platform.
DB2 failed to precompile or compile the SQL stored procedure. DB2
creates an SQL procedure as a C program that contains embedded SQL.
Errors not found during the initial parsing of the CREATE PROCEDURE
statement can be found during the precompile or compile stage.
For UNIX platforms, the full path of the file that contains diagnostic
information is:
$DB2PATH/function/routine/sqlproc/ \
$DATABASE/$SCHEMA/tmp/file-name

where $DATABASE represents the name of the database, and $SCHEMA
represents the schema name of the SQL procedure.

For OS/2 and Windows operating systems, the full path of the file that
contains diagnostic information is:
%DB2PATH%\funct ion\routine\sql proc\ \
%DATABASE%\%SCH EMA%\tmp\file-name

where %DATABASE% represents the name of the database, and %SCHEMA%
represents the schema name of the SQL procedure.

User Response: Ensure that both a compatible C compiler and a DB2
Application Development Client are installed on the server. If a
precompile or compile error occurred, refer to the messages from the
precompiler or compiler in the diagnostic file file-name.

Ensure that the DB2 registry variable DB2_SQLROUTINE_ COMPILER_PATH is
set to point to a script or batch file that sets up the C compiler
environment. On a UNIX operating system, for example, you may create a
script called "sr_cpath" in the
/home/DB2INSTANCE/sqllib/function/routine directory. To set the DB2
registry variable DB2_SQL_ROUTINE _COMPILER_PATH accordingly, issue the
following command:

db2set DB2_SQLROUTINE_ COMPILER_PATH = \
" \
/home/DB2INSTANCE/sqllib/function/ \
routine/sr_cpath"

sqlcode: -7032

sqlstate: 42904