Helpful Information
 
 
Category: DB2 Development
Creating stored procedures with db2 Personal Edition on Windows XP

Hello,

can someone help me with my problem:

I want to create a stored procedure under
db2 Personal Edition on Windows XP.
I have stored the sp in the file test.db2
and it looks like this:
CREATE PROCEDURE
test()
LANGUAGE SQL
BEGIN
DECLARE cur1
CURSOR WITH
RETURN FOR
SELECT *
FROM employee;
END @

When i try to make the sp with the command:
db2 -td@ -vf test.db2
the following error message gets printed on the screen(sorry is in german language only!):
------------------------------------------------------
CREATE PROCEDURE
test()
LANGUAGE SQL
BEGIN
DECLARE cur1
CURSOR WITH
RETURN FOR
SELECT *
FROM employee;
END

DB21034E Der Befehl wurde als SQL-Anweisung verarbeitet, da es sich um keinen
gültigen Befehl des Befehlszeilenprozessors handelte. Während der
SQL-Verarbeitung wurde Folgendes ausgegeben:
SQL7032N Die SQL-Prozedur "TEST" wurde nicht erstellt. Die Diagnosedatei ist
"P2210681.log". SQLSTATE=42904
-- LOG FILE P2210681.log FOR PROCEDURE MMM .TEST

-- DB2_SQLROUTINE_PREPOPTS=

-- PREP/BIND MESSAGES FOR C:\Programme\IBM\SQLLIB\function\routine\sqlproc\SAMPL
E\MMM\tmp\P2210681.sqc

ZEILE NACHRICHTEN FÜR P2210681.sqc
------ --------------------------------------------------------------------
SQL0060W Der Precompiler "C" wird ausgeführt.
SQL0091W Vorkompilieren oder Binden mit "0" Fehler(n) und
"0" Warnung(en) beendet.

-- CONTENTS OF C:\Programme\IBM\SQLLIB\function\routine\sr_cpath.bat



-- CONTENTS OF C:\Programme\IBM\SQLLIB\function\routine\sqlproc\SAMPLE\MMM\tmp\P
2210681.BAT

@echo on
set SQLROUTINE_FILENAME=P2210681
set db2path=C:\Programme\IBM\SQLLIB

-- COMPILATION COMMAND:

nmake /f C:\Programme\IBM\SQLLIB\function\routine\sqlproc.mak


-- CONTENTS OF C:\Programme\IBM\SQLLIB\function\routine\sqlproc\SAMPLE\MMM\tmp\P
2210681.def

LIBRARY P2210681
EXPORTS _pgsjmp@8
pgsjmp=_pgsjmp@8


-- COMPILATION MESSAGES FOR C:\Programme\IBM\SQLLIB\function\routine\sqlproc\SAM
PLE\MMM\tmp\P2210681.c
-- C COMPILATION ERROR CODES: 0 0 1

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

------------------------------------------------------
I do not know how to fix this problem because there is no
right error message in the text above???
Thanks!

The first line of your error message says:

The instruction was processed as SQL instruction, since it concerned no valid instruction of the command line processor.

It is possible that the script would not run because you weren't connected to the database or were logged in as a user other than the instance owner therefore causing a permission problem when running the script.

Try adding the following line to the top of the script:

CONNECT TO database USER userid USING password @

This will make sure that you are connected to the target database with the correct permissions. I'm not sure if it will solve your problem, but at least it will help me narrow down on the possible causes as I've not seen this error before.

thanks for the reply...
I have added the connection line but the same error message occurs???

Using your script I was able to create a stored procedure from the command line in Linux and from the DOS prompt on a Win2K laptop.

Can you run this command manually from a DB2 prompt? In other words, instead of wrapping it in a script, do you get any error messages if you go to the Command Line Processor, type in a connect statement and then type in a command to create the stored procedure?

Hello and thanks again,

I have tried to create the procedure manually in the console with following command and I got the same error message

db2 connect to sample

CREATE PROCEDURE test() LANGUAGE SQL BEGIN DECLARE cur1 CURSOR WITH RETURN FOR SELECT * FROM employee; END

When it runs on Your Machine then it has perhaps something to do with my running System? But I don't know where the Problem is? I have tried many things, even a new installation of my Microsoft Visual Studio C++ 6.0 Compiler.
And I have searched in the google groups and it seems that I am not the only one who has this Problem. There they say, that there must be a registry variable set
DB2_SQLROUTINE_COMPILER_PATH
I have set it to
C:\Programme\Microsoft Visual Studio\VC98\Bin\VCVARS32.BAT
like they said but the Problem is still there???
And the Compiler Environment must be correctly set, becaue I can compile embedded SQL C applications...

