Calling a DB/2 v8.2 stored procedure from perl

[mdaetwyler]

Hi all
I am trying to call a DB/2 v8.2 stored procedure from Perl DBI and am
getting an error message telling me, that the routine could not be
found in the library path.

[IBM][CLI Driver][DB2/NT] SQL0444N Routine "*_deposit" (specific name
"SQL050519190148810") is implemented with code in library or path
"\finban.cac_deposit", function "finban.cac_deposit" which cannot be
accessed. Reason code: "4". SQLSTATE=42724

Since 8.2 stores stored procedures internaly as byte code, the libaries
can indeed not be found in sqllib/FUNCTION/ROUTINE.

I tried to grant EXECUTE rights to all users on the packages found in
syscat.packages but this changed nothing.

Im am working with v8.2. on Windows, ActivePerl 5.61(638) and DBD-DB2
0.76 and DBI 1.34.

Cheers
Marc

[Serge Rielau]

md********@gmail.com wrote:

Hi all
I am trying to call a DB/2 v8.2 stored procedure from Perl DBI and am
getting an error message telling me, that the routine could not be
found in the library path.

[IBM][CLI Driver][DB2/NT] SQL0444N Routine "*_deposit" (specific name
"SQL050519190148810") is implemented with code in library or path
"\finban.cac_deposit", function "finban.cac_deposit" which cannot be
accessed. Reason code: "4". SQLSTATE=42724

Since 8.2 stores stored procedures internaly as byte code, the libaries
can indeed not be found in sqllib/FUNCTION/ROUTINE.

I tried to grant EXECUTE rights to all users on the packages found in
syscat.packages but this changed nothing.

Im am working with v8.2. on Windows, ActivePerl 5.61(638) and DBD-DB2
0.76 and DBI 1.34.

The problem you have is independent of PERL.
Now in V8.2 SQL Procedures which are CREATEd in V8.2 ar eall internal.
But external (java, C, ..) procedures or SQL Procedures created prior to
V8.2 still have DLLs.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Philip Nelson]

Serge Rielau wrote:

md********@gmail.com wrote:

Hi all
I am trying to call a DB/2 v8.2 stored procedure from Perl DBI and am
getting an error message telling me, that the routine could not be
found in the library path.

[IBM][CLI Driver][DB2/NT] SQL0444N Routine "*_deposit" (specific name
"SQL050519190148810") is implemented with code in library or path
"\finban.cac_deposit", function "finban.cac_deposit" which cannot be
accessed. Reason code: "4". SQLSTATE=42724

Since 8.2 stores stored procedures internaly as byte code, the libaries
can indeed not be found in sqllib/FUNCTION/ROUTINE.

I tried to grant EXECUTE rights to all users on the packages found in
syscat.packages but this changed nothing.

Im am working with v8.2. on Windows, ActivePerl 5.61(638) and DBD-DB2
0.76 and DBI 1.34.

The problem you have is independent of PERL.
Now in V8.2 SQL Procedures which are CREATEd in V8.2 ar eall internal.
But external (java, C, ..) procedures or SQL Procedures created prior to
V8.2 still have DLLs.

Cheers
Serge

To add to what Serge says, we got exactly the same problem with SPs being
called from VB after we migrated from V8.1 to V8.2 : what we found was that
at execution time DB2 was trying to convert the SP from the old, external,
format to the new, internal, format. The user running the SP did not have
authority to delete the old DLLs from the directory you noted above.

We eventually went through an exercise of deleting and recreating all the
SPs (we didn't have that many) to get round the problem, rather than giving
world update access to the directory in question.

HTH

Phil

[Serge Rielau]

Philip Nelson wrote:

Serge Rielau wrote:

md********@gmail.com wrote:

Hi all
I am trying to call a DB/2 v8.2 stored procedure from Perl DBI and am
getting an error message telling me, that the routine could not be
found in the library path.

[IBM][CLI Driver][DB2/NT] SQL0444N Routine "*_deposit" (specific name
"SQL050519190148810") is implemented with code in library or path
"\finban.cac_deposit", function "finban.cac_deposit" which cannot be
accessed. Reason code: "4". SQLSTATE=42724

Since 8.2 stores stored procedures internaly as byte code, the libaries
can indeed not be found in sqllib/FUNCTION/ROUTINE.

I tried to grant EXECUTE rights to all users on the packages found in
syscat.packages but this changed nothing.

Im am working with v8.2. on Windows, ActivePerl 5.61(638) and DBD-DB2
0.76 and DBI 1.34.
The problem you have is independent of PERL.
Now in V8.2 SQL Procedures which are CREATEd in V8.2 ar eall internal.
But external (java, C, ..) procedures or SQL Procedures created prior to
V8.2 still have DLLs.

Cheers
Serge

To add to what Serge says, we got exactly the same problem with SPs being
called from VB after we migrated from V8.1 to V8.2 : what we found was that
at execution time DB2 was trying to convert the SP from the old, external,
format to the new, internal, format. The user running the SP did not have
authority to delete the old DLLs from the directory you noted above.

Actually DB2 V8.2 for LUW will not convert SQL Procedures on its own.
I'm not sure what you observed, but it wasn't auto-conversion.
I do recommend to drop and recreate the procs in one shot though to
avoid problems many moons later with everyone scratching their head not
remembering that a conversion did take place eventually.
We eventually went through an exercise of deleting and recreating all the
SPs (we didn't have that many) to get round the problem, rather than giving
world update access to the directory in question.

HTH

Phil

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab