Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Compiling SQL procedures on DB2 UDB WE OS/2 v7.2.3

11 views
Skip to first unread message

Harald Wilhelm

unread,
Feb 4, 2002, 6:38:27 AM2/4/02
to
Hi,

we have a lot of database servers running on OS/2 Warp 4
systems. Installed is IBM DB2 UDB WE v7.2.3 (WR21269 - latest
fixpak), IBM VisualAge C++ v3 (FP8) and Warp 4 (FP 12 I think).

Procedures compile well but won't run. When executing the
"call" DB2 reports error "DB21036E - The command failed". The
DB2DIAG.LOG reports nothing, even with highest TRACE level
applied. Attached you'll find the table layout, the procedure
and the LOG of the compile job. It's just a test database. On
Linux we got it working, finally. But we need this feature on
OS2 as well.

Pleae, can somebody help us.

Many thanks in advance.

--The
table----------------------------------------------------------
--------

CREATE TABLE TEST.TEST
(SCHLUESSEL INTEGER NOT NULL GENERATED BY DEFAULT AS
IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE),
BEZEICHNUNG VARCHAR(250) NOT NULL)


---The
procedure------------------------------------------------------
-------

CREATE PROCEDURE INSERTTEST
(IN TEST_BEZEICHNUNG VARCHAR(250))
LANGUAGE SQL
BEGIN
INSERT INTO TEST.TEST (BEZEICHNUNG) VALUES
(TEST_BEZEICHNUNG);
END


---The compile
result-------------------------------------------------------

*** BIND C:\SQLLIB\function\routine\sqlproc\TEST\DB2_WWW\tmp
\P1050150.sqc ***

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

*** COMPILE C:\SQLLIB\function\routine\sqlproc\TEST\DB2_WWW\tmp
\P1050150.c ***

set SQLROUTINE_FILENAME=P1050150

icc -Ge- -Gm+ -W2 -IC:\SQLLIB\include P1050150.c /B "/NOFREE
/NOI /ST:64000" P1050150.def C:\SQLLIB\lib\db2api.lib

*** C:\SQLLIB\function\routine\sqlproc\TEST\DB2_WWW\tmp
\P1050150.def ***
LIBRARY P1050150
EXPORTS pgsjmp

[C:\SQLLIB\function\routine\sqlproc\TEST\DB2_WWW\tmp]
[C:\SQLLIB\function\routine\sqlproc\TEST\DB2_WWW\tmp]

[C:\SQLLIB\function\routine\sqlproc\TEST\DB2_WWW\tmp]set
SQLROUTINE_FILENAME=P1050150
[C:\SQLLIB\function\routine\sqlproc\TEST\DB2_WWW\tmp]

[C:\SQLLIB\function\routine\sqlproc\TEST\DB2_WWW\tmp]icc -Ge- -
Gm+ -W2 -IC:\SQLLIB\include P1050150.c /B "/NOFREE /NOI
/ST:64000" P1050150.def C:\SQLLIB\lib\db2api.lib

IBM(R) Linker for OS/2(R), Version 01.08.r1a_CTC308c
(C) Copyright IBM Corporation 1988, 1997.
(C) Copyright Microsoft Corp. 1988, 1989.
- Licensed Material - Program-Property of IBM - All Rights
Reserved.

Object Modules [.obj]: /NOFREE /NOI /ST:64000 +
Object Modules [.obj]: "P1050150.obj"
Run File [P1050150.*]: "P1050150.dll"
Map File [P1050150.map]: ""
Libraries [.lib]: "C:\SQLLIB\lib\db2api.lib"
Definitions File [nul.def]: "P1050150.def"

IBM VisualAge C++ for OS/2, Version 3

(C) Copyright IBM Corp. 1991, 1995.

- Licensed Materials - Program Property of IBM - All Rights
Reserved.


Pascal-Marc Gelinas

unread,
Feb 4, 2002, 7:27:54 AM2/4/02
to