To my Problem:
In the log file folder were some files created:
P9365100.bnd
P9365100.c
P9365100.log
P9365100.sqc
but the stored procedure is not created?
I Use DB2 Personal Edition and Visual Studio C++ 6.0 Introductory Edition?

I Have set all the registry Variables mentioned in
-----------
http://groups.google.de/groups?q=db2+stored+procedures+7032&hl=de&lr=&ie=UTF-8&selm=9mk4n1%241db0%241%40news.boulder.ibm.com&rnum=1
-------------
but the problem is still there. At the End of the log File there is a C COMPILATION ERROR CODES: 0 0 2.
What can that mean?
Here is the newest log File:
------------------------------------------------------
-- LOG FILE P0403828.log FOR PROCEDURE MMM .TEST

-- DB2_SQLROUTINE_PREPOPTS=

-- PREP/BIND MESSAGES FOR C:\Programme\IBM\SQLLIB\function\routine\sqlproc\SAMPLE\MMM\tmp\P0403828.sqc

ZEILE NACHRICHTEN FÜR P0403828.sqc
------ --------------------------------------------------------------------
SQL0060W Der Precompiler "C" wird ausgeführt.
SQL0091W Vorkompilieren oder Binden mit "0" Fehler(n) und
"0" Warnung(en) beendet.

-- DB2_SQLROUTINE_COMPILER_PATH=c:\programme\microsoft visual studio\vc98\bin\vcvars32.bat

-- DB2_SQLROUTINE_COMPILE_COMMAND=cl -Od -W2 /TC -D_X86_=1 -IC:\Programme\IBM\SQLLIB\include SQLROUTINE_FILENAME.c /link -dll -def:SQLROUTINE_FILENAME.def /out:SQLROUTINE_FILENAME.dll C:\Programme\IBM\SQLLIB\lib\db2api.lib

-- CONTENTS OF c:\programme\microsoft visual studio\vc98\bin\vcvars32.bat

@echo off
rem
rem Root of Visual Developer Studio Common files.
set VSCommonDir=C:\Programme\Microsoft Visual Studio\Common

rem
rem Root of Visual Developer Studio installed files.
rem
set MSDevDir=C:\Programme\Microsoft Visual Studio\Common\MSDev98

rem
rem Root of Visual C++ installed files.
rem
set MSVCDir=C:\Programme\Microsoft Visual Studio\VC98

rem
rem VcOsDir is used to help create either a Windows 95 or Windows NT specific path.
rem
set VcOsDir=WIN95
if "%OS%" == "Windows_NT" set VcOsDir=WINNT

rem
echo Setting environment for using Microsoft Visual C++ tools.
rem

if "%OS%" == "Windows_NT" set PATH=%MSDevDir%\BIN;%MSVCDir%\BIN;%VSCommonDir%\TOOLS\%VcOsDir%;%VSCommonDir%\TOOLS;%PATH%
if "%OS%" == "" set PATH="%MSDevDir%\BIN";"%MSVCDir%\BIN";"%VSCommonDir%\TOOLS\%VcOsDir%";"%VSCommonDir%\TOOLS";"%windir%\SYSTEM";"%PATH%"
set INCLUDE=%MSVCDir%\ATL\INCLUDE;%MSVCDir%\INCLUDE;%MSVCDir%\MFC\INCLUDE;%INCLUDE%
set LIB=%MSVCDir%\LIB;%MSVCDir%\MFC\LIB;%LIB%

set VcOsDir=
set VSCommonDir=

-- CONTENTS OF C:\Programme\IBM\SQLLIB\function\routine\sqlproc\SAMPLE\MMM\tmp\P0403828.BAT

@echo on
set SQLROUTINE_FILENAME=P0403828
set db2path=C:\Programme\IBM\SQLLIB

-- COMPILATION COMMAND:

cl -Od -W2 /TC -D_X86_=1 -IC:\Programme\IBM\SQLLIB\include P0403828.c /link -dll -def:P0403828.def /out:P0403828.dll C:\Programme\IBM\SQLLIB\lib\db2api.lib


-- CONTENTS OF C:\Programme\IBM\SQLLIB\function\routine\sqlproc\SAMPLE\MMM\tmp\P0403828.def

LIBRARY P0403828
EXPORTS _pgsjmp@8
pgsjmp=_pgsjmp@8


-- COMPILATION MESSAGES FOR C:\Programme\IBM\SQLLIB\function\routine\sqlproc\SAMPLE\MMM\tmp\P0403828.c
-- C COMPILATION ERROR CODES: 0 0 2

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

