SQL1109N - Cannot Load Stored Procedure
Dave S.
procedure from anything outside of the DB2 Universal environment:
SQL1109N - The specified DLL "gstinfo" could not be loaded
If I make the call within the CLP, everything works perfectly and the
SQL stored procedure returns the correct results. I'm sure this is a
compiler, link or environment setting but I'm pretty sure everything
is set up correctly. I've made sure the proper COMPILER ENVIRONMENT
and COMPILER OPTIONS are set up correctly for my compiler (Visual
C++).
The strange part is after the compile, the DLL that gets created is a
number (e.g. P525154.dll) and doesn't get created as the actual stored
procedure name (e.g. GSTINFO.DLL). I've renamed the created dll to
GSTINFO.DLL and placed it in the SQLLIB/BIN directory to see what
would happen, but then I get an error message that states "SQL1006N -
The specified DLL "GSTINFO" was loaded, but the function 'gstinfo'
could not be executed"
I'm pretty confident this is something I don't have set up correctly
in my environment and would appreciate any help someone could give me.
I was hoping I'd have a little better luck setting the whole DB2U
environment up for stored procedures.
Thanks for your help.
Dave
Dirk Wollscheid
what's names did you use in the CREATE PROCEDURE statement (in the external
name part)? There's two things that have to match the DLL name nad the SP
function name. It usually doesn't help to rename the DLL if the function
name is different.
Why don't you drop the SP and recreate it with the names you used in the
source code/compile-link statement?
Regards, Dirk
BTW: On windows there's a nice tool called "depends" that shows you the
symbols that are extported in a DLL (I think it comes with the resource
kit). You can use it to find out if you have the right function.
"Dave S." <schw...@selectinc.com> wrote in message
news:2fb59176.01061...@posting.google.com...
Dave S.
Here is the first line of my procedure:
CREATE PROCEDURE GSTINFO
When I compile the stored procedure, it creates a DLL called:
P5251540.DLL
Additionally, when I look at the properties of the stored proc, the
package name is ADMINISTRATOR.P5251540 Is this correct? I've dropped
the procedure and recompiled about 20 times now using different
compile flags, etc but always get the same results.
I used Depends to look at the DLL and the only function referenced was
"pgsjmp". This doesn't seem right to me, but how could the Stored Proc
work fine through the CLP...is there some type of different
implementation through the CLP?
Thanks so much for your time.
Dave
"Dirk Wollscheid" <wol...@us.ibm.com> wrote in message news:<9g5fqt$eme$1...@stlnews.stl.ibm.com>...
pm3...@attglobal.net
How did you code your call? How did you export your symbol (.def)?
You should read the CALL command doc and verify every step of the
sp name resolution. You should be able to nail it that way.
PM
Serge Rielau
Some background:
Since Fixpack 2 both DLL and package name ar ealways generated. They do
not coincide with teh specific name of the procedure. The reason being
that soem OS don't allow to remove DLLs when they are loaded.
We got lots of trouble with customers not being able to drop and recreate
procedures in V7 GA without playing with the KEEPDARI flag.
So the Pxxxxxxx.is correct.
When you rename the DLL and place it yourself you are trying to call an
"uncatalogued" procedure. There are certian rules that have to be followed
(which I don't know). But teh SQL Procedure DLL doesn't follow these
rules, which is why renaming doesn't work.
CALL in CLP is indeed a special beast. It's very close to the "spcall"
program you find in samples (cli I think). In fact CLP just runs a CLI
application to execute the CALL. This is different from what the API-calls
the precompiler generates when compiling an application.
So it is possible that something works in CLP, but not embedded or vice
versa.
Can you tell us what exactly you get when you call from somehwre lse than
CLP?
Stored procedures get resolved based on the schema name, the name (not the
specific!), and the number of parameters.
Cheers
Serge
Dave S.
reason I tried renaming the DLL is because of the error message(see
below)....I thought I'd try it and see what happened, but your
description makes good sense on why it should be named using the
Pxxxxxxx standard.
Here is how I'm trying to call the stored procedure:
CALL GSTINFO(27,'TRUE')
Here is the error I get:
Error Opening your Database.
S1000: [IBM][CLI DRIVER][DB2/NT]SQL1109N - The specified DLL "gstinfo"
could not be loaded. NUMBER:-1109
If the problem persists please contact technical support.
This is the same error I would get if I called a stored procedure that
didn't exist in the database so it obviously isn't finding the file.
Now, here is the strange thing, if I copy the Pxxxxx.dll to SQLLIB\BIN
and rename it to gstinfo.dll (I know, this is a no no), I get a
different error:
S1000: [IBM][CLI DRIVER][DB2/NT SQL1006N - The specified DLL "GSTINFO"
was loaded, but the function 'gstinfo'could not be executed"
In this last case, my program found the DLL, but couldn't execute it.
I'm missing something somewhere but having difficulties figuring out
what.
Any thoughts you'd have would be greatly appreciated and thanks for
your time.
Dave
Serge Rielau <sri...@ca.ibm.com> wrote in message news:<3B26DBCA...@ca.ibm.com>...
Dirk Wollscheid
do a
select PROCSCHEMA, procname, implementation, parm_count from
syscat.procedures where procname='GSTINFO'
on your DB and verify that there's something like Pxxxxx!pgsjmp in the
implementation column and the parm_count is two.
Just to elimate one more possible problem call the SP in your app with
<procschema>.GSTINFO (where for procschema you use the value from that query
above. As serge said SPs are resolved by schema and the default schema is
the user that connects to the DB.
The other thing you might check is that you connect to the db you created
the SP in.
Regards, Dirk
"Dave S." <schw...@selectinc.com> wrote in message
news:2fb59176.01061...@posting.google.com...
Dave S.
I ran the SQL you've suggested and there is indeed a record for my
procedure (GSTINFO) in the table. The parm_count is two and the
pxxxxxxx!pgsjmp is in the implementation column. It appears everything
is referenced correctly.
Could this be a problem with the linking/compiling. I've set
everything up as described in the DB2U documentation.
I've also made sure I connect to the database via the DB2 ODBC driver
using the same user id I've created the database with (administrator).
I've also tried calling the procedure using the <procschema>.GSTINFO
nomenclature but to no avail.
Thanks again for your time.
Dave
"Dirk Wollscheid" <wol...@us.ibm.com> wrote in message news:<9g88tl$12tg$1...@stlnews.stl.ibm.com>...
Dirk Wollscheid
>
> I ran the SQL you've suggested and there is indeed a record for my
> procedure (GSTINFO) in the table. The parm_count is two and the
> pxxxxxxx!pgsjmp is in the implementation column. It appears everything
> is referenced correctly.
>
> Could this be a problem with the linking/compiling. I've set
> everything up as described in the DB2U documentation.
No, if you can call the SP from the command line, then you didn't you
compiled it right.
> I've also made sure I connect to the database via the DB2 ODBC driver
> using the same user id I've created the database with (administrator).
> I've also tried calling the procedure using the <procschema>.GSTINFO
> nomenclature but to no avail.
What's the value of the procschema column for your SP? Maybe that's some
weird userid length bug.
Do you also use the same uppercase names in your app (I'm don't remember if
this is necessary, I'm just making guesses)
Do you have the Stored Procedure Builder on your system (It's included in
certain DB2 packages. Look under Start->DB2->Stored Procedure Builder)? With
that you can try to call the SP.
Dirk Wollscheid
>
> "Dave S." <schw...@selectinc.com> wrote in message
> news:2fb59176.01061...@posting.google.com...
> > Dirk,
> >
> > I ran the SQL you've suggested and there is indeed a record for my
> > procedure (GSTINFO) in the table. The parm_count is two and the
> > pxxxxxxx!pgsjmp is in the implementation column. It appears everything
> > is referenced correctly.
> >
> > Could this be a problem with the linking/compiling. I've set
> > everything up as described in the DB2U documentation.
>
> No, if you can call the SP from the command line, then you didn't you
> compiled it right.
I meant: if you can call the SP from the command line, then you you compiled
Dave S.
I'm using for login, ODBC access, etc. I can load, compile and run the
procedure from the stored procedure builder perfectly. I've tried
using Upper, Lower case, etc and it doesn't seem to matter.
"Dirk Wollscheid" <wol...@us.ibm.com> wrote in message news:<9g942k$ul2$1...@stlnews.stl.ibm.com>...
Dirk Wollscheid
that's very weird. SPB uses JDBC to call the SP. You should be able to do
the same from your client (it doesn't depend so much on the programming
language).
I'm out of ideas. I guess you have to call service about this.
Sorry, Dirk
Dave S.
I'm using VB with ADO via IBM's ODBC driver to access the database. I
can access any table just fine, just not the stored procedures. Should
JDBC somehow play into this?
Thanks again for all your help.
Dave
"Dirk Wollscheid" <wol...@us.ibm.com> wrote in message news:<9gaq5j$t7k$1...@stlnews.stl.ibm.com>...
Dirk Wollscheid
output and see if that matches what you do from the command line.