i'm not familiar with the compiler/platform mix but can you check that
EXPORTS pgsjmp
is ok.

most of the time the pgsjmp is decorated like _pgsjmp@parameters_bytes

Does [nul.def]: means that "P1050150.def" is empty?

Can you put the compiler/linker in verbose mode?

PM

Serge Rielau

unread,
Feb 4, 2002, 4:50:17 PM2/4/02
to
Hi Harald,

Can you post you CALL? What happens if you qualify it with the schema?

Cheers
Serge
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada


Harald Wilhelm

unread,
Feb 5, 2002, 6:54:37 AM2/5/02
to
Serge,

thanks for your answer. This is what happens when calling the
SQL procedure. As you could see in my previous message, the SQL
procedure compiled well:


[C:\]db2 connect to test

Database Connection Information

Database server = DB2/2 7.2.3
SQL authorization ID = DB2_WWW
Local database alias = TEST


[C:\]db2 call db2_www.inserttest('a')
DB21036E The CALL command failed.


[C:\]db2 call inserttest('a')
DB21036E The CALL command failed.

Harald Wilhelm

unread,
Feb 5, 2002, 7:30:41 AM2/5/02
to
Pascal-Marc,

thanks for your answer.


1.) The automatically created and exported function is declared
as follows:

SQL_API_RC SQL_API_FN pgsjmp(char *TEST_BEZEICHNUNG,sqlint16
nullinds[1],struct sqlca *ca)

This looks ok.


2.) [nul.def] is used when there's nothing declared. In this
example P1050150.def is overwriting the default. The DEF file
does not exist physically, but it seems to be piped into the
compile process.

This looks ok too.


3.) I changed the compile options to these old "Ian Ameline" C
compiler guru specification from 10 years ago ;-)

db2set DB2_SQLROUTINE_COMPILE_COMMAND="icc /Ge- /Gm+
/Wcnddcleffgeniniobsordparprorearettruuse -Ic:\sqllib\include
SQLROUTINE_FILENAME.c /B \"/A:1 /E:2 /NOD: /NOE /NOL /PACKD
/NOFREE /NOI /ST:64000\" SQLROUTINE_FILENAME.def c:\sqllib\l
ib\db2api.lib"

It doesn't work either. For the LOG please have a look at the
end of this message.


Does anybody successfully compile SQL procedures on OS/2 boxes?
With the latest FP5 the minimum requirement for the OS/2
compiler changed from VAC++ 3 to VAC++ 3,56. We tested both -
none worked.

Thanks in advance.

---The compile result------------------------------------------

*** BIND C:\SQLLIB\function\routine\sqlproc\TEST\DB2_WWW\tmp

\P1252150.sqc ***

LINE MESSAGES FOR P1252150.sqc


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

*** COMPILE C:\SQLLIB\function\routine\sqlproc\TEST\DB2_WWW\tmp

\P1252150.c ***

DB2_SQLROUTINE_COMPILE_COMMAND=icc /Ge- /Gm+
/Wcnddcleffgeniniobsordparprorearettruuse -Ic:\sqllib\include
SQLROUTINE_FILENAME.c /B "/A:1 /E:2 /NOD: /NOE /NOL /PACKD
/NOFREE /NOI /ST:64000" SQLROUTINE_FILENAME.def c:\sqllib\lib
\db2api.lib

set SQLROUTINE_FILENAME=P1252150

icc /Ge- /Gm+ /Wcnddcleffgeniniobsordparprorearettruuse -Ic:
\sqllib\include P1252150.c /B "/A:1 /E:2 /NOD: /NOE /NOL /PACKD
/NOFREE /NOI /ST:64000" P1252150.def c:\sqllib\lib\db2api.lib

*** C:\SQLLIB\function\routine\sqlproc\TEST\DB2_WWW\tmp

\P1252150.def ***
LIBRARY P1252150
EXPORTS pgsjmp