Does your DB2 installation have a db2vccmd executable in one of the directories? I do not know if this will help or solve any of your problems, but IBM made a set of Visual C++ Add-Ins to help in the creation of stored procedures and the compiling of DB2 code. Running the above command adds DB2 development tools and wizards into your Visual C++ IDE. If this file is anywhere on your machine, I figured it couldn't hurt to install this feature and you might luck out and get your environment tweaked the way it needs to be.

Since I can't replicate your error, I'm just guessing now.

I haven't found the command db2vccmd , but there is a command db2cmd.exe, which can be called via the batch-file db2vsregister.bat. This File makes the following call:
db2cmd.exe db2setcp.bat db2vscmd.exe register

The db2setcp.bat File include following lines:
@echo off
set DB2SETCPARGS=
:next
if "%1"=="" goto setcp
set DB2SETCPARGS=%DB2SETCPARGS% %1
shift
goto next
:setcp
db2clpsetcp
cls
%DB2SETCPARGS%


and when I run the db2vsregister.bat.
there is following error message printed:
IBM DB2-Entwicklungs-Add-In für Microsoft Visual Studio wird registriert...
[/I]
IBM DB2-Entwicklungs-Add-In für Microsoft Visual Basic wird registriert...Operat
ion erfolgreich.
IBM DB2-Entwicklungs-Add-In für Microsoft Visual Interdev wird registriert...Ope
ration erfolgreich.
IBM DB2-Entwicklungs-Add-In für Microsoft Visual C++ wird registriert...Fehlgesc
hlagen. (RC = 2)
IBM DB2-Tools-Add-In für Microsift Visual C++ wird registriert...Fehlgeschlagen.
(RC = 2)

Registrierung abgeschlossen.[/I]

I try to reinstall db2, something is really weird here...

after reinstallation same Problem occurs.
But a fiend has the same db2 version and the same vs 6 c++ Introductory compiler and there it runs perfectly.
I Have no Idea what is causing the Problem?

Translated your error message says:

IBM DB2-Entwicklungs-Add-In for Microsoft Visual studio registered...
IBM DB2-Entwicklungs-Add-In for Microsoft Visual basic registered... operation successfully.
IBM DB2-Entwicklungs-Add-In for Microsoft Visual Interdev registered... operation successfully.
IBM DB2-Entwicklungs-Add-In for Microsoft Visual C++ one fails registered.... (RC = 2)
IBM DB2-Tools-Add-In for Microsift Visual C++ registered... one fails. (RC = 2)

registration locked.

Obviously there is something wrong with the C++ part of your installation as that is the part that fails. The error message you're getting commonly occurs when you haven't logged into Visual C++ using the login you're trying to create the stored procedure under. Go to the following web site and click the hyperlink about activating the product at the bottom of the page:

http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0005202.htm

the registration with c++ is now successful. But the 7032 Error is the same as before.
I have here my Settings for the db2 environment, perhaps there is something wrong with that:
--------------------------------------------------
C:\>db2set -all
[e] DB2PATH=C:\Programme\IBM\SQLLIB
[i] DB2_SQLROUTINE_COMPILER_PATH=C:\Program Files\Microsoft Visual Studio\VC98\b
in\vcvars32.bat
[i] DB2_SQLROUTINE_COMPILE_COMMAND=cl -Ox -W2 -TC -D_X86_=1 -MD -IC:\Programme\I
BM\SQLLIB\include SQLROUTINE_FILENAME.c /link -dll -def:SQLROUTINE_FILENAME.def
/out:SQLROUTINE_FILENAME.dll C:\Programme\IBM\SQLLIB\lib\db2api.lib
[i] DB2INSTPROF=C:\Programme\IBM\SQLLIB
[i] DB2COMM=TCPIP
[g] DB2SYSTEM=MARCUS
[g] DB2PATH=C:\Programme\IBM\SQLLIB
[g] DB2INSTDEF=DB2
[g] DB2ADMINSERVER=DB2DAS00
--------------------------------------------------

in the upper thread was something wrong... here are the right settings, the error is there anyway...

C:\work>db2set -all
[e] DB2PATH=C:\Programme\IBM\SQLLIB
[i] DB2_SQLROUTINE_COMPILER_PATH=C:\Programme\Microsoft Visual Studio\VC98\bin\vcvars32.bat
[i] DB2_SQLROUTINE_COMPILE_COMMAND=cl -Ox -W2 -TC -D_X86_=1 -MD -IC:\Programme\I
BM\SQLLIB\include SQLROUTINE_FILENAME.c /link -dll -def:SQLROUTINE_FILENAME.def
/out:SQLROUTINE_FILENAME.dll C:\Programme\IBM\SQLLIB\lib\db2api.lib
[i] DB2INSTPROF=C:\Programme\IBM\SQLLIB
[i] DB2COMM=TCPIP
[g] DB2SYSTEM=MARCUS
[g] DB2PATH=C:\Programme\IBM\SQLLIB
[g] DB2INSTDEF=DB2
[g] DB2ADMINSERVER=DB2DAS00

perhaps I have located the Error. But I don't know how to fix this.
I Have found in the location c:\ two log files named
1. P8161326.mg1
2. P8161326.mg2
which were created after executing the command
db2 -td@ -vf test.db2
(Content at the End of this Thread)

It seems that the Problem is, that the compiler is called in the folder C:\cl ... *.c ...

which is not the folder where db2 puts the c File.
The c File is in the Folder
C:\Programme\IBM\SQLLIB\FUNCTION\ROUTINE\sqlproc\...\tmp

When I am right, the Question now is how I can change the folder in which the compiler command gets invoked?

The log files contain the following text:

P8161326.mg2:
Microsoft (R) 32-bit C/C++ Standard Compiler Version 12.00.8168 for 80x86
Copyright (C) Microsoft Corp 1984-1998. All rights reserved.


P8161326.mg1(interesting):
Setting environment for using Microsoft Visual C++ tools.
C:\>set SQLROUTINE_FILENAME=P8161326
C:\>set db2path=C:\Programme\IBM\SQLLIB
C:\>cl -Od -W2 /TC -D_X86_=1 -IC:\Programme\IBM\SQLLIB\include P8161326.c /link -dll -def:P8161326.def /out:P8161326.dll C:\Programme\IBM\SQLLIB\lib\db2api.lib
P8161326.c
fatal error C1083: Cannot open source file: 'P8161326.c': No such file or directory

Just out of curiousity, did you load the Application Development Client on this machine?

http://www-306.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/document.d2w/report?fn=db2v7i1db2i162.htm

The reason I ask is that the documentation linked to below says that if you get a SQL7032N error, you need to "Ensure that both a compatible C compiler and a DB2 Application Development Client are installed on the server."

http://www-306.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/document.d2w/report?fn=db2v7m0sql7000.htm

Also, here's a .pdf file of a seminar given by an IBM engineer at a developerWorks conference on building stored procedures. It also mentions the Application Development Client as well as a bunch of other info that might be helpful.

http://www-106.ibm.com/developerworks/db2/library/events/0205dwlive/e1110/E1110.pdf

i have read this pdf and the link about the development client already and I have all that done what they said there

I don't have the development client explicitly installed. in the manual of the db2 personal edition there is nothing mentioned about that. and a friend of mine has the same version and it runs without explicitly installing the development client???
And I can compile and create ESQL C applications, which can only be created with the dev. app. client by this ibm text in the link...

I can't explain it to myself...maybe I should try it with another c/c++ compiler

Have your read the section Building SQL procedures on Windows servers in the following page:

http://www-106.ibm.com/developerworks/db2/library/techarticle/0301alazzawe/0301alazzawe.html

Hello,

I Have found and removed the error. I am really happy.
That was a really bad error, that had nothing to do with the compiler or db2.
The Environment was correct. It had actually something to do with the execution of the compiler. In the log File was the text:
-------------------------------------------
"C:\>nmake /f C:\Programme\IBM\SQLLIB\function\routine\sqlproc.mak
cl -Ox -W2 -TC -D_X86_=1 -MD -I"C:\Programme\IBM\SQLLIB\include" P3490239.c /link -dll /out:P3490239.dll -def:P3490239.def "C:\Programme\IBM\SQLLIB\lib\db2api.lib"
P3490239.c
fatal error C1083: Cannot open source file: 'P3490239.c': No such file or directory"
-----------------------------------------------
and i saw that the compiler is called in the directory C:\
The Problem was that C:\ was not the directory where the C file to compile was. So there was the Error Message.
But now where was the mistake?
I had accidental looked at the comand processor in the Windows XP Registry. There is a Variable set in
HKEY_LOCAL_MACHINE -> Software -> Microsoft -> command Processor
which is named Autorun. There was the value cd\ set. That means everytime a ms do console gets open the Current path is set to C:\ and thats why the error occurs. Very tricky! Now it runs without Problems!

Thanks a lot for spending your time !

Great! I was just about to tell you to steal your friend's machine and be done with it.

>to steal your friend's machine

that was the next thing that I had in mind ;)










privacy (GDPR)