[C:\SQLLIB\function\routine\sqlproc\TEST\DB2_WWW\tmp]
[C:\SQLLIB\function\routine\sqlproc\TEST\DB2_WWW\tmp]

[C:\SQLLIB\function\routine\sqlproc\TEST\DB2_WWW\tmp]set

SQLROUTINE_FILENAME=P1252150

[C:\SQLLIB\function\routine\sqlproc\TEST\DB2_WWW\tmp]

[C:\SQLLIB\function\routine\sqlproc\TEST\DB2_WWW\tmp]icc /Ge-
/Gm+ /Wcnddcleffgeniniobsordparprorearettruuse -Ic:\sqllib
\include P1252150.c /B "/A:1 /E:2 /NOD: /NOE /NOL /PACKD
/NOFREE /NOI /ST:64000" P1252150.def c:\sqllib\lib\db2api.lib
c:\sqllib\include\db2psmd.h(182:32) : informational EDC0450:
Obsolete non-prototype-style function declaration.
P1252150.sqc(210:2) : informational EDC0469: The static
function 'ROLLBACKAPPSAVEPOINT' is declared or defined but
never referenced.
P1252150.sqc(210:2) : informational EDC0490: The static
variable 'SQLP_IINSERTTEST_SQLCODE_2' is set but never
referenced.
P1252150.sqc(210:2) : informational EDC0490: The static
variable 'INSERTTEST_SQLCODE_2' is set but never referenced.
P1252150.sqc(210:2) : informational EDC0490: The static
variable 'SQLP_IINSERTTEST_SQLSTATE_2' is set but never
referenced.
P1252150.sqc(210:2) : informational EDC0490: The static
variable 'SQLP_IINSERTTEST_return_value_0' is set but never
referenced.
P1252150.sqc(210:2) : informational EDC0490: The static
variable 'INSERTTEST_return_value_0' is set but never
referenced.
P1252150.sqc(210:2) : informational EDC0409: The static
variable 'result' is defined but never referenced.

IBM(R) Linker for OS/2(R), Version 01.08.r1a_CTC308c
(C) Copyright IBM Corporation 1988, 1997.
(C) Copyright Microsoft Corp. 1988, 1989.
- Licensed Material - Program-Property of IBM - All Rights
Reserved.

Object Modules [.obj]: /A:1 /E:2 /NOD: /NOE /NOL /PACKD

/NOFREE /NOI /ST:64000 +

Object Modules [.obj]: "P1252150.obj"
Run File [P1252150.*]: "P1252150.dll"
Map File [P1252150.map]: ""
Libraries [.lib]: "c:\sqllib\lib\db2api.lib"
Definitions File [nul.def]: "P1252150.def"

IBM VisualAge C++ for OS/2, Version 3

(C) Copyright IBM Corp. 1991, 1995.

- Licensed Materials - Program Property of IBM - All Rights
Reserved.

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


Pascal-Marc Gelinas

unread,
Feb 5, 2002, 6:35:22 AM2/5/02
to
As i said in my previous post i think it's the exported symbol that
looks wrong. (i may be wrong)
the prep is fine
the compile is fine
the link is fine (no errors)

Now, the call does nothing good.

Here is the content of a P4130510.def for a v4 ibm compiler on windows
pgsjmp=_pgsjmp@16

if i had a dll viewer, i could probably see this as an exported symbol in
the dll.
In your case, the .def looks null (empty).
The call statement is probably not able to find the correct exported symbol.
(entry point)

I know it's derived from some info in the .map file. (the pgsjmp@16)
It should be automatic but...
I don't know how the v3x compiler works with the .def files.

Can you keep the .def to browse it after the compile/link process
or copy it somewhere during the compile/link process.

If it does not contain something like _xxx@somenumber
then it _may_ be your problem.

re:
LIBRARY P1252150
EXPORTS pgsjmp
----

Did you try to compile and link with the parameters provided in the doc and
samples directory?

PM

0 new messages